SQL query, select nearest places by a given coordinates

Question

I have $latitude = 29.6815400 and $longitude = 64.3647100, now in MySQL I would like to take the 15 nearest places to these coordinates and I'm planning to do this query:

SELECT *
FROM places
WHERE latitude  BETWEEN($latitude  - 1, $latitude  + 1)
AND   longitude BETWEEN($longitude - 1, $logintude + 1)
LIMIT 15;

Do you think it's correct or do you suggest something else?

How to do the BEETWEEN, since I want to search trough a maximum of 50Km range the near places?

I forgot to say that I can also use PHP for do anything before to run the query.

Note: I can't use stored procedures.

@mamdouhalramadan nope , for that i asked if is that correct — itsme, Jan 10 '13 at 09:40
then. sure it is not correct. because this conditions will get you what you have in your db based on fixed condition and what you need is a dynamic approach. like using euclidean distance. but this needs stored procedures!!! — mamdouh alramadan, Jan 10 '13 at 09:45
what you think about this ? http://fr.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL — itsme, Jan 10 '13 at 09:46
it's exactly what you need. but preferably, you use stored procedures in your case. or if you are using php to do it will be great to. — mamdouh alramadan, Jan 10 '13 at 09:48
yep would like to understand how to in php, cause i really don't want anything else a query in mysql — itsme, Jan 10 '13 at 09:50
then please retag (reform) your question and add php, and I'll post an answer for you to do it with php. — mamdouh alramadan, Jan 10 '13 at 09:52

score 19 · Accepted Answer · edited Aug 19 '14 at 16:25

19

here’s the PHP formula for calculating the distance between two points:

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') 
{
   $theta = $longitude1 - $longitude2;
   $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))+
               (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
   $distance = acos($distance); $distance = rad2deg($distance); 
   $distance = $distance * 60 * 1.1515;

   switch($unit) 
   { 
     case 'Mi': break;
     case 'Km' : $distance = $distance * 1.609344; 
   } 
   return (round($distance,2)); 
}

then add a query to get all the records with distance less or equal to the one above:

$qry = "SELECT * 
        FROM (SELECT *, (((acos(sin((".$latitude."*pi()/180)) *
        sin((`geo_latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
        cos((`geo_latitude`*pi()/180)) * cos(((".$longitude."-
        `geo_longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) 
        as distance
        FROM `ci_geo`)myTable 
        WHERE distance <= ".$distance." 
        LIMIT 15";

and you can take a look here for similar computations.

and you can read more here

Update:

you have to take in mind that to calculate longitude2 and longitude2 you need to know that:

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles. This is convenient because each minute (1/60th of a degree) is approximately one mile.

A degree of longitude is widest at the equator at 69.172 miles (111.321) and gradually shrinks to zero at the poles. At 40° north or south the distance between a degree of longitude is 53 miles (85 km).

so to calculate $longitude2 $latitude2 according to 50km then approximately:

$longitude2 = $longitude1 + 0.449; //0.449 = 50km/111.321km
$latitude2 = $latitude1 + 0.450; // 0.450 = 50km/111km

edited Aug 19 '14 at 16:25

Vikas Arora

1,666
2
17
38

answered Jan 10 '13 at 10:07

mamdouh alramadan

8,349
6
36
53

this not require stored procedures right? :P – itsme Jan 10 '13 at 10:08
no this does not require stored procedures. – mamdouh alramadan Jan 10 '13 at 10:10
wonderfull i'll test that later watching also about performance benchmark and i'll tell you or directly accept the answer! – itsme Jan 10 '13 at 10:16
there is a problem i do not have **$latitude2, $longitude2** they should be dynamically calculated, the only thing i have is **a place lat and long** and i know that there are some places near in a range of 50 kilometers , is it clear? asking this cause don't know if my english is ok – itsme Jan 10 '13 at 12:58
yes it's clear. give me a minute to check it up. – mamdouh alramadan Jan 10 '13 at 13:07
ok take your time too :D – itsme Jan 10 '13 at 13:15
Ok here it is, you need to get the distance in a range here so you can specify the area of search. – mamdouh alramadan Jan 10 '13 at 13:17
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22520/discussion-between-mamdouh-alramadan-and-badaboooooom) – mamdouh alramadan Jan 10 '13 at 13:17
1

updated the query to fit the KM case. with some error modifications :) – mamdouh alramadan Jan 10 '13 at 16:34
@Badaboooooom - hope you take a look to the update :) – mamdouh alramadan Jan 11 '13 at 16:58
thanks for all and for the chat we had, finally i fixed! – itsme Jan 11 '13 at 17:02
hope I helped. welcome :) – mamdouh alramadan Jan 11 '13 at 17:15
1

You should have an ORDER BY to return the results in order of ascending distance. – stackoverflowuser2010 Feb 03 '14 at 23:09

Valerio Bozz · Answer 2 · 2022-05-16T17:17:20.900

17

As a rule of thumb, always try to do not flood MySQL with heavy queries.

Instead, you can speculate a very-fast and very-optimized SQL query selecting all the places with coordinates inside a simple square of side $radius, instead of selecting suddenly a perfect circle with that radius. Then, PHP or whatever can quickly filter the surplus.

Let me show the concept in PHP:

// your input variables
$lat    = 45.6072;
$lon    = 7.65678;
$radius = 50; // expressed in Km

// every latitude degree° is ~ 111Km
$angle_radius_lat = $radius / 111;

// longitude takes into account equator distance
$angle_radius_lon = $angle_radius_lat * cos( deg2rad( $lat ) );

// define a simple square with your lat/lng as center
$min_lat = $lat - $angle_radius_lat;
$max_lat = $lat + $angle_radius_lat;
$min_lon = $lon - $angle_radius_lon;
$max_lon = $lon + $angle_radius_lon;

// query places inside the square
// use here your own database function
$results_raw = query_results( "SELECT latitude, longitude FROM ... WHERE latitude BETWEEN $min_lat AND $max_lat AND longitude BETWEEN $min_lon AND $max_lon" );

// filter the surplus outside the circle
$results = [];
foreach( $results_raw as $result ) {

    if( getDistanceBetweenPointsNew( $lat, $lon, $result->latitude, $result->longitude, 'Km' ) <= $radius ) {
        $results[] = $result;
    }

}

// these are your places inside the circle of your radius
var_dump( $results );

In this way MySQL runs a very friendly query that does not cause a full table scan, while PHP strips out the surplus with something similar to the getDistanceBetweenPointsNew() function posted in this page, comparing the distance from the coordinates of the result set to the center of your radius.

Important: in order to do not waste the big performance gain, create a database index on your coordinates columns (latitude and longitude). Happy hacking!

edited May 16 '22 at 17:17

answered Mar 03 '15 at 21:36

Valerio Bozz

1,176
16
32

2

Your solution is very fast but there is a small error. For longitude, angle radius depend on the latitude: to calculate $min_lon et $max_lon, use $angle_radius = $radius / (111*cos($lat)); – Eric JOYÉ Mar 30 '17 at 11:20
Thanks for posting an excellent, practical answer – Ricalsin Apr 15 '17 at 21:49
Totally agree with @EricJOYÉ calculation using `cos($lat)` and it's not a small error, it's big. I get 15km difference in 50km. That's not acceptable at all. – Ergec Oct 18 '17 at 07:11
Thanks for the correction. – Valerio Bozz Feb 12 '18 at 09:57
I always thought that sql queries are faster than loop over results in code. Is that true? – IgniteCoders Apr 23 '19 at 11:21
1

@IgniteCoders Well, it depends from the query complexity and the dimension of your result set. The most important thing is to avoid a database full table scan, and with this approach you avoid it. – Valerio Bozz Apr 23 '19 at 18:01
Hi @EricJOYÉ and Ergec. Thank you again for your comments, now integrated in the answer. As per StackOverflow guidelines, if your comment is no longer updated with the answer, please delete. Otherwise it might look like that there is still a bug in the question. I will do the same them. Thank you so much! – Valerio Bozz May 13 '22 at 10:39
Also note that `cos($lat)` is not correct since `$lat` is not in radiants so I've now expressed it as `cos( deg2rad( $lat ) )` in the answer. – Valerio Bozz May 16 '22 at 17:34

score 2 · Answer 3 · edited Jun 02 '13 at 17:01

I've done something similar with a selling houses app, ordering by distance from a given point, place this in your SQL select statement:

((ACOS(SIN(' . **$search_location['lat']** . ' * PI() / 180) * SIN(**map_lat** * PI() / 180) + COS(' . **$search_location['lat']** . ' * PI() / 180) * COS(**map_lat** * PI() / 180) * COS((' . **$search_location['lng']** . ' - **map_lng**) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS "distance"

Replace $search_location with your relevant lat/lng values and the map_lat/map_lng values are the SQL columns which contain the lat/lng values. You can then order the results by distance and either use a where or having clause to filter our properties within a 50km range.

I would recommend using SQL as the approach compared to PHP in the event you require additional functionality such as paging.

nice but main problem now is exactly to set the 50km example range :P — itsme, Jan 10 '13 at 14:36

score 0 · Answer 4 · answered Mar 14 '17 at 20:55

A bit late but it may help someone - if you want the nearest city by location, I wouldn't go on distance because then an isolated location wouldn't retrieve anything. Try this:

$G_how_close_to_find_cities = "1.1"; // e.g. 1.1 = 10% , 1.2=20% etc
$G_how_many_cities_to_find_by_coordinates = "10";
$query = "SELECT * from Cities  WHERE 
                        Cities__Latitude <= ('".$latitude*$G_how_close_to_find_cities."') AND Cities__Latitude >= ('".$latitude/$G_how_close_to_find_cities."') 
                    AND Cities__Longitude <= ('".$longitude*$G_how_close_to_find_cities."') AND Cities__Longitude >= ('".$longitude/$G_how_close_to_find_cities."') 
                    ORDER BY SQRT(POWER((Cities__Latitude - ".$latitude."),2)+POWER((Cities__Longitude - ".$longitude."),2)) LIMIT 0,".$G_how_many_cities_to_find_by_coordinates;

SQL query, select nearest places by a given coordinates

4 Answers4

Linked

Related