Get Nearest Places on Google Maps, Using MySQL Spatial Data

Get nearest places on Google Maps, using MySQL spatial data

You just need use following query.

For example, you have input latitude and longitude 37 and -122 in degrees. And you want to search for users within 25 miles from current given latitude and longitude.

SELECT item1, item2, 
    ( 3959 * acos( cos( radians(37) ) 
                   * cos( radians( lat ) ) 
                   * cos( radians( lng ) 
                       - radians(-122) ) 
                   + sin( radians(37) ) 
                   * sin( radians( lat ) ) 
                 )
   ) AS distance 
FROM geocodeTable 
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

If you want search distance in kms, then replace 3959 with 6371 in above query.

You can also do this like:

Select all Latitude and longitude
Then calculate the distance for each record.
The above process can be done with multiple redirection.

For optimizing query you can use Stored Procedure.

And this can also help you.

Creating a Store Locator with PHP, MySQL & Google Maps a little different

This example takes your query (and you'll need to edit the 45.515038 and 25.366935 with the lat/lng for each objective) and outputs it as a JS array of arrays (you could make it more formal JSON if you like)

It then loops through that array, making markers for each and placing them on a map. Finally, it adds a click listener to each so that it'll display relevant information.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
<style>
#map_canvas{width:500px;height:500px;}
</style>
</head>

<body>
<div id="map_canvas"></div>
<script>
<?php
//you'll first need to connect to your db
//you also have to edit the lat/lng in your SELECT statement to be that of your objective
$result = mysql_query("SELECT  latitudine, longitudine, nume,  poze, descriere, link, (
(
    ACOS( SIN( 45.515038 * PI( ) /180 ) * SIN( latitudine * PI( ) /180 ) +
          COS( 45.515038 * PI( ) /180 ) * COS( latitudine * PI( ) /180 ) *
          COS( ( 25.366935 - longitudine ) * PI( ) /180 )
    ) *180 / PI( )
) *60 * 1.1515 * 1.609344
) AS distance
FROM `unitati`
HAVING distance <= '10'
ORDER BY distance ASC
LIMIT 0 , 30");
$c=0;
echo "var data=[";
while($markers=mysql_fetch_array($result)){
    if($c>0){echo ",";}
    echo "['".$markers[0]."','".$markers[1]."','".$markers[2]."','".$markers[3]."','".$markers[4]."','".$markers[5]."','".$markers[6]."'"."]";
    $c++;
}
echo "];";
?>
    //var data=[['45','-73','home','poz1','desc1','link1'],['43','-75','work','poz2','desc2','link2']];
    var places=new Array();
    var map;
    var MyInfoWindow = new google.maps.InfoWindow({content: 'Loading...'});
    var bounds = new google.maps.LatLngBounds();
    var myOptions = {
        zoom: 9,
        mapTypeControl: false,
        mapTypeId: google.maps.MapTypeId.ROADMAP
    };
    map = new google.maps.Map(document.getElementById('map_canvas'),myOptions);
    for(i=0;i<data.length;i++){
        var point=new google.maps.LatLng(data[i][0],data[i][1]);
        var a = new google.maps.Marker({position: point,map: map, icon:'someHotelIcon.png'});
        a.lat=data[i][0];
        a.lng=data[i][1];
        a.nume=data[i][2];
        a.poze=data[i][3];
        a.desc=data[i][4];
        a.url=data[i][5];
        places.push(a);
    }
    for(i=0;i<places.length;i++){
        var point2=new google.maps.LatLng(places[i].lat,places[i].lng);
        bounds.extend(point2);
    }
    map.fitBounds(bounds);
    var objLoc=new google.maps.LatLng(45.515038,26.366935);
    var objectiveMarker = new google.maps.Marker({position: objLoc,map: map, icon:'objectiveIcon.png'});    //---------------Marker for objective
    for (var i = 0; i < places.length; i++) {
        var marker = places[i];
        google.maps.event.addListener(marker, 'click', function () {
        MyInfoWindow.setContent(this.nume+'<br/>'+this.desc+'<br/><a href=\"'+this.url+'\">link</a>');
        MyInfoWindow.open(map, this);
        });
    }
</script>
</body>
</html>

find people within X miles of my city

It sounds very simple ... you ask the user for his address and then request google maps api for the longtitude and latitude like this

http://maps.googleapis.com/maps/api/geocode/json?address=San+Stefano+1+Sofia&sensor=false

and write them in the database.

When the user searchest for nearby people

edit (not going to be a circle, but it will do the job):

SELECT * FROM `users` AS u
WHERE u.lat IS BETWEEN {$my_lat-100} AND {$my_lat+100}
AND u.alt IS BETWEEN {$my_alt-100} AND {$my_lat+100}

like in the post you've mentioned.

Find nearest locations from json Sencha Touch 2

You'd be better off doing that processing on the backend and sending only the close records back to the client. This will keep your client app from getting bogged down.

You can use Postgresql/Postgis for this if you store the lat/long points as spatial data. You can also do this with the MySQL spatial extensions. If you want to build the haversine formula into a MySQL function, you can draw 15 mile radii around all of your points and use MySQL's builtin 'within' function. If you'd rather use a flavor of NoSQL, then CouchDB has a nice GeoCouch extension which can perform the function you desire.

Get Locations Nearest to Longest Using Php?

If you want to do the ordering first and the limiting second (ie pick the $limt closest things (offset by $start) to ($lat,$long)), you can do it within MySQL:

$query = "SELECT *,
 ((ACOS(  SIN($lat*PI()/180)*SIN(Latitude*PI()/180) 
        + COS($lat*PI()/180)*COS(Latitude*PI()/180)*COS(($long-Longitude)*PI()/180
        )
  ) * 180/PI()
 )*60 * 1.1515) AS dist
FROM  tbl_MapDetails
ORDER BY dist 
LIMIT $start,$limt";

But if you want to do the $limt first and then sort by distance (so pick $limt items and then order them by distance -- may not contain the closest location though), use @SergeyRatnikov's answer, or you need to do a nested select as:

SELECT * from
 (SELECT *,
   ...[distancecalculation]... AS dist
   FROM tbl_MapDetails
   LIMIT $start,$limt)
ORDER BY dist

Get Nearest Places on Google Maps, Using MySQL Spatial Data