Обсуждение: Zip Code Proximity
Hello All, I know there's been quite a few posts on Zip Code Proximity. Can anyone point me in the right direction to find the code to calulate the distance between two zip codes? I'm basically trying to take a zip code given by a user and return them all of the zip codes within, say 10 miles or 20 miles. I've tried the mailing list search but, they seem to be down or not available. Thanks Andy
Jeff Hoffmann wrote: > select zip, location <@> '(lat, lon)'::box > from zipcodes > order by location <@> '(lat, lon)'::box > limit 10; > oops, typo. those boxes should be points. plus, it looks like you can get zipcodes & lat-longs from the census at: http://ftp.census.gov/geo/www/gazetteer/places.html
Actually I was thinking more on the lines of: select location from test where location @ '((31.6283,93.6347), 1.39)'::circle; The above lat/lon is for: Zwolle, LA If Zylks, LA (lat/lon = 32.9696,93.9801) is also in the DB. The above query should select Zylks since 1.39 * 62.1 = 86 miles approx. 86 miles is about correct for those two zip codes. Wouldn't this be much easier? Is the data type POINT index-able? Yes, I have all of the zip codes and lat/lon information. Thanks Andy On Thu, 18 May 2000, Jeff Hoffmann wrote: > Andy Lewis wrote: > > > > Hello All, > > > > I know there's been quite a few posts on Zip Code Proximity. > > > > Can anyone point me in the right direction to find the code to calulate > > the distance between two zip codes? > > > > I'm basically trying to take a zip code given by a user and return them > > all of the zip codes within, say 10 miles or 20 miles. > > > > I've tried the mailing list search but, they seem to be down or not > > available. > > > > Thanks > > > > Andy > > i'm surprised that nobody else has apparently responded. first you need > to have a table of zipcodes & lat-longs for those zip codes. it may > take a little looking, but you should be able to find that. now take a > look at the earthdistance function in the contrib directory of the > distribution. assuming your table is something like: > > create table zipcodes ( zip int4, location point); > > next populate the table with the zipcodes > > next install the earthdistance function > > assuming you know the lat,lon of the zipcode in question, you can query > the table with something like this. it'll pick the 10 closest zipcodes > and order them by the closest: > > select zip, location <@> '(lat, lon)'::box > from zipcodes > order by location <@> '(lat, lon)'::box > limit 10; > > i'll leave using indexes as an exercise for the reader. it may or may > not help depending on whether you have all the zipcodes for the country > or not. plus i don't know if this is going to work. it should, but i > haven't tested it. >
Andy Lewis wrote: > > Actually I was thinking more on the lines of: > > select location from test where location @ '((31.6283,93.6347), 1.39)'::circle; > that works just great, assuming that you can accurately calculate the radius of the circle that you're searching for. > If Zylks, LA (lat/lon = 32.9696,93.9801) is also in the DB. > The above query should select Zylks since 1.39 * 62.1 = 86 miles approx. > 86 miles is about correct for those two zip codes. > > Wouldn't this be much easier? to me, the big question is how you're calculating the radius & if you're taking into account the curvature of the earth so that 62.1 changes in different parts of the country. if you've taken that into consideration, great. it's probably going to be ok to flatten the earth for an area that size, though. > Is the data type POINT index-able? > unfortunately, not in a really useful way for you. plus, the optimizer probably wouldn't use the index any. it's a small data set, something around 35k-40k records, right?
On Thu, 18 May 2000, Jeff Hoffmann wrote: > Andy Lewis wrote: > > > > Actually I was thinking more on the lines of: > > > > select location from test where location @ '((31.6283,93.6347), 1.39)'::circle; > > > > that works just great, assuming that you can accurately calculate the > radius of the circle that you're searching for. > I'll already have the mileage from an html form and the initial zip. Should work just fine. It doesn't have to be exact just close. > > If Zylks, LA (lat/lon = 32.9696,93.9801) is also in the DB. > > The above query should select Zylks since 1.39 * 62.1 = 86 miles approx. > > 86 miles is about correct for those two zip codes. > > > > Wouldn't this be much easier? > > to me, the big question is how you're calculating the radius & if you're > taking into account the curvature of the earth so that 62.1 changes in > different parts of the country. if you've taken that into > consideration, great. it's probably going to be ok to flatten the earth > for an area that size, though. > I don't plan on using anymore than a 100 miles, so that should work ok, I'd think. > > Is the data type POINT index-able? > > > > unfortunately, not in a really useful way for you. plus, the optimizer > probably wouldn't use the index any. it's a small data set, something > around 35k-40k records, right? > I'm not sure how many records are in the zip code DB that I currently have, shouldn't be much more than that though. Andy
On Thu, 18 May 2000, Jeff Hoffmann wrote:
I tried to get a few permutations of that to work, but with no
luck. The following will give a list of all places sorted by how far away
from my house they are:
select zipcode, city, state, point(latitude, longitude) as point
from zips
order by point_distance(location_of(95051), point(latitude,longitude))
I defined the function location_of for my own convenience:
create function location_of(integer) returns point as
'select point(latitude, longitude) from zips where zipcode = $1 '
language 'sql'
# > select zip, location <@> '(lat, lon)'::box
# > from zipcodes
# > order by location <@> '(lat, lon)'::box
# > limit 10;
# >
#
# oops, typo. those boxes should be points. plus, it looks like you can
# get zipcodes & lat-longs from the census at:
#
# http://ftp.census.gov/geo/www/gazetteer/places.html
#
#
--
dustin sallings The world is watching America,
http://2852210114/~dustin/ and America is watching TV.
Hi everyone, I'm trying to sort email style subject lines and similar stuff. When I try: (on 6.5.3) select m_subject from wm_mails where m_id > 900 and m_id < 1000 order by (CASE WHEN (lower(m_subject) like 're: %') THEN (substring(lower(m_subject) from 5)) ELSE (lower(m_subject)) END ); I get Illegal ORDER BY node = 723 In this case I'm trying to ignore leading 're: ' strings in the sort. I found that I can use something like select m_subject, case when ... then ... else end as orderfield etc order by orderfield I also managed to do it by creating a custom function: create function trimsub (text) returns text as 'select CASE WHEN (lower($1) like \'re: %\') THEN (substring(lower($1) from 5)) ELSE (lower($1)) END ' language 'SQL' ; But are there any other options where I can just put stuff in the "order by" clause? Thanks, Link.
Lincoln Yeoh <lylyeoh@mecomb.com> writes:
> [ CASE doesn't work in ORDER BY in 6.5 ]
Fixed in 7.0 (along with a fair number of other CASE-related problems,
IIRC).
regards, tom lane
Does anyone happen to have a copy of the zips.zip file from the census bureau? Their FTP server is uncooperative. Dustin Sallings wrote: > > On Thu, 18 May 2000, Jeff Hoffmann wrote: > > I tried to get a few permutations of that to work, but with no > luck. The following will give a list of all places sorted by how far away > from my house they are: > > select zipcode, city, state, point(latitude, longitude) as point > from zips > order by point_distance(location_of(95051), point(latitude,longitude)) > > I defined the function location_of for my own convenience: > > create function location_of(integer) returns point as > 'select point(latitude, longitude) from zips where zipcode = $1 ' > language 'sql' > > # > select zip, location <@> '(lat, lon)'::box > # > from zipcodes > # > order by location <@> '(lat, lon)'::box > # > limit 10; > # > > # > # oops, typo. those boxes should be points. plus, it looks like you can > # get zipcodes & lat-longs from the census at: > # > # http://ftp.census.gov/geo/www/gazetteer/places.html > # > # > > -- > dustin sallings The world is watching America, > http://2852210114/~dustin/ and America is watching TV. -- Paul Dlug Unix/Web Programmer