<!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">
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).<br>
<br>
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.".<br>
<br>
So far, everything I've read about postgis suggests this functionality
is not possible.<br>
<br>
Please pardon my ignorance if I am incorrect (which I hope I am)<br>
<br>
Daniel Ceregatti<br>
<br>
<a class="moz-txt-link-abbreviated" href="mailto:chodgson@refractions.net">chodgson@refractions.net</a> wrote:<br>
<blockquote type="cite" cite="mid1063382454.3f61edb6e7e83@hydra">
<pre wrap=""><a class="moz-txt-link-freetext" href="http://postgis.refractions.net/docs/x526.html#AEN530">http://postgis.refractions.net/docs/x526.html#AEN530</a>
RTFM :)
Chris
Quoting Daniel Ceregatti <a class="moz-txt-link-rfc2396E" href="mailto:vi@sh.nu"><vi@sh.nu></a>:
</pre>
<blockquote type="cite">
<pre wrap="">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:
<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>
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
</pre>
</blockquote>
<pre wrap=""><!---->
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>