Get all records from MySQL database that are within Google Maps .getBounds?

Ok I have a database with about 1800 rows, each has a column lat and long, what I am trying to do, it query against Google Maps V3 .getBounds The result is something like ((33.564398518424134, -79.38014701875002), (35.375726155241175, -76.08424858125002)) What I want to do, is get every record whose lat & lng in the database is within those bounds. Any suggestions?

Im thinking I would have to extrapolate the other 2 corners of the map, since .getBounds only contains 2 points (2 corners of the map I suppose), and I would want to be within 4 points. If I did that I would end up with an array something like...Now correct me if I am wrong but this should be NW, NE, SW, SE corners of the map, in lat-lng.

33.564398518424134, -79.38014701875002
33.564398518424134, -76.08424858125002
35.375726155241175, -79.38014701875002
35.375726155241175, -76.08424858125002

Ok, so If I have this data, how would I build a query to get the rows that are within these coordinates? Table is called tilistings - columns are lat and lng ...also if there is an easier way, or I am just crazy with my methodology, please feel free to let me know.

Thanks

All previous answers only work for 1/4 of the world!

W3t Tr3y's answer was close, but it had an error (extra "<").

All of them only work for USA, because it's in the NORTH hemisphere. They don't work for the south hemisphere, nor eastern countries (right of greenwich).

Here's a simple solution without functions or complicated stuff.

letters are the results in the order you get them from map.getBounds() i.e. swlat, swlng, nelat, nelng = a, b, c, d.

SELECT * FROM tilistings WHERE
(CASE WHEN a < c
        THEN lat BETWEEN a AND c
        ELSE lat BETWEEN c AND a
END) 
AND
(CASE WHEN b < d
        THEN lng BETWEEN b AND d
        ELSE lng BETWEEN d AND b
END) 

or another solution with and/or (you can test it for speed, I don't know how to run it more than once in WorkBench)

SELECT * FROM tilistings WHERE
(a < c AND lat BETWEEN a AND c) OR (c < a AND lat BETWEEN c AND a)
AND 
(b < d AND lng BETWEEN b AND d) OR (d < b AND lng BETWEEN d AND b)

Now you can map the whole world :) I hope someone can mark the above answers as incorrect, because they are making us lose a lot of time to millions of learning people like me. I wonder how they get so many votes when they actually dont work!

PS: The chances of of your map pixel edge matching the exact 15 decimals value of the coordinates is zillions of times less than the chances of missing a whole 3/4 of the world!

if from Google: ( (a, b), (c, d) )

SELECT * FROM tilistings WHERE lat > a AND lat < c AND lng > b AND lng < d

I haven't used Google's API, but my understanding is that if you get ((33.564398518424134, -79.38014701875002), (35.375726155241175, -76.08424858125002)) back from a getBounds call then (33.564398518424134, -79.38014701875002) is the Southwest corner and (35.375726155241175, -76.08424858125002) is the Northeast. I say that as I think they return the Northeast and Southwest corners and I'm assuming the points are latitude, longitude.

If that's correct, then Bensiu's query would work. Typically using BETWEEN is more efficient.

SELECT * FROM tilistings WHERE lat BETWEEN a AND c AND lng between b AND  d

I now it's to late for the comment, but maybe it will be usefull for someone. sergio unswer is not quite correct the query for whole world has to look a little bit different, if I'm not mistaken something like this one:

SELECT * FROM tilistings WHERE (sw_lat < ne_lat AND lat BETWEEN sw_lat AND ne_lat) OR (sw_lat > ne_lat AND (lat BETWEEN sw_lat AND 180 OR lat BETWEEN -180 AND ne_lat)) (sw_lon < ne_lon AND lon BETWEEN sw_lon AND ne_lon) OR (sw_lon > ne_lon AND (lon BETWEEN sw_lon AND 180 OR lon BETWEEN -180 AND ne_lon))

A slightly simplified version of Vladimir's answer is working perfectly for me.

Taking an area bound by a box with a southern edge (south), a western edge (west), a northern edge (north) and an eastern edge (east). These can be derived from the Google Map .getBounds which provide south-west corner and north-east corner - you only need the two corners as these fully describe the square bounding box.

In this example our database table is called locations and contains a column for latitude and longitude.

SELECT * FROM locations WHERE
(latitude BETWEEN south AND north) AND
((west < east AND longitude BETWEEN west AND east) OR
 (west > east AND (longitude BETWEEN west AND 180 OR longitude BETWEEN -180 AND east)))

This works based on the fact that we only need to account for crossing 180/-180 longitude line as a special case - i.e. where the western longitude is a higher value than the eastern longitude. In any case where we do not cross that line the western longitude will always be less than the eastern longitude.

With regards to latitude, the southern edge will always be a lower value than the northern latitude as there is no concept of wrapping around over the poles. To cover an area over the north pole, for example we simply have all longitudes (i.e. from -180 to 180) and latitude from the southern boundary to +90.

From my Gist https://gist.github.com/jesuGMZ/0d7f38d80e2f67d0bc4b7fb620345344

Having MySQL >5.7 with a table that contains a column type POINT named location and the following Google Maps response:

"geometry": {
    "bounds": {
        "northeast": {
            "lat": 40.5638447,
            "lng": -3.5249115
        },
        "southwest": {
            "lat": 40.3120639,
            "lng": -3.8341618
        }
    },
    //....

you can perform a SQL query to retrieve all your locations contains in that boundary like this:

SELECT * FROM my_table
WHERE Contains(
  ST_MakeEnvelope(
    ST_GeomFromText('POINT(40.5638447 -3.5249115)'),
    ST_GeomFromText('POINT(40.3120639 -3.8341618)')
  ),
  location
);

Consider to index location to improve the performance of your queries if apply. Also, is possible to use Within instead of Contains changing the order of the parameters.

Useful links:

• https://dev.mysql.com/doc/refman/5.7/en/gis-general-property-functions.html
• https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-mbr.html
• https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-makeenvelope
• https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html

This works for me:

$condition1 = $a < $c ? "lat > $a AND lat < $c" : "lat > $a OR lat < $c";
$condition2 = $b < $d ? "lon > $b AND lon < $d" : "lon > $d OR lon < $b";
$q = "SELECT * FROM tilistings WHERE ( $condition1 ) AND ( $condition2 )";

We can find result between Maps.getBounds northEast and southWest latitude and northEast and southWest longitude using below query.

Search query should be between northEast.latitude AND southWest.latitude AND northEast.longitude AND southWest.longitude

$nelat=$_GET['nelat']-0.5;
$nelng=$_GET['nelng']-0.5;
$swlat=$_GET['swlat']-0.5;
$swlng=$_GET['swlng']-0.5;

$sql ="SELECT * FROM tablename where (CASE WHEN ".$nelat." < ".$swlat."
    THEN s.latitude BETWEEN ".$nelat." AND ".$swlat."
    ELSE s.latitude BETWEEN ".$swlat." AND ".$nelat."
END) 
AND
(CASE WHEN ".$nelng." < ".$swlng."
        THEN s.longitude BETWEEN ".$nelng." AND ".$swlng."
        ELSE s.longitude BETWEEN ".$swlng." AND ".$nelng."
END)";

Take a look at the new spatial data and functions available in MySQL 5.7, now also for InnoDB.

Using the examples above, it takes about 1s for a table with 1 mio records to get the locations within a certain bounding box.

With ST_Within() and ST_MakeEnvelope() and the correct spatial index I get the result in less the 0.01s.

I'm not sure the above statement is correct. Firstly, I believe the corners are nelat, nelng, swlat, swlng not as stated above "swlat, swlng, nwlat, nwlng". Secondly I believe the nelat is always going to be higher than the swlat of the window (unless the window covers one of the geographic poles which I'm not sure is even possible with google maps).

Finally, if the window covers the international date line (approx +/-180lng) i.e d < b then surely it should be "NOT BETWEEN b AND d". Perhaps someone with more specialist knowledge could confirm?