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

[Daniel Ceregatti]

This is good news.

The post you linked to seems to cover the "conversion from miles to
degrees" in the query, but I still need to get my index built using
degrees, where all I have are lat/lon. I think I saw something on the
list regarding this, so I'll search further.

Regarding Oracle, I'm not entirely sure how it does it either. I
followed a simple "howto" like set of instructions that created the
index based on lat/lon and was subsequently queryable using the sdo_*
functions native to oracle, one of which was the sdo_within_distance ()
mentioned earlier.

Thanks for all your help, Paul and Chris.

Daniel Ceregatti

Paul Ramsey wrote:

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