Distance between Zipcodes, Sorting, Speed, Help!

tonyndory

Okay, I need some help🙁 .

I have a website and maintain a member database of over 17,000 members. I have a table which contains for each member, among other things, their zipcode/postal code.

I also have a table with every zipcode/postal code, latitude, and longitude in North America.

I have successfully formed a query to select all members, and their distance, from the aforementioned tables. It is as follows:

// given [gathered beforehand]:
$lat = 39.9538; // latitude of center of radius
$lon = -74.995; // longitude of center of radius
$miles = 150; // radius distance

$query = "SELECT profiles.*,ROUND(1.15077 * 60.0 * degrees(acos(sin(radians($lat))*sin(radians(latitude))+cos(radians($lat))*cos(radians(latitude))*cos(radians(longitude) - radians($lon)))),0) as dist 
FROM profiles,zipcodes 
WHERE ((1.15077 * 60.0 * degrees(acos(sin(radians($lat))*sin(radians(zipcodes.latitude))+cos(radians($lat))*cos(radians(zipcodes.latitude))*cos(radians(zipcodes.longitude) - radians($lon)))) < $miles 
AND profiles.zipcode = zipcodes.zipcode))";

Using that query to return results from my tables, I can return about 83 matches in 0.0715 ms.

Now, when I add "order by dist" to sort the results by the distance, the time it takes to return the same results (in order) jumps to about 0.1250 ms

The trouble is, there is no way to index the ORDER BY clause, because it is a formula, and the variables are constantly changing.

Is there no hope? Please Help!

svnfightsvn

index latitude and longitude - or even zipcode

tomhath

Do you ever expect to return a large number of rows? If not you could try fetching the results into an array and sorting it in php.

Sxooter

The trouble is, there is no way to index the ORDER BY clause, because it is a formula, and the variables are constantly changing.

Is there no hope? Please Help!

I have no problem indexing the order by part of your query, I do that kind of thing all the time. In Postgresql.

Plus, you don't have to do the math like that, since postgresql can actually compute things like distance for you with built in functions.

While I recommend MySQL for lots of simple storage type tasks, for manipulating distance data or financial stuff, I'd recommend Postgresql.

jerdo

So 5 hundreths of a millisecond is now considered a jump? You aren't going to be able to add computations to something without adding computation time. So if you are expecting the query to run at the same speed with sorting as it does without you can forget it. That pretty much stands no matter what db, language, os, etc you use. More complex operations take more time than less complex operations.

tonyndory

It's not a jump of .05 ms, I wrote it incorrectly. it's a jump of .05 seconds.

I can't index the order by clause because it is a complex formula that changes with the longitude and latitude. Try indexing 1.15077 60.0 degrees(acos(sin(radians($lat))sin(radians(latitu de))+cos(radians($lat))cos(radians(latitude))*cos(radians(longitude) - radians($lon)))) where $lon and $lat are never the same.

I am using MySql, and while Postgresql may be better for this particular query, MySql is better for the rest of the project. Just by adding this distance segment to my site, it made the site WAY too slow. So slow that people left, so this is Not the sort of "increase in computation time" that I can live with. I have seen other sites that sort their results by geographic distance, and theirs are plenty fast enough. It's not the processor of the server, as I'm running a top of the line box. Any more suggestions would be helpful.

Sxooter

Originally posted by tonyndory
It's not a jump of .05 ms, I wrote it incorrectly. it's a jump of .05 seconds.

I can't index the order by clause because it is a complex formula that changes with the longitude and latitude. Try indexing 1.15077 60.0 degrees(acos(sin(radians($lat))sin(radians(latitu de))+cos(radians($lat))cos(radians(latitude))*cos(radians(longitude) - radians($lon)))) where $lon and $lat are never the same.

I am using MySql, and while Postgresql may be better for this particular query, MySql is better for the rest of the project. Just by adding this distance segment to my site, it made the site WAY too slow. So slow that people left, so this is Not the sort of "increase in computation time" that I can live with. I have seen other sites that sort their results by geographic distance, and theirs are plenty fast enough. It's not the processor of the server, as I'm running a top of the line box. Any more suggestions would be helpful.

I find it hard to believe that Postgresql can't do the rest of the project as well as MySQL.

Other than full text indexing there isn't much I find that MySQL does out of the box that pgsql can't do, and do well. Keep in mind pgsql is slower on individual work (i.e. on user on a web site) but scales much better under heavy load (100 users on a web site).

But for the indexing distance thing, can't you store the information for both the zip codes and the people ahead of time? Or do you have to get that new each time?

If you can store that data ahead of time, then yes, you can index it.

There's lots of folks who do this exact kind of thing everyday with Postgresql, quickly. But it has to be thought out, whether it's in pgsql or mysql. One of the tricks is to index a simpler formula that produces fewer outputs. I.e. see if the user is in this "box" first, THEN figure out the distance if their within it, otherwise ignore them as too far away kind of thing.

rontgoff

Can I ask how you got the lat and long of the zip code

tgotchi

Here you go.

http://www.zipcodeworld.com