SQL lat longs within lat long bounds

Question

I am using MySQL 5.5.52. Abbreviations: Lat = Latitude, Long = Longitude, NE = North East, SW = South West.

I have two SQL tables:

First table contains "Areas". Each Area consists of ID, NE Lat, NE Long, SW Lat, SW Long.

Second table contains "Points". Each Point consists of ID, Lat, Long.

I need a SQL statement that given a list of Area IDs will return the Points in those Areas. A Point is defined being in an Area if its Lat-Long lies within the square where the top-right and bottom-left corners are defined by the Area's NE Lat-Long and SW Lat-Long.

Note: I could change the Areas to be defined as a single Lat-Long with a radius, where Points are found within the 'circles'. But ideally I don't want to do this unless there is a much more efficient SQL statement to solve this way.

So something like this: WHERE (Points.Lat > Areas.SWLat AND Points.LAT < Areas.NWLat) AND (Points.Long > Areas.SWLong AND Points.Long < Areas.NWLong) ? I think a good question is why they aren't stored as spatial data (or are they)? If you update the post to include your DML it will help a lot. — Jacob H
The short answer is I've never stored spatial data in a DB before and didn't know the best way. RKRC's answer seems to do the job for me. Is there an advantage of doing it your way (in a where clause rather than a join)? — AndyW
For all intents and purposes they are (usually) the same thing. Between is inclusive so it's <= and >=. As for Join ON vs WHERE, they are usually the same execution. — Jacob H

RKRC RKRC · Accepted Answer · 2017-06-06T15:24:07

Join two tables based on condition that point location falls within area bounds.

Select areaTable.*, pointsTable.*
From pointsTable
Join areaTable
On pointTable.lat between areaTable.SWLat and areaTable.NELat
and
pointTable.long between areaTable.SWLong and areaTable.NELong

Gives you each area and the corresponding points.

Parameterize further if needed with where clause for specific area.

Where areaTable.NELat=@areaNELat
and areaTable.NELong=@areaNELong
and areaTable.SWLat=@areaSWLat
and areaTable.SWLong=@areaSWLong

Just substitute @s with the area lat and long values you like.

SQL lat longs within lat long bounds

1 Answers