SQL select latitude and longitude point within bounding box

user_4254283 0
Viewed 2217

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?

latitude-longitude google-maps sql-server sql
2 Answers

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

user_870150

I finally found the answer after looking at the tutorial of Latitude and Longitude.

In summary: Latitude is between -90 to 90 and Longitude is between -180 to 180

//Latitude
 90 ----------------------  90

  0 ----------------------   0

-90 ---------------------- -90

//Longitude
-180        0        180
  |         |         |
  |         |         |
  |         |         |
  |         |         |
-180        0        180

Now, the bounding box can overlap. So, the left can be greater than the right or the top can be greater than the bottom in the bounding box (rectangle). Based on How to search (predefined) locations (Latitude/Longitude) within a Rectangular, using Sql? (see the last answer), the solution is simply union all the combination depending on where the bounding box is.

If you can find a better and more efficient solution, I'll award the bounty to you :)

Cheers

user_42542830
Related