Skip to main content
fun
Source Link
Rick James
  • 79.4k
  • 5
  • 51
  • 117

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.

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.

Source Link
Rick James
  • 79.4k
  • 5
  • 51
  • 117

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)