Finding a database with lat and long by radius using php mysql

Question

I am trying to develop a small site that extracts results from a database with a given radius and puts them on a map. I use google api maps to get latin and long criteria from there, pass them to my php script, which queries the database and returns the result set as a JSON object.

Im having a little problem sending both lat and long to php using json.

My main problem is that my sql for searching the database seems wrong, as it just retrieves the first 10 results in my database. I want it to return the first 10 results to the radius from the search point.

Here is my ajax code

function showCarPark(location) { var lat = location.lat(); var lng = location.lng(); //alert("Lat: " +lat.toFixed(6)); //alert("Lng: " +lng.toFixed(6)); document.getElementById('carParkResults').innerHTML = ""; var criterion = document.getElementById("address").value; var count = 0; $.ajax({ url: 'process.php', type: 'GET', data: "lat=" + lat + "&lng=" + lng, dataType: 'json', success: function(data) { jQuery.each(data, function() { $('<p>').text("Car Park: " + data[count].name).appendTo('#carParkResults'); placeCarParks(data[count].postcode,data[count].name, data[count].street, data[count].type); count++; }); }, error: function(e) { //called when there is an error console.log(e.message); alert("error" + e.message); } }); 

And here is my PHP script

 $rad = 20; $lat = $_GET['lat']; $lng = 1.4681464; //put a temporary number in as it wont pass the lng in the JSON $sql="SELECT *, (3959 * acos(cos(radians('".$lat."')) * cos(radians(lat)) * cos( radians(long) - radians('".$lng."')) + sin(radians('".$lat."')) * sin(radians(lat)))) AS distance FROM carpark HAVING distance < 15 ORDER BY distance LIMIT 0 , 10"; $result = mysql_query($sql); while($r = mysql_fetch_assoc($result)) $rows[] = $r; echo json_encode($rows); 

The columns in my table are called lat and long. Any help is appreciated.

Answer 1

In your case, try using WHERE instead of HAVING

 SELECT *, (3959 * acos(cos(radians('".$lat."')) * cos(radians(lat)) * cos( radians(long) - radians('".$lng."')) + sin(radians('".$lat."')) * sin(radians(lat)))) AS distance FROM carpark WHERE distance < 15 ORDER BY distance LIMIT 0 , 10 

Answer 2

I had no problems using HAVING. ILLin's answer is misleading. Here is an example of a valid query to find the distance from New York:

 SELECT *, (3959 * acos(cos(radians('40.7142')) * cos(radians(latitudeColName)) * cos( radians(longitudeColName) - radians('-74.0064')) + sin(radians('40.7142')) * sin(radians(latitudeColName)))) AS distance FROM myTable HAVING distance < 15 ORDER BY distance LIMIT 0 , 10 

Modify the line in the jQuery query to use this notation {name: value, name: value}:

  data: {lat : lat, long : long}, 

Answer 3

Larry Ullman did something very similar in his book PHP 5 Advanced: Visual Quickpro Guide.

Look at its sample code, try to calculate the distance:

 ROUND(DEGREES(ACOS(SIN(RADIANS($lat)) * SIN(RADIANS(latitude)) + COS(RADIANS($lat)) * COS(RADIANS(latitude)) * COS(RADIANS($long - longitude)))) * 69.09) AS distance 

Answer 4

 "SELECT *, ( 3959 * acos( cos( radians(".$lat.") ) * cos( radians( ".$lat." ) ) * cos( radians( ".$long." ) - radians(".$long.") ) + sin( radians(".$lat.") ) * sin( radians( ".$lat." ) ) ) ) AS distance FROM carpark HAVING distance < 15 ORDER BY distance LIMIT 0, 10" 

You need to correct your query in order to pull out the latitude and longitude values. You just pass the word lat / long in several places. Compare this query with yours to see what I mean.