[postgis-users] Bounding Box Query

[Tyler Mitchell]

Are you getting email from the list okay?  A couple of us had responded a 
week or so ago...

postgis-users-bounces at postgis.refractions.net wrote on 03/29/2004 05:51:08 
AM:

> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users