From jumboc@comcast.net Fri Dec  26 23:42:46 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id DD9D8D1B439
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Dec 2003 23:42:46 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 59277-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Dec 2003 19:41:59 -0400 (AST)
Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.85])
	by svr1.postgresql.org (Postfix) with ESMTP id 6C5AED1B432
	for <pgsql-sql@postgresql.org>; Fri, 26 Dec 2003 19:41:56 -0400 (AST)
Received: from andy2 (c-24-1-39-92.client.comcast.net[24.1.39.92])
	by comcast.net (rwcrmhc12) with SMTP
	id <20031226234157014000q3ove>; Fri, 26 Dec 2003 23:41:57 +0000
From: "Andy Lewis" <jumboc@comcast.net>
To: <pgsql-sql@postgresql.org>
Subject: Radius of a zip code
Date: Fri, 26 Dec 2003 17:42:08 -0600
Message-ID: <000b01c3cc09$e043fd70$0201a8c0@andy2>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000C_01C3CBD7.95A98D70"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2627
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_50_60
X-Spam-Level: 
X-Archive-Number: 200312/190
X-Sequence-Number: 16356

This is a multi-part message in MIME format.

------=_NextPart_000_000C_01C3CBD7.95A98D70
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello all,
 
I was trying to find all zip codes within a given zip code or radius.
 
I have map points and Latitude and Longitude in my zip table.
 
I remember seeing a post or two referencing this but can't see to find
it.
 
I've tried the following with no luck:
 
-- 20 Miles
--select 20 * 360.0 / (7900.0 * atan2(1,1) * 4.0);
select * from zip_code where map_loc @
circle(map_point('dallas','tx','75201'), .290105212724467 ) order by
city
 
Anyone that has this experience, can you validate this for correctness?
 
Thanks in advance,
 
Andy

------=_NextPart_000_000C_01C3CBD7.95A98D70
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>Hello=20
all,</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>I was try=
ing to find=20
all zip codes within a given zip code or radius.</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>I have ma=
p points=20
and Latitude and Longitude in my zip table.</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>I remembe=
r=20
seeing&nbsp;a post or two referencing this but can't see to find=20
it.</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>I've trie=
d the=20
following with no luck:</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>-- 20=20
Miles</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>--select =
20 * 360.0=20
/ (7900.0 * atan2(1,1) * 4.0);<BR>select * from zip_code where map_loc @=20
circle(map_point('dallas','tx','75201'), .290105212724467 ) order by=20
city</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>Anyone th=
at has this=20
experience, can you validate this for correctness?</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial size=3D2>Thanks in=
=20
advance,</FONT></SPAN></DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D456250523-26122003><FONT face=3DArial=20
size=3D2>Andy</FONT></SPAN></DIV></BODY></HTML>

------=_NextPart_000_000C_01C3CBD7.95A98D70--




From tgl@sss.pgh.pa.us Sat Dec  27 00:10:38 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id AEAF6D1B44B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 00:10:38 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 59277-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Dec 2003 20:09:51 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 3F932D1B43C
	for <pgsql-sql@postgresql.org>; Fri, 26 Dec 2003 20:09:48 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBR09o19008409;
	Fri, 26 Dec 2003 19:09:50 -0500 (EST)
To: "Andy Lewis" <jumboc@comcast.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code 
In-reply-to: <000b01c3cc09$e043fd70$0201a8c0@andy2> 
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
Comments: In-reply-to "Andy Lewis" <jumboc@comcast.net>
	message dated "Fri, 26 Dec 2003 17:42:08 -0600"
Date: Fri, 26 Dec 2003 19:09:49 -0500
Message-ID: <8408.1072483789@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/191
X-Sequence-Number: 16357

"Andy Lewis" <jumboc@comcast.net> writes:
> I was trying to find all zip codes within a given zip code or radius.

I think there are canned solutions for this available in PostGIS ---
have you looked at that?

> I've tried the following with no luck:
 
> -- 20 Miles
> --select 20 * 360.0 / (7900.0 * atan2(1,1) * 4.0);
> select * from zip_code where map_loc @
> circle(map_point('dallas','tx','75201'), .290105212724467 ) order by
> city

I'm guessing that the big problem is that you didn't measure longitude
and latitude in identical units in your table, so your "circle" isn't
real circular, and the smaller problem is that "miles" converts to
"degrees of arc" differently at different latitudes.

			regards, tom lane



From mike@fuhr.org Sat Dec  27 02:43:36 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 88A3FD1B4AC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 02:43:36 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70233-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Dec 2003 22:42:51 -0400 (AST)
Received: from quality.qadas.com (quality.qadas.com [204.227.16.4])
	by svr1.postgresql.org (Postfix) with ESMTP id DE283D1B4A6
	for <pgsql-sql@postgresql.org>; Fri, 26 Dec 2003 22:42:46 -0400 (AST)
Received: from quality.qadas.com (localhost [127.0.0.1])
	by quality.qadas.com (8.12.10/8.12.10) with ESMTP id hBR2giB3016570;
	Fri, 26 Dec 2003 19:42:44 -0700 (MST)
Received: (from mfuhr@localhost)
	by quality.qadas.com (8.12.10/8.12.10/Submit) id hBR2gibM016569;
	Fri, 26 Dec 2003 19:42:44 -0700 (MST)
Date: Fri, 26 Dec 2003 19:42:44 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Andy Lewis <jumboc@comcast.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code
Message-ID: <20031226194244.A15916@quality.qadas.com>
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000b01c3cc09$e043fd70$0201a8c0@andy2>;
	from jumboc@comcast.net on Fri, Dec 26, 2003 at 05:42:08PM -0600
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/192
X-Sequence-Number: 16358

On Fri, Dec 26, 2003 at 05:42:08PM -0600, Andy Lewis wrote:
> I was trying to find all zip codes within a given zip code or radius.
>  
> I have map points and Latitude and Longitude in my zip table.
>  
> I remember seeing a post or two referencing this but can't see to find
> it.

The code in contrib/earthdistance in the PostgreSQL source code might
be what you're looking for.  I haven't used it myself, as I had already
written a function I needed for another DBMS and ported it to PostgreSQL.

> I've tried the following with no luck:
>  
> -- 20 Miles
> --select 20 * 360.0 / (7900.0 * atan2(1,1) * 4.0);
> select * from zip_code where map_loc @
> circle(map_point('dallas','tx','75201'), .290105212724467 ) order by
> city

This isn't related to the problem, but is there a reason your map_point
function requires city, state, and zip code?  If you know the zip code
then you shouldn't need the city and state.

> Anyone that has this experience, can you validate this for correctness?

I have several databases with lat/lon coordinates and frequently make
"show me all records within a certain distance of this point" queries.
I wrote a haversine() function that uses the Haversine Formula to
calculate the great circle distance between two points on a sphere
(assuming the earth is a perfect sphere is accurate enough for my uses).
Here's a web site with related info:

http://www.census.gov/cgi-bin/geo/gisfaq?Q5.1

Here's an example of how I use the haversine() function.  I'm not using
PostgreSQL's geometric types -- latitude and longitude are stored in
separate fields.  The function takes two lat/lon coordinates in degrees
and optionally a radius (the default is 3956.0, the approximate radius
of the earth in miles); it returns the distance in whatever units the
radius is in.

SELECT a.zipcode, a.city, a.state,
       haversine(a.latitude, a.longitude, b.latitude, b.longitude) AS dist
FROM zipcode AS a, zipcode AS b
WHERE b.zipcode = 75201
  AND haversine(a.latitude, a.longitude, b.latitude, b.longitude) <= 20
ORDER BY dist;

 zipcode |     city      | state |       dist        
---------+---------------+-------+-------------------
 75201   | Dallas        | TX    |                 0
 75270   | Dallas        | TX    | 0.460576795779555
 75202   | Dallas        | TX    |  0.62326173788043
  .
  .
  .
 76012   | Arlington     | TX    |   19.644132573068
 75126   | Forney        | TX    |  19.8963253723536
 75024   | Plano         | TX    |  19.9884653971924
(106 rows)

As for validating the function's correctness, I'm using a well-known
formula and I've compared the function's output to distances measured on
a map.  I wouldn't use it for missile targeting, but it's sufficiently
accurate for "show me all stores within 20 miles of my home."

Here's the meat of the function (written in C); the coordinates have by
now been converted to radians:

  dlat = lat2 - lat1;
  dlon = lon2 - lon1;

  a1 = sin(dlat / 2.0);
  a2 = sin(dlon / 2.0);

  a = (a1 * a1) + cos(lat1) * cos(lat2) * (a2 * a2);
  c = 2.0 * atan2(sqrt(a), sqrt(1.0 - a));

  dist = radius * c;

If anybody's interested I'll post the entire file.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From mail@joeconway.com Sat Dec  27 03:13:29 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 91C09D1B471
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 03:13:29 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70233-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Dec 2003 23:12:44 -0400 (AST)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id E8D37D1B448
	for <pgsql-sql@postgresql.org>; Fri, 26 Dec 2003 23:12:39 -0400 (AST)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 746616; Fri, 26 Dec 2003 19:09:40 -0800
Message-ID: <3FECF7A3.2050108@joeconway.com>
Date: Fri, 26 Dec 2003 19:08:19 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.5) Gecko/20031007
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Michael Fuhr <mike@fuhr.org>
Cc: Andy Lewis <jumboc@comcast.net>, pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
	<20031226194244.A15916@quality.qadas.com>
In-Reply-To: <20031226194244.A15916@quality.qadas.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/193
X-Sequence-Number: 16359

Michael Fuhr wrote:
> I wrote a haversine() function that uses the Haversine Formula to
> calculate the great circle distance between two points on a sphere
> (assuming the earth is a perfect sphere is accurate enough for my uses).
> Here's a web site with related info:
> 
> http://www.census.gov/cgi-bin/geo/gisfaq?Q5.1

[...snip...]

> Here's the meat of the function (written in C); the coordinates have by
> now been converted to radians:

[...snip...]

> If anybody's interested I'll post the entire file.

FWIW, here's a plpgsql function I wrote a while ago based on the 
Haversine formula:

CREATE FUNCTION "zipdist" (float8,float8,float8,float8 ) RETURNS float8 AS '
    DECLARE
      lat1 ALIAS FOR $1;
      lon1 ALIAS FOR $2;
      lat2 ALIAS FOR $3;
      lon2 ALIAS FOR $4;
      dist float8;
    BEGIN
      dist := 0.621 * 6371.2 * 2 *
        atan2( sqrt(abs(0 + pow(sin(radians(lat2)/2 -
        radians(lat1)/2),2) + cos(radians(lat1)) * cos(radians(lat2)) *
        pow(sin(radians(lon2)/2 - radians(lon1)/2),2))),sqrt(abs(1 -
        pow(sin(radians(lat2)/2 - radians(lat1)/2),2) + cos(radians(lat1)) *
        cos(radians(lat2)) * pow(sin(radians(lon2)/2 - 
radians(lon1)/2),2))));
      return dist;
    END;
' LANGUAGE 'plpgsql';

I used the following PHP code to start looking for a match in a small 
circle, and then expand it if no matches were found:

$dist = INIT_DIST;
$cnt = 0;
$cntr = 0;
do {
      if ((! $zip == "") && (! $dist <= 0)) {
          $sql = get_zip_sql($lon1d,$lat1d,$dist,$numtoshow);
          $rs = connexec($conn,$sql);
          $rsf = rsfetchrs($rs);
          $dist *= 2;
          $cntr++;
      } else {
          $cntr = 10;
      }
} while (count($rsf) < $numadvisorstoshow && $cntr < 10);

Hopefully you get the idea. You can narrow the results using a box to 
make the query perform better, and then sort by distance to get the 
closest alternative. Here's the related part of get_zip_sql():

function get_zip_sql($lon1d,$lat1d,$dist,$numtoshow)
{
   $sql = "
     SELECT DISTINCT <fields>
     FROM tbl_a AS a
      ,tbl_d AS d
      ,tbl_a_zipcodes AS az
      ,tbl_zipcodes as z
     WHERE
      abs(z.lat - $lat1d) * 60 * 1.15078 <= $dist
      and abs(z.long - $lon1d) * 60 * 1.15078 <= $dist
      and zipdist($lat1d,$lon1d,lat,long) <= $dist
      and z.zip = az.zipcode
      <other criteria>
     ORDER BY
     LIMIT $numtoshow;
   ";

   return $sql;
}

The "X * 60 * 1.15078" converts differences in degrees lat/long into 
rough distances in miles.

Hope this helps.

Joe





From bruno@wolff.to Sat Dec  27 04:34:55 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C4375D1B432
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 04:34:55 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 80962-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 00:34:06 -0400 (AST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id 4AEBBD1B42F
	for <pgsql-sql@postgresql.org>; Sat, 27 Dec 2003 00:34:05 -0400 (AST)
Received: (qmail 30603 invoked by uid 500); 27 Dec 2003 04:34:04 -0000
Date: Fri, 26 Dec 2003 22:34:04 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Michael Fuhr <mike@fuhr.org>
Cc: Andy Lewis <jumboc@comcast.net>, pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code
Message-ID: <20031227043404.GB5264@wolff.to>
Mail-Followup-To: Michael Fuhr <mike@fuhr.org>,
	Andy Lewis <jumboc@comcast.net>, pgsql-sql@postgresql.org
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
	<20031226194244.A15916@quality.qadas.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20031226194244.A15916@quality.qadas.com>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/195
X-Sequence-Number: 16361

On Fri, Dec 26, 2003 at 19:42:44 -0700,
  Michael Fuhr <mike@fuhr.org> wrote:
> 
> I have several databases with lat/lon coordinates and frequently make
> "show me all records within a certain distance of this point" queries.
> I wrote a haversine() function that uses the Haversine Formula to
> calculate the great circle distance between two points on a sphere
> (assuming the earth is a perfect sphere is accurate enough for my uses).
> Here's a web site with related info:

The distance operator in contrib/earthdistance got changed to use
haversine instead of the naive formula in 7.3. In 7.4 it also provides
some functions that work with contrib/cube that allow for indexed
searches.



From mike@fuhr.org Sat Dec  27 05:20:41 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id CEFE6D1B478
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 05:20:41 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 83918-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 01:19:53 -0400 (AST)
Received: from quality.qadas.com (quality.qadas.com [204.227.16.4])
	by svr1.postgresql.org (Postfix) with ESMTP id 6CE54D1B43D
	for <pgsql-sql@postgresql.org>; Sat, 27 Dec 2003 01:19:52 -0400 (AST)
Received: from quality.qadas.com (localhost [127.0.0.1])
	by quality.qadas.com (8.12.10/8.12.10) with ESMTP id hBR5JqB3013066
	for <pgsql-sql@postgresql.org>; Fri, 26 Dec 2003 22:19:52 -0700 (MST)
Received: (from mfuhr@localhost)
	by quality.qadas.com (8.12.10/8.12.10/Submit) id hBR5Jqlt013065
	for pgsql-sql@postgresql.org; Fri, 26 Dec 2003 22:19:52 -0700 (MST)
Date: Fri, 26 Dec 2003 22:19:52 -0700
From: Michael Fuhr <mike@fuhr.org>
To: pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code
Message-ID: <20031226221952.A5600@quality.qadas.com>
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
	<20031226194244.A15916@quality.qadas.com>
	<20031227043404.GB5264@wolff.to>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20031227043404.GB5264@wolff.to>;
	from bruno@wolff.to on Fri, Dec 26, 2003 at 10:34:04PM -0600
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/196
X-Sequence-Number: 16362

On Fri, Dec 26, 2003 at 10:34:04PM -0600, Bruno Wolff III wrote:
> On Fri, Dec 26, 2003 at 19:42:44 -0700,
>   Michael Fuhr <mike@fuhr.org> wrote:
> > 
> > I have several databases with lat/lon coordinates and frequently make
> > "show me all records within a certain distance of this point" queries.
> > I wrote a haversine() function that uses the Haversine Formula to
> > calculate the great circle distance between two points on a sphere
> > (assuming the earth is a perfect sphere is accurate enough for my uses).
> > Here's a web site with related info:
> 
> The distance operator in contrib/earthdistance got changed to use
> haversine instead of the naive formula in 7.3. In 7.4 it also provides
> some functions that work with contrib/cube that allow for indexed
> searches.

I'll have to take a closer look at contrib/earthdistance.  I'm using the
function I wrote for legacy reasons -- I had ported an application from
another DBMS to PostgreSQL and wanted to make as few changes as possible,
so I ported the haversine() function that I had already written.

Incidentally, I see the following in README.earthdistance:

  A note on testing C extensions - it seems not enough to drop a function
  and re-create it - if I change a function, I have to stop and restart
  the backend for the new version to be seen. I guess it would be too
  messy to track which functions are added from a .so and do a dlclose
  when the last one is dropped.

Maybe you've already figured it out, but LOAD should allow you to reload
a .so file without having to restart the backend.

http://www.postgresql.org/docs/current/static/sql-load.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From bruno@wolff.to Sat Dec  27 07:51:38 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id DE8E1D1B456
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 07:51:38 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 12114-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 03:50:51 -0400 (AST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id 4BF90D1B45B
	for <pgsql-sql@postgresql.org>; Sat, 27 Dec 2003 03:50:49 -0400 (AST)
Received: (qmail 27718 invoked by uid 500); 27 Dec 2003 07:50:50 -0000
Date: Sat, 27 Dec 2003 01:50:50 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code
Message-ID: <20031227075050.GA17222@wolff.to>
Mail-Followup-To: Michael Fuhr <mike@fuhr.org>,
	pgsql-sql@postgresql.org
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
	<20031226194244.A15916@quality.qadas.com>
	<20031227043404.GB5264@wolff.to>
	<20031226221952.A5600@quality.qadas.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20031226221952.A5600@quality.qadas.com>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/198
X-Sequence-Number: 16364

On Fri, Dec 26, 2003 at 22:19:52 -0700,
  Michael Fuhr <mike@fuhr.org> wrote:
> 
> Incidentally, I see the following in README.earthdistance:
> 
>   A note on testing C extensions - it seems not enough to drop a function
>   and re-create it - if I change a function, I have to stop and restart
>   the backend for the new version to be seen. I guess it would be too
>   messy to track which functions are added from a .so and do a dlclose
>   when the last one is dropped.
> 
> Maybe you've already figured it out, but LOAD should allow you to reload
> a .so file without having to restart the backend.


I didn't write that. It came from the person(s) who worked on earthdistance
before I touched it.



From jumboc@comcast.net Sat Dec  27 15:37:36 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 12536D1B458
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 15:37:36 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 56179-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 11:36:49 -0400 (AST)
Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.202.56])
	by svr1.postgresql.org (Postfix) with ESMTP id 7C98AD1B465
	for <pgsql-sql@postgresql.org>; Sat, 27 Dec 2003 11:36:45 -0400 (AST)
Received: from andy2 (c-24-1-39-92.client.comcast.net[24.1.39.92])
	by comcast.net (sccrmhc12) with SMTP
	id <2003122715364401200rc1tne>; Sat, 27 Dec 2003 15:36:45 +0000
From: "Andy Lewis" <jumboc@comcast.net>
To: "'Michael Fuhr'" <mike@fuhr.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Radius of a zip code
Date: Sat, 27 Dec 2003 09:36:44 -0600
Message-ID: <000801c3cc8f$3bc67f80$0201a8c0@andy2>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2627
In-Reply-To: <20031226194244.A15916@quality.qadas.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/203
X-Sequence-Number: 16369

Thanks All for your suggestions, I have enough information to construct
what I need.

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org] 
Sent: Friday, December 26, 2003 8:43 PM
To: Andy Lewis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Radius of a zip code


On Fri, Dec 26, 2003 at 05:42:08PM -0600, Andy Lewis wrote:
> I was trying to find all zip codes within a given zip code or radius.
>  
> I have map points and Latitude and Longitude in my zip table.
>  
> I remember seeing a post or two referencing this but can't see to find

> it.

The code in contrib/earthdistance in the PostgreSQL source code might be
what you're looking for.  I haven't used it myself, as I had already
written a function I needed for another DBMS and ported it to
PostgreSQL.

> I've tried the following with no luck:
>  
> -- 20 Miles
> --select 20 * 360.0 / (7900.0 * atan2(1,1) * 4.0);
> select * from zip_code where map_loc @ 
> circle(map_point('dallas','tx','75201'), .290105212724467 ) order by 
> city

This isn't related to the problem, but is there a reason your map_point
function requires city, state, and zip code?  If you know the zip code
then you shouldn't need the city and state.

> Anyone that has this experience, can you validate this for 
> correctness?

I have several databases with lat/lon coordinates and frequently make
"show me all records within a certain distance of this point" queries. I
wrote a haversine() function that uses the Haversine Formula to
calculate the great circle distance between two points on a sphere
(assuming the earth is a perfect sphere is accurate enough for my uses).
Here's a web site with related info:

http://www.census.gov/cgi-bin/geo/gisfaq?Q5.1

Here's an example of how I use the haversine() function.  I'm not using
PostgreSQL's geometric types -- latitude and longitude are stored in
separate fields.  The function takes two lat/lon coordinates in degrees
and optionally a radius (the default is 3956.0, the approximate radius
of the earth in miles); it returns the distance in whatever units the
radius is in.

SELECT a.zipcode, a.city, a.state,
       haversine(a.latitude, a.longitude, b.latitude, b.longitude) AS
dist FROM zipcode AS a, zipcode AS b WHERE b.zipcode = 75201
  AND haversine(a.latitude, a.longitude, b.latitude, b.longitude) <= 20
ORDER BY dist;

 zipcode |     city      | state |       dist        
---------+---------------+-------+-------------------
 75201   | Dallas        | TX    |                 0
 75270   | Dallas        | TX    | 0.460576795779555
 75202   | Dallas        | TX    |  0.62326173788043
  .
  .
  .
 76012   | Arlington     | TX    |   19.644132573068
 75126   | Forney        | TX    |  19.8963253723536
 75024   | Plano         | TX    |  19.9884653971924
(106 rows)

As for validating the function's correctness, I'm using a well-known
formula and I've compared the function's output to distances measured on
a map.  I wouldn't use it for missile targeting, but it's sufficiently
accurate for "show me all stores within 20 miles of my home."

Here's the meat of the function (written in C); the coordinates have by
now been converted to radians:

  dlat = lat2 - lat1;
  dlon = lon2 - lon1;

  a1 = sin(dlat / 2.0);
  a2 = sin(dlon / 2.0);

  a = (a1 * a1) + cos(lat1) * cos(lat2) * (a2 * a2);
  c = 2.0 * atan2(sqrt(a), sqrt(1.0 - a));

  dist = radius * c;

If anybody's interested I'll post the entire file.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/




From tgl@sss.pgh.pa.us Sat Dec  27 18:22:13 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5CD2AD1B4C1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 18:22:13 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 66633-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Dec 2003 14:21:25 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 8125FD1B4B7
	for <pgsql-sql@postgresql.org>; Sat, 27 Dec 2003 14:21:23 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBRIL819012680;
	Sat, 27 Dec 2003 13:21:08 -0500 (EST)
To: Bruno Wolff III <bruno@wolff.to>
Cc: Michael Fuhr <mike@fuhr.org>, pgsql-sql@postgresql.org
Subject: Re: Radius of a zip code 
In-reply-to: <20031227075050.GA17222@wolff.to> 
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
	<20031226194244.A15916@quality.qadas.com>
	<20031227043404.GB5264@wolff.to>
	<20031226221952.A5600@quality.qadas.com>
	<20031227075050.GA17222@wolff.to>
Comments: In-reply-to Bruno Wolff III <bruno@wolff.to>
	message dated "Sat, 27 Dec 2003 01:50:50 -0600"
Date: Sat, 27 Dec 2003 13:21:08 -0500
Message-ID: <12679.1072549268@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/207
X-Sequence-Number: 16373

Bruno Wolff III <bruno@wolff.to> writes:
>   Michael Fuhr <mike@fuhr.org> wrote:
>> Maybe you've already figured it out, but LOAD should allow you to reload
>> a .so file without having to restart the backend.

> I didn't write that. It came from the person(s) who worked on earthdistance
> before I touched it.

I've removed the incorrect comment.

			regards, tom lane



From rr@8x.ca Thu Jan  1 05:04:37 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 717B8D1B443
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Jan 2004 05:04:37 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 72275-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Jan 2004 01:03:47 -0400 (AST)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 8016CD1B449
	for <pgsql-sql@postgresql.org>; Thu,  1 Jan 2004 01:03:47 -0400 (AST)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.12.9/8.12.9) with ESMTP id i0153RU6003248
	for <pgsql-sql@postgresql.org>; Thu, 1 Jan 2004 05:03:27 GMT
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.12.9/8.12.9/Submit) id i014uOJr098432
	for pgsql-sql@postgresql.org; Thu, 1 Jan 2004 04:56:24 GMT
From: Randolf Richardson <rr@8x.ca>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: Radius of a zip code
Date: Thu, 1 Jan 2004 04:56:24 +0000 (UTC)
Organization: Inter-Corporate Computer & Network Services, Inc.
Lines: 27
Message-ID: <Xns9462D2D4F5738rr8xca@200.46.204.72>
References: <000b01c3cc09$e043fd70$0201a8c0@andy2>
	<8408.1072483789@sss.pgh.pa.us>
X-Complaints-To: usenet@news.hub.org
User-Agent: Xnews/5.04.25
X-Face: 
 +kXh]<cm0}l<Fvzd*?E]I7=Ug!X`>8'{:R`HzXla-aT~$s8a+C)k/B%RBr$_3sP`[kA}cl7#mD^9Z:oM`HpoC_kUEH['RZ*Ob%pz\ww^q&x)~zc`.xd]VQ4WN/3t2nS-<I>BvI%LF\B4:\9$EI:/|<)`?8I_;
	xzG@SOYFir/gk_eB4"Rl43-h%)8O0sR$b&Mp3XHR(0j=
X-Face-Author: Randolf Richardson (composed with Adobe Photoshop)
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/21
X-Sequence-Number: 16414

"tgl@sss.pgh.pa.us (Tom Lane)" wrote in comp.databases.postgresql.sql:

[sNip]
> I'm guessing that the big problem is that you didn't measure longitude
> and latitude in identical units in your table, so your "circle" isn't
> real circular, and the smaller problem is that "miles" converts to
> "degrees of arc" differently at different latitudes.

    	Don't forget that there are two different types of "miles" which need 
to be considered when measuring distances:

    	    	1 statute/land mile = 1.609 km
    	    	1 nautical/sea mile = 1.85 km

    	Since kilometers are consistent over land and water (and in the great 
vacuum of space), the metric system should always be used to ensure clarity, 
unless the only land masses the user is concerned with have no bodies of 
water.

    	=)

-- 
Sir Randolf, noble spam fighter - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.