Let’s say you’re building a wepapp and you want to give your users the ability to do a radius search for something (e.g. gas stations) in a specific location. On top of that you want your users to provide your app with new gas stations anywhere. First of all you obviously need some kind of form users can post new gas stations to. This step is easy: get the latitude and longitude of the gas station and save it with all other relevant information (e.g. name, address etc.) to your database. The way to do this is Google Maps. Get an api key from Google for your domain and you’re good to go. Now, whenever a user submits a new gas station, you get the coordinates by giving Google the address your user submitted. Here’s how you do that:
$handle = fopen("http://maps.google.com/maps/geo?q=".urlencode($q)."&sensor=false&oe=utf8&gl=en&output=csv&key=".$api_key,"r");
$data = fgetcsv($handle);
where $q is your query (the address of the gas station) and $api_key your Google Maps Api-Key. Now you have in $data[1] your latitude and in $data[2] your longitude. Additionally Google provides you with an accuracy for your search in $data[0]. I strongly recommend using this detail. For more information go to the Maps Api Reference. That’s it, you now got your coordinates and should save them to your database.
radius search with mysql
Now comes the search part. It’s not tricky at all, a little math will do the thing. First of all you need a search form. When the form is submitted you should begin with once again querying Google maps for the coordinates your user submitted in the search form. This step is easy, since it is the same as above (and this time you should really consider using the accuracy Google gives you back!!!).
Now that you have the coordinates of the searchers position, you query your database for any gas stations in the circumference of the given coordinates. I’ll do it within a range of ten kilometers, but this is of course at your choice (better: at your user’s choice). Let’s say you have a table called `stations`, with a primary key called `id` and the station’s `name`, additionaly the coordinates are saved in `latitude` and `longitude`. Here’s how you do your query:
SELECT
`id`,
`name`,
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 AS `distance`
FROM `stations`
WHERE
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 < 10
ORDER BY `distance`
where $fLat is the searchers latitude and $fLon the searchers longitude. The ten in the where clause is the range you want to search within. Remember: This search is based on kilometers. If you want to search in miles, you need to change the 6380 (whoch is our earths radius) to its respective value in miles. That’s it! You now got your result set with all stations in the ambit of ten kilometers.
Leave a Reply