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

[Paul Ramsey]

It is entirely possible, you just have to be willing to make a few 
compromises.

While PostGIS does not have support for geodetic coordinates in the 
index (they are treated as if they are planar) it does have some 
geodetic coordinate handling functions for things like distance.

So what you do it create a hybrid query, where you make a rough query 
for the points you want in lat/lon, then refine the query using the 
geodetic functions.

To do that, you need to estimate how big your query rectangle is in 
degrees. So a conversion from miles (or meters) to degrees is the first 
step. Then a bounding box in degrees is constructed and used to query 
the spatial index (which is also in degrees). This result set is then 
passed through a geodetic distance filter to get the final result.

There is an email thread on exactly this problem referenced here:

http://postgis.refractions.net/pipermail/postgis-users/2002-December/001905.html

I do not know how oracle "supports" geodetics in their indexing, but it 
is possible that they just transparently wrap up the steps I have 
described (along with a few others to handle cases where bounding boxes 
wrap over 180 and 90). Not an elegant solution, but indistinguishable 
from an elegant solution for 99.99% of cases.

Paul

Daniel Ceregatti wrote:

> 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
>>  
>>


-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_