Re: Radius of a zip code - Mailing list pgsql-sql
From | Andy Lewis |
---|---|
Subject | Re: Radius of a zip code |
Date | |
Msg-id | 000801c3cc8f$3bc67f80$0201a8c0@andy2 Whole thread Raw |
In response to | Re: Radius of a zip code (Michael Fuhr <mike@fuhr.org>) |
List | pgsql-sql |
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 | 075270 | Dallas | TX | 0.46057679577955575202 | Dallas | TX | 0.62326173788043 . . .76012 | Arlington | TX | 19.64413257306875126 | Forney | TX | 19.896325372353675024 | 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/