Zipcode distance problem

Gladiator2043

I am developing an app that gets a zipcode from a user and then uses that zipcode to find the nearest store locations in miles based on the stores zipcode.

I have a database that has all the zipcodes with there corresponding latitudes and longitudes.
I also have a calculation that computes distance between the zipcodes.

The problem I am facing is that I need the result to be sorted by distance. The issue at hand is that distance is calculated on the fly and how do I sort something that is computed on the fly by a PHp function.

Any help will be highly appreciated.

devinemke

i assume your are doing your distance calc in sort sort of a loop. if so, in each iterartion add the distance to an array and then [man]sort[/man] the array.

Sxooter

When you say the results are caculated "on the fly" do you mean in the select list on the database or in php code?

If you're doing this in the SQL select list, then something like:

select distance(area1,area2) as distance order by distance

If your distance calculation is done in PHP, look at moving it to being in the SQL statement.

Gladiator2043

How do I move my code to SQL ?

The function that I am using to calculate distance is is :

function distance($lat1, $lon1, $lat2, $lon2, $unit) {

$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) sin(deg2rad($lat2)) + cos(deg2rad($lat1)) cos(deg2rad($lat2)) cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist 60 * 1.1515;
$unit = strtoupper($unit);

if ($unit == "K") {
return ($miles 1.609344);
} else if ($unit == "N") {
return ($miles 0.8684);
} else {
return $miles;
}
}

I get the zipcode from the user and i compare it to a row of data that contains the store zipcode and I want a list of ordered stores calculated on the fly. I do like your Select statement.

Sxooter

Both PostgreSQL and MySQL appear to support cos/sin et. al. they also appear to be working in RADs, not degrees.

So, convert the numbers in your database from degrees to rad in PHP, then feed it to your database kinda like this:

select (sin(lat1)sin(lat2)+cos(lat1)cos(theta)) as distance from table <where clause> order by distance;

And create a where clause that will pick the right row (I'm assuming you've already got a where clause there somewhere in your own code.)

AstroTeg

This (at the very bottom) might help explain querying distance. There's two approaches described. The one you'd probably be interested in is the one with all the math being done in the SQL. The other method uses a user function which makes the query go a bit quicker and tends to be easier to use and read. But adding a user function to your database may not be an option.

You can also play with the data. If you do a zip code search with radius query (the top most search option) and check off "Show Query," it'll show the query at the very bottom of the page. By default, it uses the user function approach. Click on "Use SQL Calculations" and it'll use SQL math to calculate the distance.

You'll find the results are ordered with the shortest distance zip at the top. If you do the "show query," you'll see how this works.

Gladiator2043

Almost there.

Ok I have two databases.

database 1 has a stores table.

store name store address store zip

database 2 has a zipcode lookup table

zipcode latitude longtiude

user comes to a page and has to enter a zipcode to find nearest stores.
The next page should come up with a list of all the stores in database 1 that are arranged from nearest to farthest store distance. any insights.

I have looked at a lot of code but am still a tad bit confused. thank you.

AstroTeg

Where are you stuck at?

You know you'll need the lat/long of the user's zip code. Then you'll need to get the lat/long of the zips your stores have. As a side note, you might add a lat/long value to each store to reduce query times (but remember to update it if the zip changes). Otherwise, you'll have to query the stores and join the stores' zip codes with the zip codes in your other database to get the lat/long values. Once you do this, you'll have the user's lat/long and the stores lat/long, you can now do some math on it and determine the distance.

Sxooter

My only recommendation would be to put all these data into one database so you CAN join it all together quickly and easily.

Gladiator2043

Ok thank you both for your valuable advice. I will see what I can do. And if i get stuck I will post some code....thank you again

kidgeek_dfw

I'd sure like to see this in action...

I've been trying to find something in php that is like this:
http://bullseye.electricvine.com/demo.asp