Query languages I: Two languages for asking

Most APIs offer a menu of requests. A question that falls outside that menu can mean downloading far more data than the answer needs.

Overpass (OpenStreetMap) and the Wikidata SPARQL endpoint accept queries. The caller defines the question, and the service works out the answer from its data.

Both languages have more than one post can cover, along with their own detailed references. The focus here is on how this changes the work: expressing a question, handling the result, and keeping the demands on a shared service reasonable.

Both of the APIs seem to look like old fashioned machines to ChatGPT .. at least they do not need giant data centres.

The menu problem

A typical REST API looks like this:

GET /museums?city=melbourne

That works when the API designer has allowed for museums and cities. Drinking fountains within 50 metres of a park is a less likely option. Without a suitable endpoint, the remaining choices may be paging through the data and filtering locally, or filing a feature request.

With a query language, the server holds both the data and the engine that searches it. Filtering happens there, so only the result needs to travel back to the client.

That flexibility takes some learning. It also makes expensive questions possible, which is why request handling deserves as much attention as the query syntax.

The examples use three packages, requests, Pandas, and GeoPandas.

Overpass: A filter over a map

OpenStreetMap has three basic element types:

• Nodes are points.

• Ways are ordered lists of nodes, used for roads and building outlines.

• Relations group elements into things such as boundaries and bus routes.

Elements carry tags: free-form key=value pairs. A drinking fountain can be represented by a node tagged amenity=drinking_water.

Overpass QL filters this data. The first question is all drinking fountains within 2 km of Sydney central station, summer is coming up and it's going to get scorching hot again:

[out:json][timeout:25];
nwr["amenity"="drinking_water"](around:2000,-33.8830,151.2065);
out center tags;

The opening line requests JSON output and sets a server timeout of 25 seconds. The selection belongs on the second line: nwr covers nodes, ways and relations, square brackets contain a tag filter, and round brackets contain a spatial filter. For a radius search, the spatial values are the radius in metres followed by latitude and longitude.

The output statement controls what comes back. Here, tags includes each element's tags, such as its name and amenity type, together with its ID. While center adds a bounding-box centre point for ways and relations. Version numbers, timestamps and contributor details are omitted.

The Python request sends the query to Overpass:

POST avoids the URL length limits that can affect long queries sent with GET. Each returned element looks roughly like this:

{
  "type": "node",
  "id": 537530883,
  "lat": -33.8679612,
  "lon": 151.2160202,
  "tags": {
    "amenity": "drinking_water",
    "bottle": "no",
    "check_date": "2023-07-23",
    "dog": "no",
    "fountain": "bubbler"
  }
} 

Nodes carry lat and lon directly. Ways and relations carry a center dictionary instead. A small helper handles both forms:

Tags become columns. Since the tags are free-form, the resulting table is likely to be sparse; only the columns relevant to the analysis need to remain.

Fountains near playgrounds

For the workout routine in the summer, let's look for drinking water features that are within 50 metres of a park.

[out:json][timeout:25];
nwr["leisure"="park"](-33.91,151.15,-33.83,151.27)->.parks;
node["amenity"="drinking_water"](around.parks:50);
out tags center;

The same POST as above works, just switch out the query statement.

The first selection finds parks inside a bounding box, ordered south, west, north, east, that roughly covers the Sydney CBD and surrounding suburbs area. It stores them in a named set with ->.parks. The fountain selection then uses a 50 m radius around that set. Named sets act as variables, allowing one selection to feed another.

Note that this query will only return the locations of the drinking fountains, the named set .parks is only for internal use and is not returned.

OSM tags depend on volunteer contributions, and volunteers disagree. A check on taginfo helps establish how a tag is used before a filter starts depending on it. An expected tag and a widely used tag are not necessarily the same thing.

Wikidata SPARQL: A pattern over a graph

Wikidata is a graph of statements. Each statement is a triple: subject, property, value. Items have Q-numbers and properties have P-numbers. These are the identifiers used in the examples:

ID	Meaning
P31	instance of
P279	subclass of
P17	country
P1082	population
P625	coordinates
P571	inception (founding date)
Q408	Australia
Q515	city

A SPARQL query describes a pattern of relationships using variables. The engine finds the data that fits. In this case, the question is Australian cities with a population above 100,000:

SELECT ?city ?cityLabel ?pop ?coord WHERE {
  ?city wdt:P31/wdt:P279* wd:Q515 ;	# instance of city 
									# (or a subclass of city)
        wdt:P17 wd:Q408 ;			# country: Australia
        wdt:P1082 ?pop .         	# has a population
  OPTIONAL { ?city wdt:P625 ?coord }	# coordinates, if known
  FILTER(?pop > 100000)
  SERVICE wikibase:label { 
	bd:serviceParam wikibase:language "en". 
  }
}
ORDER BY DESC(?pop)

The pattern requires an entity ?city that is classified as a city, located in Australia, with a population ?pop above 100,000.

The path wdt:P31/wdt:P279* allows the classification to be an instance of or a subclass of city, which is defined by the term wd:Q515. The * means zero or more steps. Paths make this kind of query possible, though they can also make it expensive if your paths is very long. We also demand ?city to have the property to be in Australia, via wdt:P17 wd:Q408, and that it has a population, via wdt:P1082 ?pop, which we declare to be ?pop.

We also add an optional declaration, if we find coordinates, then we return those in ?coord, in the line OPTIONAL { ?city wdt:P625 ?coord }.

We also apply a filter in the line FILTER(?pop > 100000), which is the most self explanatory thing in the query.

The SERVICE wikibase:label line supplies a readable name in ?cityLabel.

Python handles the request and converts the returned bindings into a table:

The helpers overpass_to_df, from the overpass example, and bindings_to_df, from here, belong in the same module as the client below; Part II will make us of them again.

Results depend on how contributors modelled the data. A city with a classification outside the query’s path will be absent. Checking for Melbourne, Sydney, Brisbane, Perth and Adelaide gives an initial sanity check before examining the less familiar results. Especially true for Australia, since you might find names on that list that feel made up.

The ?coord column needs care too. It contains WKT text such as Point(144.96 -37.81), which would be Melbourne, with longitude first. A latitude-first assumption puts the coordinates in the wrong place.

Two ways to express a question

Overpass filters map features by tags and spatial conditions. SPARQL matches relationships between entities, including facts about what something is, when it began, or who it relates to.

Those different strengths make a combined query useful. Part II uses the map features from one service and the descriptive facts from the other.

Being a polite client

Both services are free public resources supported by non-profit work. Availability comes without a promise that every request will succeed.

A considerate client needs a few basic habits:

• A User-Agent identifies the project and provides a contact address. Wikimedia’s policy asks for this, and generic library defaults may be blocked.

• Timeouts belong on both sides of the request. Overpass’s [timeout:25] limits server work; the timeout= argument in requests covers the network operation.

• Responses such as 429 and 504 call for a pause before retrying, with Retry-After respected when present.

• A local cache avoids sending the same query repeatedly during development.

An HTTP 200 response alone does not establish that an Overpass query succeeded. A timed-out query can return partial JSON with an error in a remark field, so that field also needs checking.

The request handling together with the dataframe helpers above will be the following. In Part II we will always assume that we have access to all of these functions:

The cache key is a hash of the query text. Editing a query produces a new key; repeating it reuses the saved response without another request.

This cache never expires. Since map data changes daily, fresh results require deleting the cache folder or adding expiry rules with something like requests-cache.

When a plain endpoint is better

A documented endpoint remains the simpler choice when it already answers the question, such as a request for a Wikipedia page summary. It can also be faster and less costly for the service.

A query language earns its place when the question falls outside the available endpoints and answering it locally would mean downloading a large amount of unwanted data.

Joining the results

So far, the fountain locations and city populations have come from separate queries. Part II connects the services through a shared identifier, and additional data. The missing records matter as much as the successful matches.