Hi david,
Thanks! That is very helpful.
How about the performance for the SQL to search a big table(possibly
millions of businesses)? It has to go through all the records to calculate
distance. Is there a way to index somehow for better performance?
Thanks!
David Schlotfeldt <[EMAIL PROTECTED]> wrote:
David Schlotfeldt <[EMAIL PROTECTED]> wrote:
I have a database of zip codes, city, states, longitude, and latitude. Someone here found the database on the Internet. For a website we did we needed to list all locations of a franchise within 100 miles of a given zip code. It took a little while to find how to calculate the distance using longitude and latitude. I ended up finding a JS version which I rewrote into SQL.
Another person here told me, after I did this, th at he once wrote a about this. If you search the web you should be able to find it. In his version I think he creates a database procedure for the formula. Here is my code...
===================================
// Creates a query that will select all franchises that are within 100 miles. Orders by distance.
// Conversion of km to miles -> 0.621
// Radius of the earth 6371
// Algorithm based off of http://en.wikipedia.org/wiki/Haversine_formula
// this page then referenced a JS implementation
// http://www.movable-type.co.uk/scripts/LatLong.html
// (NOTE: Maybe change to a stored procedure.)
query2 = "SELECT x.*" +
" FROM (" +
" SELECT ROUND((6371 * ( 2 * ATAN2(SQRT(z.firstPart), SQRT(1-z.firstPart)) )) * 0.621) AS distanceMiles, z.* FROM" +
" (" +
" SELECT" +
" SIN((lat2 - lat1)/2) * SIN((lat2 - lat1)/2) +" +
" (COS(lat1) * COS(lat2) * SIN((long2 - long1)/2) * SIN((long2 - long1)/2))" +
" as firstPart," +
" y.*" +
" FROM (" +
" SELECT" +
" f.*," +
" a.latitude * PI()/180 AS lat1, a.longitude * PI()/180 AS long1," +
" b.latitude * PI()/180 AS lat2, b.longitude * PI()/180 AS long2" +
" FROM zipcodes as a, franchise AS f LEFT JOIN zipcodes AS b ON f.franchise_zip = b.zip " +
" WHERE a.zip = ***ZIP CODE GOES HERE***"+
" ) AS y" +
" ) AS z" +
" ) AS x" +
" WHERE x.distanceMiles <= 100" +
" ORDER BY x.distanceMiles"
;
===================================
< BR>Kind of hard to follow but with this and the URLs in the comments you should be able to figure it.
David
Dave wrote:I searched web, but did not find useful info.Goal: Given a zip code or an address, search the database to find all businesses that are within 10 miles.To be concrete, two things:1. How to calculate distance between two addresses(or two zip codes)?2. How to list all the zip codes that are within 10 miles from a given zip code?Is there such a service out there? But going through a service would be too slow.Thank you very much for any hints.
Relax. Yahoo! Mail virus scanning helps detect nasty viruses!
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
