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?

It will depend on where you are in the world. What value are you getting for longitude? What country are you looking at? Around the international date line there are several longitudes that refer to the same line of longitude (-180, +180, etc) — geocodezip, Oct 23 '19 at 14:09
Hi @geocodezip, I am in Australia. Here is the sample lat/long (lat: -37.8182133, lng: 144.968059). When I am zoomed out to see the whole country, here is the lat/long `NW: 13.6093816, -9.851099` | `NE: 13.6093816, -107.585474` | `SE: -69.5860379, -107.585474` | `SW: -69.5860379, -9.851099` — Sam, Oct 24 '19 at 22:29
Your question is clear. Still you should post 2-3 valid sample data as it is store in DB. what you will pass as parameter ?What output you are expecting ?Then you will get lot of answer. — KumarHarsh, Oct 31 '19 at 08:35

Salman A · Accepted Answer · 2019-10-30T11:55:10.293

3

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

edited Oct 30 '19 at 11:55

answered Oct 29 '19 at 13:37

Salman A

262,204
82
430
521

1

Having `lat1>lat2` is a bit of a weird case anyway. It means you are looking at at region that contains the North pole. In those cases we are not really talking about a "bounding box" anymore. The region will appear to be a wedge or fan-shaped in some way. – Carsten Massmann Oct 30 '19 at 06:29
@cars10m I have added a jsFiddle. I failed to add that lat1 and lat2 are latitudes of north-east and south-west corners of bounding box respectively. lat1 >= lat2 is normal while lat1 < lat case does not apply to Google Maps (it does not _tile_ the map top to bottom). – Salman A Oct 30 '19 at 12:02
It worked!!! I think it will be lots faster than doing the union! You are amazing @SalmanA. Thank you! – Sam Nov 01 '19 at 08:22

score 1 · Answer 2 · answered Oct 28 '19 at 10:50

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

If the left is greater than right then why not clamp left to -180 and right to 180? — Salman A, Oct 29 '19 at 11:47

SQL select latitude and longitude point within bounding box

2 Answers2