467,150 Members | 1,167 Online

# ORDER BY Zip Code Distance

 I have successfully converted the ASP code included in the following article to PHP: http://www.4guysfromrolla.com/webtech/040100-1.shtml As described the high and low latitudes and longitudes are pre-calculated and passed to the query. SELECT * FROM Locations WHERE Latitude <= \$HighLatitude AND Latitude >= \$LowLatitude AND Longitude >= \$LowLongitude AND Longitude <= \$HighLongitude I then calculate the actual distance using the latitude and longitude for each zip returned. This works great and saves lots of cycles. The downside to this technique is the inability to sort by distance. Can someone suggest a simple way to calculate and ORDER BY distance within the query? Thanks! Jul 17 '05 #1
• viewed: 3020
Share:
20 Replies
 On 8-Feb-2004, "Xenophobe" wrote: As described the high and low latitudes and longitudes are pre-calculated and passed to the query. SELECT * FROM Locations WHERE Latitude <= \$HighLatitude AND Latitude >= \$LowLatitude AND Longitude >= \$LowLongitude AND Longitude <= \$HighLongitude I then calculate the actual distance using the latitude and longitude for each zip returned. This works great and saves lots of cycles. The downside to this technique is the inability to sort by distance. Can someone suggest a simple way to calculate and ORDER BY distance within the query? Put the following in your SQL (\$lat and \$long are the zero distance point) (69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS(\$lat))+COS(RADIANS(Latitude))* COS(RADIANS(\$lat))*COS(RADIANS(Longitude-\$long))))) AS Distance will create the Distance 'column' ORDER BY Distance -- Tom Thackrey www.creative-light.com tom (at) creative (dash) light (dot) com do NOT send email to ja*********@willglen.net (it's reserved for spammers) Jul 17 '05 #2
 Hi! On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" wrote: Doug,This is just a quick follow-up. The zip sort by distance works greatexcept... the performance is pokey for searches of 20+ miles. I could simplysort the results by company name (ditching the beautiful syntax you help mewith!), but even this won't save enough cycles for broader searches.The options (in my view) are either break out the results in multiple pagesor pre-calculate every possible lat and long and store them in the database(creating one huge table!) Can you think of anything that might help? Well, depends what you think is huge. It might still perform very well with proper indexing. How many rows are we talking? Eg. half the world fits in around 4 GB of data for going down to minutes, without even thinking about a good encoding for your latitude/ longitude index. So this would mean around 4 disk accesses, after that you read payload data. Of course I expect you are measuring only one country, so you'll have much less data. HTH, Jochen -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jul 17 '05 #12
 Hi! This is just a quick follow-up. The zip sort by distance works greatexcept... the performance is pokey for searches of 20+ miles. I could simplysort the results by company name (ditching the beautiful syntax you help mewith!), but even this won't save enough cycles for broader searches.The options (in my view) are either break out the results in multiple pagesor pre-calculate every possible lat and long and store them in the database(creating one huge table!) Can you think of anything that might help?Well, depends what you think is huge. It might still perform very wellwith proper indexing. How many rows are we talking?Eg. half the world fits in around 4 GB, sorry 5 GB, but doesn't makea difference in this range. Jochen -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jul 17 '05 #13
 Jochen, That is interesting. Can you give more details? As I read the op, the problem was to find how many points from his database lay within x miles of given point y. I am not a GIS expert by any menas, but I can't see how to avoid doing the trig. each time? What am I missing? I love to learn new stuff like this. Cheers, Doug -- Remove the blots from my address to reply "Jochen Daum" wrote in message news:e0********************************@4ax.com... Hi!This is just a quick follow-up. The zip sort by distance works greatexcept... the performance is pokey for searches of 20+ miles. I could simplysort the results by company name (ditching the beautiful syntax you help mewith!), but even this won't save enough cycles for broader searches.The options (in my view) are either break out the results in multiple pagesor pre-calculate every possible lat and long and store them in the database(creating one huge table!) Can you think of anything that might help?Well, depends what you think is huge. It might still perform very wellwith proper indexing. How many rows are we talking?Eg. half the world fits in around 4 GB, sorry 5 GB, but doesn't makea difference in this range. Jochen -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jul 17 '05 #14
 Hi! On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson" wrote: Jochen,That is interesting. Can you give more details? As I read the op, theproblem was to find how many points from his database lay within x miles ofgiven point y. I am not a GIS expert by any menas, but I can't see how toavoid doing the trig. each time? What am I missing? I love to learn newstuff like this. Made a calculation mistake there, but it still goes with less area and accuracy: Store a table with fields srclong tinyint srclongmin tinyint srclat tinyint srclatmin tinyint distance float trglong tinyint trglongmin tinyint trglat tinyint trglatmin tinyint with a clustered index on the first 5 fields. Then save all possible combinations with their distance into the database. A calculation example: All of US is less than 30 by 60 degrees latitude/longitude (sorry if I mix it up). If we store 0 and 30 minutes positions only, thats ((30*2*60*2)^2)*20 bytes = 1036800000 bytes Thats for 7200 locations. Good thing is, it performs always the same for the same number of locations, no matter what actual value you store for minutes. The other performance factor is only the number of records returned, but you would want to limit that anyway for a web page. HTH, Jochen Cheers,Doug -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jul 17 '05 #15
 "Jochen Daum" wrote in message news:er********************************@4ax.com... Hi! On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson" wrote:Jochen,That is interesting. Can you give more details? As I read the op, theproblem was to find how many points from his database lay within x miles ofgiven point y. I am not a GIS expert by any menas, but I can't see how toavoid doing the trig. each time? What am I missing? I love to learn newstuff like this. Made a calculation mistake there, but it still goes with less area and accuracy: Store a table with fields srclong tinyint srclongmin tinyint srclat tinyint srclatmin tinyint distance float trglong tinyint trglongmin tinyint trglat tinyint trglatmin tinyint with a clustered index on the first 5 fields. Then save all possible combinations with their distance into the database. A calculation example: All of US is less than 30 by 60 degrees latitude/longitude (sorry if I mix it up). If we store 0 and 30 minutes positions only, thats ((30*2*60*2)^2)*20 bytes = 1036800000 bytes Thats for 7200 locations. Good thing is, it performs always the same for the same number of locations, no matter what actual value you store for minutes. The other performance factor is only the number of records returned, but you would want to limit that anyway for a web page. HTH, JochenCheers,Doug -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jochen, That looks like a great idea. Thank you very much for the insight. Cheers, Doug -- Remove the blots from my address to reply Jul 17 '05 #16
 Jochen, You're right, "huge" is relative. The zip code table consists of over 52K records. The company supplier lists it's joined with is under 3K. By "indexing", do you mean MySQL indexing? If so, I've didn't have much luck with that. It didn't seem to speed things up although it's quite possible the wrong columns were indexed or not enough space was allocated. Here's the schema for the two tables. Can you recommend which tables to index and what sizes to define? Any suggestions would be appreciated. Thanks! ---------------------------------------- TABLE: Locations LocationID ZipCode City State StatusCode AreaCode TimeZone Latitude Longitude TABLE: Suppliers SupplierID SupplierName1 SupplierName2 Address1 Address2 City State Zip Phone "Jochen Daum" wrote in message news:lb********************************@4ax.com... Hi! On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" wrote:Doug,This is just a quick follow-up. The zip sort by distance works greatexcept... the performance is pokey for searches of 20+ miles. I could simplysort the results by company name (ditching the beautiful syntax you help mewith!), but even this won't save enough cycles for broader searches.The options (in my view) are either break out the results in multiple pagesor pre-calculate every possible lat and long and store them in the database(creating one huge table!) Can you think of anything that might help? Well, depends what you think is huge. It might still perform very well with proper indexing. How many rows are we talking? Eg. half the world fits in around 4 GB of data for going down to minutes, without even thinking about a good encoding for your latitude/ longitude index. So this would mean around 4 disk accesses, after that you read payload data. Of course I expect you are measuring only one country, so you'll have much less data. HTH, Jochen -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jul 17 '05 #17