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> (raw)
In-Reply-To: <20031226194244.A15916@quality.qadas.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jumboc@comcast.net, mike@fuhr.org
Subject: Re: Radius of a zip code
In-Reply-To: <000801c3cc8f$3bc67f80$0201a8c0@andy2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox