How to get average of lat/long from cities by county that they're in

Question

I found this script but I'd like to get the average of the latitudes/longitudes so I can just have the latitude/longitude of the counties. I tried

SELECT id, CITY, COUNTY, LONGITUDE, LATITUDE, AVG(LONGITUDE) AS val_1, AVG(LATITUDE) AS val_2
FROM directory_platform_cities 
GROUP BY id, COUNTY;

But it's just attaching the lat/long of the first city to the county, is there a query that can do this? My intention is to get the latitude/longitude of the counties using the coordinates of their respective cities.

Answer 1

Is this what you are looking for:

mysql> SELECT country, AVG(lat), AVG(lng) FROM c2014 GROUP BY country;
+---------+---------------------+---------------------+
| country | AVG(lat)            | AVG(lng)            |
+---------+---------------------+---------------------+
| ad      |  42.527902188508406 |   1.537318801102431 |
| ae      |  24.891538320635465 |   55.51244805853463 |
| af      |   34.04674051673764 |   67.26681304757301 |
| ag      |  17.085518998526485 |  -61.79143601297681 |
| ai      |   18.21587371826172 | -63.039283025832404 |
| al      |  41.088975844950475 |  20.014610561816568 |
| am      |   40.26937918217537 |   44.84238364011771 |
| an      |  13.779007457889145 |  -67.10738386983765 |
| ao      | -10.394024888552527 |  16.401272589394896 |
| ar      |  -32.13739248870794 |  -63.40704697093584 |
| at      |   47.62893757137828 |  14.375785999308414 |
| au      |  -32.58224675171816 |  144.07049698825728 |
| aw      |  12.509566132918648 |  -69.99144844386889 |
| az      |  40.179647407439965 |   47.49281764155795 |
| ba      |   44.13574533529882 |   17.93174898133396 |
| bb      |    13.1662623028257 | -59.554101581004126 |
...
| yt      | -12.817898351638043 |   45.14693607267786 |
| za      | -29.213355151321988 |  27.271365789847575 |
| zm      |  -13.74802438257624 |  27.937999186534476 |
| zr      |  -3.150024081275917 |    23.1336162007831 |
| zw      | -19.047764062703678 |  29.515317293888238 |
+---------+---------------------+---------------------+
234 rows in set (1.22 sec)

This pseudo-centroid is easy to compute, and it "reasonable" for most countries. But it can be quite far from any sort of "geographic" centroid. Algeria is easy to understand:

The pseudo-centroid for Algeria (country code DZ), for example, (lat: 35.6, lng: 3.24) is about 75 miles (120 km) from the Mediterranian, yet the country is about 1200 mi / 2000 km from north to south. The issue is that most cities are very near the coast, not scattered around the Sahara. So, the pseudo-centroid found from city lat-lngs is skewed far to the north.

A similar problem happens with cities that have annexed a lot of green space. Or counties with all their cites on a river; etc.

I looked for, but failed to find, a country where the pseudo-centroid was clearly in another country. For the curved country of Viet Nam, it almost lands in Laos. Several countries (eg, New Zealand) have it land off the coast; but I decided that does not really count.

Answer 2

My intention is to get the latitude/longitude of the counties using the coordinates of their respective cities.

This is insane and you should never do this. Counties aren't geographic and this isn't how GIS works. Moreover the cities inside the counties are totally useless for determining where the county is. Cities are near rivers, costs, railroads, and roads. Counties are just political hacks to disempower minorities, and make sure rich ranches in unincorporated territories have enough police to keep out ex-slaves.

So if you have to use counties, what you want you to do is use them as they are and not try to make sense of them.

1. Download the Cenus Shape Files for your state. They're distributed in the TIGER data set by state, and they have one or more polygons that represents each state's counties!
2. Run a query using ST_Within to determine what county your points are in.

There is no need to find any specific point in that polygon because it makes no sense.