Sophox/Example queries/Raw: Difference between revisions

From OpenStreetMap Wiki
Jump to navigation Jump to search
(Wrong section)
Line 94: Line 94:
#defaultView:Map
#defaultView:Map
SELECT * WHERE {
SELECT * WHERE {
{
?osmId osmm:type ?osmType ;
?osm osmt:office "education".
{
} UNION {
?osm osmt:office "education" ;
?osm osmt:sport "pilates".
} UNION {
}
?osm osmt:sport "pilates" .
?osm osmm:loc ?loc.
}
?osm osmm:loc ?loc .
}
}
}}
}}

Revision as of 17:34, 11 August 2020

This page collects SPARQL queries that demonstrate Sophox's capabilities and help you understand how to formulate your own queries.

information sign

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.

Some queries that no longer work were moved to a subpage - they need to be updated to use Wikidata federation service instead of assuming that all Wikidata data is present in the same database.

Simple queries

List all OSM objects with a place tag

Get started with this simple query to list OSM objects by a particular type or tag. (Compare it with a similar Overpass Query).

SELECT * WHERE {
  # Limit to subjects that have an OSM type ('n', 'r', 'w').
  # Replace ?osmType with a string 'r' to show only relations.
  ?osmId osmm:type ?osmType .

  # Limit to subjects that have an OSM tag `place`
  # Replace ?place with a string 'city' to filter the tag value to `place=city`
  ?osmId osmt:place ?place  .

  # Uncomment this line to only show places that have no `name:en` tag
  # FILTER NOT EXISTS { ?osmId osmt:name:en ?nameen . }

} LIMIT 50

Run it (edit query)

Show OSM objects by type node with a tag office=education on a map

Get started with this second simple query to show OSM objects by type node and tag office=education on a map.

#defaultView:Map
SELECT * WHERE {
  ?osmId osmm:type "n" ;
         osmt:office "education" ;
         osmm:loc ?loc .
}

Run it (edit query)

Show all OSM objects within an bbox (Dallas area) with a tag leisure=pitch without sport=* using box service

#defaultView:Map
SELECT * WHERE {
  ?osmId osmt:leisure "pitch".
  
  # Filter bbox
  SERVICE wikibase:box {
    ?osmId osmm:loc ?coordinates .
    bd:serviceParam wikibase:cornerSouthWest 'Point(-97.00 32.50)'^^geo:wktLiteral.
    bd:serviceParam wikibase:cornerNorthEast 'Point(-96.60 33.00)'^^geo:wktLiteral.
  }
  
  FILTER NOT EXISTS {
    ?osmId osmt:sport [].
  }
}

Run it (edit query)


Show all OSM objects around specific place (Suriname area) with a tag leisure=pitch without sport=* using around service

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

         FILTER NOT EXISTS { 
           ?osmId osmt:sport [] .
         }
}

Run it (edit query)

Show all OSM objects with a tag place=village and the name contains a string of "View"

#defaultView:Map
SELECT * WHERE {
  ?osmId osmm:type ?osmType ;
         osmt:place "village" ;
         osmt:name ?name ;
         osmm:loc ?loc .
  FILTER  (CONTAINS(?name, "View"))
}

Run it (edit query)

Show all OSM objects with a tag office=education or tag sport=pilates

#defaultView:Map
SELECT * WHERE {
  {
    ?osm osmt:office "education".
  } UNION {
    ?osm osmt:sport "pilates".
  }
  ?osm osmm:loc ?loc.
}

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.

#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.

#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, as an example of how to split a string in SPARQL. This query is 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. There have been proposals to enable searching by OLCs at openstreetmap.org. [1]

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)

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)

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: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)

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)

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)

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)

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, "[𐀀-􏿿]")
  
  # Isolate the character
  BIND(REPLACE(?name, "^.*([𐀀-􏿿]+).*$", "$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=*.

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)

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. [2]

#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 way 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)

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)

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 [3][4], 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. [5]

#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. [6]

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)

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.

SELECT ?osm ?name WHERE {
  ?osm osmt:name:es ?name.
  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)

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)

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)

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)

Contributing

To add an example to the Examples panel in Sophox:

  1. Prototype the example in Sophox. Make sure it runs reasonably quickly for what it demonstrates. Add adequate whitespace and comments so the reader can follow along. If the example is best understood by viewing it in a particular result view, add a #defaultView:… comment.
  2. Under the category that best fits your example, add a new level 3 section heading that succinctly describes the example. If the query takes a long time or tends to time out, append "(slow)" to the heading.
  3. Optionally add a paragraph describing the example in more detail, mentioning any limitations and assumptions, as well as any interesting OpenStreetMap tags or Wikidata properties that it depends on.
  4. Insert the {{SPARQL}} template, setting the |query = parameter to the entire SPARQL query. Replace any | characters in the query with {{!}} to prevent the query from being truncated.
  5. Reload Sophox and test out the new example.

See also