Finding points within 50 miles

Am Montag, 27. Juni 2005 01:40 schrieb CSN:

If I have a table of items with latitude and longitude
coordinates, is it possible to find all other items
that are within, say, 50 miles of an item, using the
geometric functions
(http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)?
If so, how?

I did it without some special features and datatypes some time ago. feel free
to modify and use for your own. It should give you an idea how to do it.

SELECT
c1.zip,
c2.zip,
6378.388 *
acos(
sin(radians(c1.latitude)) * sin(radians(c2.latitude))
+ cos(radians(c1.latitude)) * cos(radians(c2.latitude))
* cos(radians(c1.longitude - c2.longitude))
) AS distance
FROM
coordinates AS c1
CROSS JOIN coordinates AS c2

I had some problems with the calculation inside acos() sometimes being greater
than 1, which should not occur. Please use a
CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END
if you have the same problem.

kind regards,
janning

in the where clause use something like (requires the earthdistance contrib to
be installed):

geo_distance(point([origin longitude],[origin latitude]),point([target
longitude column],[target latitude column])))::int <= 50

On Sunday 26 June 2005 04:40 pm, CSN wrote:

If I have a table of items with latitude and longitude
coordinates, is it possible to find all other items
that are within, say, 50 miles of an item, using the
geometric functions
(http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)?
If so, how?

Thanks,
CSN

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
UC

--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417

How big is your data ? There are rather sophisticated and
very effective methods in astronomy. For example,
http://www.sai.msu.su/~megera/oddmuse/index.cgi/SkyPixelization,
http://www.sai.msu.su/~megera/oddmuse/index.cgi/pg_sphere

Oleg
On Mon, 27 Jun 2005, Janning Vygen wrote:

Am Montag, 27. Juni 2005 01:40 schrieb CSN:

If I have a table of items with latitude and longitude
coordinates, is it possible to find all other items
that are within, say, 50 miles of an item, using the
geometric functions
(http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)?
If so, how?

I did it without some special features and datatypes some time ago. feel free
to modify and use for your own. It should give you an idea how to do it.

SELECT
c1.zip,
c2.zip,
6378.388 *
acos(
sin(radians(c1.latitude)) * sin(radians(c2.latitude))
+ cos(radians(c1.latitude)) * cos(radians(c2.latitude))
* cos(radians(c1.longitude - c2.longitude))
) AS distance
FROM
coordinates AS c1
CROSS JOIN coordinates AS c2

I had some problems with the calculation inside acos() sometimes being greater
than 1, which should not occur. Please use a
CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END
if you have the same problem.

kind regards,
janning

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Uwe C. Schroeder wrote:

in the where clause use something like (requires the earthdistance contrib to
be installed):

geo_distance(point([origin longitude],[origin latitude]),point([target
longitude column],[target latitude column])))::int <= 50

I don't suppose geo_distance really returns a number in miles, does it?

On Jun 27, 2005, at 3:47 AM, Janning Vygen wrote:

I had some problems with the calculation inside acos() sometimes
being greater
than 1, which should not occur. Please use a
CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END
if you have the same problem.

We've seen this as well with the distance radius calculation. It
doesn't happen in 8.x but did happen 7.4, and then was easily worked
around by reducing the precision of the arguments. Ie, we would use
73.13 rather than 73.1343593421 as pulled from the database for the
lat/lon values of the center point.

In any case, I urge you to derive the formulas yourself from basic
research so you *know* you're getting what you think you're getting.

Vivek Khera, Ph.D.
+1-301-869-4449 x806

On Jun 26, 2005, at 7:40 PM, CSN wrote:

If I have a table of items with latitude and longitude
coordinates, is it possible to find all other items
that are within, say, 50 miles of an item, using the
geometric functions
(http://www.postgresql.org/docs/8.0/interactive/functions-
geometry.html)?
If so, how?

We optimize this query by first finding the bounding square, then
comparing the lat/lon of the other objects (in our case zip codes)
for radius. This has the advantage of deleting a *lot* of possible
values before passing them to the heavy math formulas.

so ours boils down to something along these lines ($zip_radius is the
miles we're looking for)

the distance computation:

(acos((sin($input_lat/57.2958) * sin(zip_latitude/57.2958)) + (cos
($input_lat/57.2958) * cos(zip_latitude/57.2958) * cos(zip_longitude/
57.2958 - $input_long/57.2958))) * 3963) <= $zip_radius

and the bounding box is done like this:

$lat_range = $zip_radius / ((6076. / 5280.) * 60);
$long_range = $zip_radius / (((cos($input_lat * 3.141592653589 / 180)
* 6076.) / 5280.) * 60);

so just do a +/- of the center point lat/lon with the above values
and you have your square bounding box inside which you run your
distance computation.

Putting it together is left as an exercise for the reader (hint: just
AND your pieces together...)

Vivek Khera, Ph.D.
+1-301-869-4449 x806

On Sun, Jun 26, 2005 at 16:40:03 -0700,
CSN <cool_screen_name90001@yahoo.com> wrote:

If I have a table of items with latitude and longitude
coordinates, is it possible to find all other items
that are within, say, 50 miles of an item, using the
geometric functions
(http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)?
If so, how?

You could take a look at using the parts of the earthdistance contrib
module based on the cube data type. Gist indexes are supposed to make
this kind of query fast.

Actually it does.
I'm using a bounding box too. I have a stored procedure to get me what I need - here's the relevant part of it.
Explanation: zc is the record holding the point of origin. I just added the maxdistance definition for this, because in my function its a parameter.

SELECT INTO zc z.* FROM v_profile p JOIN zipcodes z ON z.zipcode=p.zipcode WHERE p.uid=uid;
IF NOT FOUND THEN
RAISE EXCEPTION \'Cant find member %\',uid;
END IF;
maxdistance:=50;
la_min:=(zc.latn - (maxdistance::float8/70.0));
la_max:=(zc.latn + (maxdistance::float8/70.0));
lo_min:=(zc.longw - (maxdistance::float8/70.0));
lo_max:=(zc.longw + (maxdistance::float8/70.0));

stmt:=''SELECT n.username, n.uid, n.areacode, n.zipcode
geo_distance(point('' || zc.longw ||'',''|| zc.latn ||''),point(z.longw, z.latn))::int as distance,
n.image_thumbnail,n.city, n.state_code
FROM v_new_members n JOIN zipcodes z ON z.zipcode=n.zipcode
AND (z.latn BETWEEN '' || la_min || '' AND '' || la_max || '')
AND (z.longw BETWEEN '' || lo_min || '' AND '' || lo_max || '') AND
geo_distance(point(''|| zc.longw ||'',''||zc.latn||''),point(z.longw, z.latn))::int <= ''||maxdistance ;

hope that helps

UC

On Monday 27 June 2005 02:08 am, you wrote:

Uwe C. Schroeder wrote:

in the where clause use something like (requires the earthdistance contrib
to be installed):

geo_distance(point([origin longitude],[origin latitude]),point([target
longitude column],[target latitude column])))::int <= 50

I don't suppose geo_distance really returns a number in miles, does it?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
UC

--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417

I'm interested in doing a project for calculating distances similar to
this. Anyone have suggestions on how/where this type of data can be
obtained? Is it freely available anywhere?

On Jun 27, 2005, at 4:36 PM, John Browne wrote:

I'm interested in doing a project for calculating distances similar to
this. Anyone have suggestions on how/where this type of data can be
obtained? Is it freely available anywhere?

Google is your friend. There are places that sell very well kept
zipcode databases for under $50.

Vivek Khera, Ph.D.
+1-301-869-4449 x806

On Mon, Jun 27, 2005 at 17:09:37 -0400,
Vivek Khera <vivek@khera.org> wrote:

On Jun 27, 2005, at 4:36 PM, John Browne wrote:

I'm interested in doing a project for calculating distances similar to
this. Anyone have suggestions on how/where this type of data can be
obtained? Is it freely available anywhere?

Google is your friend. There are places that sell very well kept
zipcode databases for under $50.

The US government gives it away for free. Look for "tiger".

On Jun 27, 2005, at 8:42 PM, Bruno Wolff III wrote:

Google is your friend. There are places that sell very well kept
zipcode databases for under $50.

The US government gives it away for free. Look for "tiger".

That is stale data.

Vivek Khera, Ph.D.
+1-301-869-4449 x806