|
|
Posted by Michael Phipps on 10/10/30 11:23
>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
Navigation:
[Reply to this message]
|