Home is where the heart is. But is it Hart, Maryland, USA or Hart, Bayburt, Turkey? (XKCD 2480)

^{XKCD 2480, "No, the Other One". © Randall Monroe, 2021}

Another lightning round of XKCD implementations, this time looking for all the places people have to clarify they're from. I have a fair bit of personal connection with this, with many of my friends living and working in London. It was a common frustration for them venturing out of the country to explain they weren't flying across the pond, or at least The Pond to reach the States, hailing instead from London, Ontario with its sizeable population of >300k people. All living in the shadow of one of the largest and oldest cities in the world. Oof.

However, it turns out they are only runner-ups in the race to obscurity for their London namesake. No shortage of Ohioans, Kentuckians, and even... English? Yep. For the City of London, a city entirely distinct from the city we typically know as London, bequests a similar fate unto its ~8,000 inhabitants. (Obligatory CGP Grey video about this)

XKCD 2480 showed off a bunch more examples in the States, mostly living in the shadows of other American cities. America's love for Houston, Atlanta and Dallas is endearing... if not for the broken flight plans and ruined vacations in their wake.

To see how uncreative our towns' founders are, I dusted off the cities500 GeoNames dataset and the maps.lam.io codebase and threw together a search engine for those cities who begrudgingly share their namesakes. This is more or less the raw GeoNames Gazetteer data, with the alternate names filtered for duplicates and shovelled into a query-friendly Postgres schema. Just a many-to-one relationship of alternate names to each line of cities500.txt, to be more pedantic. (See the schema at the README for this dataset.). Try it below or at cities.lam.io

Superlatives

First some db setup:

SQL setup

To get some of the superlatives, we first prepare a convenience table of the top cities for each name:

-- `places` is just cities500.txt
-- `names` is the normalized table of place [1]->[*] alternate names

CREATE TABLE top_cities (
	geonameid INT REFERENCES places (geonameid) PRIMARY KEY,
	name VARCHAR(200),
	c INT
);

CREATE INDEX ON top_cities (name);

INSERT INTO top_cities
	SELECT t.geonameid, t.name, t.c FROM (
		SELECT p.geonameid, n.name, (RANK() OVER (PARTITION BY n.name ORDER BY p.pop DESC)) AS r, (COUNT(*) OVER (PARTITION BY n.name)) AS c
		FROM places p
		INNER JOIN names n ON n.geonameid = p.geonameid
		WHERE p.feature_class = 'P' -- grab only cities/villages/(etc.)
	) t WHERE r = 1;

Then another of the relative populations of the populous cities of each namesake vs. the rest:

CREATE TABLE pop_cmp (
	top_geonameid INT REFERENCES places (geonameid),
	name VARCHAR(200),
	poprest INT,
	c INT
);
CREATE INDEX ON pop_cmp (poprest);

INSERT INTO pop_cmp
	SELECT ptop.geonameid, t.name, SUM(prest.pop), COUNT(*) FROM top_cities t
		INNER JOIN places ptop ON ptop.geonameid = t.geonameid
		INNER JOIN names nrest ON nrest.name = t.name AND nrest.geonameid <> t.geonameid
		INNER JOIN places prest ON prest.geonameid = nrest.geonameid
		GROUP BY ptop.geonameid, t.name;

Then the fun part!

Results

All the queries really boil down to this:

SELECT * FROM (
	SELECT pc.name, p.country_code, (p.pop :: FLOAT) / pc.poprest AS frac, pc.c
	FROM pop_cmp pc
	INNER JOIN places p ON p.geonameid = pc.top_geonameid
	WHERE pc.poprest <> 0
) t
ORDER BY frac DESC LIMIT 10;

and its variations. Some of the interesting ones include:

Most lopsided (MAX(most populous city / rest))

	City	Province/State	Country	Population ratio (single largest pop. / Σrest)
1	Sanxay	Shanghai	China	32247.79
2	Malatya	Malatya	Turkey	22090.25
3	Bombay	Maharashtra	India	17151.13
4	Eko	Lagos	Nigeria	12820.51
5	Seulo	Seoul	South Korea	11882.1
6	Adalia	Antalya	Turkey	11664.43
7	Anfa	Casablanca-Settat	Morocco	8063.87
8	Belena	Pará	Brazil	7953.32
9	Moscu	Moscow	Russia	7500.88
10	Sangay	Shanghai	China	5903.56
11	Petrohrad	St.-Petersburg	Russia	7415.93
12	Poona	Maharashtra	India	5591.89
13	Durban	KwaZulu-Natal	South Africa	4742.07
14	Lunden	England	United Kingdom	4574.39
15	Chendu	Sichuan	China	4555.03
16	Santa Cruz de la Sierra	Santa Cruz	Bolivia	4387.1
17	Astana	Nur-Sultan	Kazakhstan	4374.73
18	Fago	North Dakota	United States	3591.61
19	Banares	Uttar Pradesh	India	3307.97
20	Wieden	Vienna	Austria	2926.42

Least lopsided (MIN(most populous city / rest))

	City	Province/State	Country	Population ratio (single largest pop. / Σrest)
1	La Soledad	Baja California Sur	Mexico	0.106
2	Rancho Nuevo	San Luis Potosí	Mexico	0.109
3	Poiana	Prahova	Romania	0.131
4	San Vicente	Valencia	Spain	0.15
5	La Luz	Michoacán	Mexico	0.155
6	San Bartolo	Chiapas	Mexico	0.155
7	El Rodeo	San Marcos	Guatemala	0.163
8	Llano Grande	Texas	United States	0.165
9	Saint-Leger	Normandy	France	0.169
10	Francisco Villa	Tlaxcala	Mexico	0.171
11	La Union	Piura	Peru	0.172
12	Ashland	California	United States	0.177
13	Santa Eulalia	Chihuahua	Mexico	0.178
14	Middletown	Ohio	United States	0.179
15	Pueblo Nuevo	Lima region	Peru	0.18
16	Saint-Hilaire	Pays de la Loire	France	0.185
17	Pervomayskoye	Chechnya	Russia	0.185
18	El Terrero	Chihuahua	Mexico	0.185
19	Berg	Bavaria	Germany	0.185
20	Kirchberg	Saxony	Germany	0.187

Most common (MAX(COUNT(cities)))

	City	Province/State	Country	Population ratio (single largest pop. / Σrest)	# cities
1	Santa Maria	Oaxaca	Mexico	0.297	145
2	San Pedro	Cortés	Honduras	0.378	121
3	Santa Cruz	Santa Cruz	Bolivia	0.703	111
4	San Juan	San Juan	Argentina	0.458	109
5	San Miguel	California	United States	1.321	108
6	San Antonio	Texas	United States	2.616	107
7	Santiago	Santiago Metropolitan	Chile	1.979	98
8	San Francisco	California	United States	1.94	93
9	San Jose	California	United States	1.334	88
10	San Isidro	Lima region	Peru	0.243	81
11	Santa María	California	United States	0.44	80
12	Buenavista	México	Mexico	1.269	76
13	Saint-Martin	Auvergne-Rhône-Alpes	France	0.282	69
14	Santa Rosa	Calabarzon	Philippines	0.278	66
15	San Lorenzo	Central	Paraguay	0.769	62
16	San Vicente	Valencia	Spain	0.15	61
17	Saint-Jean	Newfoundland and Labrador	Canada	0.448	59
18	Emiliano Zapata	Morelos	Mexico	0.366	59
19	Santa Ana	Metro Manila	Philippines	0.823	55
20	San Pablo	Minnesota	United States	0.369	52

Use it: cities.lam.io
GitHub: acrylic-origami/the-other-one