CODEWITHSUNDEEP
mysqllatitude-longitude
Stephen Watkins
Stephen Watkins

Reputation: 25765

How can I query for all records within latitude / longitude area?

I am looking to create a MySQL query that finds all records that are within a specified area of the world. Each record contains a point (lat/lon), and I have the top-right (lat/lon) and bottom-left (lat/lon) corners of the specified area.

With this information, how can I find the appropriate records?

Upvotes: 3

Views: 1754

Answers (2)

WWW
WWW

Reputation: 9860

LAT1 = MIN(top-right lat, bottom-left lat)

LAT2 = MAX(top-right lat, bottom-left lat)

LON1 = MIN(top-right lon, bottom-left lon)

LON2 = MAX(top-right lon, bottom-left lon)

SELECT fields
FROM points
WHERE lat BETWEEN LAT1 AND LAT2
AND lon BETWEEN LON1 AND LON2

This way, the query should handle if you cross the Prime Meridian or the equator with your box.

To handle the 180th meridian (or antimeridian), you would need to compare the right-lon to the left-lon, checking to see if the right number is negative and the left number is positive. If so, then you've crossed the 180th meridian. Your query would then have to look something like this:

SELECT fields
FROM points
WHERE lat BETWEEN LAT1 AND LAT2
AND (lon BETWEEN -180 AND LON1 OR lon BETWEEN LON2 AND 180)

I'd rather not think of how to handle a box that sits on the top or bottom of the planet over one of the true poles, though. =)

Upvotes: 6

Whetstone
Whetstone

Reputation: 1199

Just look for all points that are less than the right edge and greater than the left edge, which being less than the bottom edge and greater than the top edge.

Point at 4,4 and your top right is (5,5) and bottom left is (3,3) - 3<4<5 for x and 3<4<5 for y. You've got a match.

Upvotes: -1

Related Questions