How to get the near by cities in database using latitude/longitude

By | 16th March 2019

If you want to found near by city or near by latitute / langitute, first you create the MySQL table with lat and lng attributes. Your table should also have an id attribute to serve as the primary key.

INSERT INTO markers (name, lat, lng) VALUES (‘Delhi’,’28.7041′,’77.1025′);
INSERT INTO markers (name, lat, lng) VALUES (‘Noida’,’28.5355′,’77.3910′);
INSERT INTO markers (name, lat, lng) VALUES (‘Gurugram’,’28.4595′,’77.0266′);
INSERT INTO markers (name, lat, lng) VALUES (‘Mumbai’,’19.0760′,’72.8777′);
INSERT INTO markers (name, lat, lng) VALUES (‘Surat’,’21.1702′,’72.8311′);
INSERT INTO markers (name, lat, lng) VALUES (‘Pune’,’18.5204′,’73.856255′);


To find locations in your markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula.

$origLat = 42.1365;

$origLon = -71.7559;

$dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search

$query = "SELECT name, latitude, longitude, 3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2) +COS($origLat*pi()/180 )*COS(latitude*pi()/180) *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) as distance FROM markers WHERE longitude between ($origLon-$dist/cos(radians($origLat))*69) and ($origLon+$dist/cos(radians($origLat))*69) and latitude between ($origLat-($dist/69)) and ($origLat+($dist/69)) having distance < $dist ORDER BY distance limit 100";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {

echo $row['name']." > ".$row['distance']."<BR>";

}

Category: PHP

Leave a Reply

Your e-mail address will not be published. Required fields are marked *