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, that 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" ; =================================== Kind of hard to follow but with this and the URLs in the comments you should be able to figure it. David Dave wrote:
|
- Re: OT: within 10 miles David Schlotfeldt
- Re: OT: within 10 miles Dave
- Re: OT: within 10 miles Wayne Fay
- Re: OT: within 10 miles Dennis Byrne
- Re: OT: within 10 miles Mike Kienenberger
- Re: OT: within 10 miles Dave
- Re: OT: within 10 miles Wayne Fay