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

[Daniel Ceregatti]

The problem for me here has not been constructing the query to obtain
the correct data in the speediest fashion, but in creating the proper
index based on a "spheroid" set of distances. After some testing, it
seems clear that what I've created using the statements in my original
email is a 2D flat plain geometry: the distance between point (1 1) and
point (2 2) is the same as the distance between point (89 89) and point
(90 90).

I'm no map maker, and I'm not familiar with all the terminology postgis
uses, but I have successfully implemented this in oracle. I guess the
best way to describe "this" is: "A table with people and their
geographic location on the Earth in latitude/longitude that is
searchable by other users, enabling them to find people within a certain
distance from them on the Earth, anywhere on the Earth. A spatial index
enables these queries to work very quickly on a table with as many as
300,000 rows. By very quickly I mean, less than one second on a P4
2.4Ghz with 1G of ram and IDE disks.".

So far, everything I've read about postgis suggests this functionality
is not possible.

Please pardon my ignorance if I am incorrect (which I hope I am)

Daniel Ceregatti

chodgson at refractions.net wrote:

>http://postgis.refractions.net/docs/x526.html#AEN530
>
>RTFM :)
>
>Chris
>
>Quoting Daniel Ceregatti <vi at sh.nu>:
>
>  
>
>>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
>>
>>    
>>
>
>
>
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>  
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20030912/c3a0dbc6/attachment.html>