DEV Community: jiang Wang

A name is not an identifier: what 1,269 Chinese railway stations taught me about labelling

jiang Wang — Thu, 05 Nov 2026 08:47:13 +0000

I spent a week loading Chinese railway stations into a database, and the interesting part turned out to be a modelling problem rather than a scraping one.

The symptom, in one sentence: the label a user sees and the key you store are different things, and in this dataset they diverge in a way that ruins an afternoon.

The set

1,269 published railway stations across 340 cities. 262 airports across 212 cities. Every row carries a Chinese name, an English name, a city, coordinates.

The distribution is not what I expected. Beijing leads with 50 stations. Harbin has 40. Chengde — a small city most people only pass through on the way to the Qing tombs — has 26. Then Chengdu 30, Guiyang 24, Hulunbuir 21. 205 of the 340 cities have more than one.

Finding 1: the Chinese name is unique, the English name is not useful

The first query I ran was a duplicate check, expecting a mess:

SELECT name, COUNT(*) AS n
FROM railways
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY n DESC;

Zero rows. All 1,269 published station names are distinct across the entire country. That is a stronger guarantee than most geographic datasets offer — usually you find at least a handful of collisions, and you need an administrative prefix to disambiguate.

So the Chinese string is a legitimate natural key. Good.

But the English name fails for a different reason. It is not duplicated — it is unactionable. Consider 北京南站. The romanisation is Beijingnan. That is what appears on the ticket, on the departure board, and in every app. What a person says at a taxi rank is "Beijing South". What the driver reads is neither: the driver reads 北京南站 off a sign, or nothing at all.

You end up with three representations of one station, and only one of them can be compared against a physical sign in the street. The English one, which is what a foreign visitor instinctively reaches for, is the least useful of the three.

Finding 2: a third of the set is a compass direction

Counting the suffix patterns:

Suffix	Count
南 (south)	124
北 (north)	123
东 (east)	103
西 (west)	102

452 of 1,269 — 35.6% — are a city name plus a compass point. The remaining two-thirds are district names, town names, or standalone names that happen to be a station. And 301 stations are exactly the bare city + 站 form, which is the historical central terminus: 北京站, 天津站, 广州站.

That last figure is the trap. If a city has a bare-name station and compass variants, the bare name is the one a visitor will guess, and it is frequently not the one their train departs from.

Finding 3: 36 cities have more than one airport

SELECT COUNT(*) FROM (
  SELECT city_slug FROM airports GROUP BY city_slug HAVING COUNT(*) > 1
);
-- 36

Shanghai's two airports are 45 km apart. Chengdu's are on opposite sides of the metro area. The failure mode is identical to the station one, and it is worse because the English names (Pudong, Hongqiao) look like proper nouns rather than positions.

What I changed in the schema

Three decisions, all boring, all consequential:

The Chinese string is the display value and the natural key. It is the only representation that survives contact with a taxi rank.
The English name is retained for full-text search only. It never appears as the sole label anywhere in the UI.
Coordinates are mandatory, not optional. A station page without a map pin is a page that cannot resolve the ambiguity it exists to resolve.

That third rule removed 0 rows, because the upstream source had coordinates for everything — but writing it down as a constraint is what stopped me from ever building a station page without one.

The general lesson

If your dataset's user-facing label is also your join key, you will at some point join two different things that read the same. Here the Chinese name happens to be both unique and unambiguous, which is lucky. The English name is unique too, and still manages to be the wrong thing to show someone standing at a kerb.

Uniqueness and usefulness are separate properties. Only one of them is visible in a GROUP BY.

The dataset and the per-station pages are browsable at sinostay — the station list itself is at /traffic/railways/.

China in July: 27 cities are comfortable. In May, 255 are.

jiang Wang — Thu, 05 Nov 2026 03:06:32 +0000

Everyone knows Chinese summers are hot. Fewer people know how completely the country empties of comfortable places: in May, 255 of the 359 cities we track sit in a pleasant band; in July, that number falls to 27.

What we measured

We hold monthly climate normals for 359 Chinese cities — 4,308 data points covering temperature, rainfall and humidity for 2021 to 2025. For each city we defined "comfortable" as a mean temperature between 10 and 26°C with rainfall below 75% of that city's wettest month. That second condition is the one that matters: a mild month that happens to be the rainy season is not a month you want to walk a city in.

The months, ranked by how much of the country works

May 255 · October 250 · April 242 · September 214 · March 192 · November 173 · June 119 · February 59 · December 56 · January 53 · August 45 · July 27.

The shape is not symmetric. Spring and autumn are wide open — almost the entire country qualifies. July and August are not "hotter versions of May"; they are a different country.

What July actually leaves you

The 27 cities that pass in July are not the ones on the postcards. They are places at altitude, on the northern edge, or in the mountains of the southwest: Lishui at 26.0°C, Tongren at 25.7°C, Hanzhong at 25.3°C, Alashan at 25.6°C with 20mm of rain for the month, Altay at 25.4°C with 14mm. Beijing, Shanghai and Guangzhou are nowhere near the list.

That is the useful part. "China is hot in July" is a sentence that tells a traveller nothing. "27 of 359 cities are comfortable in July, and here they are" tells them where to go.

And January inverts it

January leaves 53 cities, and almost all of them are on one coastline: Qionghai at 20.2°C, Wanning at 19.7°C, Dongfang at 19.1°C, Sanya at 18.9°C, Haikou at 18.7°C, plus Zhuhai, Beihai and Zhanjiang on the Guangdong and Guangxi coast. In winter the comfortable part of China is a strip about 200km wide in the south.

Why we publish this

Climate data for China is easy to find per city and almost impossible to compare across cities, because every source uses different normals and different definitions. Holding one dataset of 359 cities on one set of five years makes questions like "where should I go in July" answerable rather than guessable.

The full set is free to browse, no signup and no email wall: sinostay.com.

The working note with the full numbers and our method is at sinostay.com/journal/3.html.

Building a 9,718-hotel database for foreign travellers in China

jiang Wang — Wed, 04 Nov 2026 07:55:14 +0000

We maintain a dataset of hotels in China that will actually let a foreigner check in — right now 9,718 properties across 359 cities, plus 7,003 attractions, 34,504 restaurants and 9,120 shopping locations with English names and coordinates.

Two things turned out to be harder than collecting the data.

Images. Every hotel needs a photo that is hotlinkable, fast and cheap to serve. We moved all of them onto object storage behind our own domain, converted them to WebP, and hashed the bytes so identical photos collapse into a single object. A typical hotel page loads around thirty images.

Showing an address to a taxi driver. A hotel listing is only half the problem; the other half is saying "this one" to someone who does not read English. Every listing carries a Chinese-name card — Chinese name, Chinese address, city — generated from the same record that renders the page.

The third problem was not technical. Scrapers pulled every image we had: one tool alone fetched 1.98 million of them in a week, and another swept every internal cache-key path we exposed. We now allow real search engines and refuse everything else, including requests with no user agent at all.

The whole thing runs on Cloudflare Workers with D1 for the database and R2 for images — about 6,700 indexable URLs served from one 9,000-line worker file.

If you are working on travel data for China, the underlying set is free to browse — no signup, no email wall.

Full dataset and methodology: https://sinostay.com/journal/1.html