I'm making an API call which should return a list of points within certain area.
This will be used on a map, so the call to API has coordinates of all four corner points.
The area will always be a rectangle and will not be rotated.
At the moment, I'm using the following algorithm for limiting the results:
$lat_limits = array(min($bottom_left_lat, $top_left_lat), max($bottom_left_lat, $top_left_lat));
$lon_limits = array(min($top_left_lon, $top_right_lon), max($top_left_lon, $top_right_lon));
My questions are:
Thanks!
Simply:
WHERE lat BETWEEN $lat_min AND $lat_max AND lng BETWEEN $lng_min AND $lng_max
and put indexes on your lat and lng columns to better optimize the query.
Just bear in mind that if left_lng > right_lng your window straddles the date line and you need to split it into 2 boxes at +/-180 degrees.
Longitude generally increases from west to east with the exception of crossing the international date line where longitude changes from 180 degrees to -180 degrees. If you use the same query in a box that straddles this line you will get results for all longitudes outside the box. You need to detect the condition and split the query conditions into 2 boxes from lng_left to 180 and -180 to lng_right.