[postgis-users] Newbie: finding close points with long/lat

[Kris R]

Hello all,

I am trying to find points that are close to a given long/lat coordinate. I 
have a DB of points of interest, all with long/lat coordinates (one column 
with longs and one with lats).

There is much information about this on the list, but most of the distance 
queries seem to be focused on cartesian coordinates, and not long/lat.

I figured out how to get the distance between two points (although I am not 
sure of in what units) from another post on the list 
(http://postgis.refractions.net/pipermail/postgis-users/2004-August/005472.html), 
but I cannot figure out how to use the && operator and take advantage of the 
index I created
(CREATE INDEX places_index ON places USING gist (the_geom);). A similar 
query without using an index takes several minutes (ORDER BY distance...).

The query I have is based on what I've found on the list and in the PostGIS 
introduction document, but it seems to crash Postgres after about 10 seconds 
(postgres.exe crashes on WinXP). The point (-122.1 37.63) is some place in 
California, and I just want to find 3 cities nearby and the distance to the 
point.The BOX3D coordinates were chosen just to form a box around the point 
- my database does have plenty of other points inside the box.

SELECT name, distance_spheroid('POINT(-122.1 37.63)', the_geom, 
'SPHEROID["GRS_1980",6378137,298.257222101]')
FROM places
WHERE the_geom && GeometryFromText('BOX3D(-122.20798 37.50,-121.99 
37.70)',8307)
LIMIT 3;

I am using the DCMM PostGIS installer 
(http://dcmms.sourceforge.net/index.php) and Postgres installer from 
pgFoundry (http://pgfoundry.org/projects/pginstaller), both beta editions.

I would be very grateful if anyone would offer some hint as to what I am 
doing wrong.

If it would be easier to reproject all my data to some cartesian projection 
that would be fine, although I am not quite sure how to go about doing that.

Thanks in advance,
Kris