Ok, I have been able to get the code to work.
Here is the working version:
SELECT str_num||' ' ||name as full_address,
distance(the_geom,'POINT(2227284.1963667543 1435389.6730164126)'::geometry) as dist
FROM address order by dist limit 1
This works fine as far as retrieving the closest address to point.
There is however, a slight problem.
Retrieval is very slow due to the table being very large
This is where bounding boxes come in and I have attempted to use it but it is blowing up real big.
Here is the ugly code with where predicate added.
Does anyone have a clue as to what needs to change here for this to work.
I know this forum caters to OpenACS users but I have been able to resolve the first problem as a result of the pointers I received here.
Hopefully, I can get more pointers.
SELECT str_num||' ' ||name as full_address,
distance(the_geom,'POINT(2227284.1963667543 1435389.6730164126)'::geometry) as dist
FROM address order by dist limit 1
WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
AND distance( the_geom( 'POINT(2227284.1963667543 1435389.6730164126)', -1 ) ) < 100
First I don't know how this part of the code:
'BOX3D(90900 190900, 100100 200100)'::box3d
is derived. I mean the values 90900 190900, 100100 200100.
Second, obviously the syntax is wrond or else it would have worked.
Did I mention I am a complete newbie to openGIS/postGIS/postgreSQL? but I am learning real fast.
