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/.
Top comments (0)