MySQL Nested Statement for Geographic Coordinates

Question

Not sure if this is a logic question, or a MySQL question... but, still need help.

I have a list of regions, with coordinates in each one. The database is about 115MB in size. Here's a sampling of the records:

|  PC1  |  PC2  |   CITY    | PROV  | AREACODE  |    LAT      |     LNG      | 
|  A1A  |  0B8  | ST. JOHNS |  NL   |    709    |47.5986880000|-52.7255210000|
|  A1A  |  0B9  | ST. JOHNS |  NL   |    709    |47.5505750000|-52.7460720000|

So on, and so forth...

What I need to do, is get the record in each city which represents the maximum latitude and longitude value, then the maximum latitude and minimum longitude value, then the record with the minimum latitudinal value and minimum longitudinal value, then the record with the minimum latitudinal value and the maximum longitudinal value.

What i started out with was:

SELECT MAX(lat), MAX(lng), MIN(lat), MIN(lng), city FROM places GROUP BY city

This gave me ONE record per city, with the MAX/MIN values I asked for, but those min and max represented individual queries for the minimum latitude, maximum latitude, etc... which gave me a pretty good estimation of area so I could draw a polygon over a Google Map... but they're squares, and there's lots of overlap, and I know I can make this better... these results represent the MAXIMUM north, south, east, west of ANY record in group of records of city..

What I want to do, is search for ONE record which has the max latitudinal and max longitudinal value, then the max latitudinal and min longitudinal value, ... etc..

Not sure how to nest these SQL statements... I tried a few IN statements, but always resulted in one record which didn't quite represent any of the max or min values.

Answer 1

No sure if I understood your question correctly, but you can look into the HAVING clause

SELECT lat, lng, city FROM places GROUP BY city HAVING lat=MAX(lat)

or the UNION

(SELECT MAX(lat), MAX(lng), city FROM places GROUP BY city)
 UNION
(SELECT MAX(lat), MIN(lng), city FROM places GROUP BY city)

or a mix of the two

(SELECT MAX(lat), MAX(lng), city FROM places GROUP BY city HAVING lat=MAX(lat))
 UNION
(SELECT MAX(lat), MIN(lng), city FROM places GROUP BY city HAVING lng=MIN(lng))

Answer 2

What I want to do, is search for ONE record which has the max latitudinal and max longitudinal value

But there almost certainly won't be ONE record that has both the max lat and the max long. What is your desired result when one place is further north but another place is further east?

Are you looking for the ‘most north-easterly’ place per city? In which case:

SELECT * FROM places WHERE city='ST. JOHNS'
ORDER BY MAX(lat+lng) DESC LIMIT 1;

and similarly with MIN and/or lat-lng for the other diagonal directions.

To get the most north-easterly for each city, you have a ‘per-group maximum’ problem, which is a common SQL trouble. See eg this question for discussion. An example of most-northeasterly-place-per-city:

SELECT p0.*
FROM places AS p0
LEFT JOIN places AS p1 ON p1.city=p0.city AND p1.lat+p1.lng>p0.lat+p0.lng
WHERE p1.city IS NULL

However these computed-order queries are unindexable so likely inefficient unless you actually add an indexed column for lat+lng.

Then you have the problem of trying to fit all four queries (the NE, SE, SW and NW versions) into the same query. The best way to solve this problem is usually not to: Do four separate queries. Typically, the various contortions you get yourself into trying to squeeze the four separate operations into one query leave you with something less efficient and much less readable than four separate queries.

In any case I don't really see where any of this gets you. If you are trying to draw a minimal non-rectangular poly around a set of map points, you would have to select every point for a city and use a convex hull algorithm or a quadtree of some sort. Far too involved to do in SQL.