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

Solution 1:

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!

Solution 2:

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

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

Solution 3:

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