Re: Zip code
- From: "Michael Phipps" <gravityspike_spamfree_@xxxxxxxxxxxxxxx>
- Date: Fri, 29 Jul 2005 09:10:30 +1000
>I have a database of zipcodes with latitude and longitude. I also have the
>method of calculating the distance between two zipcodes. What I want to
>know is if there is an efficient algorithm for obtaining the zip codes
>within a specified distance of the first zipcode without having to retrieve
>and calculate for every record in the database.
How about a single select statement?
In this case I use a table called cities, where I have the fields city,
latitude and longitude. I have indexed the lat / long for improved speed.
$sqlstr="select city, ((acos(sin((latitude)*(pi()/180)) *
sin((".$latitude.")*(pi()/180)) + cos((latitude)*(pi()/180)) *
cos((".$latitude.")*(pi()/180)) * cos((longitude -
".$longitude.")*(pi()/180))))*(180/pi())* 60 * 1.1515 * 1.609344) dist,
latitude, longitude from cities order by dist limit 30";
On my server this query returns results in under a second when the table is
indexed correctly.
This query is only returning the 30 closest cities to a latitude longitude,
but perhaps you can search where the dist is less than so many kilometers.
oh the *1.609344 at the end of the equation converts miles to kilometers.
I was originally thinking something much more complex was necessary, but it
isn't.
Hope it helps!
OH - where did you get the lat long database?
Michael
.
- References:
- Zip code
- From: News
- Zip code
- Prev by Date: Re: Script preview
- Next by Date: Re: PHP svn client or Wiki on the top of svn?
- Previous by thread: Re: Zip code
- Next by thread: config files
- Index(es):
Relevant Pages
|
