[postgis-users] Bounding Box Query

[David Blasby]

iheanyi Okeh wrote:


> 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 query calculates the distance from 'POINT(2227284.1963667543
1435389.6730164126)' to *every* geometry in your table.  It then sorts 
all these distances, and returns the first one.


  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

If you know the largest distance you're interested in - you can use the 
index.  If your <max distance> is small-ish, this should work very quick.

Dont forget to put an index on your table and "vacuum analyse;" your 
database.

dave