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.
>