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!
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…