[postgis-users] Bounding Box Query

iheanyi Okeh simflex at hotmail.com
Mon Mar 29 05:51:08 PST 2004


I had posted this question here before but didn't get any response.
I was encouraged to try it again.

I have a query that retrieves an address closest to a point given an x/y 
coordinate pair.

This is that query:

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

The above query works as far as returning the closest address to a point 
based on x/y coordinate pair.

Hower, performance is really bad as it takes quite sometime for this query 
to retrieve a record.

I have a about bounding boxes or overlap operator (&&) as a means of 
improving performance.

I don't quite understand it how works.
For instance, given the below query (which doesn't work by the way),

SELECT str_num || ' ' || name as full_address,
   distance(the_geom,'POINT(2227284.1963667543
1435389.6730164126)'::geometry) as dist
   FROM address
   WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
   AND distance( the_geom( 'POINT(2227284.1963667543 1435389.6730164126)', 
-1 ) ) < 100
ORDER BY dist limit 1

My questions are:
1, how are the arguments in bo3d shown below generated? I just filled in 
those numbers without knowing where they come from or how they are 
generated.

   WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
   AND distance( the_geom( 'POINT(2227284.1963667543 1435389.6730164126)', 
-1 ) ) < 100

2, how can I apply the information on question 1 to with the query above 
(the one indicated as a working code)?

All research I have done so far have not helped me.
Your assistance is greatly appreciated.

_________________________________________________________________
All the action. All the drama. Get NCAA hoops coverage at MSN Sports by 
ESPN. http://msn.espn.go.com/index.html?partnersite=espn




More information about the postgis-users mailing list