1

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.

  • I feel like if the coordinates of each city are their own center points than their average would be the coordinates of their respective county. – Dan185 Nov 29 '18 at 20:43
  • 1
    Please don't close, the query may have issues and not make geographic sense, but the question about sql is legitimate. @op group only by county, and remove on the select everything except the averages and the county field. – jynus Nov 29 '18 at 20:47
  • That did it. I think I had that at one point but was confused because there's so many duplicate city names/counties. – Dan185 Nov 29 '18 at 20:51
  • But why does it not make sense geographically? The average of each city would be the coordinates of the county – Dan185 Nov 29 '18 at 22:07
1

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.

0

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.

  • My reaction, too, was this only worked for convex hulls. Most of the population of Ontario is close to the Southern border so there's a reasonable chance it's pseudo-centroid is in the US state of Michigan. – Michael Green Dec 2 '18 at 11:09
  • There are also enclaves - parts of one country entirely within another. Belgium/Netherlands have enclaves within enclaves. Good luck averaging that. – Michael Green Dec 2 '18 at 11:11
  • Croatia would have to be a contender given its extended C-shape? – Michael Green Dec 2 '18 at 11:27
  • @MichaelGreen - 46.084, -81.697 - well inside Ontario, near Whitefish Falls. Enclaves between India and Bangladesh are quite a mess. Croatia - Yes: Near Gorinja: 44.839, 16.214 in Bosnia and Herzegovina. Not an enclave, but there is a place in Washington state where high school kids take a bus through Canada to get to school. – Rick James Dec 2 '18 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.