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

Question

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

Answer 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!

Answer 2

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

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