Profpatsch/users/Profpatsch/twin-towns

Every sister city on Wikidata, on one hoverable map

View full contents ↓

twin-towns(1)

twin-towns - hoverable world map of sister cities, from Wikidata

twin-towns ingest --db path [--endpoint url] [--timeout duration]
twin-towns serve --db path [--addr host:port]

twin-towns draws every twin-town / sister-city relation recorded on Wikidata as a world map. Hovering a city highlights it, draws an arc to each of its twins and lists them in a sidebar; clicking pins the selection so the pointer can be moved away.

Where Wikidata records when a twinning began (P580) or ended (P582), the year is shown next to the twin and the map can be wound back through it: a histogram of twinnings per year sits above a slider, and moving the slider hides every town and link not yet twinned by that year. Dissolved twinnings are drawn dashed and in their own colour rather than being hidden, because a twinning that ended is a fact about the world and not an absence of one.

A search box finds towns by name, matching loosely enough that 'brln' finds Berlin and 'zurich' finds "Z"urich", since names are compared with diacritics stripped. Towns the year filter is currently hiding are still listed, greyed out and ranked last: they can be selected anyway, which pins them and shows where they are. Hiding them outright would make the box appear broken, as searching a town you know exists would answer "no match" with no hint that a slider is the reason.

Following a twin from the sidebar selects it on the map rather than loading a page: the address becomes /?q=Q64 and Back walks the towns visited, so a chain of twinnings can be explored without discarding the viewport, the year filter, or the dataset already in memory. The server-rendered /place/... page is still what a modified click, a crawler, or a browser without JavaScript gets.

The year window, the undated toggle, the zoom and pan, the pinned town and the colour theme are remembered in localStorage between visits. A ?q= in the URL overrides the remembered selection, so a shared link opens on the place it names.

The map is drawn dark or light according to the operating system's preference, and a button beside the title cycles that through three states: follow the system, light, dark. Three and not two, because once a choice is pinned there would otherwise be no value left meaning "follow the system", and that is the right answer for anyone whose desktop switches at sunset. The light palette is not the dark one inverted -- a dot that glows on near-black is invisible on near-white -- so the map colours are chosen separately for each. The choice applies to /place/... as well, though that page carries no button of its own: it is the no-JavaScript path, and a control that does nothing without JavaScript does not belong on it. Without JavaScript both pages still follow the system preference; only the override needs it.

It is split into two subcommands: one that ingests the current state of Wikidata into SQLite, and one that serves that database over HTTP. Ingest is the only part that touches the network, so the server can run offline and is never slowed by an upstream outage.

ingest --db path [--endpoint url [--timeout duration]]

Query the Wikidata Query Service for every
`P190`
(twinned administrative body)
statement and the metadata of the entities involved, then replace the
contents of the database with the result.
The replacement happens in a single transaction, so an interrupted or failed
ingest leaves the previously served data intact.

As a guard against publishing an empty map when the endpoint misbehaves,
a result with no places or no pairs is refused rather than written.

serve --db path [--addr host:port]

Serve the map.
Defaults to
*127.0.0.1:8778*.
The dataset is read once at startup and held in memory, so requests never
touch SQLite except for the per-place pages.

/

The map.
Accepts
*?q=Q64*
to open with that place already selected, which is what makes the sidebar
permalink round-trip back into the map.

/data.json

The whole dataset in one payload: every plottable place and every edge
between two plottable places.
Edges are a flat array of four numbers each -
*a*, *b*, *startYear*, *endYear*
- where the two indices point into the places array and a year of
*0*
means the date is not recorded.
Served gzipped from memory with a strong
`ETag`.

/place/Q64

One place and all its twins, server-rendered.
This is the no-JavaScript path, and the only place where twins that have no
coordinates are visible at all.

/static/map.js, /static/style.css

Embedded assets.
Served
`no-cache`
with a strong ETag, so a visit costs a conditional request and almost always
a 304.
They are only tens of kilobytes and change only on deploy, and the
alternative is worse: with no explicit directive a browser guesses a
freshness lifetime from
`Last-Modified`,
which for an embedded asset can only be the process start time, so the
window grows with uptime and a deploy reaches returning visitors hours or
days late -- while
*/data.json*,
which is
`no-cache`,
has already changed underneath the stale code.

Two tables. place holds one row per entity appearing on either side of a twinning, with its label, country and coordinates; twin holds one row per unordered pair, normalised so that a < b and enforced by a CHECK constraint.

twin also carries started and ended, the P580 and P582 qualifiers of the underlying statement, as ISO dates. Both are usually NULL: only about a third of twinnings record when they began and under one percent when they ended. A date whose month or day Wikidata gives as '00' is stored as the bare year rather than being rounded to January 1st.

The place coordinates are nullable and the twin table references places that may have none, which is deliberate - see CAVEATS.

Populate a database and serve it:

twin-towns ingest --db twin-towns.db
twin-towns serve --db twin-towns.db --addr 127.0.0.1:8778

Refreshing the data is always two steps, because serve reads the whole dataset once at startup and never looks at the file again:

twin-towns ingest --db twin-towns.db
systemctl --user restart twin-towns

On the deployed host the same two steps are two units, and the restart is separate for a reason: the ingest unit runs as a systemd.exec(5) DynamicUser and is refused permission to restart anything.

systemctl start twin-towns-ingest
systemctl restart twin-towns

There is no timer. Wikidata gains a handful of P190 statements a day, each ingest costs about a minute of a free public SPARQL endpoint's time, and a refresh that changes what the map claims is worth performing deliberately rather than silently.

Wikidata property P190

Wikidata content is available under CC0.

Profpatsch

Wikidata documents P190 as a symmetric property, and it is tempting to deduplicate the pairs in SPARQL with 'FILTER(STR(?a) < STR(?b))'. That is wrong: 6537 statements exist in only one direction, and the filter silently discards every pair whose single statement happens to point "backwards", yielding 25980 pairs where 29212 exist. twin-towns therefore fetches both directions and normalises in code. If you change the query, do not reintroduce that filter.

About 110 of the 20141 twinned entities have no P625 coordinate. They are still ingested and still listed on /place/..., but they cannot appear on the map, and an edge is only drawn when both endpoints are plottable. A city's twin count on the map can therefore be lower than on its page; the page is the truthful one.

Every coordinate rendered comes from Wikidata and nothing else. At 20031 points the dots trace the continents by themselves, and a graticule supplies the spatial reference that outlines otherwise would. This is a deliberate refusal to acquire a second dataset: Wikidata stores country outlines only as a P3896 pointer to a Commons .map page, which would mean 205 separate fetches of roughly 30 MB of unsimplified geometry, for a backdrop the data already draws.

Only about 10000 of the 29000 pairs record a start date, and only 7400 of the 20000 mapped towns have even one dated twinning. The histogram and the year slider therefore describe the dated subset, not the world: winding the slider back to 1970 shows the roughly 1300 towns known to have twinned by then, not the number that actually had.

This is why undated twinnings are shown by default and hidden only by an explicit toggle, rather than being quietly dropped when the slider moves. With the toggle on, a town stays on the map as long as any of its twinnings is undated - which for two thirds of them is always.

Both directions of a symmetric statement can carry a start date, and about 494 pairs give two different ones - usually adjacent years, sometimes adjacent days. Neither is authoritative, so ingest keeps the earliest start and the latest end: the twinning demonstrably spanned at least that.

The live data contains start dates that are blank-node URIs (Wikidata's "unknown value"), a year 51983 and a year 199. These are rejected at ingest rather than stored, and counted in the "unusable date values" line. Anything outside the years 700 to next year is treated as a typo; the oldest genuine date in the set is 1561, for Greater London and Moscow, which dates the relationship to the Muscovy Company rather than to any modern twinning.

Places and pairs are fetched by separate queries, so a statement added between them can name an entity that was not fetched. Such statements are dropped and counted in the "statements skipped" line rather than violating the foreign key. The count is normally small; a large one means something changed upstream.

/data.json is roughly 1.5 MB of JSON. This is the price of instant hovering over 20k dots: a round trip per hover would be unusable, and the payload is smaller than a handful of map tiles. It does mean the first paint waits for that fetch.

Every colour is defined once as light-dark( light, dark), which needs Firefox 120, Chrome 123 or Safari 17.5. Older browsers do not merely fall back to one theme: they fail to parse the declaration and get no colour at all, which is unreadable rather than merely wrong. The alternative was writing the light palette out a second time inside a @media block and keeping the copies in step by hand, which is the more common bug.

Note that light-dark() is defined for only. The dots' transparency therefore lives in the alpha channel of --dot rather than in a fill-opacity of their own: a light-dark(0.75, 0.55) is not a number, so the property would be invalid and silently fall back to 1 -- in both themes at once, flattening the overlapping dots that are most of what the map shows. For the same reason --link exists as an opaque copy of the same hue, for text and the histogram bars, which set their own opacity and would otherwise apply it twice.