<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
Hi,<br>
<br>
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:<br>
<br>
CREATE TABLE users (<br>
username varchar (50),<br>
latitude numeric,<br>
longitude numeric,<br>
zip numeric<br>
);<br>
COPY users (username, latitude, longitude, zip) FROM stdin;<br>
babe5000 41.2642 -74.3694 10990<br>
usuck_247 32.8068 -117.1685 92111<br>
NIKMM 33.8563 -116.5712 92262<br>
-- snip...<br>
\.<br>
<br>
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:<br>
<br>
SELECT AddGeometryColumn('maps', 'users', 'user_geom', 4269, 'POINT', 2
);<br>
<br>
update users set user_geom = GeometryFromText('POINT(' || latitude || '
' || longitude|| ')',4269);<br>
<br>
create index user_geom_indx on users using gist (user_geom
GIST_GEOMETRY_OPS);<br>
<br>
VACUUM ANALYZE users;<br>
<br>
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:<br>
<br>
select count (1) from users where distance (user_geom, GeometryFromText
('POINT (34.0998 -118.4128)', 4269)) < 100;<br>
<br>
(That point in the GeometryFromText argument happens to be lat/lon for
Beverly Hills, CA...you
know, 90210)<br>
<br>
The problem here is that "100" seems to mean "100 units". I have yet to
determine exactly what this unit is.<br>
<br>
I obtained the SRID for the queries above from this post: <br>
<br>
<a class="moz-txt-link-freetext"
href="http://postgis.refractions.net/pipermail/postgis-users/2003-July/002791.html">http://postgis.refractions.net/pipermail/postgis-users/2003-July/002791.html</a><br>
<br>
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<br>
<br>
"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"<br>
<br>
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?<br>
<br>
Thanks,<br>
<br>
Daniel Ceregatti<br>
</body>
</html>