SPARQL examples

Examples of SPARQL queries are available for the following query engines:

• Sophox
• QLever

To compare engines with each other and with the Overpass Turbo syntax:

• SPARQL vs Overpass QL examples lists generic examples that do not demonstrate engine-specific features

This disambiguation page lists articles associated with the same title.
If an internal link led you here, you may wish to change the link to point directly to the intended article.

Expand

Sophox examples

For compatibility with Sophox, the Sophox examples are transcluded below:

The Examples panel at https://sophox.org/ automatically loads the contents of this page. Please maintain the structure of this page so that the panel continues to display all the examples correctly.

Simple queries

place=*

Displays a table of 50 features tagged place=* anywhere in the world, along with their place=* values. In this query, the place key is the predicate. Sophox supports some other kinds of predicates that are not OpenStreetMap keys, so OpenStreetMap keys always begin with the osmt: prefix.

SELECT * WHERE {
  ?place osmt:place ?placeType.
}
LIMIT 50

Run it (edit query)

office=newspaper nodes

Displays a table of nodes tagged office=newspaper anywhere in the world. Predicates for OpenStreetMap metadata begin with the osmm: prefix. You only need to include the osmm:type predicate if you want to display each element's type or restrict the results to elements of a particular type.

SELECT * WHERE {
  ?newspaper osmt:office "newspaper";
             # "n" for nodes, "w" for ways, or "r" for relations
             # Replace "n" with ?type to include all three types and indicate the type in a separate column
             osmm:type "n".
}

Run it (edit query)

office=newspaper on a map

Displays an interactive map of anything tagged office=newspaper anywhere in the world. In Sophox, you can use the view menu to switch any query to an interactive map, as long as one of the columns contains well-known text of Point geometries, such as the object of the osmm:loc predicate. This query demonstrates setting the view to an interactive map by default.

#defaultView:Map
SELECT * WHERE {
  ?newspaper osmt:office "newspaper";
             osmm:loc ?coordinates.
}

Run it (edit query)

office=newspaper and name=*

Displays a table of anything that is tagged both office=newspaper and name=* anywhere in the world.

This query is considerably faster than querying for anything tagged name=*, because that tag is extremely common. However, it is only slightly faster than querying for anything tagged office=newspaper. For relatively uncommon tags, it is unnecessary and potentially counterproductive to overspecify the query with many predicates.

SELECT * WHERE {
  ?newspaper osmt:office "newspaper";
             osmt:name ?name.
}

Run it (edit query)

office=newspaper and optionally name=*

Displays a table of anything that is tagged office=newspaper anywhere in the world. For easier identification, this query displays each feature's name=* if available, but it also includes features without name=*.

To display name=* and name:en=* only when both tags are also present, add a sentence about name:en=* to the OPTIONAL expression below. To display name=* and/or name:en=*, add a separate OPTIONAL expression for name:en=*.

SELECT * WHERE {
  ?newspaper osmt:office "newspaper".
  OPTIONAL {
    ?newspaper osmt:name ?name.
  }
}

Run it (edit query)

office=newspaper but not name=*

Displays a table of anything tagged office=newspaper but not name=*.

SELECT * WHERE {
  ?newspaper osmt:office "newspaper".
  
  FILTER NOT EXISTS {
    # The [] is a placeholder for an ignored object; replacing it with ?name would have the same effect
    ?newspaper osmt:name [].
  }
}

Run it (edit query)

office=newspaper or office=advertising_agency

Displays a table of anything that is tagged either office=newspaper or office=advertising_agency anywhere in the world. The two tags share the same key, so this query uses a VALUES expression to vary between two values.

SELECT * WHERE {
  VALUES ?offices { "newspaper" "advertising_agency" }
  ?office osmt:office ?offices.
}

Run it (edit query)

office=newspaper or disused:office=newspaper

Displays a table of anything that is tagged either office=newspaper or disused:office=newspaper anywhere in the world. The two tags share the same value, so this query uses a single sentence that alternates between two predicates.

SELECT * WHERE {
  ?newspaper (osmt:office|osmt:disused:office) "newspaper".
}

Run it (edit query)

office=newspaper or studio=radio

Displays a table of anything that is tagged either office=newspaper or studio=radio anywhere in the world. Because the two tags share neither a key nor a value, this query unions together two separate sentences that share the same subject.

SELECT * WHERE {
  {
    ?mediaOutlet osmt:office "newspaper".
  } UNION {
    ?mediaOutlet osmt:studio "radio".
  }
}

Run it (edit query)

leisure=pitch within a specific bounding box

Displays an interactive map of anything tagged leisure=pitch that is located within a specific bounding box encompassing the Dallas area. (For ways and areas, the centroid is compared to the specified bounding box.) You can use a tool like bboxfinder or geojson.io to calculate a bounding box.

When writing a well-known text literal, put the longitude before the latitude. Unlike the Overpass API, Sophox generally performs faster with a global query than when restricting the same query by geography.

#defaultView:Map
SELECT * WHERE {
  ?pitch osmt:leisure "pitch".
  
  SERVICE wikibase:box {
    ?pitch osmm:loc ?coordinates.
    
    # Southwest longitude and latitude of the Dallas area
    bd:serviceParam wikibase:cornerSouthWest "Point(-97.00 32.50)"^^geo:wktLiteral.
    # Northeast longitude and latitude of the Dallas area
    bd:serviceParam wikibase:cornerNorthEast "Point(-96.60 33.00)"^^geo:wktLiteral.
  }
}

Run it (edit query)

leisure=pitch near a specific coordinate

Displays an interactive map of anything tagged leisure=pitch that is located within 300 kilometres (190 mi) of a specific coordinate, approximating Suriname. (For ways and areas, the centroid is compared to the specified bounding box.)

When writing a well-known text literal, put the longitude before the latitude. Unlike the Overpass API, Sophox generally performs faster with a global query than when restricting the same query by geography.

#defaultView:Map
SELECT * WHERE {
  ?pitch osmt:leisure "pitch" .
  
  SERVICE wikibase:around { 
    ?pitch osmm:loc ?coordinates.
    
    bd:serviceParam wikibase:center "Point(-56.00 4.00)"^^geo:wktLiteral. # somewhere in Suriname
    bd:serviceParam wikibase:radius "300". # kilometers
    bd:serviceParam wikibase:distance ?distance.
  }
}

Run it (edit query)

leisure=pitch near you

Displays an interactive map of anything tagged leisure=pitch that is located within 10 kilometres (6.2 mi) of your current location. (For ways and areas, the centroid is compared to the specified bounding box.) When you run this query, your browser will ask you for permission to give Sophox your location to customize the query.

#defaultView:Map
SELECT * WHERE {
  ?pitch osmt:leisure "pitch" .
  
  SERVICE wikibase:around { 
    ?pitch osmm:loc ?coordinates.
    
    bd:serviceParam wikibase:center "[AUTO_COORDINATES]".
    bd:serviceParam wikibase:radius "10". # kilometers
    bd:serviceParam wikibase:distance ?distance.
  }
}

Run it (edit query)

place=village whose name=* contains "View"

Displays a table of anything that is tagged with both place=village and name=*, as long as name=* contains "View". Use a filter for free-form keys where hard-coding a complete list of tag values to match would be unwieldy and impractical. Functions such as CONTAINS are slower than literal objects in sentences, such as "village" below, because Sophox has to fetch every name and evaluate it individually instead of looking up entries in an optimized index.

SELECT * WHERE {
  ?village osmt:place "village";
           osmt:name ?name.
  FILTER CONTAINS(?name, "View")
}

Run it (edit query)

place=village whose name=* ends with -boro or -borough

Displays a table of anything that is tagged with both place=village and name=*, as long as name=* ends with -boro or -borough. This query demonstrates combining string functions using logical operators for readability, but SPARQL also supports regular expressions for more complex filtering.

SELECT * WHERE {
  ?village osmt:place "village";
           osmt:name ?name.
  FILTER(STRENDS(?name, "boro") || STRENDS(?name, "borough"))
  # Alternatively, use a regular expression
#  FILTER REGEX(?name, "boro(ugh)?$")
}

Run it (edit query)

Members of type=site relations with the role entrance

Displays a table of nodes, ways, and relations that are members of type=site relations with the role entrance.

SELECT * WHERE {
  ?relation osmt:type "site";
            osmm:type "r";
            osmm:has ?member;
            ?member "entrance".
}

Run it (edit query)

leisure=* last edited between specific dates

Displays a table of anything tagged leisure=* that was edited in August 2020 (UTC) and has not been edited since then. Sophox does not store full OpenStreetMap edit history; it only knows when each feature was last edited and by whom.

SELECT * WHERE {
  ?leisure osmt:leisure [];
           osmm:timestamp ?timestamp.
  
  # Filter by UTC timestamps in ISO 8601 format
  FILTER("2020-08-01T00:00:00Z"^^xsd:dateTime < ?timestamp &&
         ?timestamp < "2020-09-01T00:00:00Z"^^xsd:dateTime)
}

Run it (edit query)

Seas named by pirates

Displays a table of seas with English names turned into pirate-speak – arrr! TileMill had a Pirates map template that, among other cartographic flourishes, labeled the Sargasso Sea as "Sarrgasso Sea". [1] This query demonstrates text replacement using the REPLACE() function.

SELECT * WHERE {
  ?sea osmt:place "sea";
       osmt:name:en ?english.
  FILTER CONTAINS(?english, "ar")
  BIND(REPLACE(?english, "ar", "arrr") AS ?pirate)
}

Run it (edit query)

Lane counts of running tracks on a line graph

Displays a line graph of running tracks by lane count, with the lane count along the X axis and the number of tracks with that lane count along the Y axis. This query demonstrates aggregation, as well as casting tag values for a more sophisticated comparison.

#defaultView:LineChart
SELECT ?lanes (COUNT(*) AS ?count) WHERE {
  # Query OpenStreetMap for running tracks
  ?track osmt:leisure "track";
         # Get the number of lanes as a string
         osmt:lanes ?lanesString.
  # Cast the number of lanes to an integer; this variable is unbound for any lane count that isn’t an integer
  BIND(xsd:integer(?lanesString) AS ?lanes)
  # Only include lane counts that are integers
  FILTER BOUND(?lanes)
}
GROUP BY ?lanes
ORDER BY ?lanes

Run it (edit query)

Antipode of a city

Displays a map of the city closest to the antipode of Perth – that is, halfway around the world from Perth.

This query demonstrates Sophox's support for well-known text literals and a basic subset of GeoSPARQL functions.

#defaultView:Map
SELECT ?city ?cityLabel ?coordinates ?distanceLabel WHERE {
  # Perth
  BIND('Point(115.8604796 -31.9527121)'^^geo:wktLiteral AS ?perth)
  
  # Query OpenStreetMap for cities and their coordinates
  ?city osmt:place "city";
        osmm:loc ?coordinates.
  
  # Synthesize the antipode (the point halfway around the world)
  BIND(geof:longitude(?perth) AS ?perthLongitude)
  BIND(geof:latitude(?perth) AS ?perthLatitude)
  BIND(CONCAT('Point(',
              xsd:string(-?perthLongitude / ABS(?perthLongitude) * (180 - ABS(?perthLongitude))), ' ',
              xsd:string(-?perthLatitude), ')') AS ?antipode)
  
  # Calculate the city’s distance from the antipode
  BIND(geof:distance(?antipode, ?coordinates) AS ?distance)
  BIND(CONCAT(xsd:string(ROUND(?distance)), ' km') AS ?distanceLabel)
  
  # If the city is more than a few hundred kilometers from the antipode, it isn’t really an antipode
  FILTER(?distance < 300)
  
  # Get the city’s name
  OPTIONAL { ?city osmt:name:en ?name_en. }
  OPTIONAL { ?city osmt:name ?name. }
  OPTIONAL { ?city osmt:int_name ?int_name. }
  BIND(COALESCE(?name_en, ?name, ?int_name) AS ?cityLabel)
}
ORDER BY ASC(?distance)
LIMIT 1

Run it (edit query)

Cities in a metropolitan area

Displays a map of cities and towns in the San Jose–San Francisco–Oakland combined statistical area. In the United States, OpenStreetMap does not represent metropolitan statistical areas (MSAs), micropolitan statistical areas (μSAs), and combined statistical areas (CSAs) as boundary relations, but Wikidata makes it possible to query based on the counties that are included in a statistical area.

This query demonstrates a basic federated query, as well as property path syntax in SPARQL.

#defaultView:Map
SELECT DISTINCT ?osm ?osmLabel ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Cities and towns
    VALUES ?cities { wd:Q515 wd:Q3957 }
    ?wd wdt:P31/wdt:P279* ?cities;
        # That lie within parts of the San Jose–San Francisco–Oakland combined statistical area
        wdt:P131*/wdt:P361+ wd:Q7413927.
  }
  
  # Query OpenStreetMap for these places
  ?osm osmt:wikidata ?wd;
       # And their coordinates
       osmm:loc ?coordinates.
  
  # Get the place’s name
  OPTIONAL {
    ?osm osmt:name ?osmLabel
  }
}

Run it (edit query)

Network value split by colons

Displays the colon-delimited components of a hierarchical network=* value, specifically network=US:OH:SAN:Fremont.

SPARQL lacks a built-in way to split a string, but this query demonstrates a workaround based on the solution in this gist.

SELECT ?n ?regex ?component WHERE {
  # Start with one deeply hierarchical network=* value
  BIND("US:OH:SAN:Fremont" AS ?network)
  
  # Split the value at each colon up to 10 times
  BIND(":" AS ?separator)
  VALUES ?n { 0 1 2 3 4 5 6 7 8 9 }
  
  # For the nth split, build a regular expression that isolates the (n+1)th occurrence from the n previous occurrences and any subsequent text
  # Also match the full value if there are no more occurrences of the separator
  BIND(CONCAT("^(?:[^", ?separator, "]*", ?separator, "){", STR(?n), "}([^", ?separator, "]*).*$|.*") AS ?regex)
  
  # Replace the full value with the (n+1)th occurrence
  BIND(REPLACE(?network, ?regex, "$1") AS ?component)
  
  # Omit empty components, including components that are empty because there are fewer than 10 components
  FILTER(STRLEN(?component) > 0)
}
ORDER BY ?n

Run it (edit query)

Addressing

Streets in the U.S. with the most addresses

Displays a table of the streets in the United States that appear in the most street addresses. There are too many addresses in the world to efficiently search globally, so this query limits the candidate features to the U.S. by requiring addr:state=* to be set to a standard USPS state or territory abbreviation. To distinguish similarly named streets in different cities, it groups the results by ZIP code. The top row actually shows up because someone has tagged each individual solar panel in this solar farm with the nearby street.

SELECT ?street ?postcode (COUNT(*) AS ?count) WHERE {
  # Prevent the optimizer from searching for all addresses before we have the state abbreviations
  hint:Query hint:optimizer "None".
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # FIPS 5-2 alpha codes for states and territories of the United States 
    ?wd wdt:P5086 ?state.
  }
  
  # Query OpenStreetMap for addresses qualified by street, state, and ZIP code
  ?osm osmt:addr:state ?state;
       osmt:addr:postcode ?postcode;
       osmt:addr:street ?street.
}
GROUP BY ?postcode ?street
HAVING(?count > 500)
ORDER BY DESC(?count)

Run it (edit query)

Street addresses and Open Location Codes of schools in the Navajo Nation

Displays a table of street addresses and Open Location Codes (similar to Google's Plus Codes) of schools in the Navajo Nation. Many homes and points of interest in the Navajo Nation lack traditional street addresses, so a local community group has begun posting Plus Codes. [2] There have been proposals to enable searching by OLCs at openstreetmap.org. [3]

This query is rather verbose because SPARQL lacks an exponent operator or function. It is only possible to form base-10 exponents using scientific notation.

SELECT ?school ?address1 ?address2 ?olc WHERE {
  # Query OpenStreetMap for schools
  ?school osmt:amenity "school".
  
  # Filter to roughly the territory of the Navajo Nation
  SERVICE wikibase:box {
    ?school osmm:loc ?coordinates .
    bd:serviceParam wikibase:cornerSouthWest 'Point(-111.4601789 35.277247)'^^geo:wktLiteral.
    bd:serviceParam wikibase:cornerNorthEast 'Point(-108.3078547 37.3911089)'^^geo:wktLiteral.
  }
  
  # Get the first address line
  OPTIONAL {
    ?school osmt:addr:housenumber ?houseNumber;
            osmt:addr:street ?street.
    BIND(CONCAT(?houseNumber, " ", ?street) AS ?address1)
  }
  
  # Get the second address line
  OPTIONAL {
    ?school osmt:addr:city ?city.
    OPTIONAL {
      ?school osmt:addr:state ?state.
    }
    OPTIONAL {
      ?school osmt:addr:postcode ?zipCode.
    }
    BIND(CONCAT(?city, ", ", COALESCE(?state, ""), " ", COALESCE(?zipCode, "")) AS ?address2)
  }
  
  # Get the WGS84 coordinates relative to the South Pole at 90°S and the antimeridian at 180°W
  BIND(geof:latitude(?coordinates) + 90 AS ?latitude)
  BIND(geof:longitude(?coordinates) + 180 AS ?longitude)
  
  # Get the 20°×20° block as a pair of base-20 digits
  BIND(xsd:integer(FLOOR(?latitude / 20)) AS ?base20Latitude)
  BIND(xsd:integer(FLOOR(?longitude / 20)) AS ?base20Longitude)
  
  # Map the base-20 digits to OLC code digits
  BIND("23456789CFGHJMPQRVWX" AS ?digits)
  BIND(SUBSTR(?digits, ?base20Latitude + 1, 1) AS ?latitudeDigit)
  BIND(SUBSTR(?digits, ?base20Longitude + 1, 1) AS ?longitudeDigit)
  BIND(CONCAT(?latitudeDigit, ?longitudeDigit) AS ?block)
  
  # Subdivide the block and repeat the process for the next pair of base-20 digits
  BIND(xsd:integer(FLOOR(?latitude - (?base20Latitude * 20))) AS ?base20Latitude2)
  BIND(xsd:integer(FLOOR(?longitude - (?base20Longitude * 20))) AS ?base20Longitude2)
  
  # Map the base-20 digits to OLC code digits
  BIND(SUBSTR(?digits, ?base20Latitude2 + 1, 1) AS ?latitudeDigit2)
  BIND(SUBSTR(?digits, ?base20Longitude2 + 1, 1) AS ?longitudeDigit2)
  BIND(CONCAT(?latitudeDigit2, ?longitudeDigit2) AS ?block2)
  
  # Subdivide the block and repeat the process for the next pair of base-20 digits
  BIND(xsd:integer(FLOOR((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20)) AS ?base20Latitude3)
  BIND(xsd:integer(FLOOR((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20)) AS ?base20Longitude3)
  
  # Map the base-20 digits to OLC code digits
  BIND(SUBSTR(?digits, ?base20Latitude3 + 1, 1) AS ?latitudeDigit3)
  BIND(SUBSTR(?digits, ?base20Longitude3 + 1, 1) AS ?longitudeDigit3)
  BIND(CONCAT(?latitudeDigit3, ?longitudeDigit3) AS ?block3)
  
  # Subdivide the block and repeat the process for the next pair of base-20 digits
  BIND(xsd:integer(FLOOR(((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20 - ?base20Latitude3) * 20)) AS ?base20Latitude4)
  BIND(xsd:integer(FLOOR(((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20 - ?base20Longitude3) * 20)) AS ?base20Longitude4)
  
  # Map the base-20 digits to OLC code digits
  BIND(SUBSTR(?digits, ?base20Latitude4 + 1, 1) AS ?latitudeDigit4)
  BIND(SUBSTR(?digits, ?base20Longitude4 + 1, 1) AS ?longitudeDigit4)
  BIND(CONCAT(?latitudeDigit4, ?longitudeDigit4) AS ?block4)
  
  # Subdivide the block and repeat the process for the next pair of base-20 digits
  BIND(xsd:integer(FLOOR((((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20 - ?base20Latitude3) * 20 - ?base20Latitude4) * 20)) AS ?base20Latitude5)
  BIND(xsd:integer(FLOOR((((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20 - ?base20Longitude3) * 20 - ?base20Longitude4) * 20)) AS ?base20Longitude5)
  
  # Map the base-20 digits to OLC code digits
  BIND(SUBSTR(?digits, ?base20Latitude5 + 1, 1) AS ?latitudeDigit5)
  BIND(SUBSTR(?digits, ?base20Longitude5 + 1, 1) AS ?longitudeDigit5)
  BIND(CONCAT(?latitudeDigit5, ?longitudeDigit5) AS ?block5)
  
  # Subdivide the block into 20 cells and get a pair of base-20 digits
  BIND(xsd:integer(FLOOR(((((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20 - ?base20Latitude3) * 20 - ?base20Latitude4) * 20 - ?base20Latitude5) * 5)) AS ?base20Latitude6)
  BIND(xsd:integer(FLOOR(((((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20 - ?base20Longitude3) * 20 - ?base20Longitude4) * 20 - ?base20Longitude5) * 4)) AS ?base20Longitude6)
  
  # Get the cell’s OLC code digit
  BIND(?base20Latitude6 * 4 + 1 + ?base20Longitude6 / 4 AS ?cell)
  BIND(SUBSTR(?digits, ?cell, 1) AS ?cellDigit)
  
  # Combine all the digits, adding a + after 8 digits
  BIND(CONCAT(?block, ?block2, ?block3, ?block4, "+", ?block5, ?cellDigit) AS ?olc)
}

Run it (edit query)

Boundaries

Show geoshapes (geometries) of all political subdivisions of a country

#defaultView:MapRegions
SELECT 
  ?id
  (SAMPLE(?label_en) as ?label_en)
  (SAMPLE(?label_fr) as ?label_fr)
  (SAMPLE(?iso_3166_2) as ?iso_3166_2)
  (SAMPLE(?flag) as ?flag)
WHERE {
  # List of regions, whose sub-regions we want
  VALUES ?entity { wd:Q16 } 

  SERVICE <https://query.wikidata.org/sparql> { 
    # P150 = "contains administrative territorial entity"
    # but must not have a P582 (end date) qualifier
    ?entity p:P150 ?statement .
    ?statement ps:P150 ?id .
    FILTER NOT EXISTS { ?statement pq:P582 ?x }

    # Get labels, ISO code, and flag image
    # for each sub-region, if available
    OPTIONAL { ?id rdfs:label ?label_en . FILTER(LANG(?label_en) = "en") }
    OPTIONAL { ?id rdfs:label ?label_fr . FILTER(LANG(?label_fr) = "fr") }
    OPTIONAL { ?id wdt:P300 ?iso_3166_2 }
    OPTIONAL { ?id wdt:P41 ?flag }
  }
}
# remove possible ID duplicates
GROUP BY ?id

Run it (edit query)

Recursive subareas of a boundary relation

Displays a table of subareas of Indiana, their subareas, and so on recursively. osmm:has is one of the few uses of property paths within pure OpenStreetMap data.

SELECT ?subarea ?adminLevel ?name WHERE {
  # Query OpenStreetMap for members of the Indiana boundary relation, their members, and so on
  osmrel:161816 osmm:has+ ?subarea.
  
  # The subarea must have the role “subarea” in some relation (not necessarily the Indiana relation, but potentially an intermediate relation)
  ?area ?subarea "subarea".
  
  # Get the subarea’s name and administrative level
  ?subarea osmt:name ?name;
           osmt:admin_level ?adminLevel.
}
ORDER BY ?adminLevel ?name

Run it (edit query)

Construction

Distribution of floor counts among school buildings

Displays a line graph of the distribution of floor counts among school buildings and building parts. Each floor count is the number of floors above ground level, including the ground floor.

#defaultView:LineChart
SELECT ?floors (COUNT(*) AS ?buildings) WHERE {
  # Get school buildings and building parts and their floor counts
  VALUES ?schools { "school" "college" "university" "kindergarten" "childcare" }
  ?building (osmt:amenity|osmt:building|osmt:building:part) ?schools;
            osmt:building:levels ?levels.
  # Convert floor counts to numbers and round them
  BIND(ROUND(xsd:decimal(?levels)) AS ?floors)
  # Omit non-numeric floor counts
  FILTER(BOUND(?floors))
}
GROUP BY ?floors
ORDER BY ?floors

Run it (edit query)

Average building heights by floor count (slow)

Displays a line graph of average building heights (measured in meters) by the number of floors above ground level (including the ground floor). 3D building renderers typically assume that a floor is about 3 meters tall. This graph shows how well that assumption holds up among buildings and building parts that are tagged with both building:levels=* and building:height=* (or height=*).

#defaultView:LineChart
SELECT ?floorCount (AVG(?metersTall) AS ?averageHeight) WHERE {
  # Get buildings and building parts, as well as their floor counts and heights
  ?building osmt:building:levels ?levels;
            (osmt:height|osmt:building:height) ?height.
  # Convert floor counts to numbers
  BIND(xsd:decimal(?levels) AS ?floorCount)
  # Convert heights to numbers
  BIND(xsd:decimal(?height) AS ?metersTall)
  # Omit non-numeric floor counts and heights
  FILTER(BOUND(?floorCount) && BOUND(?metersTall))
}
GROUP BY ?floorCount
ORDER BY ?floorCount

Run it (edit query)

Most complex 3D buildings

Displays a table (which you can view as an interactive map) of the 250 most intricately mapped 3D buildings in OpenStreetMap, as measured by the number of building relation members. This query assumes the use of building:part=* areas in building relations, based on the Simple 3D buildings specification.

SELECT ?building (COUNT(?part) AS ?count) (GROUP_CONCAT(DISTINCT ?role; SEPARATOR=", ") AS ?roles) (SAMPLE(?coordinates) AS ?coordinates) WHERE {
  # Query OpenStreetMap for building relations
  ?building osmt:type "building";
            osmm:type "r";
            # Specifically members of those relations
            osmm:has ?part;
            # And the relations’ coordinates
            osmm:loc ?coordinates.
  
  # Collect the building members’ roles for informational purposes
  OPTIONAL {
    ?building ?part ?role.
  }
}
GROUP BY ?building
ORDER BY DESC(?count)
LIMIT 250

Run it (edit query)

Creative Commons–licensed 3D models of structures

Displays a gallery of 3D models of buildings and other structures mapped in OpenStreetMap that are available under Creative Commons licenses at Wikimedia Commons. This query demonstrates querying the MediaWiki API Query Service for categories that contain specific pages.

#defaultView:ImageGrid
SELECT DISTINCT ?osm ?model ?license WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None".
  
  # Query the Wikidata Query Service for
  SERVICE <https://query.wikidata.org/sparql> {
    # Models of things
    ?wd wdt:P4896 ?model.
  }
  
  # Query OpenStreetMap for features corresponding to these things
  # This greatly narrows the set of results, making the subsequent MediaWiki API call much faster
  ?osm osmt:wikidata ?wd.
  
  # Query the Wikidata Query Service again for
  SERVICE <https://query.wikidata.org/sparql> {
    # The same model of the same thing
    ?wd wdt:P4896 ?model.
    
    # Convert the file path URL into a file description page title
    BIND(CONCAT("File:", wikibase:decodeUri(STRAFTER(STR(?model), "/Special:FilePath/"))) AS ?title)
    
    # Query the MediaWiki API Query Service for
    SERVICE wikibase:mwapi {
      # Categories that contain these pages
      bd:serviceParam wikibase:api "Categories";
                      wikibase:endpoint "commons.wikimedia.org";
                      wikibase:limit 50;
                      mwapi:titles ?title.
      # Output the page title and category
      ?title wikibase:apiOutput mwapi:title.
      ?category wikibase:apiOutput mwapi:category.
    }
    
    # Only include the model if it has been categorized with a Creative Commons license
    FILTER STRSTARTS(?category, "Category:CC-")
    
    # Extract a “license” name out of the category page title
    BIND(STRAFTER(?category, "Category:") AS ?license)
  }
}

Run it (edit query)

Things that mimic chimneys

Sophox provides direct access to key usage statistics from taginfo, but not tag combinations. Moreover, taginfo does not track combinations of relatively rare keys and tags. The following query returns what taginfo's Combinations tab would report for mimics=chimney if it were a much more common tag, so you can find out what structures typically mimic chimneys and what else we know about these structures. It requires running two subqueries about tags and unioning the results to two subqueries about keys.

SELECT ?key ?value ?count (100 * ?count / ?count_all AS ?percent) WHERE {
  {
    # Count occurrences of tags on elements that also have the input tag
    {
      SELECT ?key ?value (COUNT(*) AS ?count) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value.
      }
      GROUP BY ?key ?value
    }
    # Count elements that have the input tag
    {
      # Only count each element once.
      SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value.
      }
    }
  } UNION {
    # Count occurrences of keys on elements that also have the input tag
    {
      SELECT ?key (COUNT(*) AS ?count) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value.
      }
      GROUP BY ?key
    }
    # Count elements that have the input tag
    {
      SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value.
      }
    }
  }
  # Filter out metadata predicates (e.g., rdf:, osmm: prefixes)
  FILTER(STRSTARTS(STR(?key), "https://wiki.openstreetmap.org/wiki/"))
}
ORDER BY DESC(?count)
LIMIT 50

Run it (edit query)

Users who maps things that mimic chimneys

The following query returns things that mimic chimneys along with a list of users who most recently edited an element with each tag combination. It uses an aggregate function to avoid having to nest yet another level of subqueries just to group the elements by user.

SELECT ?key ?value ?count (100 * ?count / ?count_all AS ?percent) ?users WHERE {
  {
    # Count occurrences of tags on elements that also have the input tag
    {
      # List the users for each tag, separated by semicolons, truncated at 100 characters for readability
      SELECT ?key ?value (COUNT(*) AS ?count) (SUBSTR(GROUP_CONCAT(DISTINCT ?user; SEPARATOR="; "), 0, 100) AS ?users) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value;
                 # Get the user who last modified the element
                 osmm:user ?user;
                 # Get the date the element was last modified
                 osmm:timestamp ?timestamp.
      }
      GROUP BY ?key ?value
      # Order the results from newest to oldest so that the list of users is in that order
      ORDER BY DESC(?timestamp)
    }
    # Count elements that have the input tag
    {
      SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value.
      }
    }
  } UNION {
    # Count occurrences of keys on elements that also have the input tag
    {
      # List the users for each key, separated by semicolons, truncated at 100 characters for readability
      SELECT ?key (COUNT(*) AS ?count) (SUBSTR(GROUP_CONCAT(DISTINCT ?user; SEPARATOR="; "), 0, 100) AS ?users) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value;
                 # Get the user who last modified the element
                 osmm:user ?user;
                 # Get the date the element was last modified
                 osmm:timestamp ?timestamp.
      }
      GROUP BY ?key
      # Order the results from newest to oldest so that the list of users is in that order
      ORDER BY DESC(?timestamp)
    }
    # Count elements that have the input tag
    {
      SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
        ?element osmt:mimics "chimney";
                 ?key ?value.
      }
    }
  }
  # Filter out metadata predicates (e.g., rdf:, osmm: prefixes)
  FILTER(STRSTARTS(STR(?key), "https://wiki.openstreetmap.org/wiki/"))
}
ORDER BY DESC(?count)
LIMIT 50

Run it (edit query)

Culture

Award-winning buildings

Displays a map of structures that have won architectural awards.

#defaultView:Map
SELECT DISTINCT ?osm ?name ?layer ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Architectural structures that won awards
    ?wd wdt:P31/wdt:P279* wd:Q811979;
        wdt:P166 ?award.
    # Where the awards are architectural awards
    ?award wdt:P31 wd:Q1829324.
    
    # Get the award’s name
    OPTIONAL {
      ?award rdfs:label ?layer.
      FILTER(LANG(?layer) = "en")
    }
  }
  
  # Query OpenStreetMap for those structures
  ?osm osmt:wikidata ?wd.
  
  # Get coordinates to display on a map
  ?osm osmm:loc ?coordinates.
  
  # Get the structure’s name
  OPTIONAL { ?osm osmt:name ?name }
}

Run it (edit query)

Depictions of fictional characters

Displays a map of artwork, monuments, and memorials that commemorate or depict fictional characters.

#defaultView:Map
SELECT DISTINCT ?wd ?wdLabel ?osm ?name ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for fictional characters or their depictions
  SERVICE <https://query.wikidata.org/sparql> {
    ?wd wdt:P180*/wdt:P31 wd:Q95074.
  }
  # Query OpenStreetMap for artwork, monuments, or memorials that commemorate or depict this character
  ?osm (osmt:wikidata|osmt:subject:wikidata) ?wd.
  {
    VALUES ?memorials { "memorial" "monument" }
    ?osm osmt:historic ?memorials.
  } UNION {
    ?osm osmt:tourism "artwork".
  }
  
  # Get coordinates to display on a map
  ?osm osmm:loc ?coordinates.
  
  # Get names
  OPTIONAL { ?osm osmt:name ?name }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Run it (edit query)

Sports pitches that require a certain number of players

Displays a map of sports pitches for team sports that require between 10 and 20 players on the field. It includes tags that have data items but not tag description pages.

#defaultView:Map
SELECT ?osm ?wdLabel ?maxPlayers ?coordinates WHERE {
  hint:Query hint:optimizer "None" .
  
  {
    SELECT ?v ?wdLabel ?maxPlayers WHERE {
      # Query the OpenStreetMap Wiki for data items of tags
      ?osmd osmdt:P10 osmd:Q723;
            # their key-value pairs
            osmdt:P19 ?kv;
            # their corresponding Wikidata concepts
            osmdt:P12 ?qid.

      # Convert the Wikidata QID from a string into a URL, which is how entities are normally represented
      BIND(IRI(CONCAT("http://www.wikidata.org/entity/", ?qid)) AS ?wd)
      
      # Isolate the value
      BIND(STRAFTER(?kv, "=") AS ?v)

      # Query Wikidata for
      SERVICE <https://query.wikidata.org/sparql> {
        # The matching Wikidata item as long as it is a team sport
        ?wd (wdt:P31|wdt:P279)+ wd:Q216048;
            wdt:P1873 ?maxPlayers.

        # As long as between 10 and 20 players would be on the field
        FILTER(?maxPlayers >= 10 && ?maxPlayers <= 20)

        # Get the item’s label
        OPTIONAL {
          ?wd rdfs:label ?wdLabel.
          FILTER(LANG(?wdLabel) = "en")
        }
      }
    }
  }
  
  # Query OpenStreetMap for features tagged with this sport
  ?osm osmt:sport ?v;
       osmm:loc ?coordinates.
}

Run it (edit query)

Flags by frequency and type

Displays a bar chart of flags by how frequently they have been tagged with flag:wikidata=*, color-coded by flag type. When multiple flags fly from a single flagpole, each flag is counted once. Flag types are determined through Wikidata statements instead of flag:type=* for reliability when multiple flags are on a single flagpole. Flag images from Wikimedia Commons are also included to enable an image grid view.

# Flags by frequency and type
# Flag images are included to enable an image grid view
#defaultView:BarChart
SELECT DISTINCT (SAMPLE(?name) AS ?name) (SAMPLE(?image) AS ?image) (SAMPLE(?count) AS ?count) (SAMPLE(?classLabel) AS ?classLabel) WHERE {
  # Query OpenStreetMap for flags and counts
  {
    # Limit to flagpoles that are tagged flag:wikidata=*
    # flag:type=* and flag:name=* are too ambiguous
    SELECT ?flag (COUNT(*) AS ?count) {
      ?flagpole osmt:flag:wikidata ?flag;
                osmt:man_made "flagpole".
    }
    GROUP BY ?flag
    # Exclude rarely mapped flags to keep the bar chart view’s X axis tidy
    HAVING (?count >= 5)
  }
  
  # Query Wikidata for these flags
  SERVICE <https://query.wikidata.org/sparql> {
    # Flags or flag designs
    VALUES ?flags { wd:Q14660 wd:Q69506823 }
    ?flag wdt:P31/wdt:P279* ?flags;
          wdt:P31 ?class.
    
    # Map common flag subclasses to succinct labels
    # In the bar chart view, each bar is color-coded by subclass
    VALUES (?class ?classLabel) {
      (wd:Q186516 "national")
      (wd:Q22807280 "regional")
      (wd:Q97486724 "local")
      (wd:Q21850100 "local")
      (wd:Q97486760 "local")
      (wd:Q602300 "military")
      (wd:Q27077627 "military")
      (wd:Q104911496 "military")
      (wd:Q83302753 "religious")
      (wd:Q74051479 "commercial")
      (UNDEF "other")
    }
    
    # Get the flag’s image
    OPTIONAL {
      ?flag wdt:P18 ?image;
            p:P18 ?imageStatement.
      # Excluding statements about former flag designs
      FILTER NOT EXISTS {
        ?imageStatement pq:P582 ?imageEndTime.
      }
    }
    
    # Get the flag’s name
    OPTIONAL {
      ?flag rdfs:label ?flagLabel.
      FILTER(LANG(?flagLabel) = "en")
      # Shorten the name to better fit as X axis labels
      BIND(REPLACE(REPLACE(?flagLabel, "^[Ff]lag of (?:the )?| flag$", ""), "United States ", "U.S. ") AS ?name)
    }
  }
}
GROUP BY ?flag
ORDER BY DESC(?count)

Run it (edit query)

Flags by frequency and represented population

Displays a scatter chart of flags by how frequently they have been mapped in OpenStreetMap and the number of people they represent. When multiple flags fly from a single flagpole, each flag is counted once.

# Flags by frequency and represented population
#defaultView:ScatterChart
SELECT DISTINCT (MAX(?population) AS ?population) (SAMPLE(?count) AS ?count) (SAMPLE(?name) AS ?name) WHERE {
  # Query OpenStreetMap for flags and counts
  {
    # Limit to flagpoles that are tagged flag:wikidata=*
    # flag:type=* and flag:name=* are too ambiguous
    SELECT ?flag (COUNT(*) AS ?count) {
      ?flagpole osmt:flag:wikidata ?flag;
                osmt:man_made "flagpole".
    }
    GROUP BY ?flag
    # Exclude rarely mapped flags to keep the legend from getting unwieldy
    HAVING (?count >= 5)
  }
  
  # Query Wikidata for these flags
  SERVICE <https://query.wikidata.org/sparql> {
    # Flags or flag designs
    VALUES ?flags { wd:Q14660 wd:Q69506823 }
    ?flag wdt:P31/wdt:P279* ?flags;
          # Representing or located in
          (wdt:P1001|wdt:P1268) ?subject.
    
    # Get the population or member count
    ?subject (wdt:P1082|wdt:P2124) ?population.
    
    # Get the flag’s name
    OPTIONAL {
      ?flag rdfs:label ?flagLabel.
      FILTER(LANG(?flagLabel) = "en")
      # Shorten the name to avoid redundancy in the legend
      BIND(REPLACE(?flagLabel, "^[Ff]lag of (?:the )?| flag$", "") AS ?name)
    }
  }
}
GROUP BY ?flag
ORDER BY DESC(?count)

Run it (edit query)

Official colors

Displays a map of things that have official colors in Wikidata, such as school colors. Each thing is marked by a dot of the official color. If something has multiple official colors, the colors are clustered: clicking the dot causes each a dot to branch out for each official color.

#defaultView:Map
SELECT DISTINCT ?osm ?name ?rgb ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for things that have
  SERVICE <https://query.wikidata.org/sparql> {
    # Hexadecimal RGB color codes of official colors
    ?wd wdt:P6364/wdt:P465 ?rgb.
  }
  
  # Query OpenStreetMap for those things
  ?osm osmt:wikidata ?wd.
  
  # Get coordinates to display on a map
  ?osm osmm:loc ?coordinates.
  
  # Get the structure’s name
  OPTIONAL { ?osm osmt:name ?name }
}

Run it (edit query)

Most prevalent cuisines

This query returns the 100 most prevalent cuisines. The cuisine=* key accepts a list of keywords separated by semicolons. osm2rdf does not automatically split cuisine=* by semicolons, but you can split them yourself using a regular expression:

SELECT (SAMPLE(?value) AS ?cuisine) (COUNT(*) AS ?count) WHERE {
  ?osm osmt:cuisine ?list.
  # Isolate every run of non-semicolon characters, deleting the surrounding text. Each regular expression match produces a separate query result before grouping.
  BIND(REPLACE(?list, ".*?([^;]+).*", "$1") AS ?value)
}
GROUP BY ?value
ORDER BY DESC(?count)
LIMIT 100

Run it (edit query)

Economy

Credit unions

Displays a map of credit union branch locations. The branches of a credit union are tagged amenity=bank like those of any other bank, but they have more restrictive membership criteria and different fee structures. This query only finds credit unions that are explicitly tagged with brand:wikidata=* or operator:wikidata=*. Alternatively, you could query for bank branches with "Credit Union" in the name, but this approach would not include credit unions that have less descriptive names.

#defaultView:Map
SELECT DISTINCT ?osm ?osmLabel ?wd ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Credit unions
    ?wd wdt:P31/wdt:P279* wd:Q745877.
  }
  
  # Query OpenStreetMap for branches operated by these credit unions
  ?osm (osmt:wikidata|osmt:brand:wikidata|osmt:operator:wikidata) ?wd;
       osmt:amenity "bank";
       osmm:loc ?coordinates.
  
  # Get the bank branches’ names
  OPTIONAL {
    ?osm osmt:name ?osmLabel
  }
}

Run it (edit query)

Proportion of eateries open on Sundays

Displays a bubble chart comparing the number of cafés, fast food restaurants, and sit-down restaurants that are open on Sundays to those that are closed on Sundays.

#defaultView:BubbleChart
SELECT ?sundayLabel (COUNT(*) AS ?count) WHERE {
  # Query OpenStreetMap for eateries tagged with opening hours
  VALUES ?eateries { "cafe" "fast_food" "restaurant" }
  ?eatery osmt:opening_hours ?openingHours;
          osmt:amenity ?eateries.
  
  # Crudely check the opening hours for a date specification that includes Sunday and is not a closure exception
  BIND(REGEX(?openingHours, "24/7|\\b(?:Su|(?:Tu|We|Th|Fr|Sa)-Mo|(?:We|Th|Fr|Sa)-(?:Mo|Tu)|(?:Th|Fr|Sa)-We|(?:Fr|Sa)-Th|Sa-Fr)(?:([-,]|[A-Z][a-z])+)*(?:\\[[^\\]]+\\])? +(?!off|closed)") AS ?sunday)
  
  # Map Boolean values to human-readable values
  VALUES (?sunday ?sundayLabel) {
    (true "Open")
    (false "Closed")
  }
}
GROUP BY ?sundayLabel

Run it (edit query)

Total electrical output

Displays the total electrical output of every electricity-generating feature in OpenStreetMap.

SELECT (COUNT(?osm) AS ?count) (SUM(?wattage) AS ?wattage) WHERE {
  # Query for electricity generators and their electrical output
  ?osm osmt:generator:output:electricity ?output.
  # Output must be expressed in metric units
  FILTER(STRENDS(?output, "W"))
  
  # Parse the output
  BIND(xsd:decimal(STRBEFORE(?output, " ")) AS ?quantity)
  BIND(STRAFTER(?output, " ") AS ?unit)
  
  # Map units to their conversion factors
  VALUES (?unit ?conversion) {
    ("W" 1)
    ("kW" 1e3)
    ("KW" 1e3)
    ("MW" 1e6)
    ("GW" 1e9)
    (UNDEF 0)
  }
  
  # Convert to watts
  BIND(?quantity * ?conversion AS ?wattage)
}

Run it (edit query)

Freely licensed vector logos of common brands

Displays an image gallery of freely licensed SVG logos of brands with at least 1,000 locations in OpenStreetMap. Non-SVG logos such as photos of signs are excluded. These logos are included in the Name Suggestion Index along with social media profile pictures.

#defaultView:ImageGrid
SELECT ?wd ?name ?logo ?count WHERE {
  hint:Query hint:optimizer "None" .
  
  {
    # Query OpenStreetMap for
    SELECT ?wd (COUNT(*) AS ?count) WHERE {
      # Commercial brands
      ?osm osmt:brand:wikidata ?wd.
    }
    GROUP BY ?wd
    # With at least 1,000 locations
    HAVING(?count >= 1000)
  }
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # The brand’s logo
    ?wd wdt:P154 ?logo.
    
    # As long as it’s an SVG image and not, say, a photo of a sign
    FILTER(STRENDS(STR(?logo), ".svg"))
    
    # Get the brand name
    OPTIONAL {
      ?wd rdfs:label ?name.
      FILTER(LANG(?name) = "en")
    }
  }
}
ORDER BY DESC(?count)

Run it (edit query)

Brands by number of locations and Twitter subscribers

Displays a scatter chart of each brand's locations in OpenStreetMap versus its number of Twitter subscribers.

#defaultView:ScatterChart
SELECT (SUM(?subscribers) AS ?subscribers) (SAMPLE(?count) AS ?count) (SAMPLE(?name) AS ?name) WHERE {
  hint:Query hint:optimizer "None" .
  
  {
    # Query OpenStreetMap for
    SELECT ?wd (COUNT(*) AS ?count) WHERE {
      # Commercial brands
      ?osm osmt:brand:wikidata ?wd.
    }
    GROUP BY ?wd
    # With at least 100 locations
    HAVING(?count >= 100)
  }
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # The brand’s Twitter user name and number of subscribers
    ?wd p:P2002 [ps:P2002 ?twitter; pq:P3744 ?subscribers].
    
    # Get the brand name
    OPTIONAL {
      ?wd rdfs:label ?name.
      FILTER(LANG(?name) = "en")
    }
  }
}
GROUP BY ?wd
ORDER BY DESC(?count)

Run it (edit query)

Highest fees

Displays a table of the 100 highest fees charged for using various kinds of facilities, normalized to United States dollars. This query is only a rough comparison based on some benchmark exchange rates tagged in Wikidata; the conversions are not regularly updated and may be months old.

SELECT ?osm ?amount ?iso4217 ?amountUSD WHERE {
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Currencies
    ?currency wdt:P31 wd:Q8142;
              # Their ISO 4217 codes
              wdt:P498 ?iso4217;
              # Their exchange rate statements
              p:P2284 ?rateStatement.
    
    OPTIONAL {
      # Preferred statement about a USD exchange rate
      ?rateStatement wikibase:rank wikibase:PreferredRank;
                     psv:P2284 [
                       wikibase:quantityAmount ?preferredConversion;
                       wikibase:quantityUnit wd:Q4917;
                     ].
    }
    OPTIONAL {
      # Normal statement about a USD exchange rate
      ?rateStatement wikibase:rank wikibase:NormalRank;
                     psv:P2284 [
                       wikibase:quantityAmount ?normalConversion;
                       wikibase:quantityUnit wd:Q4917;
                     ].
    }
    
    # Normally a triple would use the preferred statement if available, falling back to a normal statement
    # However, we have to do the fallback manually because we got the raw statements in order to filter by qualifier
    BIND(COALESCE(?preferredConversion, ?normalConversion) AS ?conversion)
    FILTER BOUND(?conversion)
  }
  
  # Query OpenStreetMap for prices of services
  ?osm osmt:charge ?charge.
  
  # Only flat fees, since rates by time are difficult to compare across service types
  FILTER REGEX(?charge, "^[0-9]+(?:\\.[0-9]+)? [A-Z]{3}$")
  
  # Parse the price and convert it from the local currency to USD
  BIND(xsd:decimal(STRBEFORE(?charge, " ")) AS ?amount)
  BIND(STRAFTER(?charge, " ") AS ?iso4217)
  BIND(?amount * ?conversion AS ?amountUSD)
}
ORDER BY DESC(?amountUSD)
LIMIT 100

Run it (edit query)

Education

Playground maps by frequency

Displays a table of playground maps and their depicted geographies by frequency. Blank political subdivision maps are included in the results, so you can switch to the image grid view to see visual approximations of the maps.

# Playground maps and their depicted geographies by frequency
SELECT (SAMPLE(?place) AS ?place) (SAMPLE(?placeName) AS ?placeName) (SAMPLE(?mapImage) AS ?mapImage) (SAMPLE(?count) AS ?count) WHERE {
  # Query OpenStreetMap for playground maps and counts
  {
    # Limit to maps that are tagged subject:wikidata=*
    # subject=* is too ambiguous
    SELECT ?place (COUNT(*) AS ?count) {
      ?map osmt:playground "map";
           osmt:subject:wikidata ?place.
    }
    GROUP BY ?place
  }
  
  # Query Wikidata for the depicted geographies
  SERVICE <https://query.wikidata.org/sparql> {
    ?place wdt:P31/wdt:P279* wd:Q17334923.
    
    # Get the geography’s location map (blank political subdivision map)
    OPTIONAL {
      ?place wdt:P1943 ?mapImage.
    }
    
    # Get the geography’s name
    OPTIONAL {
      ?place rdfs:label ?placeName.
      FILTER(LANG(?placeName) = "en")
    }
  }
}
GROUP BY ?place
ORDER BY DESC(?count)

Run it (edit query)

Grade distribution of schools that have playground maps

Displays a table of U.S.-style grade levels and the number of schools that offer that grade and a have a playground map.

# Grade distribution of schools that have playground maps
SELECT ?grade (COUNT(*) AS ?count) (SAMPLE(?gradeNumber) AS ?gradeNumber) WHERE {
  {
    # Query OpenStreetMap for playground maps and grades of nearby schools
    SELECT ?map (SAMPLE(?school) AS ?school) (SAMPLE(?schoolGrades) AS ?schoolGrades) (MIN(?distance) AS ?distance) WHERE {
      # Playground maps and their coordinates
      ?map osmt:playground "map";
           osmm:loc ?mapCoordinates.
      # School-like campuses
      VALUES ?schools { "school" "kindergarten" "childcare" }
      ?school osmt:amenity ?schools.
      
      # Require each school to be within 100 m of a playground map
      SERVICE wikibase:around {
        ?school osmm:loc ?schoolCoordinates.
        bd:serviceParam wikibase:center ?mapCoordinates;
                        wikibase:radius "0.1";
                        wikibase:distance ?distance.
      }
      
      # Get the school’s grades
      OPTIONAL {
        ?school osmt:grades ?schoolGrades.
      }
    }
    GROUP BY ?map
  }
  
  # Associate each grade with a numeric grade number for sorting
  VALUES (?grade ?gradeNumber) {
    ("PK" -3) ("TK" -2) ("JK" -1) ("K" 0)
    ("1" 1) ("2" 2) ("3" 3) ("4" 4)
    ("5" 5) ("6" 6) ("7" 7) ("8" 8)
    ("9" 9) ("10" 10) ("11" 11) ("12" 12)
    ("13" 13) ("SP" 14)
  }
  # Associate each grade with a regular expression that detects the grade within a range such as “K-8”
  VALUES (?grade ?gradeRegex) {
    ("PK" "PK") ("TK" "TK") ("JK" "JK") ("K" "K")
    ("1" "1|[PTJ]K-([2-9]|1[0-3])") ("2" "2|([PTJ]K|1)-([3-9]|1[0-3])") ("3" "3|([PTJ]K|[12])-([4-9]|1[0-3])") ("4" "4|([PTJ]K|[1-3])-([5-9]|1[0-3])")
    ("5" "5|([PTJ]K|[1-4])-([6-9]|1[0-3])") ("6" "6|([PTJ]K||[1-5])-([7-9]|1[0-3])") ("7" "7|([PTJ]K|[1-6])-([89]|1[0-3])") ("8" "8|([PTJ]K|[1-7])-(9|1[0-3])")
    ("9" "9|([PTJ]K|[1-8])-(1[0-3])") ("10" "10|([PTJ]K|[1-9])-(1[1-3])") ("11" "11|([PTJ]K|[1-9]|10)-(1[23])") ("12" "12|([PTJ]K|[1-9]|1[01])-13")
    ("13" "13") ("SP" "SP")
  }
  # Filter by whether the school offers the grade
  FILTER REGEX(?schoolGrades, CONCAT("(^|[-,;])(", ?gradeRegex, ")([-,;]|$)"))
}
# Pivot by grade
GROUP BY ?grade
ORDER BY ?gradeNumber

Run it (edit query)

History

Whig memorials and namesakes

Displays a map of anything that memorializes or is named after a Whig Party member.

#defaultView:Map
SELECT DISTINCT ?wd ?wdLabel ?osm ?name ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for people or their namesakes who
  SERVICE <https://query.wikidata.org/sparql> {
    # Belong to the Whig Party in the United States
    ?wd wdt:P138*/wdt:P102 wd:Q42183.
  }
  # Query OpenStreetMap for features that are named after this person, commemorate this person, or are their namesake
  ?osm (osmt:wikidata|osmt:name:etymology:wikidata|osmt:subject:wikidata) ?wd.
  
  # Get coordinates to display on a map
  ?osm osmm:loc ?coordinates.
  
  # Get names
  OPTIONAL { ?osm osmt:name ?name }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Run it (edit query)

Decommissioned aircraft by manufacturer

Displays an interactive map of decommissioned aircraft (historic=aircraft), color-coded by manufacturer based on manufacturer:wikidata=* and/or aircraft:model:wikidata=*. Use the layer selector to filter the map by manufacturer.

#defaultView:Map
SELECT DISTINCT ?osm ?makeLabel ?modelLabel ?layer ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query OpenStreetMap for decommissioned aircraft
  ?osm osmt:historic "aircraft".
  
  OPTIONAL {
    # Get the make and/or model
    ?osm (osmt:manufacturer:wikidata|osmt:aircraft:model:wikidata) ?makeModel.
    
    # Query Wikidata for the make/model
    SERVICE <https://query.wikidata.org/sparql> {
      {
        # If it’s a model, get the make
        ?makeModel wdt:P31 wd:Q76920834;
                   wdt:P176 ?make.
        BIND(?makeModel AS ?model)
        
        # Get the model name
        OPTIONAL {
          ?model rdfs:label ?modelLabel.
          FILTER(LANG(?modelLabel) = "en")
        }
      } UNION {
        # A make must be a business to distinguish it from a model
        ?makeModel wdt:P31/wdt:P279* wd:Q4830453.
        BIND(?makeModel AS ?make)
      } UNION {
        # Or it must be part of an industry (e.g., Wright Brothers)
        ?makeModel (wdt:P106|wdt:P452) [].
        BIND(?makeModel AS ?make)
      }
      
      # Get the manufacturer’s name
      OPTIONAL {
        ?make rdfs:label ?makeLabel.
        FILTER(LANG(?makeLabel) = "en")
      }
    }
  }
  BIND(COALESCE(?makeLabel, "Unknown") AS ?layer)
  
  # Get coordinates to display on a map
  ?osm osmm:loc ?coordinates.
}
ORDER BY ASC(?makeLabel)

Run it (edit query)

Language

Default languages

Displays a table of administrative boundary relations and the default languages of names within them, as indicated by default_language=*. For convenience, the ISO 3166 alpha-2 code is also provided.

SELECT DISTINCT ?osm ?adminLevel ?name ?iso3166 ?defaultLanguage WHERE {
  # Query OpenStreetMap for default languages of administrative boundary relations
  ?osm osmt:default_language ?defaultLanguage;
       osmt:boundary "administrative";
       osmm:type "r".
  
  # Get the admin level
  OPTIONAL {
    ?osm osmt:admin_level ?adminLevel.
  }
  
  # Get the name
  OPTIONAL {
    ?osm osmt:name ?name.
  }
  
  # Get the ISO 3166 alpha-2 code
  OPTIONAL {
    ?osm (osmt:ISO3166-1|osmt:ISO3166-1:alpha2|osmt:ISO3166-2) ?iso3166.
  }
}
ORDER BY ?adminLevel ?iso3166

Run it (edit query)

Countries with the most localized names

Displays a table of admin_level=2 boundary relations sorted by the number of name:* tags (which are mostly translations).

SELECT ?osm (SAMPLE(?name) AS ?name) (COUNT(*) AS ?languages) WHERE {
  # Query OpenStreetMap for top-level administrative boundary relations
  ?osm osmt:boundary "administrative";
       osmt:admin_level "2";
       osmm:type "r";
       # Get the name
       osmt:name ?name;
       # Get every predicate (including every key)
       ?predicate [].
  # Only predicates that are keys beginning with “name:”
  FILTER(STRSTARTS(STR(?predicate), "https://wiki.openstreetmap.org/wiki/Key:name:"))
}
GROUP BY ?osm
ORDER BY DESC(?languages)

Run it (edit query)

Homographs

Displays a table of names that have multiple distinct pronunciations tagged with name:pronunciation=*. In reality, there are many more homographs in OpenStreetMap, but typically the most "obvious" pronunciation goes untagged because text-to-speech engines would likely pronounce it correctly anyways.

SELECT ?name ?ipa1 (SAMPLE(?osm1) AS ?osm1) ?ipa2 (SAMPLE(?osm2) AS ?osm2) WHERE {
  ?osm1 osmt:name:pronunciation ?ipa1;
        osmt:name ?name.
  ?osm2 osmt:name:pronunciation ?ipa2;
        osmt:name ?name.
  FILTER NOT EXISTS {
    ?osm2 osmt:name:pronunciation ?ipa1.
  }
  FILTER(?ipa1 < ?ipa2)
}
GROUP BY ?name ?ipa1 ?ipa2
ORDER BY ASC(?name)

Run it (edit query)

Town names outside the Basic Multilingual Plane

Displays a table of towns whose names include characters that fall outside the Basic Multilingual Plane of Unicode. This most frequently occurs with place names in Chinese. Some renderers, including Mapbox GL, are unable to render characters in higher planes, so they label these places incorrectly or not at all.

SELECT ?place ?name ?character WHERE {
  # Query OpenStreetMap for towns and their names
  ?place osmt:place "town";
         osmt:name ?name.
  
  # As long as the name includes a character between U+10000 and U+10FFFF
  FILTER REGEX(?name, "[\\x{10000}-\\x{10FFFF}]")
  # Can also be written this way, meaning “not between U+0000 and U+FFFF”
#  FILTER REGEX(?name, "[^\\x00-\\uFFFF]")
  
  # Isolate the character
  BIND(REPLACE(?name, "^.*([\\x{10000}-\\x{10FFFF}]+).*$", "$1") AS ?character)
}
ORDER BY ?character

Run it (edit query)

Regional capital cities in Chinese

Displays a table of regional capital cities (capital=4) and their names in Chinese. A renderer could use a lookup table like this to more thoroughly localize a world map into a single language even where the local mapping community has ensured that language-suffixed name=* tags avoid transliteration.

SELECT ?osm ?name ?wd ?wdLabel WHERE {
  # Query OpenStreetMap for regional capital cities
  ?osm osmt:place ?place;
       osmt:capital "4";
       osmt:name ?name;
       osmt:wikidata ?wd.
  
  # Query Wikidata for the corresponding item
  SERVICE <https://query.wikidata.org/sparql> {
    # Get the item’s label in Chinese
    ?wd rdfs:label ?wdLabel.
    FILTER(LANG(?wdLabel) = "zh")
  }
}
ORDER BY ?name

Run it (edit query)

Place names in emoji 😎

Displays a table of places in OpenStreetMap and their names in native languages and emoji. This query demonstrates Sophox as an alternative to a rejected proposal and reverted changeset that would have tagged these places with name:Zsye=*. (Tip: use the scatterplot view to see how some of the abbreviations that are behind how emoji flags function try to base themselves on the English name, whereas others use a non-English name or resort to a less intuitive initialism)

SELECT (SAMPLE(?osm) AS ?osm) (SAMPLE(?name) AS ?name) (SAMPLE(?unicode) AS ?name_Zsye) WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query Wikidata for places that have Unicode representations or whose flags have Unicode representations
  SERVICE <https://query.wikidata.org/sparql> {
    ?wd wdt:P625 ?coordinates.
    {
      ?wd wdt:P487 ?unicode.
    } UNION {
      ?wd wdt:P163 ?flag.
      ?flag wdt:P487 ?unicode.
    }
  }
  
  # Query OpenStreetMap for places corresponding to those things
  ?osm osmt:wikidata ?wd.
  
  # Get the place’s name
  OPTIONAL {
    ?osm osmt:name ?name
  }
}
GROUP BY ?wd
ORDER BY ASC(?name_Zsye)

Run it (edit query)

Languages by number of translations and speakers

Displays a scatter chart of languages by how many OpenStreetMap features have names in that language (along the Y axis) and how many people speak the language (along the X axis), to give a sense of which languages are over- or underrepresented in international place names. This query does not consider names in local languages. For performance reasons, this query assumes the maximum number of speakers for a given language and only considers alpha-2 and alpha-3 language codes, not full IETF/BCP47 language tags.

#defaultView:ScatterChart
SELECT (MAX(?speakers) AS ?speakers) (SAMPLE(?count) AS ?count) (SAMPLE(?languageLabel) AS ?languageLabel) WHERE {
  hint:Query hint:optimizer "None" .
  
  # Query the OpenStreetMap Wiki for data items and their usage statistics.
  ?osmd osmdt:P16 ?key;
        osmm:count_all ?count.
  
  # Only name keys
  FILTER STRSTARTS(?key, "name:")
  BIND(STRAFTER(?key, "name:") AS ?bcp47)
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Modern languages and their IETF language tags and number of speakers
    ?language wdt:P31 wd:Q1288568;
              wdt:P305 ?bcp47;
              wdt:P1098 ?speakers.
    
    # Get the language’s name in English
    OPTIONAL {
      ?language rdfs:label ?languageLabel.
      FILTER(LANG(?languageLabel) = "en")
    }
  }
}
GROUP BY ?osmd ?language
ORDER BY DESC(?count)

Run it (edit query)

Localizable keys

Displays a table of keys that have language-specific variants (such as :en) that are used at least 1,000 times.

SELECT (SAMPLE(?baseKey) AS ?baseKey) ?baseKeyID (SUM(?count) AS ?count) WHERE {
  # Query the OpenStreetMap Wiki for data items about keys
  ?key osmdt:P16 ?keyID;
       # Get the number of features tagged with this key
       osmm:count_all ?count.
  
  # Only include keys that contain a 2-letter suffix
  # Language codes can also be 3 characters long or include script and region codes
  # But assume that any localizable key is localized into a language with an ISO 639-1 alpha-2 code
  FILTER REGEX(?keyID, ":[a-z]{2}(?::.+)?$")
  
  # Isolate the base key without the language suffix
  BIND(REPLACE(?keyID, "^(.+):[a-z]{2}(:.+)?$", "$1$2") AS ?baseKeyID)
  
  # Omit language:*=*, which indicates whether a language is spoken or taught but which isn’t localized per se
  FILTER(?baseKeyID != "language")
  
  # Isolate the language suffix
  BIND(REPLACE(?keyID, "^.+:([a-z]{2})(?::.+)?$", "$1") AS ?language)
  
  # Omit two-letter prepositions and common words
  FILTER(?language != "id" && ?language != "in" && ?language != "to")
  
  # Query the OpenStreetMap Wiki for the base key
  OPTIONAL {
    ?baseKey osmdt:P16 ?baseKeyID.
  }
}
GROUP BY ?baseKeyID
HAVING(?count > 1000)
ORDER BY DESC(?count)

Run it (edit query)

Nature

Threatened species of trees

Displays an interactive map of trees whose species is categorized as threatened on the IUCN Red List.

#defaultView:Map
SELECT ?tree ?taxon ?taxonLabel ?layer ?coordinates WHERE {
  # Query OpenStreetMap for trees and their taxa
  ?tree osmt:natural "tree";
        (osmt:genus:wikidata|osmt:species:wikidata|osmt:taxon:wikidata) ?taxon;
        osmm:loc ?coordinates.
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Taxa categorized as threatened on the IUCN Red List
    VALUES (?status ?layer) {
      (wd:Q278113 "Vulnerable (VU)")
      (wd:Q11394 "Endangered (EN)")
      (wd:Q219127 "Critically endangered (CR)")
    }
    ?taxon wdt:P141 ?status.
    
    # Get the English common name
    OPTIONAL {
      ?taxon wdt:P1843 ?taxonLabel.
      FILTER(LANG(?taxonLabel) = "en")
    }
  }
}

Run it (edit query)

Religion

Protestant denomination tags

Displays a table of key=value pairs of tags denoting Protestant Christian denominations. denomination=protestant is only for cases where you do not know the more specific denomination associated with a given place of worship. This table allows a renderer developer to use the same icon for all Protestant denominations. It includes tags that have data items but not tag description pages.

SELECT DISTINCT ?osmd ?kv ?wd ?wdLabel WHERE {
  # Query the OpenStreetMap Wiki for data items of tags
  ?osmd osmdt:P10 osmd:Q211;
        # their key-value pairs
        osmdt:P19 ?kv;
        # their corresponding Wikidata concepts
        osmdt:P12 ?qid.
  
  # Convert the Wikidata QID from a string into a URL, which is how entities are normally represented
  BIND(IRI(CONCAT("http://www.wikidata.org/entity/", ?qid)) AS ?wd)
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # The matching Wikidata item as long as it is a part or subclass of Protestantism or broke away from a Protestant denomination
    ?wd (wdt:P279|wdt:P361|wdt:P807)* wd:Q23540.
    
    # Get the item’s label
    OPTIONAL {
      ?wd rdfs:label ?wdLabel.
      FILTER(LANG(?wdLabel) = "en")
    }
  }
}
ORDER BY ?kv

Run it (edit query)

Southern Baptist church services by day of the week

Displays a table of the days of the week and the number of Southern Baptist churches that have worship services on each day, based on the service_times=* key.

In the process of isolating the days of the week, this query shows how to parse keys parts of the opening_hours=* syntax. The string splitting in this query is based on the solution in this gist.

SELECT ?day (SAMPLE(?dayLabel) AS ?dayLabel) (SUM(?open) AS ?count) WHERE {
  # Get Southern Baptist churches and their worship times
  ?osm osmt:amenity "place_of_worship";
       osmt:religion "christian";
       osmt:denomination "southern_baptist";
       osmt:service_times ?timeDomain.
  
  # Multiple rule sequences are delimited by semicolons or “||” operators
  # Split the time domain into up to 5 rule sequences
  # This is akin to an array of indices that you iterate over in an imperative programming language
  # The number of splits increases the number of rows combinatorically, so increasing this number can greatly affect performance
  VALUES ?i { 0 1 2 3 4 5 }
  
  # For the ith split, build a regular expression that isolates the (i+1)th rule sequence from the i previous rule sequences and any subsequent text
  # Also clear out the full time domain if there are no more occurrences of the separator
  BIND(CONCAT("^(?:.*?(?:;|\\|\\|) *){", STR(?i), "}((?:[^;|]|\\|(?!\\|))*).*$|.*") AS ?ruleSequenceRegex)
  
  # Replace the full time domain with the (i+1)th rule sequence
  BIND(REPLACE(?timeDomain, ?ruleSequenceRegex, "$1") AS ?rules)
  
  # Omit empty rule sequences, including rule sequences that are empty because there are fewer than ?i rule sequences
  FILTER(STRLEN(?rules) > 0)
  
  # Split the comma-delimited rule sequence into up to 4 rules
  # This is akin to an array of indices that you iterate over in an imperative programming language
  # The number of splits increases the number of rows combinatorically, so increasing this number can greatly affect performance
  VALUES ?j { 0 1 2 3 4 5 }
  
  # For the jth split, build a regular expression that isolates the (j+1)th rule from the j previous rules and any subsequent text
  # Also clear out the full rule sequence if there are no more occurrences of the separator
  BIND(CONCAT("^(?:.*?, *){", STR(?j), "}([^,]*).*$|.*") AS ?ruleRegex)
  
  # Replace the full rule sequence with the (j+1)th rule
  BIND(REPLACE(?rules, ?ruleRegex, "$1") AS ?rule)
  
  # Omit empty rules, including rules that are empty because there are fewer than ?j rules
  FILTER(STRLEN(?rule) > 0)
  
  # Omit rules that say the church is closed on a given day or equivocate about whether it’s open on that day
  FILTER(!REGEX(?rule, "^(?:Su|Mo|Tu|We|Th|Fr|Sa)(?:-(?:Su|Mo|Tu|We|Th|Fr|Sa)) +(?:off|closed|unknown|\")"))
  
  # Isolate the rule’s day (or its start day, in the case of a range) at the beginning of the rule
  # Also clear out the full rule if no day is specified; an additional rule that only specifies a time is unimportant for this query
  BIND(REPLACE(?rule, "^(Su|Mo|Tu|We|Th|Fr|Sa)\\b.+$|.*", "$1") AS ?startDay)
  
  # Map the two-letter start day abbreviation to a number that can be compared
  VALUES (?startDay ?startDayNumber) {
    ("Su" 0) ("Mo" 1) ("Tu" 2) ("We" 3) ("Th" 4) ("Fr" 5) ("Sa" 6)
  }
  
  # Isolate the rule’s end day, in case of a range
  # Also clear out the full rule if no end day is specified
  BIND(REPLACE(?rule, "^(?:Su|Mo|Tu|We|Th|Fr|Sa)-(Su|Mo|Tu|We|Th|Fr|Sa)\\b.+$|.*", "$1") AS ?rawEndDay)
  
  # For non-ranges, the end day is the same as the start day
  BIND(IF(STRLEN(?rawEndDay) > 0, ?rawEndDay, ?startDay) AS ?endDay)
  
  # Map the two-letter end day abbreviation to a number that can be compared
  VALUES (?endDay ?endDayNumber) {
    ("Su" 0) ("Mo" 1) ("Tu" 2) ("We" 3) ("Th" 4) ("Fr" 5) ("Sa" 6)
  }
  
  # Map the day number to a human-readable day name
  # Since ?day was unbound until now, this statement also has the effect of producing a separate row for each day of the week
  VALUES (?day ?dayLabel) {
    (0 "Sunday")
    (1 "Monday")
    (2 "Tuesday")
    (3 "Wednesday")
    (4 "Thursday")
    (5 "Friday")
    (6 "Saturday")
  }
  
  # The church is open if the range started no later than this day and ends no earlier than this day
  # Normally, this is just a matter of comparing the day numbers
  # However, if a range like “Sa-Su” wraps around the weekend, all that matters is that the range ends no earlier than this day
  # Produce a 0 or 1 that gets summed up when grouping by day
  BIND(IF((?startDayNumber <= ?day || ?startDayNumber > ?endDayNumber) && ?endDayNumber >= ?day, 1, 0) AS ?open)
}
GROUP BY ?day
ORDER BY ?day

Run it (edit query)

Safety

Most prolific mappers of fire stations

Displays a table of the users who are the most recent editors of the most fire stations, excluding bots.

SELECT ?user (COUNT(*) AS ?count) WHERE {
  # Query OpenStreetMap for fire stations and the users who last edited them
  ?fire_station osmt:amenity "fire_station";
                osmm:user ?user.
  
  # Exclude user names that end with the word "bot"
  FILTER(!REGEX(?user, "\\bbot$"))
}
GROUP BY ?user
ORDER BY DESC(?count)
LIMIT 10

Run it (edit query)

Police stations near fire stations

Displays a table of 100 police stations located within 0.5 kilometres (0.31 mi) of a fire station.

SELECT * WHERE {
  # Query OpenStreetMap for fire stations
  ?fireStation osmt:amenity "fire_station";
               osmm:loc ?fireStationCoordinates.
  # And police stations
  ?policeStation osmt:amenity "police".
  
  # Require each police station to be within 0.5 km of a fire station
  SERVICE wikibase:around { 
    ?policeStation osmm:loc ?policeStationCoordinates.
    bd:serviceParam wikibase:center ?fireStationCoordinates;
                    wikibase:radius "0.5";
                    wikibase:distance ?distance.
  }
}
LIMIT 100

Run it (edit query)

Outdoor warning sirens by make and model

Displays a map of outdoor warning sirens, color-coded by manufacturer. A layer selector lets you toggle individual manufacturers on and off. Clicking a siren shows its make and model if available.

#defaultView:Map
SELECT DISTINCT ?osm ?makeLabel ?modelLabel ?layer ?coordinates WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
  hint:Query hint:optimizer "None" .
  
  # Query OpenStreetMap for outdoor warning sirens
  ?osm osmt:emergency "siren".
  
  OPTIONAL {
    # Get the make and/or model
    ?osm (osmt:manufacturer:wikidata|osmt:siren:model:wikidata) ?makeModel.
    
    # Query Wikidata for the make/model
    SERVICE <https://query.wikidata.org/sparql> {
      {
        # If it’s a model, get the make
        ?makeModel wdt:P31 wd:Q76920834;
                   wdt:P176 ?make.
        BIND(?makeModel AS ?model)
        
        # Get the model name
        OPTIONAL {
          ?model rdfs:label ?modelLabel.
          FILTER(LANG(?modelLabel) = "en")
        }
      } UNION {
        # A make must be a business to distinguish it from a model
        ?makeModel wdt:P31/wdt:P279* wd:Q4830453.
        BIND(?makeModel AS ?make)
      }

      # Get the manufacturer’s name
      OPTIONAL {
        ?make rdfs:label ?makeLabel.
        FILTER(LANG(?makeLabel) = "en")
      }
    }
  }
  BIND(COALESCE(?makeLabel, "Unknown") AS ?layer)
  
  # Get coordinates to display on a map
  ?osm osmm:loc ?coordinates.
}
ORDER BY ASC(?makeLabel)

Run it (edit query)

Transportation

Cycling routes by network

Displays a tree map of cycling route relations in the United States organized according to the hierarchical cycle_network=* tagging scheme.

#defaultView:TreeMap
SELECT DISTINCT ?one ?two ?three ?ref ?route WHERE {
  # Cycling routes
  ?route osmt:cycle_network ?network;
         osmt:type "route";
         osmt:route "bicycle";
         osmm:type "r";
         osmt:ref ?ref.
  
  # Belonging to U.S. route networks
  FILTER(STRSTARTS(?network, "US:"))
  
  # Country
  BIND(STRBEFORE(?network, ":") AS ?one)
  BIND(STRAFTER(?network, ":") AS ?afterOne)
  
  # State
  BIND(IF(CONTAINS(?afterOne, ":"), STRBEFORE(?afterOne, ":"), ?afterOne) AS ?two)
  BIND(STRAFTER(?afterOne, ":") AS ?afterTwo)
  
  # Locality
  BIND(IF(CONTAINS(?afterTwo, ":"), STRBEFORE(?afterTwo, ":"), ?afterTwo) AS ?three)
}

Run it (edit query)

Public transportation route colors

Displays a bubble chart of colors by the number of public transportation routes that are assigned a given color. The colour=* key can be set to either a CSS color keyword or a hexadecimal RGB triplet, so this chart conflates the two syntaxes using Wikidata. For performance reasons, only rail routes are included in this chart. A less eye-catching but more informative chart would arrange the data points in a color coordinate space. [4]

#defaultView:BubbleChart
SELECT (SAMPLE(?name) AS ?name) (COUNT(DISTINCT ?route) AS ?count) ?rgb WHERE {
  # Query OpenStreetMap for public transportation route relations and their colors
  VALUES ?types { "route" "route_master" }
  VALUES ?routes { "train" "light_rail" "subway" "tram" }
  ?route osmt:route ?routes;
         osmt:type ?types;
         osmt:colour ?color;
         osmm:type "r".
  
  # Some colors are tagged as hexadecimal RGB triplets
  OPTIONAL {
    FILTER(STRSTARTS(?color, "#"))
    BIND(REPLACE(UCASE(?color), "#", "") AS ?rgb)
  }
  
  # Some colors are tagged as CSS color keywords
  OPTIONAL {
    FILTER(!STRSTARTS(?color, "#"))
    BIND(LCASE(?color) AS ?css)
  }
  
  # Conflate CSS color keywords with hexadecimal RGB triplets by querying Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    OPTIONAL {
      # Colors corresponding to the CSS color keyword
      ?wd wdt:P31/wdt:P279* wd:Q1075;
          wdt:P8112 ?css;
          wdt:P465 ?rgb.
    }
  }
  
  # A bubble needs a name to show up at all
  BIND(COALESCE(?css, ?rgb) AS ?name)
}
GROUP BY ?rgb

Run it (edit query)

Highest-numbered junctions

Displays a map of the 1,000 highest-numbered highway=motorway_junctions, highway=motorway_link, highway=trunk_link, etc. Many of these junction numbers are formed by combining a route number with a sequential junction number. However, the returned junctions along Interstate 10 in Texas are mileages measured from the Texas–New Mexico state line at the other end of Texas.

#defaultView:Map{"hide": "?coordinates"}
SELECT ?osm ?ref ?coordinates WHERE {
  {
    # Numbered junctions
    ?osm osmt:highway "motorway_junction";
         osmt:ref ?ref.
    # At least 3 digits
    FILTER(STRLEN(?ref) > 2)
  } UNION {
    # Numbered ramps and slip roads
    ?osm osmt:highway ?class;
         (osmt:junction:ref|osmt:junction_ref) ?ref.
    # At least 3 digits
    FILTER(STRENDS(?class, "_link") && STRLEN(?ref) > 2)
  }
  
  # Get the coordinates
  ?osm osmm:loc ?coordinates.
  
  # Isolate the digits, in case the junction number has an alphabetic prefix or suffix
  BIND(xsd:decimal(REPLACE(?ref, "^.+\\b([0-9]{3,})\\b.+$", "$1")) AS ?number)
}
ORDER BY DESC(?number)
LIMIT 1000

Run it (edit query)

Wrong-way concurrencies (slow)

Displays a table of wrong-way concurrencies – that is, roadways that belong to two routes but go in the opposite cardinal direction along either route according to guide signs. (Compare to OverpassQL)

SELECT DISTINCT ?way ?network1 ?ref1 ?role1 ?network2 ?ref2 ?role2 WHERE {
  # Roadways
  ?way osmt:highway [];
       osmm:type "w".
  
  # Two different road routes
  ?route1 osmm:has ?way;
          osmt:route "road";
          osmt:type "route";
          osmm:type "r";
          osmt:network ?network1;
          osmt:ref ?ref1;
          ?way ?role1.
  ?route2 osmm:has ?way;
          osmt:route "road";
          osmt:type "route";
          osmm:type "r";
          osmt:network ?network2;
          osmt:ref ?ref2;
          ?way ?role2.
  FILTER(?route1 != ?route2)
  
  # The two routes must have two different cardinal direction roles for the same member way
  FILTER(?role1 < ?role2)
  FILTER(?role1 IN("north", "south", "east", "west") && ?role2 IN("north", "south", "east", "west"))
}

Run it (edit query)

MUTCD traffic signs by series and color

Displays a map of traffic signs conforming to the Manual on Uniform Traffic Control Devices (a predominantly U.S. standard). Each sign series is a separate layer that you can toggle on and off. Each marker is color-coded by the sign's background color. Click a marker to show a diagram of the sign. This query only displays signs for which there are corresponding Wikidata items. As of July 2020, Wikidata items have only been created for several of the most commonly mapped signs.

#defaultView:Map{"hide": ["?coordinates", "?rgb"]}
SELECT DISTINCT ?osm ?mutcd ?image ?coordinates ?layer ?rgb WHERE {
  hint:Query hint:optimizer "None".
  
  {
    # Query Wikidata for
    SELECT (SAMPLE(?trafficSign) AS ?trafficSign) (SAMPLE(?mutcd) AS ?mutcd) (SAMPLE(?image) AS ?image) (SAMPLE(?rgb) AS ?rgb) (SAMPLE(?layer) AS ?layer) WHERE {
      SERVICE <https://query.wikidata.org/sparql> {
        # Sign series that are part of the MUTCD
        wd:Q800352 wdt:P527 ?series.
        # Signs that are part of the series
        ?series wdt:P527+ ?wd.
        
        # Get the sign’s reference number
        ?wd wdt:P528 ?mutcd.
        
        # Convert the reference number to an OpenStreetMap traffic_sign value
        BIND(CONCAT("US:", ?mutcd) AS ?trafficSign)
        
        # Get a representative image of the sign
        OPTIONAL {
          ?wd wdt:P18 ?image.
        }
        
        OPTIONAL {
          # Get a statement about the color of the sign
          ?wd wdt:P361*/p:P462 ?colorStatement.
          OPTIONAL {
            # Get the hexadecimal RGB triplet of the background
            ?colorStatement pq:P465 ?backgroundRGB;
                            pq:P518 wd:Q13217555.
            # Must not be an optional color (expressed as “rarely”)
            FILTER NOT EXISTS {
              ?colorStatement pq:P5102 wd:Q28962310.
            }
          }
          OPTIONAL {
            # Get the hexadecimal RGB triplet of some other color on the sign
            ?colorStatement pq:P465 ?unqualifiedRGB.
            # Must not be the text color
            FILTER NOT EXISTS {
              ?colorStatement pq:P518 wd:Q1640824.
            }
            # Must not be an optional color (expressed as “rarely”)
            FILTER NOT EXISTS {
              ?colorStatement pq:P5102 wd:Q28962310.
            }
          }
          
          # Prefer the background color if known
          BIND(COALESCE(?backgroundRGB, ?unqualifiedRGB) AS ?rgb)
          FILTER(BOUND(?rgb))
        }
        
        # Use the series name as the map layer
        OPTIONAL {
          ?series rdfs:label ?layer.
          FILTER(LANG(?layer) = "en")
        }
      }
    }
    # Some signs have multiple, equally prominent colors, but one is enough for coloring the map marker
    GROUP BY ?wd
  }
  
  # Query OpenStreetMap for traffic signs that correspond to Wikidata signs
  ?osm osmt:traffic_sign ?trafficSign;
       osmm:loc ?coordinates.
}

Run it (edit query)

Inputs to conditional turn restrictions

Displays a table of the inputs required to fully evaluate conditions of conditional turn restrictions, sorted by prevalence, along with example conditions that include these inputs. A condition may depend on the time of day (specified in opening_hours=* syntax) or a property of the road, vehicle, or user. The more of these inputs a router has access to, the less likely that it would suggest an illegal or impractical maneuver. Some routers like OSRM internally recognize time-dependent conditions, but a router could also determine the value of other conditions based on external APIs or user input.

In the process of isolating the properties, this query shows how to fully parse conditional restriction syntax. The string splitting in this query is based on the solution in this gist.

SELECT (COUNT(*) AS ?count) ?property (SAMPLE(?condition) AS ?example) WHERE {
  # Get conditional turn restrictions
  ?osm osmt:restriction:conditional ?conditionals.
  
  # Multiple tag values are delimited by semicolons
  # But alternative conditions for a single turn restriction are also delimited using semicolons (and grouped inside parentheses)
  # Tokenize these “OR” delimiters to prevent them from being split prematurely
  # For each semicolon, look up to 100 characters behind to see if it follows an unclosed parenthesis
  BIND(REPLACE(?conditionals, "(?<=\\([^)]{1,100});", "<SEMICOLON>") AS ?tokenizedConditionals)
  
  # Split the semicolon-delimited tag value list into up to 3 tag values
  # This is akin to an array of indices that you iterate over in an imperative programming language
  # The number of splits increases the number of rows combinatorically, so increasing this number can greatly affect performance
  VALUES ?i { 0 1 2 }
  
  # For the ith split, build a regular expression that isolates the (i+1)th tag value from the i previous tag values and any subsequent text
  # Also clear out the full tag value list if there are no more occurrences of the separator
  BIND(CONCAT("^(?:[^;]*; *){", STR(?i), "}([^;]*).*$|.*") AS ?conditionalRegex)
  
  # Replace the full tag value list with the (i+1)th tag value
  BIND(REPLACE(?tokenizedConditionals, ?conditionalRegex, "$1") AS ?tokenizedConditional)
  
  # Omit empty tag values, including tag values that are empty because there are fewer than ?i tag values
  FILTER(STRLEN(?tokenizedConditional) > 0)
  
  # Now that each tag value has been isolated, untokenize the semicolons that delimit alternative conditions
  BIND(REPLACE(?tokenizedConditional, "<SEMICOLON>", ";") AS ?conditional)
  
  # Get the evaluated value on the left-hand side of the @, trimming whitespace
  BIND(REPLACE(?conditional, " *@.+$", "") AS ?value)
  
  # Get the condition on the right-hand side of the @, trimming whitespace
  # Strip any parentheses that may group alternative conditions
  # Also clear out the full tag value if there is no condition, indicating a catch-all value
  BIND(REPLACE(REPLACE(?conditional, "^.+?@ *|^[^@]*$", ""), "^\\((.+)\\)$", "$1") AS ?conditions)
  
  # Split the semicolon-delimited condition list into up to 4 conditions
  # This is akin to an array of indices that you iterate over in an imperative programming language
  # The number of splits increases the number of rows combinatorically, so increasing this number can greatly affect performance
  VALUES ?j { 0 1 2 3 }
  
  # For the jth split, build a regular expression that isolates the (j+1)th condition from the j previous conditions and any subsequent text
  # Also clear out the full condition list if there are no more occurrences of the separator
  BIND(CONCAT("^(?:[^;]*; *){", STR(?j), "}([^;]*).*$|.*") AS ?conditionRegex)
  
  # Replace the full condition list with the (j+1)th condition
  BIND(REPLACE(?conditions, ?conditionRegex, "$1") AS ?condition)
  
  # Omit empty conditions, including conditions that are empty because there are fewer than ?j conditions
  FILTER(STRLEN(?condition) > 0)
  
  # A condition may be a list of multiple clauses separated by the “AND” operator (also spelled “and”)
  # Split the condition into up to 3 clauses
  # This is akin to an array of indices that you iterate over in an imperative programming language
  # The number of splits increases the number of rows combinatorically, so increasing this number can greatly affect performance
  VALUES ?k { 0 1 2 }
  
  # For the kth split, build a regular expression that isolates the (k+1)th clause from the k previous clauses and any subsequent text
  # Also clear out the full condition if there are no more occurrences of the separator
  BIND(CONCAT("^(?:.*? *(?:AND|and) *){", STR(?k), "}(.*? *(?=(?:AND|and) *)|.*?$).*$|.*") AS ?clauseRegex)
  
  # Replace the full condition with the (k+1)th clause
  BIND(REPLACE(?condition, ?clauseRegex, "$1") AS ?clause)
  
  # Omit empty clauses, including clauses that are empty because there are fewer than ?k clauses
  FILTER(STRLEN(?clause) > 0)
  
  # Correct opening_hours=* syntax generally begins with a number, quoted description, or capitalized day of the week, month, or holiday symbol
  # There are few exceptions that are lowercased keywords
  BIND(IF(REGEX(?clause, "^(?:[^a-z]|dawn|sunrise|sunset|dusk|week|easter)"),
          "(time)",
          # If it isn’t a time, then it’s an expression that begins with a property
          # Isolate the property from the rest of the expression
          # Like ordinary keys, properties are alphanumeric and uncapitalized
          REPLACE(?clause, "^([a-z0-9_:]+).*$", "$1")) AS ?property)
}
GROUP BY ?property
ORDER BY DESC(?count)

Run it (edit query)

Human-readable route refs

Displays the tagged ref=* of 28301898 28301898 along with its correctly formatted route number. For historical technical reasons, mappers in many U.S. states long ago standardized on prefixes for ref=* tags on ways that are partially machine-readable: not quite unique, but unique enough for a U.S.-specific CartoCSS stylesheet like MapQuest Open to choose route shields using regular expressions. Unfortunately, these prefixes often do not correspond to the actual prefixes used officially or by the general public. At the same time, network=* tags are fully unique and machine-readable, but they correspond even less to the actual prefixes. This query demonstrates cross-referencing the network=* tags with data items and Wikidata to correctly format the route number for display to a user, respecting any state-specific practices.

# Combine the roadway’s formatted route numbers, separated by a slash
SELECT ?highway (SAMPLE(?wayRef) AS ?wayRef) (GROUP_CONCAT(DISTINCT ?formattedRef; SEPARATOR="/") AS ?formattedRef) WHERE {
  {
    SELECT DISTINCT ?highway ?wayRef ?network ?formattedRef WHERE {
      # Start with a roadway in OpenStreetMap
      BIND(osmway:28301898 AS ?highway)
      
      # For comparison, get the way ref
      ?highway osmt:ref ?wayRef.
      
      # Query OpenStreetMap for road routes that contain the roadway
      ?route osmt:route "road";
             osmm:type "r";
             osmm:has ?highway;
             osmt:network ?network;
             osmt:ref ?ref.
      
      # Get the state the route travels through as a FIPS 5-2 alpha code (two-letter state abbreviation)
      OPTIONAL {
        ?route osmt:is_in:state ?stateCode.
      }
      
      # Rank the route’s network by the number of semicolons, roughly corresponding to the level of government responsible for the route
      BIND(STRLEN(REPLACE(?network, "[^:]", "")) AS ?networkLevel)
      # And the length of the network as a tiebreaker
      BIND(STRLEN(?network) AS ?networkLength)
      # Sort the route number numerically
      BIND(xsd:integer(?ref) AS ?refNumber)
      
      # Query the OpenStreetMap Wiki for a data item that corresponds to the route’s network and get the corresponding Wikidata QID
      BIND(CONCAT("network=", ?network) AS ?networkKV)
      ?osmdRoute osmdt:P19 ?networkKV;
                 osmdt:P12 ?qid.
      
      # Convert the Wikidata QID from a string into a URL, which is how entities are normally represented
      BIND(IRI(CONCAT("http://www.wikidata.org/entity/", ?qid)) AS ?wdRoute)
      
      # Query Wikidata for
      SERVICE <https://query.wikidata.org/sparql> {
        # A route number formatter
        ?wdRoute wdt:P8498 ?genericRefFormatter.
        
        OPTIONAL {
          # A state corresponding to the two-letter state abbreviation
          ?state wdt:P5086 ?stateCode.
          
          # A route number formatter valid only in the state
          ?wdRoute p:P8498 [ps:P8498 ?stateRefFormatter; pq:P3005 ?state].
        }
        
        # Prefer a state-specific route number formatter over a generic route number formatter
        BIND(COALESCE(?stateRefFormatter, ?genericRefFormatter) AS ?refFormatter)
      }
      
      # Format the route number by replacing the placeholder in the formatter
      # REPLACE() takes a regular expression, so “$” needs to be escaped
      BIND(REPLACE(?refFormatter, "\\$1", ?ref) AS ?formattedRef)
    }
    ORDER BY ?networkLevel ?networkLength ?refNumber
  }
}
GROUP BY ?highway

Run it (edit query)

Roads with the widest range of speeds

Displays a table of 250 roads with the greatest differential between the maximum speed limit (maxspeed=*) and minimum speed limit (minspeed=*). Large differentials can be a safety hazard. To avoid redundancy, individual ways are grouped by road classification, name, and route numbers in addition to the differential. This query demonstrates a simple approach to parsing measurements for keys that can be measured in a limited number of units; see "Total electrical output" for a more extensible approach.

SELECT (SAMPLE(?osm) AS ?osm) ?class ?name ?ref (SAMPLE(?maximumSpeed) AS ?maximumSpeed) (SAMPLE(?minimumSpeed) AS ?minimumSpeed) ?differential WHERE {
  # Query OpenStreetMap for anything with both a minimum and maximum speed limit
  ?osm osmt:maxspeed ?maximumSpeed;
       osmt:minspeed ?minimumSpeed.
  
  # Parse the speed limits into numbers
  # Convert miles per hour and knots to kilometers per hour for consistency
  BIND(xsd:float(REPLACE(?maximumSpeed, " .+", "")) * IF(STRENDS(?maximumSpeed, "mph"), 1.609344,
                                                         IF(STRENDS(?maximumSpeed, "knots"), 1.852,
                                                            1)) AS ?maximumSpeedQuantity)
  BIND(xsd:float(REPLACE(?minimumSpeed, " .+", "")) * IF(STRENDS(?minimumSpeed, "mph"), 1.609344,
                                                         IF(STRENDS(?minimumSpeed, "knots"), 1.852,
                                                            1)) AS ?minimumSpeedQuantity)
  
  # Calculate the difference between the maximum and minimum allowed speeds
  BIND(?maximumSpeedQuantity - ?minimumSpeedQuantity AS ?differential)
  
  # A single road may be split up into many ways with the same speed limits
  # Crudely group ways by their classifications, names, and route numbers to avoid redundancy
  OPTIONAL {
    ?osm osmt:highway ?class.
  }
  OPTIONAL {
    ?osm osmt:name ?name.
  }
  OPTIONAL {
    ?osm osmt:ref ?ref.
  }
}
GROUP BY ?class ?name ?ref ?differential
ORDER BY DESC(?differential)
LIMIT 250

Run it (edit query)

Interstate highway relations

Displays a tree of Interstate highway relations in the United States. The tree groups each route's relations under a superrelation where applicable. The tree decorates each superrelation with a shield (based on either the linked Wikidata item or a URL in symbol=*) and each child relation with the flag of the state that the relation traverses. To make related route relations easier to distinguish, each row also displays states and a cardinal direction if available. Right-click on an item in the tree to visit the corresponding route relation.

#defaultView:Tree
SELECT ?linkedSuperRoute ?shield ?formattedRef ?superStateCode ?superDirection ?linkedRoute ?flag ?stateCode ?direction WHERE {
  # Query OpenStreetMap for Interstate route superrelations and their refs
  ?superRoute osmt:network "US:I";
              osmt:ref ?ref;
              osmm:type "r".

  # It’s a superrelation if it isn’t a member of a super-super route relation
  # A freestanding route relation is a superrelation for the purpose of this query
  FILTER NOT EXISTS {
    ?superSuperRoute osmm:has ?superRoute;
                     osmt:type "route".
  }

  # Omit special routes like business loops
  FILTER NOT EXISTS {
    # Most special routes have distinct network tags, but some are tagged with only a modifier
    ?superRoute osmt:modifier [].
  }

  # Get the shield from Wikidata
  OPTIONAL {
    ?superRoute osmt:wikidata ?superWikidata.

    # Query Wikidata for
    SERVICE <https://query.wikidata.org/sparql> {
      # A part of the Interstate Highway System
      ?superWikidata wdt:P16 wd:Q94247;
                     # Get its shield or the shield of something it is a part of
                     wdt:P361?/wdt:P14 ?shield.
    }
  }

  # Get the shield from OpenStreetMap
  OPTIONAL {
    # Get the symbol, which is supposed to be a description but which commonly contains a Wikimedia Commons SVG image URL
    ?superRoute osmt:symbol ?symbol.

    # Isolate the SVG image name
    BIND(wikibase:decodeUri(REPLACE(?symbol, "^https?://(?:upload\\.wikimedia\\.org/wikipedia/commons/(?:thumb/)?./../|commons\\.wikimedia\\.org/wiki/File:)(.+\\.svg)(?:/\\d+px-$1\\.png)?$", "$1")) AS ?shieldName)

    # Convert the name to a URL that Sophox will recognize as an image URL
    BIND(IRI(CONCAT("http://commons.wikimedia.org/wiki/Special:FilePath/", ENCODE_FOR_URI(?shieldName))) AS ?shield)
  }

  # Get the state the route is in and cardinal direction, to differentiate between freestanding route relations
  OPTIONAL {
    ?superRoute osmt:is_in:state ?superStateCode.
  }
  OPTIONAL {
    ?superRoute osmt:direction ?superDirection.
  }

  # Also query OpenStreetMap for a route relation that’s a member of the superrelation
  OPTIONAL {
    ?superRoute osmm:has ?route.
    ?route osmt:network "US:I";
           osmt:ref ?ref;
           osmm:type "r".

    # Get the state the route is in, to differentiate between route relations in the same superrelation
    OPTIONAL {
      ?route osmt:is_in:state ?stateCode.

      # Try a little harder
      OPTIONAL {
        # Query Wikidata for
        SERVICE <https://query.wikidata.org/sparql> {
          # The state with the same FIPS 5-2 alpha state code
          ?state wdt:P5086 ?stateCode;
                 wdt:P41 ?flag.
        }
      }
    }

    # Keep trying to get the state based on the linked Wikidata entity
    OPTIONAL {
      ?route osmt:wikidata ?wikidata.

      OPTIONAL {
        SERVICE <https://query.wikidata.org/sparql> {
          # Same as above, but distinguish between the “Interstate __ in __” entity and the overall “Interstate __” entity
          # The latter is likely located in multiple states, so prefer the more specific entity
          ?wikidata wdt:P361/wdt:P16 wd:Q94247;
                    wdt:P131+ ?state.

          ?state wdt:P5086 ?stateCode;
                 wdt:P41 ?flag.
        }
      }
    }

    # Get the cardinal direction, to differentiate between route relations in the same superrelation
    OPTIONAL {
      ?route osmt:direction ?direction.
    }

    FILTER NOT EXISTS {
      ?route osmt:modifier [].
    }
  }

  # Format the ref for display
  BIND(CONCAT("I-", ?ref) AS ?formattedRef)

  # Normalize the ref for consistent sorting
  # Isolate the digits, stripping any H prefix (for Hawaii) or alphabetic branch suffix
  BIND(REPLACE(?ref, "^H|[A-Z]$", "") AS ?numericRef)

  # Pad the digits to a 3-digit number
  BIND(CONCAT(SUBSTR("000", 1, 3 - STRLEN(?numericRef)), ?numericRef) AS ?paddedNumericRef)

  # Replace the digits part of the ref with a padded copy
  BIND(REPLACE(?ref, "^(H?)([0-9]{1,3})([A-Z])?$", CONCAT("$1", ?paddedNumericRef, "$3")) AS ?paddedRef)

  # Rearrange the digits so that the parent ref comes first
  # For example, I-80 is the parent route of I-680, so sort I-680 as 806
  BIND(REPLACE(?paddedRef, "^(H?)([0-9])([0-9][0-9])([A-Z])?$", "$1$3$2$4") AS ?sortedRef)

  # Work around https://github.com/Sophox/sophox/issues/21
  BIND(IRI(CONCAT(STR(?superRoute), "?entity=", COALESCE(STR(?superWikidata), "/entity/Q0"))) AS ?linkedSuperRoute)
  BIND(IRI(CONCAT(STR(?route), "?entity=", COALESCE(STR(?wikidata), "/entity/Q0"))) AS ?linkedRoute)
}
ORDER BY ?sortedRef ?stateCode ?direction

Run it (edit query)

Data items

Most used keys without description in any language

SELECT ?osmd ?keyId ?key_usage WHERE {

  # has a key and a count, but without any descriptions
  ?osmd osmdt:P16 ?keyId;
        osmm:count_all ?key_usage.
  FILTER NOT EXISTS { ?osmd schema:description []. }
}
ORDER BY DESC(?key_usage)
LIMIT 100

Run it (edit query)

Most used keys and tags without description in any language

SELECT ?osmd ?id ?usage {
  {
    SELECT ?osmd ?id ?usage WHERE {
      # an entity has a key ID and a usage count
      ?osmd osmdt:P16 ?id;
            osmm:count_all ?usage.
    }
  }
  UNION
  {
    SELECT ?osmd ?id ?usage WHERE {
      # an entity has a tag ID and a tag usage count
      ?osmd osmdt:P19 ?id;
            osmm:tag_count_all ?usage.
    }
  }

  # Only list those Entry IDs that have no descriptions
  FILTER NOT EXISTS { ?osmd schema:description []. }

# Limit to top 100 entries
} ORDER BY DESC(?usage) LIMIT 100

Run it (edit query)

Description counts per language

SELECT ?lang (COUNT(*) AS ?count) WHERE {
  
  VALUES ?types { osmd:Q7 osmd:Q2 osmd:Q6 osmd:Q4667 }
  
  ?item_id osmdt:P2 ?types;
           schema:description ?description.
  
  BIND(LANG(?description) AS ?lang)
}
GROUP BY ?lang
ORDER BY DESC(?count)

Run it (edit query)

Find keys and tags without description in a given language sorted by usage

SELECT ?osmd ?id ?osmdDescription ?usage {
  
  # Set the language code to use.
  # The result is limited to the 100 top used keys and tags.
  BIND ('ru' as ?lang)
  
  {
    SELECT ?osmd ?id ?usage WHERE {
      # an entity has a key ID and a count, but no descriptions
      ?osmd osmdt:P16 ?id;
            osmm:count_all ?usage.
    }
  }
  UNION
  {
    SELECT ?osmd ?id ?usage WHERE {
      # an entity has a tag ID and a count, but no descriptions
      ?osmd osmdt:P19 ?id;
            osmm:tag_count_all ?usage.
    }
  }
  FILTER NOT EXISTS {
    ?osmd schema:description ?description.
    FILTER (lang(?description) = ?lang)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY DESC(?usage) LIMIT 100

Run it (edit query)

Find all untranslated Keys and Tags by usage

SELECT ?osmd ?osmdLabel ?osmdDescription ?key_usage ?tag_key_usage WHERE {
  
  # Which language to search
  VALUES ?lang { 'ru' }
  
  # We are only interested in Tag:* and Key:* data items
  VALUES ?tag_or_key { osmd:Q2 osmd:Q7 }
  
  # Instanceof = tag or key; and must have __a__ description
  ?osmd osmdt:P2 ?tag_or_key;
        schema:description ?description_en.

  # Limit the result to just english descriptions
  FILTER EXISTS {
    FILTER (lang(?description_en) = 'en')
  }

  # Remove deprecated items from the list
  MINUS { ?osmd osmdt:P6 osmd:Q5061 }

  # If the data item contains a description in the local language, skip it
  FILTER NOT EXISTS {
    ?osmd schema:description ?description_local.
    FILTER (lang(?description_local) = ?lang)
  }

  # Counts are only available for keys, so if this is a tag, get corresponding key usage
  OPTIONAL { ?osmd osmm:count_all ?key_usage. }
  OPTIONAL { ?osmd osmdt:P10/osmm:count_all ?tag_key_usage. }
  
  # Get English label and description (generates *Label and *Description vars)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }

} ORDER BY DESC(?key_usage) DESC(?tag_key_usage)

Run it (edit query)

Find all Keys:* and Tags:* with identical descriptions in multiple languages

# Group by description of all keys and tags (ignoring language)
# Filter those groups that have more than one item, and where list of languages has a needed code
# Add "en" label for output
SELECT ?osmd ?osmdLabel ?description ?langs WHERE {
  {
    SELECT ?osmd ?description
           (COUNT(*) AS ?count)
           (group_concat(?lang;separator=', ') as ?langs)
    WHERE {
      VALUES ?key_or_tag { osmd:Q7 osmd:Q2 }
      ?osmd osmdt:P2 ?key_or_tag;
            schema:description ?desc.
      BIND(STR(?desc) AS ?description)
      BIND(LANG(?desc) AS ?lang)
    }
    GROUP BY ?osmd ?description
  }
  FILTER(?count > 1)
  # Uncomment to filter by the language code
  # FILTER(contains(?langs, "fr"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Run it (edit query)

Mismatched STATUS between languages in keys and tags

# List of all OSM Keys and Tags that have mismatching STATUS (P6) in various languages, most used first.
# Shows default value (English) and the mismatched value plus all regions that have it.
# If there are more than one mismatching value, they are shown on separate lines.
SELECT ?osmd ?osmdLabel ?defaultLabel ?mismatchLabel ?regions ?key_usage ?tag_key_usage WHERE {
  {
    # See qualifier docs:
    # https://en.wikibooks.org/wiki/SPARQL/WIKIDATA_Qualifiers,_References_and_Ranks
    SELECT ?osmd ?mismatch (GROUP_CONCAT(DISTINCT ?langCode ; separator=',') as ?regions) WHERE {
      
      # Uncomment the VALUES line to limit mismatches to just a given language
      # You can search for multiple space-separated values, e.g.  { 'de' 'fr' 'pl' }
      # VALUES ?langCode { 'de' }
      
      ?osmd osmp:P6 ?statement.
      ?statement osmps:P6 ?mismatch.
      ?statement osmpq:P26/osmdt:P32 ?langCode.
      ?statement wikibase:rank wikibase:NormalRank.
    } group by ?osmd ?mismatch
  }
  
  ?osmd osmdt:P6 ?default.
  # Counts are only available for keys, so if this is a tag, get corresponding key usage
  OPTIONAL { ?osmd osmm:count_all ?key_usage. }
  OPTIONAL { ?osmd osmdt:P10/osmm:count_all ?tag_key_usage. }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY DESC(?key_usage) DESC(?tag_key_usage) ?osmdLabel ?mismatchLabel

Run it (edit query)

Approved but unused keys

SELECT * WHERE {
  # Query the OpenStreetMap Wiki for data items whose status is approved
  ?osmd osmdt:P6 osmd:Q15;
        # Get the key ID
        osmdt:P16 ?key;
        # Get the number of features tagged with this key
        osmm:count_all ?count.
  # Only include unused keys and exclude keys ending in colons (which are actually namespaces)
  FILTER(?count = 0 && !STRENDS(?key, ":"))
}

Run it (edit query)

Most deeply nested subkeys

Displays a table of the 50 most deeply nested subkeys that are in wide enough use to have data items about them.

SELECT * WHERE {
  # Query the OpenStreetMap Wiki for data items about keys
  ?osmd osmdt:P16 ?key.
  
  # Remove non-colons and count the colons that remain
  # The depth is one more than the number of colons
  BIND(STRLEN(REPLACE(?key, "[^:]", "")) + 1 AS ?depth)
}
ORDER BY DESC(?depth)
LIMIT 50

Run it (edit query)

Quality control

Find objects that fail regex validation defined in data item

SELECT ?feature ?value WHERE {
  # Get regex and subject ref for the needed key.
  # For "population"  it will be  "[0-9]+"  and  osmt:population
  ?osmd osmdt:P16 "population";
        osmdt:P13 ?regex;
        osmm:key ?keySubj.
  # Surround the regex with ^ and $ characters (to match the whole value string)
  BIND (CONCAT("^", ?regex, "$") as ?re)
  
  # Find all OSM features with the needed key,
  # And only include those whose value doesn't match needed regex
  ?feature ?keySubj ?value.  
  FILTER(!REGEX(?value, ?re))
} LIMIT 10

Run it (edit query)

Find mismatching "wikipedia" and "wikidata" tags (faster)

SELECT * WHERE {
  # must have both WP and WD tags
  ?osmId osmt:wikipedia ?wp ;
         osmt:wikidata ?wd .
  
  # ignore non-standard values for wp & wd
  FILTER (ISURI(?wp) && ISURI(?wd))
  
  # when WP sitelink is not a part of the WD entry
  FILTER NOT EXISTS {
    ?wp schema:about ?wd .
  }
} LIMIT 10

Run it (edit query)

mismatching "wikipedia" and "wikidata" tags (slower)

#defaultView:Map
SELECT ?osmId ?wdLabel ?wpFromOSM ?wpFromWd ?wd ?loc WHERE {
  ?osmId osmt:wikidata ?wd ;         # osm object must have a "wikidata" tag
         osmt:wikipedia ?wpFromOSM ; # osm object must have a "wikipedia" tag
         osmm:loc ?loc .             # get object's location

  # wikipedia tag link must not be listed in the corresponding wikidata object
  FILTER NOT EXISTS { ?wpFromOSM schema:about ?wd . }

  OPTIONAL {
    # if Wikidata has another link in the same domain, show it
    ?wpFromWd schema:about ?wd .
    FILTER( SUBSTR( STR(?wpFromOSM), 0, 25 ) = SUBSTR( STR(?wpFromWd), 0, 25 ) )
  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Run it (edit query)

Find when more than one relation link to the same Wikidata ID

SELECT ?osmid ?adminlvl ?wd ?wdLabel {
  # find relation with a wikidata tag
  ?osmid osmm:type 'r' ;
         osmt:wikidata ?wd ;
         osmt:admin_level ?adminlvl .

  # add user's or english label to the found wikidata
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

  # the ?wd variable must also be matching this subquery
  # it finds wd tags that appear more than once
  { SELECT ?wd { 
    ?osmid osmm:type 'r' ;
           osmt:wikidata ?wd ;
           osmt:admin_level ?adminlvl .
    }
    GROUP BY ?wd
    HAVING (COUNT(*) > 1)
  }

} LIMIT 10

Run it (edit query)

Show relations with no sub-relations or nodes, having same wikidata tag with the parent **SLOW**

#defaultView:Map
SELECT
  ?rel
  (SAMPLE(?location) as ?location)
  (sum(?failed) as ?failCount)
  (count(?mwd) as ?memberWithWdCount)
  (count(?member) as ?memberCount)
  ((count(?member) - count(?mwd)) as ?diffCount)

WHERE {
  # Find relations with wikidata tag and at least one member
  ?rel osmm:type 'r';
       osmt:wikidata ?wd;
       osmm:loc ?location;
       osmm:has ?member .

  # Get member's type
  ?member osmm:type ?mtype .

  # Get member's wikidata tag if it exists
  OPTIONAL { ?member osmt:wikidata ?mwd }

  # If any of the conditions are met, set ?failed to 1.
  # The sum of ?failed must be 0 for the relation to be shown
  BIND (if((?mtype='r' || ?mtype='n' || (bound(?mwd) && ?mwd!=?wd)), 1, 0) as ?failed)
}
GROUP BY ?rel
HAVING (?memberWithWdCount > 0 && ?failCount = 0)
ORDER BY DESC(?memberCount)

Run it (edit query)

Find OSM objects, where wikidata should probably be brand:wikidata

#defaultView:Map
SELECT ?osmId ?location ?bwd ?bwdLabel ?bwdDescription WHERE {

  # Subquery finds brand:wikidata IDs used more than 10 times
  {
    SELECT ?bwd (count(*) as ?count) WHERE {
      ?o osmt:brand:wikidata ?bwd .
    }
    group by ?bwd
    having (?count > 10)
  }

  # Find OSM objects where wikidata or operator:wikidata tag
  # is one of the common brand:wikidata IDs
  VALUES ?tag { osmt:wikidata osmt:operator:wikidata }

  ?osmId ?tag ?bwd ;
         osmm:loc ?location .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,ru,es,de,zh,ja". }
}

Run it (edit query)

Find OSM objects, where wikidata should probably be species:wikidata

#defaultView:Map
SELECT ?osmId ?location ?swd ?swdLabel ?swdDescription WHERE {

  # Subquery finds species:wikidata IDs used more than 10 times
  {
    SELECT ?swd (count(*) as ?count) WHERE {
      ?o osmt:species:wikidata ?swd .
    }
    group by ?swd
    having (?count > 3)
  }

  # Find OSM objects where wikidata tag is one of the common species:wikidata IDs
  ?osmId osmt:wikidata ?swd ;
         osmm:loc ?location .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,ru,es,de,zh,ja". }
}

Run it (edit query)

website or url the same as wikipedia tag

#defaultView:Map
SELECT ?osmId ?wp ?loc WHERE {
  
  { SELECT ?osmId ?wp ?loc WHERE {
    ?osmId osmt:wikipedia ?wp ;
           osmt:url ?url ;
           osmm:loc ?loc .
    FILTER( REPLACE( ?url, 'http://', 'https://') = str(?wp) )
  } }
UNION
  { SELECT ?osmId ?wp ?loc WHERE {
    ?osmId osmt:wikipedia ?wp ;
           osmt:website ?url ;
           osmm:loc ?loc .
    FILTER( REPLACE( ?url, 'http://', 'https://') = str(?wp) )
  } }
}

Run it (edit query)

Phonewords

Displays a table of features that have phone=* set to a phoneword. phone=* is supposed to be set to a numeric phone number. Move each feature's phoneword to phone:mnemonic=* and set phone=* to the given numeric number. Switch to the map view to see these phone numbers on a map.

SELECT ?osm ?phone_mnemonic ?phone ?coordinates WHERE {
  # Phone numbers and coordinates
  ?osm osmt:phone ?phone_mnemonic;
       osmm:loc ?coordinates.
  
  # Filter numbers that look like NANP numbers
  # For example: +1 (800) 555-ABCD, 1 800 555 ABCD, (800) 555-ABCD, etc.
  FILTER(REGEX(?phone_mnemonic, "^\\+?1[-. ]([A-Z0-9][-. ]?){10}|^\\+?1? ?\\(?[0-9]{3}\\) ?[A-Z]{3}[- .][A-Z]{4}") &&
         # Must contain a letter in the area code, exchange, or first place of the subscriber number
         # Try to exclude numeric numbers that have extensions
         REGEX(SUBSTR(?phone_mnemonic, 1, 14), "[A-Z]"))
  
  # Map digits to letters
  VALUES ?digits  { "1234567890 222 333 444 555 666 7777 888 9999" }
  VALUES ?letters { "1234567890 ABC DEF GHI JKL MNO PQRS TUV WXYZ" }
  
  # Strip any punctuation and extraneous digits
  BIND(REGEX(?phone_mnemonic, "^\\+?1[- .]") AS ?hasCountryCode)
  BIND(SUBSTR(REPLACE(?phone_mnemonic, "[^A-Z0-9]", ""), IF(?hasCountryCode, 2, 1), 10) AS ?alphanumerics)
  
  # Extract the area code
  BIND(SUBSTR(?alphanumerics, 1, 3) AS ?areaCodeMnemonics)
  # For each character in the area code
  BIND(CONCAT(
    # Look for the character in the digits string and get the character at the same index in the letters string
    SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?areaCodeMnemonics, 1, 1))) + 1, 1),
    SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?areaCodeMnemonics, 2, 1))) + 1, 1),
    SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?areaCodeMnemonics, 3, 1))) + 1, 1)) AS ?areaCode)
  
  # Extract the exchange
  BIND(SUBSTR(?alphanumerics, 4, 3) AS ?exchangeMnemonics)
  BIND(CONCAT(SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?exchangeMnemonics, 1, 1))) + 1, 1),
              SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?exchangeMnemonics, 2, 1))) + 1, 1),
              SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?exchangeMnemonics, 3, 1))) + 1, 1)) AS ?exchange)
  
  # Extract the subscriber number
  BIND(SUBSTR(?alphanumerics, 7, 4) AS ?subscriberMnemonics)
  BIND(CONCAT(SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?subscriberMnemonics, 1, 1))) + 1, 1),
              SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?subscriberMnemonics, 2, 1))) + 1, 1),
              SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?subscriberMnemonics, 3, 1))) + 1, 1),
              SUBSTR(?digits, STRLEN(STRBEFORE(?letters, SUBSTR(?subscriberMnemonics, 4, 1))) + 1, 1)) AS ?subscriber)
  
  # Put the pieces back together
  BIND(CONCAT("+1-", ?areaCode, "-", ?exchange, "-", ?subscriber) AS ?phone)
}

Run it (edit query)

Flagpoles tagged as flags

Displays a table of flagpoles tagged as the flags they fly. The flags should be tagged as flag:name=* and flag:wikidata=*, not name=* and wikidata=*.

SELECT ?flagpole ?wd ?wdLabel WHERE {
  hint:Query hint:optimizer "None".
  ?flagpole osmt:man_made "flagpole";
            osmt:wikidata ?wd.
  SERVICE <https://query.wikidata.org/sparql> {
    ?wd wdt:P31/wdt:P279* wd:Q14660.
    OPTIONAL {
      ?wd rdfs:label ?wdLabel.
      FILTER(LANG(?wdLabel) = "en")
    }
  }
}

Run it (edit query)

Invalid pronunciations

Displays a table of features tagged with name:pronunciation=* values containing characters not accepted as part of the International Phonetic Alphabet. Many of these pronunciations are either X-SAMPA notation or an ad-hoc pronunciation respelling scheme (common in English). Some of these pronunciations would be valid IPA except for the inclusion of a lookalike character, such as ' instead of ˈ to mark primary stress or : instead of ː to mark a long vowel. Even these seemingly benign typographic errors can lead to unexpected results from text-to-speech engines, so proper typography is preferred despite the inconvenience.

SELECT ?osm ?name ?pronunciation WHERE {
  # Query OpenStreetMap for name pronunciations
  ?osm osmt:name:pronunciation ?pronunciation.
  
  # Look for characters that are not part of the International Phonetic Alphabet
  # These pronunciations may be X-SAMPA, a phonetic respelling scheme, or merely a typo such as ' for ˈ
  FILTER(REGEX(?pronunciation, "[-A-Z_<>`\\:\"'%@{}0-9?&*^!|=+~]"))
  
  # Get the name to make phonetic respellings easier to spot
  OPTIONAL {
    ?osm osmt:name ?name.
  }
}
ORDER BY ?name

Run it (edit query)

Bus stops in ALL CAPS

Displays an interactive map of bus stops whose names are spelled in ALL CAPITAL LETTERS. Many of these bus stops likely come from poorly written imports of GTFS data; the names should be respelled in sentence case or title case as appropriate. Note that there are some false positives caused by acronyms and initialisms, such as the names of public transportation agencies.

#defaultView:Map
SELECT ?osm ?name ?coordinates WHERE {
  # Query OpenStreetMap for bus stops and their names and coordinates
  ?osm osmt:highway "bus_stop";
       osmt:name ?name;
       osmm:loc ?coordinates.
  # As long as the name contains a capitalized word at least 5 letters long
  # But does not contain any lowercase letter
  FILTER(REGEX(?name, "[A-Z]{5,}") && !REGEX(?name, "[a-z]"))
}

Run it (edit query)

Missing Wikidata items of U.S. airports

Displays a table of aviation facilities in the United States and their missing wikidata=* tags by matching the facility's Federal Aviation Administration location identifier (FAA LID) between OpenStreetMap's faa=* key and Wikidata's corresponding property. This table is sorted by the alphabetic part of each LID.

SELECT ?lid ?osm ?wd WHERE {
  # Query OpenStreetMap for FAA LIDs of airfields
  ?osm osmt:faa ?lid.
  
  # Exclude airfields that are tagged with Wikidata QIDs
  FILTER NOT EXISTS {
    ?osm osmt:wikidata [].
  }
  
  # Strip the numeric part of the FAA LID to sort by the alphabetic part
  BIND(REPLACE(?lid, "[0-9]+", "") AS ?key)
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # Items that have the same FAA LID
    ?wd wdt:P240 ?lid.
  }
}
ORDER BY ?key ?lid

Run it (edit query)

Features checked after they were edited

Displays a table of features that a mapper claimed to have reviewed for accuracy after the feature was last edited. The check_date=* key is supposed to contain the date on which a mapper reviewed the feature for accuracy, no later than the date they edited it, but many mappers misunderstand the key as a way to indicate when another mapper should recheck the feature in the future.

SELECT ?osm ?editedDateTime ?checkedDateTime WHERE {
  # Query OpenStreetMap for features that have been checked
  ?osm osmt:check_date ?checkedDate;
       # The timestamp of the last edit
       osmm:timestamp ?editedDateTime.
  
  # Only consider last checked dates in ISO 8601 format
  FILTER REGEX(?checkedDate, "^[0-9]{4}(?:-[0-9]{2}(?:-[0-9]{2}(?:T.+)?)?)?$")
  
  # Convert the last checked date to a timestamp
  BIND(STRDT(?checkedDate, xsd:dateTime) AS ?checkedDateTime)
  
  # Only include the feature if its last checked timestamp is past its last edited timestamp
  FILTER(?checkedDateTime > ?editedDateTime)
}
ORDER BY DESC(?checkedDateTime)

Run it (edit query)

Overdue openings

Displays a table of features whose expected date of completion is in the past. After verifying that the feature has in fact opened, replace the opening_date=* key with a start_date=* key that indicates the actual date of completion. This query returns many examples of confusion between opening_date=* and start_date=*. Indeed, some features are centuries overdue!

SELECT ?osm ?openingDateTime WHERE {
  # Query OpenStreetMap for features that are expected to open in the future
  ?osm osmt:opening_date ?openingDate.
  
  # Only consider opening dates in ISO 8601 format
  FILTER REGEX(?openingDate, "^[0-9]{4}(?:-[0-9]{2}(?:-[0-9]{2}(?:T.+)?)?)?$")
  
  # Convert the opening date to a timestamp
  BIND(STRDT(?openingDate, xsd:dateTime) AS ?openingDateTime)
  
  # Only include the feature if its opening timestamp is in the past
  FILTER(?openingDateTime < NOW())
}
ORDER BY ?openingDateTime

Run it (edit query)

One-way roads with too few turn lanes

Displays an interactive map of one-way roads that have fewer turn lanes (and through lanes) than total lanes, as indicated by the lanes=* and turn:lanes=* tags. A more balanced map would also account for *:forward, *:backward, and *:both_ways tags.

#defaultView:Map{"hide": "?coordinates"}
SELECT ?osm ?lanes ?turnLanes ?turnLaneCount ?coordinates WHERE {
  # Query OpenStreetMap for one-way roadways with lanes and turn lanes
  ?osm osmt:lanes ?lanes;
       osmt:turn:lanes ?turnLanes;
       osmt:oneway "yes";
       osmm:loc ?coordinates.
  
  # Convert the lane count to an integer
  BIND(xsd:integer(?lanes) AS ?laneCount)
  
  # Count the turn lanes and through lanes based on the number of lane separators
  # Remove everything that isn't a lane separator, count the remaining characters, and add one (because a separator separates two lanes)
  BIND(STRLEN(REPLACE(?turnLanes, "[^|]", "")) + 1 AS ?turnLaneCount)
  
  # Only include the roadway if it has fewer turn lanes and through lanes than total lanes
  FILTER(?turnLaneCount < ?laneCount)
}

Run it (edit query)

Abbreviated street addresses in Cincinnati

Displays a table of features tagged with street addresses in Cincinnati that contain abbreviations, which are discouraged in OpenStreetMap. This query detects standard USPS abbreviations for geographic directions and street suffixes. Rather than manually copy-pasting the abbreviations from [5][6], this query obtains them from Wikidata's lexicographical database, a nascent structured data alternative to Wiktionary. For example, this lexeme indicates that "stravenue" is abbreviated "STRA". Wikidata makes it straightforward to expand the query to include nonstandard abbreviations or adapt it to other languages.

SELECT ?osm ?houseNumber ?street ?postcode WHERE {
  # Query Wikidata before OpenStreetMap; otherwise, every address result gets duplicated for every abbreviation
  hint:Query hint:optimizer "None".
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # A vertical bar–delimited string that looks like “ST|DR|AVE|BLVD”
    SELECT (GROUP_CONCAT(?abbreviation; SEPARATOR="|") AS ?abbreviationRegex) WHERE {
      # Lexemes in English and two of their forms, a spelled-out form and an abbreviated form
      ?lexeme dct:language wd:Q1860;
              ontolex:lexicalForm ?wordForm;
              ontolex:lexicalForm ?abbreviatedForm.
      
      # Get the spelled-out form’s grammatical number and written representation
      ?wordForm wikibase:grammaticalFeature ?number;
                ontolex:representation ?word.
      
      # Make sure the spelled-out form is not a standard USPS abbreviation
      FILTER NOT EXISTS {
        ?wordForm wikibase:grammaticalFeature wd:Q30619513.
      }
      
      # The abbreviated form must be a standard USPS abbreviation with the same grammatical number
      ?abbreviatedForm wikibase:grammaticalFeature wd:Q30619513;
                       wikibase:grammaticalFeature ?number;
                       ontolex:representation ?abbreviation.
      
      # Make sure the abbreviated form is not simultaneously singular and plural, which would produce false positives
      # For example, the USPS normalizes both “PIKE” and “PIKES” to “PIKE”
      FILTER NOT EXISTS {
        ?abbreviatedForm wikibase:grammaticalFeature ?otherNumber.
      # The form’s two grammatical numbers would be the opposite of each other
        ?number wdt:P461 ?otherNumber.
      }
      
      # Filter out words that the USPS spells out in full but includes in Appendix C1
      # For example, the USPS spells out “ROW”
      FILTER(UCASE(?word) != ?abbreviation)
    }
  }
  
  # Query OpenStreetMap for fully qualified addresses in Cincinnati, Ohio
  ?osm osmt:addr:state "OH";
       osmt:addr:city "Cincinnati";
       osmt:addr:postcode ?postcode;
       osmt:addr:street ?street;
       osmt:addr:housenumber ?houseNumber.
  
  # Only include street addresses that contain abbreviations, case insensitively
  FILTER REGEX(?street, CONCAT("\\b(?:", ?abbreviationRegex, ")\\b"), "i")
}

Run it (edit query)

HTML markup in notes and descriptions

DIsplays a table of note=* and description=* tags that contain HTML markup. Many of these tags were added as part of an import of mines and quarries from the Geographic Names Information System (GNIS). Others were manually added by mappers who had no easy way to insert a newline in their editor. openstreetmap.org does not render arbitrary HTML markup from tag values, and the note=* and description=* keys are not officially HTML formatted. If a data consumer does render HTML markup from these keys, it should prepared to handle values that would be malformed HTML, such as < expressing "less than", and strictly sanitize the markup to avoid security issues.

SELECT ?osm ?note WHERE {
  # Query Wikidata before OpenStreetMap; otherwise, every note gets duplicated for every HTML tag
  hint:Query hint:optimizer "None".
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # A vertical bar–delimited string that looks like “a|center|table”
    SELECT (GROUP_CONCAT(?tag; SEPARATOR="|") AS ?tagRegex) WHERE {
      # HTML elements and their names
      ?wd wdt:P31/wdt:P279* wd:Q179551;
          wdt:P1813 ?tag.
    }
  }
  
  # Query OpenStreetMap for notes and descriptions
  ?osm (osmt:description|osmt:note) ?note.
  
  # Only include notes that match a regular expression for opening HTML tags
  FILTER REGEX(?note, CONCAT("<(?:", ?tagRegex, ")/?[ >]"), "i")
}
LIMIT 25

Run it (edit query)

Anonymous edits

Displays an interactive map of features last edited anonymously or by the user whose user name is the empty string. These edge cases can cause bugs in some editors and data consumers, and these bugs are most prevalent in areas where OpenStreetMap had started to gain in popularity by the time anonymous editing was disabled. [7]

#defaultView:Map
SELECT * WHERE {
  # Query OpenStreetMap for features last edited by an anonymous user or a user who has named themselves the empty string
  ?osm osmm:user "";
       osmm:loc ?coordinates.
}

Run it (edit query)

Possible self-promotion

Displays a table of features with the same names as the users who last edited them. Often, a search engine optimization (SEO) practitioner creates a single-purpose account just to map the business that hired them. That can be legitimate; however, such edits often need scrutiny because SEO practitioners do not always take the time to learn how to map well. In other cases, a name match is simply a coincidence. [8]

SELECT * WHERE {
  # Query OpenStreetMap for features with the same name as the user who last edited it
  ?osm osmm:user ?user;
       osmt:name ?user.
}
LIMIT 50

Run it (edit query)

Roads renamed to businesses

Displays a table of roads that have been renamed and given a description by a user who also goes by the new name. Some search engine optimization (SEO) practitioners create throwaway accounts based on the business's name, then rename the road instead of adding a business where it belongs. This is a counterproductive practice, but efforts to contact these SEO mappers have been unsuccessful at curbing the practice as of October 2020.

SELECT ?road ?name ?user ?description WHERE {
  ?road osmt:highway [];
        osmt:name ?name;
        osmt:description ?description;
        osmm:type "w";
        osmm:user ?user.
  FILTER REGEX(SUBSTR(?user, 1, 4), "\\w")
  BIND(REPLACE(LCASE(?name), "\\W+", "") AS ?sanitizedName)
  BIND(REPLACE(LCASE(?user), "\\W+", "") AS ?sanitizedUser)
  FILTER(SUBSTR(?sanitizedName, 1, 4) = SUBSTR(?sanitizedUser, 1, 4) && STRLEN(?description) > 50 && CONTAINS(?description, " "))
}

Run it (edit query)

Most-edited national boundaries

Displays a table of the ten national-level boundary relations or ways that have been edited the most times based on their version numbers.

SELECT * WHERE {
  ?boundary osmt:boundary [];
            osmt:admin_level "2";
            osmm:version ?version.
}
ORDER BY DESC(?version)
LIMIT 10

Run it (edit query)

Features recently added to interactive maps in English Wikipedia articles

Displays a table of the 100 most recent additions of interactive OpenStreetMap maps to English Wikipedia articles along with the OpenStreetMap features that are most likely to be highlighted in those maps. These features may now benefit from increased visibility thanks to inclusion in a Wikipedia article; however, they could also become vandalism targets. This query demonstrates querying the MediaWiki API Query Service.

SELECT * WHERE {
  # Prevent optimizer from querying for everything tagged with wikidata=* before querying the service
  hint:Query hint:optimizer "None".
  
  # Query the MediaWiki API Query Service through the Wikidata Query Service, where it is enabled
  SERVICE <https://query.wikidata.org/sparql> {
    SERVICE wikibase:mwapi {
      # Get the 100 most recent <mapframe> or <maplink> additions to English Wikipedia articles
      bd:serviceParam wikibase:api "Generator";
                      wikibase:endpoint "en.wikipedia.org";
                      wikibase:limit 100;
                      mwapi:generator "categorymembers";
                      # Inserting <mapframe> and <maplink> automatically adds an article to this category
                      mwapi:gcmtitle "Category:Pages using the Kartographer extension";
                      # Articles, not other namespaces like portals
                      mwapi:gcmnamespace "0";
                      # Sort by most recent addition to the category
                      mwapi:gcmsort "timestamp";
                      mwapi:gcmdir "descending";
                      mwapi:gcmprop "ids|title|timestamp".
      # Output the title, Wikidata item, and last-edited timestamp
      ?title wikibase:apiOutput mwapi:title.
      ?wd wikibase:apiOutputItem mwapi:item.
      ?timestamp wikibase:apiOutput mwapi:timestamp.
    }
    
    # Some articles have no linked Wikidata item yet
    # Leaving this variable unbound would cause the query to search for all Wikidata-tagged features in OpenStreetMap
    FILTER(BOUND(?wd))
  }
  
  # Query OpenStreetMap for features representing the subject of the article
  ?osm osmt:wikidata ?wd.
}
# Uncomment this line to sort the most recently edited articles to the top, regardless of when a map was added to the article
#ORDER BY DESC(?timestamp)

Run it (edit query)

Chinese characters in Spanish names

Displays a table of names that are purportedly in Spanish but contain Chinese characters.

#defaultView:Map
SELECT ?osm ?name ?coordinates WHERE {
  ?osm osmt:name:es ?name;
  osmm:loc ?coordinates.
  FILTER REGEX(?name, "\\p{script=Hani}")
}

Run it (edit query)

U.S. schools with invalid grade numbers

Displays an interactive map of schools in the United States that have invalid grade numbers. In the United States, grades=* should be set to the actual grade numbers in use "on the ground", in contrast to the "international scale" that more closely resembles ISCED levels. This query demonstrates a couple tricks for restricting results to a specific country more efficiently than with the box service.

#defaultView:Map
SELECT ?school ?grades ?coordinates WHERE {
  # Query OpenStreetMap for schools and their websites, grade levels, and coordinates
  ?school osmt:amenity "school";
          osmt:website ?website;
          osmt:grades ?grades;
          osmm:loc ?coordinates.
  
  # Nonsensical grade number
  FILTER REGEX(?grades, "^-[0-9]|\\b(?:0|14)\\b")
  
  # Narrow down to the U.S., where grade numbers should not conform to an international scale
  {
    # Official website has a U.S. domain
    ?school osmt:website ?website.
    FILTER REGEX(?website, "^\\w+://[^/]+\\.(?:us|gov|edu|org|net|com)(?:[/:]|$)")
  } UNION {
    # Or address contains a state
    ?school osmt:addr:state [].
  }
  
  # Crudely restrict to part of the northwestern semihemisphere since some other countries like Australia put states in addresses
  FILTER(geof:latitude(?coordinates) > 14 && geof:longitude(?coordinates) < -60)
}

Run it (edit query)

IP addresses as websites

Displays a table of the most common IPv4 addresses in the domain names of official websites of features in OpenStreetMap. IP addresses are not necessarily static, so these URLs need to be rechecked frequently for reachability.

SELECT ?ip (COUNT(*) AS ?count) (SAMPLE(?osm) AS ?osm) (SAMPLE(?website) AS ?website) WHERE {
  ?osm osmt:website ?website.
  FILTER REGEX(?website, "^\\w+://(?:[^:]+:[^@]@)?(?:[12]?[0-9]?[0-9]\\.){3}[12]?[0-9]?[0-9](?:[:/]|$)")
  BIND(REPLACE(?website, "^\\w+://(?:[^:]+:[^@]@)?((?:[12]?[0-9]?[0-9]\\.){3}[12]?[0-9]?[0-9])(?:[:/]|$).*", "$1") AS ?ip)
}
GROUP BY ?ip
ORDER BY DESC(?count)

Run it (edit query)

URLs containing tracking identifiers

Displays a table of URLs in website=* and other tags that contain tracking identifiers in query parameters (or faux query parameters in URL hashes).

SELECT * WHERE {
  ?osm (osmt:website|osmt:contact:website|osmt:source:website|osmt:operator:website|osmt:source:url) ?website.
  FILTER REGEX(?website, "[?#&](mc_id|utm_\\w+|fbclid|gclid|campaign_ref|gclsrc|dclid|WT.tsrc|wt.tsrc|zanpid|yclid|igshid)=")
}

Run it (edit query)

URLs that don't start with http

Displays a table of URLs in website=* and other tags that don't start with "http" so probably lack a protocol, and won't dispaly correctly in many places that display these tags as links.

SELECT * WHERE {
  ?osm (osmt:website|osmt:contact:website|osmt:source:website|osmt:operator:website|osmt:source:url|osmt:url|osmt:website:mobile|osmt:heritage:website|osmt:brand:website|osmt:opening_hours:url|osmt:inscription:url) ?website.
  FILTER(!STRSTARTS(?website, "http"))
  FILTER(?website != "")
} ORDER BY ?website

Run it (edit query)

Malformed ways

Displays a table of malformed ways that consist of fewer than two nodes.

SELECT * WHERE {
  ?osm osmm:loc:error ?error.
  FILTER STRSTARTS(?error, "need at least two points for linestring")
}

Run it (edit query)

Incorrectly named features

Displays a table of features that are explicitly not named the names they are named:

SELECT * WHERE {
  ?osm osmt:not:name ?name;
       osmt:name ?name.
}

Run it (edit query)

Boundaries that are subareas of more local boundaries

Returns a table of administrative boundary relations that incorrectly contain larger administrative boundary relations as subareas.

SELECT ?super ?superLevelNumber ?sub ?subLevelNumber WHERE {
  # Query OpenStreetMap for administrative boundary relations and their subareas
  ?super osmt:boundary "administrative";
         osmm:type "r";
         osmt:admin_level ?superLevel;
         osmm:has ?sub;
         ?sub "subarea".
  
  # Get the administrative level of the subarea
  ?sub osmt:admin_level ?subLevel.
  
  # Only include cases where the subarea’s administrative level is lesser (more local) or equal to the superarea’s administrative level
  BIND(xsd:integer(?subLevel) AS ?subLevelNumber)
  BIND(xsd:integer(?superLevel) AS ?superLevelNumber)
  FILTER(?subLevelNumber <= ?superLevelNumber)
}
ORDER BY ?subLevelNumber ?superLevelNumber

Run it (edit query)

Boundary labels that are not nodes

Displays a table of administrative boundary relations whose labels are ways or relations. Much of the purpose of a label member in a boundary relation is to abstract the place represented by the boundary to a point feature, making it easier to label the place or associate the boundary with a specific coordinate. Setting the label to a way or relation somewhat defeats this purpose.

SELECT ?boundary ?label WHERE {
  # Query OpenStreetMap for administrative boundary relations and their labels
  ?boundary osmt:boundary "administrative";
            osmm:type "r";
            osmm:has ?label;
            ?label "label".
  
  # The label must be a way or relation but not a node
  VALUES ?labelTypes { "w" "r" }
  ?label osmm:type ?labelTypes.
}

Run it (edit query)

Features tagged with mutually exclusive keys

Displays a table of 500 features tagged with two keys that are incompatible with each other. For example, some features are tagged both noref=* and ref=* because a later mapper neglected to remove noref=* when adding ref=*.

SELECT ?osm ?key1 ?value1 ?key2 ?value2 WHERE {
  # Query the OpenStreetMap Wiki for pairs of data items about incompatible keys and their permanent key IDs
  ?item1 osmdt:P44 ?item2;
         osmdt:P16 ?keyID1.
  ?item2 osmdt:P16 ?keyID2.
  
  # Incompatibility is symmetric, so filter out redundant pairs
  FILTER(?keyID1 < ?keyID2)
  
  # Convert key IDs to keys by creating a URL that matches the osmt: prefix
  # osmm:key would be more convenient, but not every data item about a key has an osmm:key triple
  BIND(IRI(CONCAT("https://wiki.openstreetmap.org/wiki/Key:", ?keyID1)) AS ?key1)
  BIND(IRI(CONCAT("https://wiki.openstreetmap.org/wiki/Key:", ?keyID2)) AS ?key2)
  
  # Query OopenStreetMap for features that are tagged with both incompatible keys
  ?osm ?key1 ?value1;
       ?key2 ?value2.
}
LIMIT 500

Run it (edit query)

Features with identical phone and fax numbers

Displays a table of features tagged with the same number as both the phone number and fax number. Though it is possible for the same number to answer both voice calls and faxes, it is atypical for business phone lines to be configured this way.

SELECT * WHERE {
  ?osm (osmt:phone|osmt:contact:phone) ?number;
       (osmt:fax|osmt:contact:fax) ?number.
}

Run it (edit query)

Relations without members

Displays a table of 100 relations that have no members. These relations may have had members at some point, but a mapper may have attempted to discard the relation without deleting it. These days, editors like iD automatically delete a relation after removing its last member.

SELECT * WHERE {
  ?relation osmm:type "r".
  FILTER NOT EXISTS {
    ?relation osmm:has [].
  }
}
LIMIT 100

Run it (edit query)

Residential roads with too many lanes (slow)

Displays a table of 50 residential roads that have 10 or more lanes. It is unlikely that any of these roads has so many lanes, but if so, it would probably qualify for a higher road classification. More likely, a mapper accidentally entered a speed limit in the adjacent Lanes field in iD, or JOSM autocompleted a larger number after the mapper tried to enter a smaller one.

SELECT * WHERE {
  ?osm osmt:highway "residential";
       osmt:lanes ?lanes.
  FILTER(STRLEN(?lanes) > 1)
}
LIMIT 50

Run it (edit query)

Route relations that conflict with old way refs

Returns an interactive map of every roadway in Indiana that is a member of an Indiana State Road route relation but simultaneously is tagged old_ref=* for that route. It could be the case that, in reality, the route has been realigned or truncated to remove this roadway from the route, but the route relation has not been updated.

#defaultView:Map
SELECT ?way ?oldWayRef ?rel ?relRef ?coordinates WHERE {
  # The way says it used to be part of an Indiana State Road
  ?way osmt:old_ref ?oldWayRef;
       osmm:type "w";
       osmm:loc ?coordinates.
  FILTER(CONTAINS(?oldWayRef, "SR ") || CONTAINS(?oldWayRef, "IN "))
  
  # But that Indiana State Road’s route relation still contains the way
  ?rel osmt:network "US:IN";
       osmt:ref ?relRef;
       osmm:has ?way.
  BIND(REPLACE(?oldWayRef, ".*(?:SR|IN) (\\d+).*", "$1") AS ?relRef)
}

Run it (edit query)

Roadways with missing or mistagged refs

Displays an interactive map of Interstate highways, U.S. routes, and state routes in Indiana, Michigan, and Ohio whose ref=* lacks the prefix corresponding to the route relation of which the roadway is a member. In some cases, a mapper may have neglected to tag a short way with a ref when mapping a complex intersection or forgotten to update the route relation after rerouting the route relation onto a different road. In other cases, the ref=* is present but doesn't use the expected prefix.

#defaultView:Map
SELECT ?route ?highway ?ref ?coordinates WHERE {
  # Associate networks with their standard ref prefixes
  VALUES (?network ?refPrefix) {
    ("US:I" "I")
    ("US:US" "US")
    ("US:IN" "SR")
    ("US:MI" "M")
    ("US:OH" "SR")
  }
  
  # Query OpenStreetMap for routes belonging to these networks
  ?route osmt:network ?network;
         # Must be a numbered route
         osmt:ref [];
         osmt:route "road";
         osmt:type "route";
         osmm:type "r";
         osmm:has ?highway.
  
  # Omit special routes, which may not be signposted consistently on the ground
  FILTER NOT EXISTS {
    ?route osmt:modifier [].
  }
  
  # Query OpenStreetMap for highways along the routes
  ?highway osmt:highway [];
           osmm:loc ?coordinates.
  
  # Omit highways that contain the appropriate ref prefix
  FILTER NOT EXISTS {
    ?highway (osmt:ref|osmt:unsigned_ref) ?ref.
    FILTER REGEX(?ref, CONCAT(?refPrefix, "[- ]"))
  }
  
  # It’s debatable whether half a roundabout should be tagged with a way ref
  # There can’t be a gap in the route relation, so a way ref would be consistent with the route relation
  # But the convention is to leave roundabouts unnamed, so a way ref would be inconsistent with name=*
  FILTER NOT EXISTS {
    ?highway osmt:junction "roundabout".
  }
}

Run it (edit query)

Upside-down buildings

Returns a table of every building or building part that is tagged with a height=* that is smaller than the min_height=*. A common mistake when mapping elevated structures is to set height=* to the effective height, subtracting the bottom from the top. In fact, height=* is supposed to be the height at the top compared to ground level, irrespective of min_height=*.

SELECT ?building ?bottomMeters ?topMeters ?effectiveHeight WHERE {
  # Query OpenStreetMap for buildings and building parts and the heights of their tops and bottoms
  ?building osmt:height ?top;
            osmt:min_height ?bottom;
            (osmt:building|osmt:building:part) [].
  
  # Convert the top height to meters
  # Cast the height to compare it numerically
  BIND(IF(REGEX(?top, "^[0-9.]+'[0-9.]+\"$"), (xsd:float(STRBEFORE(?top, "'")) * 12 + xsd:float(STRBEFORE(STRAFTER(?top, "'"), "\""))) * 0.0254,
          IF(REGEX(?top, "^[0-9.]+'$"), xsd:float(STRBEFORE(?top, "'")) * 0.3048,
             xsd:float(?top))) AS ?topMeters)
  
  # Convert the bottom height to meters
  # Cast the height to compare it numerically
  BIND(IF(REGEX(?bottom, "^[0-9.]+'[0-9.]+\"$"), (xsd:float(STRBEFORE(?bottom, "'")) * 12 + xsd:float(STRBEFORE(STRAFTER(?bottom, "'"), "\""))) * 0.0254,
          IF(REGEX(?bottom, "^[0-9.]+'$"), xsd:float(STRBEFORE(?bottom, "'")) * 0.3048,
             xsd:float(?bottom))) AS ?bottomMeters)
  
  # Only include a building if its top is lower than its bottom
  FILTER(?topMeters < ?bottomMeters)
  
  # Calculate the effective height for sorting
  BIND(?topMeters - ?bottomMeters AS ?effectiveHeight)
}
ORDER BY ASC(?effectiveHeight)

Run it (edit query)

oneways with lanes in both directions

# works with defaultView:Map
select distinct ?way ?coordinates where {
  { #### any oneway that has lanes:forward and lanes:backward and neither is 0
    values ?oneway { # valid values of oneway
      "yes"
      "1"
      "-1"
      "reverse"
      "alternating"
      "alternate"
      "reversible"
    }

    ?way osmt:oneway ?oneway;
         osmt:lanes:forward ?forward;
         osmt:lanes:backward ?backward;
         osmm:loc ?coordinates.

    filter(?forward != "0" && ?backward != "0") # neither is 0
  } union { # any oneway=[yes|1|reverse|-1] that has more lanes than lanes:[forward|backward]
    values (?oneway ?direction) { # mapping of oneway values to :forward and :backward
      ("yes" osmt:lanes:forward)
      ("reverse" osmt:lanes:backward)
      ("1" osmt:lanes:forward)
      ("-1" osmt:lanes:backward)
    }

    ?way osmt:oneway ?oneway;
         osmt:lanes ?total;
         ?direction ?alsoTotal;
         osmm:loc ?coordinates.

    filter(?total != ?alsoTotal)
  }
}

Run it (edit query)

Pairs of turn restrictions that prohibit a different maneuver over the same geometry

Returns a table of 500 pairs of turn restrictions that indicate two different turn restrictions over the same geometry. For example, it should not be possible for a maneuver from the same roadway, via the same intersection, to the same roadway to be simultaneously a left turn and a right turn.

SELECT * WHERE {
  ?relation1 osmt:restriction ?restriction1;
             osmm:type "r";
             osmm:has ?from;
             ?from "from";
             osmm:has ?via;
             ?via "via";
             osmm:has ?to;
             ?to "to".
  ?relation2 osmt:restriction ?restriction2;
             osmm:type "r";
             osmm:has ?from;
             ?from "from";
             osmm:has ?via;
             ?via "via";
             osmm:has ?to;
             ?to "to".
  FILTER(?restriction1 != ?restriction2)
}
LIMIT 500

Run it (edit query)

Redundant or illegal U-turn restrictions

Displays an interactive map of malformed, U-turn-related restriction relations. Most are no U-turn restrictions that prohibit U-turning from a one-way street onto the same street, which would be implied by the oneway=yes tag. Others supposedly require the driver to make this illegal and unsafe maneuver. This error usually occurs when dividing a road into a dual carriageway but neglecting to replace the turn restriction's from or to member with the new parallel one-way.

#defaultView:Map
SELECT * WHERE {
  # Query OpenStreetMap for no U-turn and U-turn only restrictions 
  VALUES (?restrictions ?layer) { ("no_u_turn" "No U-turn") ("only_u_turn" "U-turn only") }
  ?restriction osmt:restriction ?restrictions;
               # Where the maneuver goes from a way to the same way
               osmm:has ?way;
               ?way "from";
               osmm:has ?way;
               ?way "to".
  # And the way is a one-way
  ?way osmt:oneway "yes";
       osmm:loc ?coordinates.
}

Run it (edit query)

Unclosed areas

Displays an interactive map of ways that are explicitly tagged as areas using area=yes but are not closed ways.

#defaultView:Map
SELECT * WHERE {
  ?area osmt:area "yes";
        osmm:isClosed false;
        osmm:loc ?coordinates.
}

Run it (edit query)

Imports and automated edits

Ways left untagged by the OSMF Redaction Bot

Displays an interactive map of ways left untagged by the OSMF Redaction Bot. This query does not include ways that were subsequently edited by another user. The redaction bot removed all the tags from the way because they had been added by a user who did not agree to OpenStreetMap's adoption of the Open Database License in 2012. In some cases, it is possible that the tags were originally added by another user but were considered tainted because of a split way that did not preserve the original history.

#defaultView:Map
SELECT ?osm ?coordinates WHERE {
  # Query OpenStreetMap for ways last touched by the OSMF Redaction Account and their coordinates
  ?osm osmm:type "w";
       osmm:user "OSMF Redaction Account";
       osmm:loc ?coordinates.
  
  # Only include ways for which there are no key predicates (typically abbreviated as osmt:)
  FILTER NOT EXISTS {
    ?osm ?predicate [].
    FILTER(STRSTARTS(STR(?predicate), "https://wiki.openstreetmap.org/wiki/Key:"))
  }
}

Run it (edit query)

Coincident GNIS reservoirs and dams

Displays an interactive map of reservoirs and dams imported from the Geographic Names Information System (GNIS) as pairs of nodes at the same exact coordinates. Convert the landuse=reservoir node to an area representing the entire reservoir. Convert the waterway=dam node to a way or area connected to the reservoir that represents the dam structure.

There are other cases of coincident nodes imported from GNIS, such as post offices at the same coordinates as their towns, or multiple coincident "towers" representing colocated television and radio stations.

#defaultView:Map{"hide": "?coordinates"}
SELECT ?poi1 ?name1 ?poi2 ?name2 ?coordinates WHERE {
  # Query OpenStreetMap for a reservoir with a GNIS feature ID
  ?poi1 (osmt:gnis:id|osmt:gnis:feature_id) ?featureID1;
        osmt:landuse "reservoir";
        osmt:name ?name1;
        osmm:loc ?coordinates.
  # Query OpenStreetMap for a dam with a GNIS feature ID at the same coordinates
  ?poi2 (osmt:gnis:id|osmt:gnis:feature_id) ?featureID2;
        osmt:waterway "dam";
        osmt:name ?name2;
        osmm:loc ?coordinates.
}

Run it (edit query)

Missing or untagged GNIS features

Displays a map of GNIS features that are either unmapped or not yet tagged with gnis:feature_id=* in the given U.S. state (Ohio by default). Click on a feature to reveal its name and suggested tags.

Features tagged with wikidata=* are excluded from the query, since that tag makes it possible for a data consumer to connect the map feature to the GNIS feature via Wikidata.

The features are marked on the map based on the coordinates in Wikidata. Before copying features wholesale from this query's results, uncomment the relevant sentence to filter the results down to only the features whose coordinates come directly from GNIS, as opposed to being imported from Wikipedia. (Wikipedia sourced many coordinates from copyrighted maps such as Google Maps. For these GNIS features, it's unlikely the coordinates come from GNIS, but it's better to take the time to manually review each feature, copying the coordinates from GNIS where appropriate.)

#defaultView:Map
#TEMPLATE={ "template": { "en": "Missing GNIS features in ?state" }, "variables": { "?state": { "query": "SELECT ?id WHERE { ?id wdt:P31 wd:Q35657. }" } } }
SELECT ?wd (GROUP_CONCAT(DISTINCT ?tag; SEPARATOR="\n") AS ?tags) (SAMPLE(?name) AS ?name) (SAMPLE(?fid) AS ?fid) ?coordinates WHERE {
  # Prevent optimizer from querying for everything in OSM before querying the service.
  hint:Query hint:optimizer "None" .
  
  SERVICE <https://query.wikidata.org/sparql> {
    BIND(wd:Q1397 AS ?state)
    
    # Get a GNIS feature’s ID and coordinates
    ?wd wdt:P590 ?fid;
        wdt:P131+ ?state;
        wdt:P625 ?coordinates.
    
    # Uncomment this sentence to require the coordinate to come straight from GNIS
    # Most coordinates were imported from Wikipedia, where they may have come from a copyrighted map
#    ?wd p:P625 [
#      ps:P625 ?coordinatesStatement;
#              prov:wasDerivedFrom [
#                # Stated in GNIS
#                pr:P248 wd:Q136736;
#              ]
#    ].

    # Get the name
    OPTIONAL {
      ?wd rdfs:label ?name.
      FILTER(LANG(?name) = "en")
    }

    # Suggest a feature tag
    OPTIONAL {
      ?wd wdt:P31/wdt:P1282 ?tagPageName.
      BIND(REPLACE(REPLACE(?tagPageName, "^Key:(.+)", "$1=*"), "^Tag:", "") AS ?tag)
    }
  }
  
  FILTER NOT EXISTS {
    ?osm (osmt:ref:gnis|osmt:GNISID|osmt:gnis:id|osmt:gnis:feature_id) ?fid.
  }
  
  # A lot of these features are already tagged with Wikidata QIDs, which is good enough
  FILTER NOT EXISTS {
    ?osm (osmt:wikidata) ?wd.
  }
}
GROUP BY ?wd ?coordinates
ORDER BY ASC(?fid)

Run it (edit query)

TIGER desert counties (slow)

Displays a table of counties and county equivalents in the United States sorted by the number of linear features (roads, power lines) that are still untouched since the TIGER 2015 import. By and large, these counties have not benefited from TIGER fixup efforts, though a few very populous counties are overrepresented in the table. This query considers whether a way has been edited since an import account added it, allowing that a another bot may have come in afterwards to expand abbreviations in the road name. However, it does not account for TheDutchMan13's removal of tiger:zip_left=* and tiger:zip_right=* tags, which took place long after TIGER cleanup efforts began.

Compared to these Overpass API queries, this query only looks at the metadata of the way, not its constituent nodes. So if a mapper cleans it up by moving its nodes but avoids adding or removing any nodes and avoids changing any tags, the way is counted as untouched. On the other hand, the query counts each TIGER/Line ID (tiger:tlid=*) once, avoiding an overcount in some cases.

SELECT ?county (COUNT(*) AS ?count) WHERE {
  {
    SELECT ?tlid (SAMPLE(?county) AS ?county) WHERE {
      {
        # Initial TIGER 2005 import
        ?osm osmt:tiger:reviewed "no";
             osmm:user "DaveHansenTiger";
             osmm:version 1;
             osmm:timestamp ?timestamp.
      } UNION {
        # Initial TIGER 2005 import in some Pennsylvania counties
        ?osm osmt:tiger:reviewed "no";
             osmm:user "Milenko";
             osmm:version 1;
             osmm:timestamp ?timestamp.
        FILTER(?timestamp > "2007-10-29"^^xsd:dateTime && ?timestamp < "2007-12-12"^^xsd:dateTime)
      } UNION {
        # Abbreviation expansion and UUID removal
        ?osm osmt:tiger:reviewed "no";
             osmm:user "balrog-kun";
             osmm:version 2;
             osmm:timestamp ?timestamp.
        FILTER(?timestamp > "2010-03-21"^^xsd:dateTime && ?timestamp < "2010-04-08"^^xsd:dateTime)
      } UNION {
        # Abbreviation expansion
        ?osm osmt:tiger:reviewed "no";
             osmm:user "bot-mode";
             osmm:version 2;
             osmm:timestamp ?timestamp.
        FILTER(?timestamp > "2012-12-06"^^xsd:dateTime && ?timestamp < "2013-04-25"^^xsd:dateTime)
      }
      
      ?osm osmt:tiger:tlid ?tlid;
           osmt:tiger:county ?county.
    }
    GROUP BY ?tlid
  }
}
GROUP BY ?county
ORDER BY DESC(?count)

Run it (edit query)

TIGER desert ZIP codes (slow)

Displays a table of ZIP code tabulation areas (ZCTAs) in the United States sorted by the number of linear features (roads, power lines) that are still untouched since the TIGER 2015 import. ZCTAs do not necessarily correspond to ZIP codes but are similar enough to pinpoint TIGER deserts. By and large, these ZIP codes have not benefited from TIGER fixup efforts, though a few very populous counties are overrepresented in the table. This query considers whether a way has been edited since an import account added it, allowing that a another bot may have come in afterwards to expand abbreviations in the road name. However, it does not account for TheDutchMan13's removal of tiger:zip_left=* and tiger:zip_right=* tags, since this query uses those tags to rank ZIP codes.

Compared to these Overpass API queries, this query only looks at the metadata of the way, not its constituent nodes. So if a mapper cleans it up by moving its nodes but avoids adding or removing any nodes and avoids changing any tags, the way is counted as untouched. On the other hand, the query counts each TIGER/Line ID (tiger:tlid=*) once, avoiding an overcount in some cases.

This query runs even slower than the TIGER desert counties query. It only counts ways tagged with tiger:zip_left=* or tiger:zip_right=* keys, so it excludes most service roads.

SELECT ?zips (COUNT(*) AS ?count) WHERE {
  {
    SELECT ?tlid (GROUP_CONCAT(DISTINCT ?zip; SEPARATOR=":") AS ?zips) WHERE {
      {
        ?osm osmt:tiger:reviewed "no";
             osmm:user "DaveHansenTiger";
             osmm:version 1;
             osmm:timestamp ?timestamp.
      } UNION {
        ?osm osmt:tiger:reviewed "no";
             osmm:user "Milenko";
             osmm:version 1;
             osmm:timestamp ?timestamp.
        FILTER(?timestamp > "2007-10-29"^^xsd:dateTime && ?timestamp < "2007-12-12"^^xsd:dateTime)
      } UNION {
        ?osm osmt:tiger:reviewed "no";
             osmm:user "balrog-kun";
             osmm:version 2;
             osmm:timestamp ?timestamp.
        FILTER(?timestamp > "2010-03-21"^^xsd:dateTime && ?timestamp < "2010-04-08"^^xsd:dateTime)
      } UNION {
        ?osm osmt:tiger:reviewed "no";
             osmm:user "bot-mode";
             osmm:version 2;
             osmm:timestamp ?timestamp.
        FILTER(?timestamp > "2012-12-06"^^xsd:dateTime && ?timestamp < "2013-04-25"^^xsd:dateTime)
      }
      ?osm osmt:tiger:tlid ?tlid;
           (osmt:tiger:zip_left|osmt:tiger:zip_right) ?zip.
    }
    GROUP BY ?tlid
  }
}
GROUP BY ?zips
ORDER BY DESC(?count)

Run it (edit query)

Counties with the most untouched GNIS POIs per capita

Displays a table of counties of the United States sorted by the number of POIs in the county that were imported from the Geographic Names Information System (GNIS) in 2007 or 2009 and have not been edited since. The number of unedited POIs is normalized by the county's population, to provide a more accurate picture of the areas that are underperforming in terms of GNIS cleanup. This query accounts for several mass edits of GNIS POIs after the import, as well as changesets that reverted many of these edits.

This query demonstrates a named subquery, a Blazegraph extension that improves the query's performance considerably by enforcing the order in which each subquery takes place.

SELECT DISTINCT ?stateLabel ?countyLabel ?unreviewed ?population ?unreviewedPerCapita

WITH {
  # Query OpenStreetMap for
  SELECT ?stateID ?countyID (COUNT(*) AS ?unreviewed) WHERE {
    
    # GNIS populated places and other points of interest, whether imported or hand-entered
    ?osm (osmt:gnis:ST_num|osmt:gnis:state_id) ?stateID;
         (osmt:gnis:County_num|osmt:gnis:county_id) ?countyID;
         (osmt:gnis:id|osmt:gnis:feature_id) ?featureID.
    
    {
      # Virtually every GNIS place was geocoded with an is_in=* tag soon after
      ?osm osmm:version 2;
           osmm:user "davidearl";
           osmm:changeset ?changeset.
      # This user continued to map by hand after the import, so limit to bulk changesets
      FILTER(?changeset >= 100864 && ?changeset <= 196981)
    } UNION {
      # GNIS import of other points of interest
      ?osm osmm:version 1;
           osmm:user "iandees";
           osmm:changeset ?changeset.
      # This user continued to map by hand after the import, so limit to bulk changesets
      FILTER(?changeset >= 628790 && ?changeset <= 794649)
    } UNION {
      # Guessing religions and denominations of GNIS places of worship
      ?osm osmt:amenity "place_of_worship";
           osmm:version 2;
           osmm:user "Geogast";
           osmm:changeset ?changeset.
      # Geogast tagged religions and denominations from changeset 2528312 in 2009 and late as changeset 36242898 in 2015
      # But this catches the changesets that touched hundreds or thousands of features
      FILTER(?changeset >= 3155808 && ?changeset <= 4025459)
    } UNION {
      # Mass reverts of edits to GNIS POIs
      VALUES ?version { 3 4 }
      VALUES ?changeset {
        # Wikidata tagging by LogicalViolinist
        43780386
        # Deletion of deprecated tags by GuyLamar2006
        75952000
      }
      ?osm osmm:user "woodpeck_repair";
           osmm:version ?version;
           osmm:changeset ?changeset.
    }
  }
  GROUP BY ?stateID ?countyID
} AS %gnis

WHERE {
  INCLUDE %gnis.
  
  # A FIPS 6-4 code combines the state and county IDs
  BIND(CONCAT(?stateID, ?countyID) AS ?fips)
  
  # Query Wikidata for
  SERVICE <https://query.wikidata.org/sparql> {
    # The county or county equivalent with the matching FIPS 6-4 code
    ?county wdt:P882 ?fips;
            # Its population (assuming the current population has preferred rank
            wdt:P1082 ?population;
            # Its name in English
            rdfs:label ?countyLabel.
    FILTER(LANG(?countyLabel) = "en")
  }
  
  # Query Wikidata again for
  SERVICE <https://query.wikidata.org/sparql> {
    # The state with the matching FIPS 5-2 numeric code
    ?state wdt:P5087 ?stateID;
           # Its name in English
           rdfs:label ?stateLabel.
    FILTER(LANG(?stateLabel) = "en")
  }
  
  # Calculate the county’s unreviewed POIs per capita
  BIND(?unreviewed / ?population AS ?unreviewedPerCapita)
}
ORDER BY DESC(?unreviewedPerCapita)

Run it (edit query)

Overwritten GNIS post office names

Displays an interactive map of United States Post Office locations that were imported from the Geographic Names Information System (GNIS), then retagged according to the Name Suggestion Index, overwriting the branch name with a generic name. Select a result, follow the link to the GNIS feature page, and set name=* back to the name listed on that page. If the page also lists an address, also tag the post office with that address.

#defaultView:Map
SELECT * WHERE {
  ?osm osmt:brand:wikidata wd:Q668687;
       osmt:name "United States Post Office";
       osmt:gnis:feature_id ?featureID;
       osmm:loc ?coordinates.
  BIND(IRI(CONCAT("https://geonames.usgs.gov/apex/f?p=GNISPQ:3:::NO::P3_FID:", ?featureID)) AS ?gnis)
}

Run it (edit query)

Miscellaneous

Calculate simple centroid of a relation

The location is calculated by taking all relation's members, and averaging all of their longitudes and latitudes.
Note: This is not very accurate, especially for relations whose members are near anti-meridian.

#defaultView:Map
SELECT
  ?rel
  # Calculate average lng and lat, and reconstruct a geopoint literal
  (strdt(
    concat(
      'Point(', STR(xsd:float(AVG(?longitude))), ' ', STR(xsd:float(AVG(?latitude))), ')'
    ), geo:wktLiteral
  ) as ?loc)
WHERE {

  # Limit to just a few relation IDs, otherwise the server times out
  BIND( osmrel:13 as ?rel )

  # Subject must be a relation, and must not already have a location
  ?rel osmm:type 'r' .
  FILTER NOT EXISTS { ?rel osmm:loc ?relLoc . }

  # Relation must have members, and those members must have locations
  ?rel osmm:has ?member .
  ?member osmm:loc ?loc .

  # Extract each member's longitude and latitude
  BIND( geof:longitude(?loc) as ?longitude )
  BIND( geof:latitude(?loc) as ?latitude )
}
GROUP BY ?rel  # group all found items by relation
LIMIT 10

Run it (edit query)

Use regex to search tags: a word in wikipedia tag ends in a "ville"

SELECT ?osmId ?place ?wp ?loc WHERE {
  # must have place and wikipedia tags
  ?osmId osmt:place ?place ;
         osmt:wikipedia ?wp .
  
  # Find any wikipedia link with a word ending in "ville"
  # Unlike other tags, wikipedia and wikidata tags are stored as URIs,
  # so they must be converted into a string before using string functions.
  FILTER (regex(str(?wp), "ville([^a-z]|$)", "i"))
  
  # If location metatag exists, include it
  # Use "Table" button to switch to the map view
  OPTIONAL { ?osmId osmm:loc ?loc . }
}
LIMIT 10

Run it (edit query)

Show a map of the user's edits between dates, if edit is still last, by obj type

#defaultView:Map
SELECT * WHERE {

  ?osmId osmm:user 'nyuriks' ;
         osmm:loc ?loc ;
         osmm:type ?layer .

  # Filter by timestamp
  ?osmId osmm:timestamp ?ts .
  FILTER ("2017-03-01T00:00:00Z"^^xsd:dateTime < ?ts &&
          ?ts < "2017-03-05T00:00:00Z"^^xsd:dateTime)
}

Run it (edit query)