How can I write an query to list all countries and territories [admin_level=2] that drive on the right side, with their official English names, areas, total expressway lengths [highway=motorway] and sort descendingly by ratios of expressway lengths over corresponding areas?
You’ll probably have to use a tool other than the Overpass API, for a few reasons:
- An OverpassQL query can only measure the perimeter of a boundary, not its area.
- Aggregating the lengths of all the
highway=motorwayways in even a single country would be very memory-intensive, so it would be very difficult to get an answer on a public Overpass API instance. - OverpassQL doesn’t have a built-in syntax for sorting anything; you’re expected to export the CSV and postprocess it using other tools.
QLever is better-suited to the kind of report you want to generate. This query returns pretty much what you’re asking for:
PREFIX ogc: <http://www.opengis.net/rdf#>
PREFIX osm2rdf: <https://osm2rdf.cs.uni-freiburg.de/rdf#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX osmkey: <https://www.openstreetmap.org/wiki/Key:>
SELECT
?country (SAMPLE(?name) AS ?country_name)
(SAMPLE(?area) AS ?country_area)
(SUM(?length) AS ?expressway_length)
(SUM(?length)/SAMPLE(?area) AS ?expressway_length_by_area)
WHERE {
?country osmkey:boundary "administrative" .
?country osmkey:admin_level "2"^^xsd:int .
?country osmkey:driving_side "right" .
?country osmkey:name:en ?name .
?country osm2rdf:area ?area .
?expressway osmkey:highway "motorway" .
?expressway ogc:sfIntersects ?country .
?expressway osm2rdf:length ?length .
}
GROUP BY ?country
ORDER BY DESC(?expressway_length_by_area)
You’ll notice that it only returns nine countries. Many more countries drive on the right but don’t have an explicit driving_side=right tag on the boundary relation. It is possible to get this information from Wikidata instead, but apparently calculating the distances of 156 countries requires slightly too much memory for the public QLever instance too (8.2 GB, over the limit of 7.1 GB). The Analysis function says the bottleneck is joining the country and expressway tables. This query only scales to a handful of countries, such as the five ASEAN member states or 22 members of the Francophonie.
Maybe someone more familiar with SPARQL could optimize it further so it works with more countries at a time. In the meantime, you could try running it over various subsets of countries, perhaps alphabetically, and combine the results.
Here are some other tools that you might be interested in:
- Geofabrik maintains a series of regional taginfo instances. If you only care about raw numbers of ways, regardless of how long they are, you can manually compare the number of
highway=motorwayways to the number ofhighway=*ways in each country’s taginfo instance, for example Vietnam. - Ohsome Dashboard is designed for queries such as “the total
highway=motorwaydistance in ten specific countries”. The underlying API may allow you to include every country in a single query, though such large queries will take time. - GeoDesk has a query syntax similar to OverpassQL but performs much better on high-volume queries.
4 Likes
Thank you so much for your answer!
By the way, is there another app like GeoDesk that can work in a browser?
If you’re comfortable with programming languages, I suppose you could use the Ohsome or QLever API within an interactive Web-based environment such as Observable or Jupyter to get a similar experience.
Can a query that select only RHD countries with at least 100km of expressways work?
This SPARQL query would return something like that, but it exceeds QLever’s memory limits and still has the caveat I mentioned that driving_side=right isn’t tagged on as many regions at it should be. Alternatively, you could pass the geometry of all the right-hand-driving regions into the Ohsome API. You can use Ohsome Dashboard to prototype your API request.
Is this driving side tag supposed to be added to the country relation as checking Italy, it isnt. To quote the wiki
“Add driving_side=left/right to the country’s relation or to the highway. Only add it to a highway when it’s driving side is an exception to the general rule (and check that the general rule is tagged in your country relation).”
And checking the UK, there it has been, driving_side=left, so something to fix, or Is it assumed to be right and only needs specifying if left? Checking The Netherland they also don’t have the tag.
That passage is saying you should only apply driving_side=* to a highway=* way when you need to override the driving_side=* tag on a surrounding boundary relation. It assumes the country’s boundary relation has the tag, but many of them don’t.
I should have bolded the first line, checking more, Germany does have the tag, Denmark hasnt, so as you say, many havent. Tomorrow one less.
edit: And so done, found that 2 language tags needed updating name:bat-smg > name:sgs (wiki) and name:eml > name:egl.
1 Like
Thank you so much again!
Oh right, I am planning to gradually add them to all territories