Get nearby locations using latitude and longitude

Question

I have a hotspots table

 id name date 1 foo 2011-06-15 15:10:34 2 bar 2011-07-02 16:45:18 

And the locations table

 hotspotid zipcode latitude longitude 1 90001 xxxxx xxxxx 2 45802 xxxxx xxxxx 

How can I list hotspots near a certain width and longitude with miles limit using an SQL query?

PS I would like a clean SQL solution, but php is ok if necessary.

thanks

Answer 1

First you need a geographic location for each access point, ideally using latitude and longitude. You can use the Google Geocoding API to go through each record and save lat / long.

Then you can combine nearby access points with this SQL query and sort by distance.

Hope this helps.

Answer 2

Just browsing, I found this link , although I'm not sure if this is exactly what you are looking for.

Here is a post that better describes the library. This is not an answer to SQl, although you can take lats / longs, dump them in db and program the distance request.

Answer 3

For a clean SQL solution, you can look at databases such as

http://www.zip-codes.com

If you are looking for a free solution, I used to use the maxminds ip address database, but their accuracy in the zip code is very poor. In addition, the maxminds database is designed to find locations from an IP address.

If you want to find the location of the zip code, you can subscribe to the Google Maps API key and use your geolocation search service. This solution will most likely require PHP or javascript to search. But the service is well documented and very effective in my experience.

http://code.google.com/apis/maps/documentation/javascript/basics.html#Geolocation

Answer 4

what you are calculating is the "large circle distance" between two pairs of lat and long values.

this can be done in SQL, and especially if you want to derive the calculation and add it to the database procedure ... just order the distance when calculating

Answer 5

If you only care about things at a certain distance from the glasses, you should look at this:

http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

Answer 6

Please try this for the nearest location.

 SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<='10' ORDER BY `distance` ASC