[postgis-users] Bounding Box Query

[iheanyi Okeh]

Tyler, you and David Blasby just solved my problem.
David solved it with this code:

  SELECT str_num || ' ' || name as full_address,
     distance(the_geom,'POINT(2227284.1963667543
  1435389.6730164126)'::geometry) as dist
     FROM address
WHERE the_Geom && expand('POINT(2227284.1963667543
  1435389.6730164126)'::geometry, <max distance>) )
     ORDER BY dist limit 1

which is very similar to yours.
Only difference is the ::geometry David added to the whereclause and it 
works like a dream now.
It is extremely fast and returns accurate results.
David Thank you.

Tyler, you helped me understand precisely what I wanted and needed to 
understand and that is how and where the arguments in the where clause came 
from.

>You can do this by adding to your WHERE clause:
>AND a.the_geom && p.the_geom

So the trick is that we want to expand the bounding box so that it
>overlaps more features, but not ALL of them!  Or else you're back to your
>performance problem again.  You can use the EXPAND() function to do this -
>but you need to decide first on the kind of distance tolerance you want it
>to check around the features.

You explained everything beautifully.
Thank you Tyler.

Thanks to both of you very much!

Just so both  of you know, I am embedding the query in ny asp code and so it 
looks like this in my code right now.

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=pg gis"

X = request.querystring("LON")

Y = request.querystring("LAT")
SQL = "SELECT str_num || ' ' || name as location," & _
       "distance(the_geom,'POINT(" & X & " " & Y & ")'::geometry) as dist " 
& _
       "FROM address " & _
	   "WHERE the_Geom && expand('POINT(" & X & " " & Y & ")'::geometry,1000) " 
& _
       "ORDER BY dist limit 1 "
  Set cur = conn.Execute( SQL )

_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee® 
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963