SQL select latitude and longitude point within bounding box

Question

I am using google map and I would like to query my SQL to find all point within the bounding box.

In google map, I am using this javascript to get the bounding rectangle

var bounds = map.getBounds();
var ne = bounds.getNorthEast(), sw = bounds.getSouthWest();
var args = {
    NW: { lat: ne.lat(), lng: sw.lng() },
    NE: { lat: ne.lat(), lng: ne.lng() },
    SE: { lat: sw.lat(), lng: ne.lng() },
    SW: { lat: sw.lat(), lng: sw.lng() },
}; //NW = North-West, NE = North-East, SE = South-East, SW = South-West

I am, then, using LINQ to select all places from my SQL database:

//nw = North-West, ne = North-East, se = South-East, sw = South-West
double minLat = Math.Min(nw.Lat, Math.Min(ne.Lat, Math.Min(se.Lat, sw.Lat)));
double maxLat = Math.Max(nw.Lat, Math.Max(ne.Lat, Math.Max(se.Lat, sw.Lat)));
double minLng = Math.Min(nw.Lng, Math.Min(ne.Lng, Math.Min(se.Lng, sw.Lng)));
double maxLng = Math.Max(nw.Lng, Math.Max(ne.Lng, Math.Max(se.Lng, sw.Lng)));

return (from rec in tblPlaces.AsNoTracking()
        where (rec.Lat >= minLat) && (rec.Lat <= maxLat) && (rec.Lng >= minLng) && (rec.Lng <= maxLng)
        select rec).ToList<tblPlace>();

It works well when it is on quite a zoom (google zoom <= 15). But when zooming out to country size (ie. you can see the whole country), it doesn't find the point in my database.

On the debugging, I found the longitude number is way smaller than any point in my database. How is it possible? I zoomed out to see the whole country.

Is the way I select the latitude and longitude is wrong?

Answer 1

I am not an expert in geography but this looks simple. Let us start with longitude. The bounding box could be on one side or across the antimeridian:

-180         0         +180
  |                      |
  |       +-----+        |
  |   -10 |  x  | +10    |
  |       +-----+        |
  |                      |
  |                   +-----+
  |              +170 |  x  | -170
  |                   +-----+
  |                      |

A given longitude exists inside the bounding box if:

lng1 <= lng2 AND (lng1 <= lng AND lng <= lng2) /* both edges on same side */
OR
lng1 >  lng2 AND (lng1 <= lng OR  lng <= lng2) /* edges on opposite sides */

A given latitude exists inside the bounding box if:

lat1 >= lat2 AND (lat1 >= lat AND lat >= lat2) /* both edges on same side */
OR
lat1 <  lat2 AND (lat1 >= lat OR  lat >= lat2) /* edges on opposite sides */

If latitudes do not wrap around e.g. in Google Maps API then opposite sides test is not required.

Some tests on db<>fiddle and a jsFiddle showing how LatLngBounds work