[postgis-users] creating a spatial index for finding objects withing a certaindistance in miles based on lat/lon

[Daniel Ceregatti]

Hi,

I've been searching this list for a document that covers the creation of 
a spatial index that would allow me to query on distance in miles based 
on lat/lon. So far I've come up empty. I want functionality that would 
allow a person, via a web interface to a database, who may be located 
anywhere in the world, to look for people in the database that are 
within a certain distance in miles from the person searching. Each 
person in the database has a lat/lon associated with their profile. I've 
tried to read the documentation to attempt to extrapolate this 
information but some things are still rather vague to me. Perhaps I'm 
simply not searching for the correct terminology. Anyhow, I've attempted 
the following using a postgis enabled postgres server:

CREATE TABLE users (
  username varchar (50),
  latitude numeric,
  longitude numeric,
  zip numeric
);
COPY users (username, latitude, longitude, zip) FROM stdin;
babe5000        41.2642 -74.3694        10990
usuck_247       32.8068 -117.1685       92111
NIKMM   33.8563 -116.5712       92262
-- snip...
\.

This imported appoximately 220,000 rows. I used data from an existing 
oracle system. This system uses oracle spatial. My goal is to replace 
oracle with postgres, as a proof-of-concept. I continued:

SELECT AddGeometryColumn('maps', 'users', 'user_geom', 4269, 'POINT', 2 );

update users set user_geom = GeometryFromText('POINT(' || latitude || ' 
' || longitude|| ')',4269);

create index user_geom_indx on users using gist (user_geom 
GIST_GEOMETRY_OPS);

VACUUM ANALYZE users;

So far so good. This seems to have created what I need, as far as I can 
tell. Thing is, I then query this new column using the examples provided:

select count (1) from users where distance (user_geom, GeometryFromText 
('POINT (34.0998  -118.4128)', 4269)) < 100;

(That point in the GeometryFromText argument happens to be lat/lon for 
Beverly Hills, CA...you know, 90210)

The problem here is that "100" seems to mean "100 units". I have yet to 
determine exactly what this unit is.

I obtained the SRID for the queries above from this post:

http://postgis.refractions.net/pipermail/postgis-users/2003-July/002791.html

This post also had some useful info, as it referenced the oracle 
equivalent "SRID" of 8307, which I used to create my oracle based 
spatial index. Oracle spatial has a function called sdo_within_distance 
that accepts two geometries and a unit identifier, as in 'MILES', and 
returns true or false if the two are within the distance. Example

"select sdo_within_distance (select user_geom from users where username 
= 'searchee', select user_geom from users where username = 'searcher', 
'distance=100 unit=MILE') from dual"

My questions are: Is there any way to have a query match rows based on a 
defined distance criterion, such as miles, using spatial with postgis? 
If so, what am I doing wrong? How do I specify the distance unit?

Thanks,

Daniel Ceregatti
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20030911/45453a48/attachment.html>