Sophox/Example queries/Raw: Difference between revisions
Minh Nguyen (talk | contribs) (Wrong section) |
Minh Nguyen (talk | contribs) |
||
| Line 94: | Line 94: | ||
#defaultView:Map |
#defaultView:Map |
||
SELECT * WHERE { |
SELECT * WHERE { |
||
{ |
|||
?osmId osmm:type ?osmType ; |
|||
| ⚫ | |||
} UNION { |
|||
| ⚫ | |||
| ⚫ | |||
} UNION { |
|||
} |
|||
| ⚫ | |||
| ⚫ | |||
} |
|||
| ⚫ | |||
} |
} |
||
}} |
}} |
||
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.
.svg){kind=icon}
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
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 .
}
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 [].
}
}
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 [] .
}
}
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"))
}
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.
}
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
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
}
}
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
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)
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)
}
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
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
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
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 }
}
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". }
}
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.
}
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)
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)
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 }
}
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
}
}
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
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)
}
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)
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
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". }
}
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
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)
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)
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
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
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)
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)
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
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
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
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
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)
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)
}
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
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
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"))
}
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.
}
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)
Human-readable route refs
Displays the tagged ref=* of 28301898
28301898 along with its correctly formatted route number. For historical technical reasons, mappers in many U.S. states long ago standardized on prefixes for ref=* tags on ways that are partially machine-readable: not quite unique, but unique enough for a U.S.-specific CartoCSS stylesheet like MapQuest Open to choose route shields using regular expressions. Unfortunately, these prefixes often do not correspond to the actual prefixes used officially or by the general public. At the same time, network=* tags are fully unique and machine-readable, but they correspond even less to the actual prefixes. This query demonstrates cross-referencing the network=* tags with data items and Wikidata to correctly format the route number for display to a user, respecting any state-specific practices.
# Combine the roadway’s formatted route numbers, separated by a slash
SELECT ?highway (SAMPLE(?wayRef) AS ?wayRef) (GROUP_CONCAT(DISTINCT ?formattedRef; SEPARATOR="/") AS ?formattedRef) WHERE {
{
SELECT DISTINCT ?highway ?wayRef ?network ?formattedRef WHERE {
# Start with a roadway in OpenStreetMap
BIND(osmway:28301898 AS ?highway)
# For comparison, get the way ref
?highway osmt:ref ?wayRef.
# Query OpenStreetMap for road routes that contain the roadway
?route osmt:route "road";
osmm:type "r";
osmm:has ?highway;
osmt:network ?network;
osmt:ref ?ref.
# Get the state the route travels through as a FIPS 5-2 alpha code (two-letter state abbreviation)
OPTIONAL {
?route osmt:is_in:state ?stateCode.
}
# Rank the route’s network by the number of semicolons, roughly corresponding to the level of government responsible for the route
BIND(STRLEN(REPLACE(?network, "[^:]", "")) AS ?networkLevel)
# And the length of the network as a tiebreaker
BIND(STRLEN(?network) AS ?networkLength)
# Sort the route number numerically
BIND(xsd:integer(?ref) AS ?refNumber)
# Query the OpenStreetMap Wiki for a data item that corresponds to the route’s network and get the corresponding Wikidata QID
BIND(CONCAT("network=", ?network) AS ?networkKV)
?osmdRoute osmdt:P19 ?networkKV;
osmdt:P12 ?qid.
# Convert the Wikidata QID from a string into a URL, which is how entities are normally represented
BIND(IRI(CONCAT("http://www.wikidata.org/entity/", ?qid)) AS ?wdRoute)
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# A route number formatter
?wdRoute wdt:P8498 ?genericRefFormatter.
OPTIONAL {
# A state corresponding to the two-letter state abbreviation
?state wdt:P5086 ?stateCode.
# A route number formatter valid only in the state
?wdRoute p:P8498 [ps:P8498 ?stateRefFormatter; pq:P3005 ?state].
}
# Prefer a state-specific route number formatter over a generic route number formatter
BIND(COALESCE(?stateRefFormatter, ?genericRefFormatter) AS ?refFormatter)
}
# Format the route number by replacing the placeholder in the formatter
# REPLACE() takes a regular expression, so “$” needs to be escaped
BIND(REPLACE(?refFormatter, "\\$1", ?ref) AS ?formattedRef)
}
ORDER BY ?networkLevel ?networkLength ?refNumber
}
}
GROUP BY ?highway
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
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
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
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)
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
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)
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". }
}
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
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
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
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". }
}
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
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)
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". }
}
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". }
}
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) )
} }
}
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)
}
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")
}
}
}
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
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]"))
}
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
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)
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
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)
}
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")
}
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
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.
}
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
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
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)
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}")
}
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)
}
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:"))
}
}
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.
}
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)
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)
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)
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)
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
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
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)
}
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
Contributing
To add an example to the Examples panel in Sophox:
- 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. - 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.
- 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.
- 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. - Reload Sophox and test out the new example.
See also
- SPARQL tutorial at Wikidata
- SPARQL textbook at Wikibooks
- Wikibase RDF format
- OSM RDF format
- Wikipedia Link Improvement Project
- comparison (SPARQL vs Overpass QL) - see overview about some simple query examples