Query languages II: Joining maps and facts

A map of museums answers a location question. Adding the year each museum was founded calls for another source.

Part I queried Overpass and Wikidata separately. This part brings them together around one question: museums in inner Melbourne and their founding years.

OpenStreetMap provides the locations and only occasionally a founding date. Wikidata provides founding dates, but its coordinate coverage is patchy and it lacks the mapped geometry needed here.

A shared identifier allows the datasets to be joined without fuzzy matching on names or distances.

I think ChatGPT doesn't realise that it generated puzzle pieces that do not fit together ... and are from different cities

The join key is a tag

Many OSM features carry a tag such as wikidata=Q123456. It identifies the Wikidata entity associated with the mapped feature: a foreign key between projects that share no database.

Plenty of museums lack this tag. Those rows cannot participate in this join, so the workflow counts them before filtering them out.

Overpass supplies the museums and their Wikidata tags. Wikidata supplies founding dates for those Q-numbers, and pandas joins the results on the identifier. GeoPandas then turns the coordinates into geometry.

Step 1: Museums from Overpass

The example uses the helpers from Part I. Its extent is a bounding box covering the CBD and its surroundings, with coordinates ordered south, west, north, east:

A bounding box avoids the ambiguity of a named area: “Melbourne” can refer to several administrative areas in OSM.

The next filter retains rows with a usable join key, checking that each value has the form of a Q-number:

The printed ratio shows how much of the original selection survives. As so often in data anlysis, this does not mean that the idea does not work, it just means that you additional data sources for the rest, or just good old manual labor to get the final sources.

Many museums are mapped as building outlines, represented by ways. center reduces each outline to a representative point suitable for a map of dots. That point does not identify an entrance and is unsuitable as a routing destination.

Step 2: Wikidata facts for the selected items

The Q-numbers are already known, so a VALUES clause supplies them directly:

SPARQL’s literal curly braces need doubling inside a Python f-string. The two languages have rather different plans for the same punctuation.

OPTIONAL keeps museums without a P571 statement in the results, i.e. the ones that miss the founding date in the data. A required pattern would exclude them; the optional pattern leaves the date blank instead.

Starting from known items narrows the query’s work. Batches keep each request small, even though for our example we will find considerably less than 100 museums:

Wikidata returns dates as strings such as 1854-01-01T00:00:00Z. The following step extracts the year and groups the records by item:

A plain wdt:P571 value omits its precision information, so 1854-01-01 may refer to a year rather than a known day in January. This example retains only the year.

An item can also have several inception statements, as might happen with a refounded museum. Grouping by Q-number and taking the earliest year gives one value per item.

Step 3: Merge and build the GeoDataFrame

With both tables prepared, an ordinary left join brings the dates onto the museum records:

The order in points_from_xy is longitude, then latitude: x, then y. The same coordinate-order issue appeared in the WKT values in Part I. It tends to be a standard, but one still needs to be on the lookout in every case.

EPSG:4326 describes the coordinates supplied by OSM and Wikidata. Measurements in metres, including buffers and distances, require reprojection first. For Melbourne, GDA2020 / MGA zone 55 is a suitable choice:

A missing year can reflect incomplete documentation rather than a failed join. A mapped museum may lack a founding date in Wikidata, perhaps because it is new or small and has received little attention. For a complete project we would need to flag these cases, consult other sources and then fill up the final information by hand.

Step 4: Inspect the map

The interactive map makes the dated and undated records visible:

This requires folium, mapclassify, and matplotlib and renders an interactive map that you can open in a browser after running

python -m http.server 8000 --bind 127.0.0.1

and gives something along the following lines

Colour represents the recorded founding year; grey dots mark museums without one in the result. The tooltip then lists the precise date.

Starting from Wikidata

The join also works in the other direction. The large Australian cities from Part I provide a set of Q-numbers for finding matching OSM features:

The parentheses form an Overpass union: each statement runs and the results are combined. A city can match several features, commonly a place node and an administrative boundary relation. The boundary or place tags help select the intended kind of feature.

Full polygons require more work than centre points. Assembling relations from raw Overpass JSON gets fiddly; osmnx uses Overpass and handles the geometry assembly.

Keeping queries manageable

Query cost depends on the amount of work required to find and return the answer.

For Overpass, tag filters within a small bounding box keep the search contained. The same filter across a whole country is a much larger request. Her out count; gives a preview of the result size without downloading the features:

Centre points are lighter than full geometry, so out geom is best reserved for work that needs the outlines. Regular expressions over tag values can also become costly across large areas.

For Wikidata, LIMIT 20 keeps the output manageable during development. A list of known items, as in Step 2, gives the query a narrow starting point. Broad instance searches have much more work to do.

Paths such as wdt:P279* also deserve attention. A narrow class such as “museum” is more manageable than a broad one; constraints such as country help restrict the search. When a query reaches the public endpoint’s 60-second limit, narrowing the question is more useful than repeatedly sending it unchanged.

Small queries make the result easier to inspect before widening the search. A long wait alone says little about whether the question was expressed correctly.

Where this approach falls short

Bulk extracts belong outside the public Overpass request workflow. A result containing hundreds of thousands of features, such as every building in Victoria, is better obtained as a regional PBF extract from Geofabrik and read locally with pyrosm or osmium. This avoids a large request to shared infrastructure and provides a saved dataset for repeatable work.

Live queries are also a poor dependency for a web page request. Public endpoints have variable latency, rate limits and no service-level agreement. A scheduled job can fetch the data into an application database, leaving the page to use the stored result. An application that requires live Overpass access may need its own instance.

Reproducible analysis needs saved results. OSM and Wikidata change continually, so a report should retain its input data together with the retrieval date.

Missing records need a different kind of care. Both datasets rely on contributions, and a museum can exist without appearing in the query results. The usable wikidata tag ratio from Step 1 makes one source of missing coverage visible.

Questions beyond the endpoint menu

The museum example shows how a question can be assembled from two services even when neither offers an endpoint for the complete answer. Both return structured data that fits into a GeoDataFrame and the analysis that follows.

The GIS series covers the mapping techniques, and the City2Graph posts cover the network side. Museums, fountains and cities provide further data for that work. The practical limit is still the quality of the source records and the amount of work a shared service can reasonably handle.