Re: OT: within 10 miles

[David Schlotfeldt]

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: 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!