Radius Search PHP, MYSQL and Google Maps

Question

Im searching a database with the latitude and longitude of a location. I want to retrieve all of the locations within a certain radius.

I then encode the returned results into a JSON and retrieve the data using ajax, however I get an undefined error meaning that there is no data returned from the database.

Can anybody see where im going wrong?

Heres my query

$sql="SELECT *, ACOS( SIN( RADIANS( `lat` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `lat` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `lng` ) - RADIANS( $fLon )) ) * 6380 AS `distance`
FROM `markers`
WHERE ACOS( SIN( RADIANS( `lat` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `lat` ) )
    * COS( RADIANS( $fLat )) * COS( RADIANS( `lng` ) - RADIANS( $fLon )) ) * 6380 < 10
    ORDER BY `distance`";


    $result = mysql_query($sql);

    while($r = mysql_fetch_assoc($result)) $rows[] = $r;

    echo json_encode($rows); 

Solution 1

I'm not much help with trig or SQL, I've just seen enough Google maps questions to point you to this tutorial:

https://developers.google.com/maps/articles/phpsqlsearch#findnearsql

There is a query for finding spots by distance in a radius that you can try, hope it works for you.

Use 3959 (in miles) or 6371 (in km)

Solution 2

Try this one:

$sql="SELECT * , ACOS( SIN( RADIANS( 'lat' ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( 'lat' ) ) * COS( RADIANS( $fLat )) * COS( RADIANS( 'lng' ) - RADIANS( $fLon )) ) * 6380 AS distance FROM 'markers'
WHERE  distance < 10
ORDER BY distance";

$result = mysql_query($sql);

$rows = mysql_fetch_assoc($result));

echo json_encode($rows);  `

Comments

[earth_tom]

Suggestion: Try the query without the WHERE clause (or a simpler WHERE clause) and see if you can get some results first. Divide and conquer to isolate the source of the issue. looking at the distances that result might be informative.

[lnelson92]

Thanks, ive already looked at that tutorial. And copied all the code in (changing the relavant bits) and that returns an empty xml document .

[Heitor Chang]

Sorry it wasn't helpful. If you leave out the distance < 25 part, or increase it a lot, say thousands, I figure it should return several markers. If not, could it be a type mismatch in the database? The math might be processed incorrectly.