Zip Code Proximity

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:

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

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