Sophox/Example queries/Raw
.svg){kind=icon}
Simple queries
place=*
Displays a table of 50 features tagged place=* anywhere in the world, along with their place=* values. In this query, the place key is the predicate. Sophox supports some other kinds of predicates that are not OpenStreetMap keys, so OpenStreetMap keys always begin with the osmt: prefix.
SELECT * WHERE {
?place osmt:place ?placeType.
}
LIMIT 50
office=newspaper nodes
Displays a table of nodes tagged office=newspaper anywhere in the world. Predicates for OpenStreetMap metadata begin with the osmm: prefix. You only need to include the osmm:type predicate if you want to display each element's type or restrict the results to elements of a particular type.
SELECT * WHERE {
?newspaper osmt:office "newspaper";
# "n" for nodes, "w" for ways, or "r" for relations
# Replace "n" with ?type to include all three types and indicate the type in a separate column
osmm:type "n".
}
office=newspaper on a map
Displays an interactive map of anything tagged office=newspaper anywhere in the world. In Sophox, you can use the view menu to switch any query to an interactive map, as long as one of the columns contains well-known text of Point geometries, such as the object of the osmm:loc predicate. This query demonstrates setting the view to an interactive map by default.
#defaultView:Map
SELECT * WHERE {
?newspaper osmt:office "newspaper";
osmm:loc ?coordinates.
}
office=newspaper and name=*
Displays a table of anything that is tagged both office=newspaper and name=* anywhere in the world.
This query is considerably faster than querying for anything tagged name=*, because that tag is extremely common. However, it is only slightly faster than querying for anything tagged office=newspaper. For relatively uncommon tags, it is unnecessary and potentially counterproductive to overspecify the query with many predicates.
SELECT * WHERE {
?newspaper osmt:office "newspaper";
osmt:name ?name.
}
office=newspaper and optionally name=*
Displays a table of anything that is tagged office=newspaper anywhere in the world. For easier identification, this query displays each feature's name=* if available, but it also includes features without name=*.
To display name=* and name:en=* only when both tags are also present, add a sentence about name:en=* to the OPTIONAL expression below. To display name=* and/or name:en=*, add a separate OPTIONAL expression for name:en=*.
SELECT * WHERE {
?newspaper osmt:office "newspaper".
OPTIONAL {
?newspaper osmt:name ?name.
}
}
office=newspaper but not name=*
Displays a table of anything tagged office=newspaper but not name=*.
SELECT * WHERE {
?newspaper osmt:office "newspaper".
FILTER NOT EXISTS {
# The [] is a placeholder for an ignored object; replacing it with ?name would have the same effect
?newspaper osmt:name [].
}
}
office=newspaper or office=advertising_agency
Displays a table of anything that is tagged either office=newspaper or office=advertising_agency anywhere in the world. The two tags share the same key, so this query uses a VALUES expression to vary between two values.
SELECT * WHERE {
VALUES ?offices { "newspaper" "advertising_agency" }
?office osmt:office ?offices.
}
office=newspaper or disused:office=newspaper
Displays a table of anything that is tagged either office=newspaper or disused:office=newspaper anywhere in the world. The two tags share the same value, so this query uses a single sentence that alternates between two predicates.
SELECT * WHERE {
?newspaper (osmt:office|osmt:disused:office) "newspaper".
}
office=newspaper or studio=radio
Displays a table of anything that is tagged either office=newspaper or studio=radio anywhere in the world. Because the two tags share neither a key nor a value, this query unions together two separate sentences that share the same subject.
SELECT * WHERE {
{
?mediaOutlet osmt:office "newspaper".
} UNION {
?mediaOutlet osmt:studio "radio".
}
}
leisure=pitch within a specific bounding box
Displays an interactive map of anything tagged leisure=pitch that is located within a specific bounding box encompassing the Dallas area. (For ways and areas, the centroid is compared to the specified bounding box.) You can use a tool like bboxfinder or geojson.io to calculate a bounding box.
When writing a well-known text literal, put the longitude before the latitude. Unlike the Overpass API, Sophox generally performs faster with a global query than when restricting the same query by geography.
#defaultView:Map
SELECT * WHERE {
?pitch osmt:leisure "pitch".
SERVICE wikibase:box {
?pitch osmm:loc ?coordinates.
# Southwest longitude and latitude of the Dallas area
bd:serviceParam wikibase:cornerSouthWest "Point(-97.00 32.50)"^^geo:wktLiteral.
# Northeast longitude and latitude of the Dallas area
bd:serviceParam wikibase:cornerNorthEast "Point(-96.60 33.00)"^^geo:wktLiteral.
}
}
leisure=pitch near a specific coordinate
Displays an interactive map of anything tagged leisure=pitch that is located within 300 kilometres (190 mi) of a specific coordinate, approximating Suriname. (For ways and areas, the centroid is compared to the specified bounding box.)
When writing a well-known text literal, put the longitude before the latitude. Unlike the Overpass API, Sophox generally performs faster with a global query than when restricting the same query by geography.
#defaultView:Map
SELECT * WHERE {
?pitch osmt:leisure "pitch" .
SERVICE wikibase:around {
?pitch osmm:loc ?coordinates.
bd:serviceParam wikibase:center "Point(-56.00 4.00)"^^geo:wktLiteral. # somewhere in Suriname
bd:serviceParam wikibase:radius "300". # kilometers
bd:serviceParam wikibase:distance ?distance.
}
}
leisure=pitch near you
Displays an interactive map of anything tagged leisure=pitch that is located within 10 kilometres (6.2 mi) of your current location. (For ways and areas, the centroid is compared to the specified bounding box.) When you run this query, your browser will ask you for permission to give Sophox your location to customize the query.
#defaultView:Map
SELECT * WHERE {
?pitch osmt:leisure "pitch" .
SERVICE wikibase:around {
?pitch osmm:loc ?coordinates.
bd:serviceParam wikibase:center "[AUTO_COORDINATES]".
bd:serviceParam wikibase:radius "10". # kilometers
bd:serviceParam wikibase:distance ?distance.
}
}
place=village whose name=* contains "View"
Displays a table of anything that is tagged with both place=village and name=*, as long as name=* contains "View". Use a filter for free-form keys where hard-coding a complete list of tag values to match would be unwieldy and impractical. Functions such as CONTAINS are slower than literal objects in sentences, such as "village" below, because Sophox has to fetch every name and evaluate it individually instead of looking up entries in an optimized index.
SELECT * WHERE {
?village osmt:place "village";
osmt:name ?name.
FILTER CONTAINS(?name, "View")
}
place=village whose name=* ends with -boro or -borough
Displays a table of anything that is tagged with both place=village and name=*, as long as name=* ends with -boro or -borough. This query demonstrates combining string functions using logical operators for readability, but SPARQL also supports regular expressions for more complex filtering.
SELECT * WHERE {
?village osmt:place "village";
osmt:name ?name.
FILTER(STRENDS(?name, "boro") || STRENDS(?name, "borough"))
# Alternatively, use a regular expression
# FILTER REGEX(?name, "boro(ugh)?$")
}
Members of type=site relations with the role 
entrance
Displays a table of nodes, ways, and relations that are members of type=site relations with the role entrance.
SELECT * WHERE {
?relation osmt:type "site";
osmm:type "r";
osmm:has ?member;
?member "entrance".
}
leisure=* last edited between specific dates
Displays a table of anything tagged leisure=* that was edited in August 2020 (UTC) and has not been edited since then. Sophox does not store full OpenStreetMap edit history; it only knows when each feature was last edited and by whom.
SELECT * WHERE {
?leisure osmt:leisure [];
osmm:timestamp ?timestamp.
# Filter by UTC timestamps in ISO 8601 format
FILTER("2020-08-01T00:00:00Z"^^xsd:dateTime < ?timestamp &&
?timestamp < "2020-09-01T00:00:00Z"^^xsd:dateTime)
}
Seas named by pirates
Displays a table of seas with English names turned into pirate-speak – arrr! TileMill had a Pirates map template that, among other cartographic flourishes, labeled the Sargasso Sea as "Sarrgasso Sea". [1] This query demonstrates text replacement using the REPLACE() function.
SELECT * WHERE {
?sea osmt:place "sea";
osmt:name:en ?english.
FILTER CONTAINS(?english, "ar")
BIND(REPLACE(?english, "ar", "arrr") AS ?pirate)
}
Lane counts of running tracks on a line graph
Displays a line graph of running tracks by lane count, with the lane count along the X axis and the number of tracks with that lane count along the Y axis. This query demonstrates aggregation, as well as casting tag values for a more sophisticated comparison.
#defaultView:LineChart
SELECT ?lanes (COUNT(*) AS ?count) WHERE {
# Query OpenStreetMap for running tracks
?track osmt:leisure "track";
# Get the number of lanes as a string
osmt:lanes ?lanesString.
# Cast the number of lanes to an integer; this variable is unbound for any lane count that isn’t an integer
BIND(xsd:integer(?lanesString) AS ?lanes)
# Only include lane counts that are integers
FILTER BOUND(?lanes)
}
GROUP BY ?lanes
ORDER BY ?lanes
Antipode of a city
Displays a map of the city closest to the antipode of Perth – that is, halfway around the world from Perth.
This query demonstrates Sophox's support for well-known text literals and a basic subset of GeoSPARQL functions.
#defaultView:Map
SELECT ?city ?cityLabel ?coordinates ?distanceLabel WHERE {
# Perth
BIND('Point(115.8604796 -31.9527121)'^^geo:wktLiteral AS ?perth)
# Query OpenStreetMap for cities and their coordinates
?city osmt:place "city";
osmm:loc ?coordinates.
# Synthesize the antipode (the point halfway around the world)
BIND(geof:longitude(?perth) AS ?perthLongitude)
BIND(geof:latitude(?perth) AS ?perthLatitude)
BIND(CONCAT('Point(',
xsd:string(-?perthLongitude / ABS(?perthLongitude) * (180 - ABS(?perthLongitude))), ' ',
xsd:string(-?perthLatitude), ')') AS ?antipode)
# Calculate the city’s distance from the antipode
BIND(geof:distance(?antipode, ?coordinates) AS ?distance)
BIND(CONCAT(xsd:string(ROUND(?distance)), ' km') AS ?distanceLabel)
# If the city is more than a few hundred kilometers from the antipode, it isn’t really an antipode
FILTER(?distance < 300)
# Get the city’s name
OPTIONAL { ?city osmt:name:en ?name_en. }
OPTIONAL { ?city osmt:name ?name. }
OPTIONAL { ?city osmt:int_name ?int_name. }
BIND(COALESCE(?name_en, ?name, ?int_name) AS ?cityLabel)
}
ORDER BY ASC(?distance)
LIMIT 1
Cities in a metropolitan area
Displays a map of cities and towns in the San Jose–San Francisco–Oakland combined statistical area. In the United States, OpenStreetMap does not represent metropolitan statistical areas (MSAs), micropolitan statistical areas (μSAs), and combined statistical areas (CSAs) as boundary relations, but Wikidata makes it possible to query based on the counties that are included in a statistical area.
This query demonstrates a basic federated query, as well as property path syntax in SPARQL.
#defaultView:Map
SELECT DISTINCT ?osm ?osmLabel ?coordinates WHERE {
# Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
hint:Query hint:optimizer "None" .
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# Cities and towns
VALUES ?cities { wd:Q515 wd:Q3957 }
?wd wdt:P31/wdt:P279* ?cities;
# That lie within parts of the San Jose–San Francisco–Oakland combined statistical area
wdt:P131*/wdt:P361+ wd:Q7413927.
}
# Query OpenStreetMap for these places
?osm osmt:wikidata ?wd;
# And their coordinates
osmm:loc ?coordinates.
# Get the place’s name
OPTIONAL {
?osm osmt:name ?osmLabel
}
}
Network value split by colons
Displays the colon-delimited components of a hierarchical network=* value, specifically network=US:OH:SAN:Fremont.
SPARQL lacks a built-in way to split a string, but this query demonstrates a workaround based on the solution in this gist.
SELECT ?n ?regex ?component WHERE {
# Start with one deeply hierarchical network=* value
BIND("US:OH:SAN:Fremont" AS ?network)
# Split the value at each colon up to 10 times
BIND(":" AS ?separator)
VALUES ?n { 0 1 2 3 4 5 6 7 8 9 }
# For the nth split, build a regular expression that isolates the (n+1)th occurrence from the n previous occurrences and any subsequent text
# Also match the full value if there are no more occurrences of the separator
BIND(CONCAT("^(?:[^", ?separator, "]*", ?separator, "){", STR(?n), "}([^", ?separator, "]*).*$|.*") AS ?regex)
# Replace the full value with the (n+1)th occurrence
BIND(REPLACE(?network, ?regex, "$1") AS ?component)
# Omit empty components, including components that are empty because there are fewer than 10 components
FILTER(STRLEN(?component) > 0)
}
ORDER BY ?n
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. [2] There have been proposals to enable searching by OLCs at openstreetmap.org. [3]
This query is rather verbose because SPARQL lacks an exponent operator or function. It is only possible to form base-10 exponents using scientific notation.
SELECT ?school ?address1 ?address2 ?olc WHERE {
# Query OpenStreetMap for schools
?school osmt:amenity "school".
# Filter to roughly the territory of the Navajo Nation
SERVICE wikibase:box {
?school osmm:loc ?coordinates .
bd:serviceParam wikibase:cornerSouthWest 'Point(-111.4601789 35.277247)'^^geo:wktLiteral.
bd:serviceParam wikibase:cornerNorthEast 'Point(-108.3078547 37.3911089)'^^geo:wktLiteral.
}
# Get the first address line
OPTIONAL {
?school osmt:addr:housenumber ?houseNumber;
osmt:addr:street ?street.
BIND(CONCAT(?houseNumber, " ", ?street) AS ?address1)
}
# Get the second address line
OPTIONAL {
?school osmt:addr:city ?city.
OPTIONAL {
?school osmt:addr:state ?state.
}
OPTIONAL {
?school osmt:addr:postcode ?zipCode.
}
BIND(CONCAT(?city, ", ", COALESCE(?state, ""), " ", COALESCE(?zipCode, "")) AS ?address2)
}
# Get the WGS84 coordinates relative to the South Pole at 90°S and the antimeridian at 180°W
BIND(geof:latitude(?coordinates) + 90 AS ?latitude)
BIND(geof:longitude(?coordinates) + 180 AS ?longitude)
# Get the 20°×20° block as a pair of base-20 digits
BIND(xsd:integer(FLOOR(?latitude / 20)) AS ?base20Latitude)
BIND(xsd:integer(FLOOR(?longitude / 20)) AS ?base20Longitude)
# Map the base-20 digits to OLC code digits
BIND("23456789CFGHJMPQRVWX" AS ?digits)
BIND(SUBSTR(?digits, ?base20Latitude + 1, 1) AS ?latitudeDigit)
BIND(SUBSTR(?digits, ?base20Longitude + 1, 1) AS ?longitudeDigit)
BIND(CONCAT(?latitudeDigit, ?longitudeDigit) AS ?block)
# Subdivide the block and repeat the process for the next pair of base-20 digits
BIND(xsd:integer(FLOOR(?latitude - (?base20Latitude * 20))) AS ?base20Latitude2)
BIND(xsd:integer(FLOOR(?longitude - (?base20Longitude * 20))) AS ?base20Longitude2)
# Map the base-20 digits to OLC code digits
BIND(SUBSTR(?digits, ?base20Latitude2 + 1, 1) AS ?latitudeDigit2)
BIND(SUBSTR(?digits, ?base20Longitude2 + 1, 1) AS ?longitudeDigit2)
BIND(CONCAT(?latitudeDigit2, ?longitudeDigit2) AS ?block2)
# Subdivide the block and repeat the process for the next pair of base-20 digits
BIND(xsd:integer(FLOOR((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20)) AS ?base20Latitude3)
BIND(xsd:integer(FLOOR((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20)) AS ?base20Longitude3)
# Map the base-20 digits to OLC code digits
BIND(SUBSTR(?digits, ?base20Latitude3 + 1, 1) AS ?latitudeDigit3)
BIND(SUBSTR(?digits, ?base20Longitude3 + 1, 1) AS ?longitudeDigit3)
BIND(CONCAT(?latitudeDigit3, ?longitudeDigit3) AS ?block3)
# Subdivide the block and repeat the process for the next pair of base-20 digits
BIND(xsd:integer(FLOOR(((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20 - ?base20Latitude3) * 20)) AS ?base20Latitude4)
BIND(xsd:integer(FLOOR(((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20 - ?base20Longitude3) * 20)) AS ?base20Longitude4)
# Map the base-20 digits to OLC code digits
BIND(SUBSTR(?digits, ?base20Latitude4 + 1, 1) AS ?latitudeDigit4)
BIND(SUBSTR(?digits, ?base20Longitude4 + 1, 1) AS ?longitudeDigit4)
BIND(CONCAT(?latitudeDigit4, ?longitudeDigit4) AS ?block4)
# Subdivide the block and repeat the process for the next pair of base-20 digits
BIND(xsd:integer(FLOOR((((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20 - ?base20Latitude3) * 20 - ?base20Latitude4) * 20)) AS ?base20Latitude5)
BIND(xsd:integer(FLOOR((((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20 - ?base20Longitude3) * 20 - ?base20Longitude4) * 20)) AS ?base20Longitude5)
# Map the base-20 digits to OLC code digits
BIND(SUBSTR(?digits, ?base20Latitude5 + 1, 1) AS ?latitudeDigit5)
BIND(SUBSTR(?digits, ?base20Longitude5 + 1, 1) AS ?longitudeDigit5)
BIND(CONCAT(?latitudeDigit5, ?longitudeDigit5) AS ?block5)
# Subdivide the block into 20 cells and get a pair of base-20 digits
BIND(xsd:integer(FLOOR(((((?latitude - ?base20Latitude * 20 - ?base20Latitude2) * 20 - ?base20Latitude3) * 20 - ?base20Latitude4) * 20 - ?base20Latitude5) * 5)) AS ?base20Latitude6)
BIND(xsd:integer(FLOOR(((((?longitude - ?base20Longitude * 20 - ?base20Longitude2) * 20 - ?base20Longitude3) * 20 - ?base20Longitude4) * 20 - ?base20Longitude5) * 4)) AS ?base20Longitude6)
# Get the cell’s OLC code digit
BIND(?base20Latitude6 * 4 + 1 + ?base20Longitude6 / 4 AS ?cell)
BIND(SUBSTR(?digits, ?cell, 1) AS ?cellDigit)
# Combine all the digits, adding a + after 8 digits
BIND(CONCAT(?block, ?block2, ?block3, ?block4, "+", ?block5, ?cellDigit) AS ?olc)
}
Boundaries
Show geoshapes (geometries) of all political subdivisions of a country
#defaultView:MapRegions
SELECT
?id
(SAMPLE(?label_en) as ?label_en)
(SAMPLE(?label_fr) as ?label_fr)
(SAMPLE(?iso_3166_2) as ?iso_3166_2)
(SAMPLE(?flag) as ?flag)
WHERE {
# List of regions, whose sub-regions we want
VALUES ?entity { wd:Q16 }
SERVICE <https://query.wikidata.org/sparql> {
# P150 = "contains administrative territorial entity"
# but must not have a P582 (end date) qualifier
?entity p:P150 ?statement .
?statement ps:P150 ?id .
FILTER NOT EXISTS { ?statement pq:P582 ?x }
# Get labels, ISO code, and flag image
# for each sub-region, if available
OPTIONAL { ?id rdfs:label ?label_en . FILTER(LANG(?label_en) = "en") }
OPTIONAL { ?id rdfs:label ?label_fr . FILTER(LANG(?label_fr) = "fr") }
OPTIONAL { ?id wdt:P300 ?iso_3166_2 }
OPTIONAL { ?id wdt:P41 ?flag }
}
}
# remove possible ID duplicates
GROUP BY ?id
Recursive subareas of a boundary relation
Displays a table of subareas of Indiana, their subareas, and so on recursively. osmm:has is one of the few uses of property paths within pure OpenStreetMap data.
SELECT ?subarea ?adminLevel ?name WHERE {
# Query OpenStreetMap for members of the Indiana boundary relation, their members, and so on
osmrel:161816 osmm:has+ ?subarea.
# The subarea must have the role “subarea” in some relation (not necessarily the Indiana relation, but potentially an intermediate relation)
?area ?subarea "subarea".
# Get the subarea’s name and administrative level
?subarea osmt:name ?name;
osmt:admin_level ?adminLevel.
}
ORDER BY ?adminLevel ?name
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
Creative Commons–licensed 3D models of structures
Displays a gallery of 3D models of buildings and other structures mapped in OpenStreetMap that are available under Creative Commons licenses at Wikimedia Commons. This query demonstrates querying the MediaWiki API Query Service for categories that contain specific pages.
#defaultView:ImageGrid
SELECT DISTINCT ?osm ?model ?license WHERE {
# Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
hint:Query hint:optimizer "None".
# Query the Wikidata Query Service for
SERVICE <https://query.wikidata.org/sparql> {
# Models of things
?wd wdt:P4896 ?model.
}
# Query OpenStreetMap for features corresponding to these things
# This greatly narrows the set of results, making the subsequent MediaWiki API call much faster
?osm osmt:wikidata ?wd.
# Query the Wikidata Query Service again for
SERVICE <https://query.wikidata.org/sparql> {
# The same model of the same thing
?wd wdt:P4896 ?model.
# Convert the file path URL into a file description page title
BIND(CONCAT("File:", wikibase:decodeUri(STRAFTER(STR(?model), "/Special:FilePath/"))) AS ?title)
# Query the MediaWiki API Query Service for
SERVICE wikibase:mwapi {
# Categories that contain these pages
bd:serviceParam wikibase:api "Categories";
wikibase:endpoint "commons.wikimedia.org";
wikibase:limit 50;
mwapi:titles ?title.
# Output the page title and category
?title wikibase:apiOutput mwapi:title.
?category wikibase:apiOutput mwapi:category.
}
# Only include the model if it has been categorized with a Creative Commons license
FILTER STRSTARTS(?category, "Category:CC-")
# Extract a “license” name out of the category page title
BIND(STRAFTER(?category, "Category:") AS ?license)
}
}
Things that mimic chimneys
Sophox provides direct access to key usage statistics from taginfo, but not tag combinations. Moreover, taginfo does not track combinations of relatively rare keys and tags. The following query returns what taginfo's Combinations tab would report for mimics=chimney if it were a much more common tag, so you can find out what structures typically mimic chimneys and what else we know about these structures. It requires running two subqueries about tags and unioning the results to two subqueries about keys.
SELECT ?key ?value ?count (100 * ?count / ?count_all AS ?percent) WHERE {
{
# Count occurrences of tags on elements that also have the input tag
{
SELECT ?key ?value (COUNT(*) AS ?count) WHERE {
?element osmt:mimics "chimney";
?key ?value.
}
GROUP BY ?key ?value
}
# Count elements that have the input tag
{
# Only count each element once.
SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
?element osmt:mimics "chimney";
?key ?value.
}
}
} UNION {
# Count occurrences of keys on elements that also have the input tag
{
SELECT ?key (COUNT(*) AS ?count) WHERE {
?element osmt:mimics "chimney";
?key ?value.
}
GROUP BY ?key
}
# Count elements that have the input tag
{
SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
?element osmt:mimics "chimney";
?key ?value.
}
}
}
# Filter out metadata predicates (e.g., rdf:, osmm: prefixes)
FILTER(STRSTARTS(STR(?key), "https://wiki.openstreetmap.org/wiki/"))
}
ORDER BY DESC(?count)
LIMIT 50
Users who maps things that mimic chimneys
The following query returns things that mimic chimneys along with a list of users who most recently edited an element with each tag combination. It uses an aggregate function to avoid having to nest yet another level of subqueries just to group the elements by user.
SELECT ?key ?value ?count (100 * ?count / ?count_all AS ?percent) ?users WHERE {
{
# Count occurrences of tags on elements that also have the input tag
{
# List the users for each tag, separated by semicolons, truncated at 100 characters for readability
SELECT ?key ?value (COUNT(*) AS ?count) (SUBSTR(GROUP_CONCAT(DISTINCT ?user; SEPARATOR="; "), 0, 100) AS ?users) WHERE {
?element osmt:mimics "chimney";
?key ?value;
# Get the user who last modified the element
osmm:user ?user;
# Get the date the element was last modified
osmm:timestamp ?timestamp.
}
GROUP BY ?key ?value
# Order the results from newest to oldest so that the list of users is in that order
ORDER BY DESC(?timestamp)
}
# Count elements that have the input tag
{
SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
?element osmt:mimics "chimney";
?key ?value.
}
}
} UNION {
# Count occurrences of keys on elements that also have the input tag
{
# List the users for each key, separated by semicolons, truncated at 100 characters for readability
SELECT ?key (COUNT(*) AS ?count) (SUBSTR(GROUP_CONCAT(DISTINCT ?user; SEPARATOR="; "), 0, 100) AS ?users) WHERE {
?element osmt:mimics "chimney";
?key ?value;
# Get the user who last modified the element
osmm:user ?user;
# Get the date the element was last modified
osmm:timestamp ?timestamp.
}
GROUP BY ?key
# Order the results from newest to oldest so that the list of users is in that order
ORDER BY DESC(?timestamp)
}
# Count elements that have the input tag
{
SELECT (COUNT(DISTINCT ?element) AS ?count_all) WHERE {
?element osmt:mimics "chimney";
?key ?value.
}
}
}
# Filter out metadata predicates (e.g., rdf:, osmm: prefixes)
FILTER(STRSTARTS(STR(?key), "https://wiki.openstreetmap.org/wiki/"))
}
ORDER BY DESC(?count)
LIMIT 50
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:Q104911496 "military")
(wd:Q83302753 "religious")
(wd:Q74051479 "commercial")
(UNDEF "other")
}
# Get the flag’s image
OPTIONAL {
?flag wdt:P18 ?image;
p:P18 ?imageStatement.
# Excluding statements about former flag designs
FILTER NOT EXISTS {
?imageStatement pq:P582 ?imageEndTime.
}
}
# Get the flag’s name
OPTIONAL {
?flag rdfs:label ?flagLabel.
FILTER(LANG(?flagLabel) = "en")
# Shorten the name to better fit as X axis labels
BIND(REPLACE(REPLACE(?flagLabel, "^[Ff]lag of (?:the )?| flag$", ""), "United States ", "U.S. ") AS ?name)
}
}
}
GROUP BY ?flag
ORDER BY DESC(?count)
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 }
}
Most prevalent cuisines
This query returns the 100 most prevalent cuisines. The cuisine=* key accepts a list of keywords separated by semicolons. osm2rdf does not automatically split cuisine=* by semicolons, but you can split them yourself using a regular expression:
SELECT (SAMPLE(?value) AS ?cuisine) (COUNT(*) AS ?count) WHERE {
?osm osmt:cuisine ?list.
# Isolate every run of non-semicolon characters, deleting the surrounding text. Each regular expression match produces a separate query result before grouping.
BIND(REPLACE(?list, ".*?([^;]+).*", "$1") AS ?value)
}
GROUP BY ?value
ORDER BY DESC(?count)
LIMIT 100
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)
Brands by number of locations and Twitter subscribers
Displays a scatter chart of each brand's locations in OpenStreetMap versus its number of Twitter subscribers.
#defaultView:ScatterChart
SELECT (SUM(?subscribers) AS ?subscribers) (SAMPLE(?count) AS ?count) (SAMPLE(?name) AS ?name) WHERE {
hint:Query hint:optimizer "None" .
{
# Query OpenStreetMap for
SELECT ?wd (COUNT(*) AS ?count) WHERE {
# Commercial brands
?osm osmt:brand:wikidata ?wd.
}
GROUP BY ?wd
# With at least 100 locations
HAVING(?count >= 100)
}
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# The brand’s Twitter user name and number of subscribers
?wd p:P2002 [ps:P2002 ?twitter; pq:P3744 ?subscribers].
# Get the brand name
OPTIONAL {
?wd rdfs:label ?name.
FILTER(LANG(?name) = "en")
}
}
}
GROUP BY ?wd
ORDER BY DESC(?count)
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
Education
Playground maps by frequency
Displays a table of playground maps and their depicted geographies by frequency. Blank political subdivision maps are included in the results, so you can switch to the image grid view to see visual approximations of the maps.
# Playground maps and their depicted geographies by frequency
SELECT (SAMPLE(?place) AS ?place) (SAMPLE(?placeName) AS ?placeName) (SAMPLE(?mapImage) AS ?mapImage) (SAMPLE(?count) AS ?count) WHERE {
# Query OpenStreetMap for playground maps and counts
{
# Limit to maps that are tagged subject:wikidata=*
# subject=* is too ambiguous
SELECT ?place (COUNT(*) AS ?count) {
?map osmt:playground "map";
osmt:subject:wikidata ?place.
}
GROUP BY ?place
}
# Query Wikidata for the depicted geographies
SERVICE <https://query.wikidata.org/sparql> {
?place wdt:P31/wdt:P279* wd:Q17334923.
# Get the geography’s location map (blank political subdivision map)
OPTIONAL {
?place wdt:P1943 ?mapImage.
}
# Get the geography’s name
OPTIONAL {
?place rdfs:label ?placeName.
FILTER(LANG(?placeName) = "en")
}
}
}
GROUP BY ?place
ORDER BY DESC(?count)
Grade distribution of schools that have playground maps
Displays a table of U.S.-style grade levels and the number of schools that offer that grade and a have a playground map.
# Grade distribution of schools that have playground maps
SELECT ?grade (COUNT(*) AS ?count) (SAMPLE(?gradeNumber) AS ?gradeNumber) WHERE {
{
# Query OpenStreetMap for playground maps and grades of nearby schools
SELECT ?map (SAMPLE(?school) AS ?school) (SAMPLE(?schoolGrades) AS ?schoolGrades) (MIN(?distance) AS ?distance) WHERE {
# Playground maps and their coordinates
?map osmt:playground "map";
osmm:loc ?mapCoordinates.
# School-like campuses
VALUES ?schools { "school" "kindergarten" "childcare" }
?school osmt:amenity ?schools.
# Require each school to be within 100 m of a playground map
SERVICE wikibase:around {
?school osmm:loc ?schoolCoordinates.
bd:serviceParam wikibase:center ?mapCoordinates;
wikibase:radius "0.1";
wikibase:distance ?distance.
}
# Get the school’s grades
OPTIONAL {
?school osmt:grades ?schoolGrades.
}
}
GROUP BY ?map
}
# Associate each grade with a numeric grade number for sorting
VALUES (?grade ?gradeNumber) {
("PK" -3) ("TK" -2) ("JK" -1) ("K" 0)
("1" 1) ("2" 2) ("3" 3) ("4" 4)
("5" 5) ("6" 6) ("7" 7) ("8" 8)
("9" 9) ("10" 10) ("11" 11) ("12" 12)
("13" 13) ("SP" 14)
}
# Associate each grade with a regular expression that detects the grade within a range such as “K-8”
VALUES (?grade ?gradeRegex) {
("PK" "PK") ("TK" "TK") ("JK" "JK") ("K" "K")
("1" "1|[PTJ]K-([2-9]|1[0-3])") ("2" "2|([PTJ]K|1)-([3-9]|1[0-3])") ("3" "3|([PTJ]K|[12])-([4-9]|1[0-3])") ("4" "4|([PTJ]K|[1-3])-([5-9]|1[0-3])")
("5" "5|([PTJ]K|[1-4])-([6-9]|1[0-3])") ("6" "6|([PTJ]K||[1-5])-([7-9]|1[0-3])") ("7" "7|([PTJ]K|[1-6])-([89]|1[0-3])") ("8" "8|([PTJ]K|[1-7])-(9|1[0-3])")
("9" "9|([PTJ]K|[1-8])-(1[0-3])") ("10" "10|([PTJ]K|[1-9])-(1[1-3])") ("11" "11|([PTJ]K|[1-9]|10)-(1[23])") ("12" "12|([PTJ]K|[1-9]|1[01])-13")
("13" "13") ("SP" "SP")
}
# Filter by whether the school offers the grade
FILTER REGEX(?schoolGrades, CONCAT("(^|[-,;])(", ?gradeRegex, ")([-,;]|$)"))
}
# Pivot by grade
GROUP BY ?grade
ORDER BY ?gradeNumber
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". }
}
Decommissioned aircraft by manufacturer
Displays an interactive map of decommissioned aircraft (historic=aircraft), color-coded by manufacturer based on manufacturer:wikidata=* and/or aircraft:model:wikidata=*. Use the layer selector to filter the map by manufacturer.
#defaultView:Map
SELECT DISTINCT ?osm ?makeLabel ?modelLabel ?layer ?coordinates WHERE {
# Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
hint:Query hint:optimizer "None" .
# Query OpenStreetMap for decommissioned aircraft
?osm osmt:historic "aircraft".
OPTIONAL {
# Get the make and/or model
?osm (osmt:manufacturer:wikidata|osmt:aircraft:model:wikidata) ?makeModel.
# Query Wikidata for the make/model
SERVICE <https://query.wikidata.org/sparql> {
{
# If it’s a model, get the make
?makeModel wdt:P31 wd:Q76920834;
wdt:P176 ?make.
BIND(?makeModel AS ?model)
# Get the model name
OPTIONAL {
?model rdfs:label ?modelLabel.
FILTER(LANG(?modelLabel) = "en")
}
} UNION {
# A make must be a business to distinguish it from a model
?makeModel wdt:P31/wdt:P279* wd:Q4830453.
BIND(?makeModel AS ?make)
} UNION {
# Or it must be part of an industry (e.g., Wright Brothers)
?makeModel (wdt:P106|wdt:P452) [].
BIND(?makeModel AS ?make)
}
# Get the manufacturer’s name
OPTIONAL {
?make rdfs:label ?makeLabel.
FILTER(LANG(?makeLabel) = "en")
}
}
}
BIND(COALESCE(?makeLabel, "Unknown") AS ?layer)
# Get coordinates to display on a map
?osm osmm:loc ?coordinates.
}
ORDER BY ASC(?makeLabel)
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, "[\\x{10000}-\\x{10FFFF}]")
# Can also be written this way, meaning “not between U+0000 and U+FFFF”
# FILTER REGEX(?name, "[^\\x00-\\uFFFF]")
# Isolate the character
BIND(REPLACE(?name, "^.*([\\x{10000}-\\x{10FFFF}]+).*$", "$1") AS ?character)
}
ORDER BY ?character
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=*. (Tip: use the scatterplot view to see how some of the abbreviations that are behind how emoji flags function try to base themselves on the English name, whereas others use a non-English name or resort to a less intuitive initialism)
SELECT (SAMPLE(?osm) AS ?osm) (SAMPLE(?name) AS ?name) (SAMPLE(?unicode) AS ?name_Zsye) WHERE {
# Prevent optimizer from querying for everything tagged with wikidata=* before querying the service.
hint:Query hint:optimizer "None" .
# Query Wikidata for places that have Unicode representations or whose flags have Unicode representations
SERVICE <https://query.wikidata.org/sparql> {
?wd wdt:P625 ?coordinates.
{
?wd wdt:P487 ?unicode.
} UNION {
?wd wdt:P163 ?flag.
?flag wdt:P487 ?unicode.
}
}
# Query OpenStreetMap for places corresponding to those things
?osm osmt:wikidata ?wd.
# Get the place’s name
OPTIONAL {
?osm osmt:name ?name
}
}
GROUP BY ?wd
ORDER BY ASC(?name_Zsye)
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)
Localizable keys
Displays a table of keys that have language-specific variants (such as :en) that are used at least 1,000 times.
SELECT (SAMPLE(?baseKey) AS ?baseKey) ?baseKeyID (SUM(?count) AS ?count) WHERE {
# Query the OpenStreetMap Wiki for data items about keys
?key osmdt:P16 ?keyID;
# Get the number of features tagged with this key
osmm:count_all ?count.
# Only include keys that contain a 2-letter suffix
# Language codes can also be 3 characters long or include script and region codes
# But assume that any localizable key is localized into a language with an ISO 639-1 alpha-2 code
FILTER REGEX(?keyID, ":[a-z]{2}(?::.+)?$")
# Isolate the base key without the language suffix
BIND(REPLACE(?keyID, "^(.+):[a-z]{2}(:.+)?$", "$1$2") AS ?baseKeyID)
# Omit language:*=*, which indicates whether a language is spoken or taught but which isn’t localized per se
FILTER(?baseKeyID != "language")
# Isolate the language suffix
BIND(REPLACE(?keyID, "^.+:([a-z]{2})(?::.+)?$", "$1") AS ?language)
# Omit two-letter prepositions and common words
FILTER(?language != "id" && ?language != "in" && ?language != "to")
# Query the OpenStreetMap Wiki for the base key
OPTIONAL {
?baseKey osmdt:P16 ?baseKeyID.
}
}
GROUP BY ?baseKeyID
HAVING(?count > 1000)
ORDER BY DESC(?count)
Nature
Threatened species of trees
Displays an interactive map of trees whose species is categorized as threatened on the IUCN Red List.
#defaultView:Map
SELECT ?tree ?taxon ?taxonLabel ?layer ?coordinates WHERE {
# Query OpenStreetMap for trees and their taxa
?tree osmt:natural "tree";
(osmt:genus:wikidata|osmt:species:wikidata|osmt:taxon:wikidata) ?taxon;
osmm:loc ?coordinates.
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# Taxa categorized as threatened on the IUCN Red List
VALUES (?status ?layer) {
(wd:Q278113 "Vulnerable (VU)")
(wd:Q11394 "Endangered (EN)")
(wd:Q219127 "Critically endangered (CR)")
}
?taxon wdt:P141 ?status.
# Get the English common name
OPTIONAL {
?taxon wdt:P1843 ?taxonLabel.
FILTER(LANG(?taxonLabel) = "en")
}
}
}
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. [4]
#defaultView:BubbleChart
SELECT (SAMPLE(?name) AS ?name) (COUNT(DISTINCT ?route) AS ?count) ?rgb WHERE {
# Query OpenStreetMap for public transportation route relations and their colors
VALUES ?types { "route" "route_master" }
VALUES ?routes { "train" "light_rail" "subway" "tram" }
?route osmt:route ?routes;
osmt:type ?types;
osmt:colour ?color;
osmm:type "r".
# Some colors are tagged as hexadecimal RGB triplets
OPTIONAL {
FILTER(STRSTARTS(?color, "#"))
BIND(REPLACE(UCASE(?color), "#", "") AS ?rgb)
}
# Some colors are tagged as CSS color keywords
OPTIONAL {
FILTER(!STRSTARTS(?color, "#"))
BIND(LCASE(?color) AS ?css)
}
# Conflate CSS color keywords with hexadecimal RGB triplets by querying Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
OPTIONAL {
# Colors corresponding to the CSS color keyword
?wd wdt:P31/wdt:P279* wd:Q1075;
wdt:P8112 ?css;
wdt:P465 ?rgb.
}
}
# A bubble needs a name to show up at all
BIND(COALESCE(?css, ?rgb) AS ?name)
}
GROUP BY ?rgb
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
Interstate highway relations
Displays a tree of Interstate highway relations in the United States. The tree groups each route's relations under a superrelation where applicable. The tree decorates each superrelation with a shield (based on either the linked Wikidata item or a URL in symbol=*) and each child relation with the flag of the state that the relation traverses. To make related route relations easier to distinguish, each row also displays states and a cardinal direction if available. Right-click on an item in the tree to visit the corresponding route relation.
#defaultView:Tree
SELECT ?linkedSuperRoute ?shield ?formattedRef ?superStateCode ?superDirection ?linkedRoute ?flag ?stateCode ?direction WHERE {
# Query OpenStreetMap for Interstate route superrelations and their refs
?superRoute osmt:network "US:I";
osmt:ref ?ref;
osmm:type "r".
# It’s a superrelation if it isn’t a member of a super-super route relation
# A freestanding route relation is a superrelation for the purpose of this query
FILTER NOT EXISTS {
?superSuperRoute osmm:has ?superRoute;
osmt:type "route".
}
# Omit special routes like business loops
FILTER NOT EXISTS {
# Most special routes have distinct network tags, but some are tagged with only a modifier
?superRoute osmt:modifier [].
}
# Get the shield from Wikidata
OPTIONAL {
?superRoute osmt:wikidata ?superWikidata.
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# A part of the Interstate Highway System
?superWikidata wdt:P16 wd:Q94247;
# Get its shield or the shield of something it is a part of
wdt:P361?/wdt:P14 ?shield.
}
}
# Get the shield from OpenStreetMap
OPTIONAL {
# Get the symbol, which is supposed to be a description but which commonly contains a Wikimedia Commons SVG image URL
?superRoute osmt:symbol ?symbol.
# Isolate the SVG image name
BIND(wikibase:decodeUri(REPLACE(?symbol, "^https?://(?:upload\\.wikimedia\\.org/wikipedia/commons/(?:thumb/)?./../|commons\\.wikimedia\\.org/wiki/File:)(.+\\.svg)(?:/\\d+px-$1\\.png)?$", "$1")) AS ?shieldName)
# Convert the name to a URL that Sophox will recognize as an image URL
BIND(IRI(CONCAT("http://commons.wikimedia.org/wiki/Special:FilePath/", ENCODE_FOR_URI(?shieldName))) AS ?shield)
}
# Get the state the route is in and cardinal direction, to differentiate between freestanding route relations
OPTIONAL {
?superRoute osmt:is_in:state ?superStateCode.
}
OPTIONAL {
?superRoute osmt:direction ?superDirection.
}
# Also query OpenStreetMap for a route relation that’s a member of the superrelation
OPTIONAL {
?superRoute osmm:has ?route.
?route osmt:network "US:I";
osmt:ref ?ref;
osmm:type "r".
# Get the state the route is in, to differentiate between route relations in the same superrelation
OPTIONAL {
?route osmt:is_in:state ?stateCode.
# Try a little harder
OPTIONAL {
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# The state with the same FIPS 5-2 alpha state code
?state wdt:P5086 ?stateCode;
wdt:P41 ?flag.
}
}
}
# Keep trying to get the state based on the linked Wikidata entity
OPTIONAL {
?route osmt:wikidata ?wikidata.
OPTIONAL {
SERVICE <https://query.wikidata.org/sparql> {
# Same as above, but distinguish between the “Interstate __ in __” entity and the overall “Interstate __” entity
# The latter is likely located in multiple states, so prefer the more specific entity
?wikidata wdt:P361/wdt:P16 wd:Q94247;
wdt:P131+ ?state.
?state wdt:P5086 ?stateCode;
wdt:P41 ?flag.
}
}
}
# Get the cardinal direction, to differentiate between route relations in the same superrelation
OPTIONAL {
?route osmt:direction ?direction.
}
FILTER NOT EXISTS {
?route osmt:modifier [].
}
}
# Format the ref for display
BIND(CONCAT("I-", ?ref) AS ?formattedRef)
# Normalize the ref for consistent sorting
# Isolate the digits, stripping any H prefix (for Hawaii) or alphabetic branch suffix
BIND(REPLACE(?ref, "^H|[A-Z]$", "") AS ?numericRef)
# Pad the digits to a 3-digit number
BIND(CONCAT(SUBSTR("000", 1, 3 - STRLEN(?numericRef)), ?numericRef) AS ?paddedNumericRef)
# Replace the digits part of the ref with a padded copy
BIND(REPLACE(?ref, "^(H?)([0-9]{1,3})([A-Z])?$", CONCAT("$1", ?paddedNumericRef, "$3")) AS ?paddedRef)
# Rearrange the digits so that the parent ref comes first
# For example, I-80 is the parent route of I-680, so sort I-680 as 806
BIND(REPLACE(?paddedRef, "^(H?)([0-9])([0-9][0-9])([A-Z])?$", "$1$3$2$4") AS ?sortedRef)
# Work around https://github.com/Sophox/sophox/issues/21
BIND(IRI(CONCAT(STR(?superRoute), "?entity=", COALESCE(STR(?superWikidata), "/entity/Q0"))) AS ?linkedSuperRoute)
BIND(IRI(CONCAT(STR(?route), "?entity=", COALESCE(STR(?wikidata), "/entity/Q0"))) AS ?linkedRoute)
}
ORDER BY ?sortedRef ?stateCode ?direction
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
Approved but unused keys
SELECT * WHERE {
# Query the OpenStreetMap Wiki for data items whose status is approved
?osmd osmdt:P6 osmd:Q15;
# Get the key ID
osmdt:P16 ?key;
# Get the number of features tagged with this key
osmm:count_all ?count.
# Only include unused keys and exclude keys ending in colons (which are actually namespaces)
FILTER(?count = 0 && !STRENDS(?key, ":"))
}
Most deeply nested subkeys
Displays a table of the 50 most deeply nested subkeys that are in wide enough use to have data items about them.
SELECT * WHERE {
# Query the OpenStreetMap Wiki for data items about keys
?osmd osmdt:P16 ?key.
# Remove non-colons and count the colons that remain
# The depth is one more than the number of colons
BIND(STRLEN(REPLACE(?key, "[^:]", "")) + 1 AS ?depth)
}
ORDER BY DESC(?depth)
LIMIT 50
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 [5][6], this query obtains them from Wikidata's lexicographical database, a nascent structured data alternative to Wiktionary. For example, this lexeme indicates that "stravenue" is abbreviated "STRA". Wikidata makes it straightforward to expand the query to include nonstandard abbreviations or adapt it to other languages.
SELECT ?osm ?houseNumber ?street ?postcode WHERE {
# Query Wikidata before OpenStreetMap; otherwise, every address result gets duplicated for every abbreviation
hint:Query hint:optimizer "None".
# Query Wikidata for
SERVICE <https://query.wikidata.org/sparql> {
# A vertical bar–delimited string that looks like “ST|DR|AVE|BLVD”
SELECT (GROUP_CONCAT(?abbreviation; SEPARATOR="|") AS ?abbreviationRegex) WHERE {
# Lexemes in English and two of their forms, a spelled-out form and an abbreviated form
?lexeme dct:language wd:Q1860;
ontolex:lexicalForm ?wordForm;
ontolex:lexicalForm ?abbreviatedForm.
# Get the spelled-out form’s grammatical number and written representation
?wordForm wikibase:grammaticalFeature ?number;
ontolex:representation ?word.
# Make sure the spelled-out form is not a standard USPS abbreviation
FILTER NOT EXISTS {
?wordForm wikibase:grammaticalFeature wd:Q30619513.
}
# The abbreviated form must be a standard USPS abbreviation with the same grammatical number
?abbreviatedForm wikibase:grammaticalFeature wd:Q30619513;
wikibase:grammaticalFeature ?number;
ontolex:representation ?abbreviation.
# Make sure the abbreviated form is not simultaneously singular and plural, which would produce false positives
# For example, the USPS normalizes both “PIKE” and “PIKES” to “PIKE”
FILTER NOT EXISTS {
?abbreviatedForm wikibase:grammaticalFeature ?otherNumber.
# The form’s two grammatical numbers would be the opposite of each other
?number wdt:P461 ?otherNumber.
}
# Filter out words that the USPS spells out in full but includes in Appendix C1
# For example, the USPS spells out “ROW”
FILTER(UCASE(?word) != ?abbreviation)
}
}
# Query OpenStreetMap for fully qualified addresses in Cincinnati, Ohio
?osm osmt:addr:state "OH";
osmt:addr:city "Cincinnati";
osmt:addr:postcode ?postcode;
osmt:addr:street ?street;
osmt:addr:housenumber ?houseNumber.
# Only include street addresses that contain abbreviations, case insensitively
FILTER REGEX(?street, CONCAT("\\b(?:", ?abbreviationRegex, ")\\b"), "i")
}
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. [7]
#defaultView:Map
SELECT * WHERE {
# Query OpenStreetMap for features last edited by an anonymous user or a user who has named themselves the empty string
?osm osmm:user "";
osmm:loc ?coordinates.
}
Possible self-promotion
Displays a table of features with the same names as the users who last edited them. Often, a search engine optimization (SEO) practitioner creates a single-purpose account just to map the business that hired them. That can be legitimate; however, such edits often need scrutiny because SEO practitioners do not always take the time to learn how to map well. In other cases, a name match is simply a coincidence. [8]
SELECT * WHERE {
# Query OpenStreetMap for features with the same name as the user who last edited it
?osm osmm:user ?user;
osmt:name ?user.
}
LIMIT 50
Roads renamed to businesses
Displays a table of roads that have been renamed and given a description by a user who also goes by the new name. Some search engine optimization (SEO) practitioners create throwaway accounts based on the business's name, then rename the road instead of adding a business where it belongs. This is a counterproductive practice, but efforts to contact these SEO mappers have been unsuccessful at curbing the practice as of October 2020.
SELECT ?road ?name ?user ?description WHERE {
?road osmt:highway [];
osmt:name ?name;
osmt:description ?description;
osmm:type "w";
osmm:user ?user.
FILTER REGEX(SUBSTR(?user, 1, 4), "\\w")
BIND(REPLACE(LCASE(?name), "\\W+", "") AS ?sanitizedName)
BIND(REPLACE(LCASE(?user), "\\W+", "") AS ?sanitizedUser)
FILTER(SUBSTR(?sanitizedName, 1, 4) = SUBSTR(?sanitizedUser, 1, 4) && STRLEN(?description) > 50 && CONTAINS(?description, " "))
}
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.
#defaultView:Map
SELECT ?osm ?name ?coordinates WHERE {
?osm osmt:name:es ?name;
osmm:loc ?coordinates.
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)
}
IP addresses as websites
Displays a table of the most common IPv4 addresses in the domain names of official websites of features in OpenStreetMap. IP addresses are not necessarily static, so these URLs need to be rechecked frequently for reachability.
SELECT ?ip (COUNT(*) AS ?count) (SAMPLE(?osm) AS ?osm) (SAMPLE(?website) AS ?website) WHERE {
?osm osmt:website ?website.
FILTER REGEX(?website, "^\\w+://(?:[^:]+:[^@]@)?(?:[12]?[0-9]?[0-9]\\.){3}[12]?[0-9]?[0-9](?:[:/]|$)")
BIND(REPLACE(?website, "^\\w+://(?:[^:]+:[^@]@)?((?:[12]?[0-9]?[0-9]\\.){3}[12]?[0-9]?[0-9])(?:[:/]|$).*", "$1") AS ?ip)
}
GROUP BY ?ip
ORDER BY DESC(?count)
URLs containing tracking identifiers
Displays a table of URLs in website=* and other tags that contain tracking identifiers in query parameters (or faux query parameters in URL hashes).
SELECT * WHERE {
?osm (osmt:website|osmt:contact:website|osmt:source:website|osmt:operator:website|osmt:source:url) ?website.
FILTER REGEX(?website, "[?#&](mc_id|utm_\\w+|fbclid|gclid|campaign_ref|gclsrc|dclid|WT.tsrc|wt.tsrc|zanpid|yclid|igshid)=")
}
URLs that don't start with http
Displays a table of URLs in website=* and other tags that don't start with "http" so probably lack a protocol, and won't dispaly correctly in many places that display these tags as links.
SELECT * WHERE {
?osm (osmt:website|osmt:contact:website|osmt:source:website|osmt:operator:website|osmt:source:url|osmt:url|osmt:website:mobile|osmt:heritage:website|osmt:brand:website|osmt:opening_hours:url|osmt:inscription:url) ?website.
FILTER(!STRSTARTS(?website, "http"))
FILTER(?website != "")
} ORDER BY ?website
Malformed ways
Displays a table of malformed ways that consist of fewer than two nodes.
SELECT * WHERE {
?osm osmm:loc:error ?error.
FILTER STRSTARTS(?error, "need at least two points for linestring")
}
Incorrectly named features
Displays a table of features that are explicitly not named the names they are named:
SELECT * WHERE {
?osm osmt:not:name ?name;
osmt:name ?name.
}
Boundaries that are subareas of more local boundaries
Returns a table of administrative boundary relations that incorrectly contain larger administrative boundary relations as subareas.
SELECT ?super ?superLevelNumber ?sub ?subLevelNumber WHERE {
# Query OpenStreetMap for administrative boundary relations and their subareas
?super osmt:boundary "administrative";
osmm:type "r";
osmt:admin_level ?superLevel;
osmm:has ?sub;
?sub "subarea".
# Get the administrative level of the subarea
?sub osmt:admin_level ?subLevel.
# Only include cases where the subarea’s administrative level is lesser (more local) or equal to the superarea’s administrative level
BIND(xsd:integer(?subLevel) AS ?subLevelNumber)
BIND(xsd:integer(?superLevel) AS ?superLevelNumber)
FILTER(?subLevelNumber <= ?superLevelNumber)
}
ORDER BY ?subLevelNumber ?superLevelNumber
Boundary labels that are not nodes
Displays a table of administrative boundary relations whose labels are ways or relations. Much of the purpose of a label member in a boundary relation is to abstract the place represented by the boundary to a point feature, making it easier to label the place or associate the boundary with a specific coordinate. Setting the label to a way or relation somewhat defeats this purpose.
SELECT ?boundary ?label WHERE {
# Query OpenStreetMap for administrative boundary relations and their labels
?boundary osmt:boundary "administrative";
osmm:type "r";
osmm:has ?label;
?label "label".
# The label must be a way or relation but not a node
VALUES ?labelTypes { "w" "r" }
?label osmm:type ?labelTypes.
}
Features tagged with mutually exclusive keys
Displays a table of 500 features tagged with two keys that are incompatible with each other. For example, some features are tagged both noref=* and ref=* because a later mapper neglected to remove noref=* when adding ref=*.
SELECT ?osm ?key1 ?value1 ?key2 ?value2 WHERE {
# Query the OpenStreetMap Wiki for pairs of data items about incompatible keys and their permanent key IDs
?item1 osmdt:P44 ?item2;
osmdt:P16 ?keyID1.
?item2 osmdt:P16 ?keyID2.
# Incompatibility is symmetric, so filter out redundant pairs
FILTER(?keyID1 < ?keyID2)
# Convert key IDs to keys by creating a URL that matches the osmt: prefix
# osmm:key would be more convenient, but not every data item about a key has an osmm:key triple
BIND(IRI(CONCAT("https://wiki.openstreetmap.org/wiki/Key:", ?keyID1)) AS ?key1)
BIND(IRI(CONCAT("https://wiki.openstreetmap.org/wiki/Key:", ?keyID2)) AS ?key2)
# Query OopenStreetMap for features that are tagged with both incompatible keys
?osm ?key1 ?value1;
?key2 ?value2.
}
LIMIT 500
Features with identical phone and fax numbers
Displays a table of features tagged with the same number as both the phone number and fax number. Though it is possible for the same number to answer both voice calls and faxes, it is atypical for business phone lines to be configured this way.
SELECT * WHERE {
?osm (osmt:phone|osmt:contact:phone) ?number;
(osmt:fax|osmt:contact:fax) ?number.
}
Relations without members
Displays a table of 100 relations that have no members. These relations may have had members at some point, but a mapper may have attempted to discard the relation without deleting it. These days, editors like iD automatically delete a relation after removing its last member.
SELECT * WHERE {
?relation osmm:type "r".
FILTER NOT EXISTS {
?relation osmm:has [].
}
}
LIMIT 100
Residential roads with too many lanes (slow)
Displays a table of 50 residential roads that have 10 or more lanes. It is unlikely that any of these roads has so many lanes, but if so, it would probably qualify for a higher road classification. More likely, a mapper accidentally entered a speed limit in the adjacent Lanes field in iD, or JOSM autocompleted a larger number after the mapper tried to enter a smaller one.
SELECT * WHERE {
?osm osmt:highway "residential";
osmt:lanes ?lanes.
FILTER(STRLEN(?lanes) > 1)
}
LIMIT 50
Route relations that conflict with old way refs
Returns an interactive map of every roadway in Indiana that is a member of an Indiana State Road route relation but simultaneously is tagged old_ref=* for that route. It could be the case that, in reality, the route has been realigned or truncated to remove this roadway from the route, but the route relation has not been updated.
#defaultView:Map
SELECT ?way ?oldWayRef ?rel ?relRef ?coordinates WHERE {
# The way says it used to be part of an Indiana State Road
?way osmt:old_ref ?oldWayRef;
osmm:type "w";
osmm:loc ?coordinates.
FILTER(CONTAINS(?oldWayRef, "SR ") || CONTAINS(?oldWayRef, "IN "))
# But that Indiana State Road’s route relation still contains the way
?rel osmt:network "US:IN";
osmt:ref ?relRef;
osmm:has ?way.
BIND(REPLACE(?oldWayRef, ".*(?:SR|IN) (\\d+).*", "$1") AS ?relRef)
}
Roadways with missing or mistagged refs
Displays an interactive map of Interstate highways, U.S. routes, and state routes in Indiana, Michigan, and Ohio whose ref=* lacks the prefix corresponding to the route relation of which the roadway is a member. In some cases, a mapper may have neglected to tag a short way with a ref when mapping a complex intersection or forgotten to update the route relation after rerouting the route relation onto a different road. In other cases, the ref=* is present but doesn't use the expected prefix.
#defaultView:Map
SELECT ?route ?highway ?ref ?coordinates WHERE {
# Associate networks with their standard ref prefixes
VALUES (?network ?refPrefix) {
("US:I" "I")
("US:US" "US")
("US:IN" "SR")
("US:MI" "M")
("US:OH" "SR")
}
# Query OpenStreetMap for routes belonging to these networks
?route osmt:network ?network;
# Must be a numbered route
osmt:ref [];
osmt:route "road";
osmt:type "route";
osmm:type "r";
osmm:has ?highway.
# Omit special routes, which may not be signposted consistently on the ground
FILTER NOT EXISTS {
?route osmt:modifier [].
}
# Query OpenStreetMap for highways along the routes
?highway osmt:highway [];
osmm:loc ?coordinates.
# Omit highways that contain the appropriate ref prefix
FILTER NOT EXISTS {
?highway (osmt:ref|osmt:unsigned_ref) ?ref.
FILTER REGEX(?ref, CONCAT(?refPrefix, "[- ]"))
}
# It’s debatable whether half a roundabout should be tagged with a way ref
# There can’t be a gap in the route relation, so a way ref would be consistent with the route relation
# But the convention is to leave roundabouts unnamed, so a way ref would be inconsistent with name=*
FILTER NOT EXISTS {
?highway osmt:junction "roundabout".
}
}
Upside-down buildings
Returns a table of every building or building part that is tagged with a height=* that is smaller than the min_height=*. A common mistake when mapping elevated structures is to set height=* to the effective height, subtracting the bottom from the top. In fact, height=* is supposed to be the height at the top compared to ground level, irrespective of min_height=*.
SELECT ?building ?bottomMeters ?topMeters ?effectiveHeight WHERE {
# Query OpenStreetMap for buildings and building parts and the heights of their tops and bottoms
?building osmt:height ?top;
osmt:min_height ?bottom;
(osmt:building|osmt:building:part) [].
# Convert the top height to meters
# Cast the height to compare it numerically
BIND(IF(REGEX(?top, "^[0-9.]+'[0-9.]+\"$"), (xsd:float(STRBEFORE(?top, "'")) * 12 + xsd:float(STRBEFORE(STRAFTER(?top, "'"), "\""))) * 0.0254,
IF(REGEX(?top, "^[0-9.]+'$"), xsd:float(STRBEFORE(?top, "'")) * 0.3048,
xsd:float(?top))) AS ?topMeters)
# Convert the bottom height to meters
# Cast the height to compare it numerically
BIND(IF(REGEX(?bottom, "^[0-9.]+'[0-9.]+\"$"), (xsd:float(STRBEFORE(?bottom, "'")) * 12 + xsd:float(STRBEFORE(STRAFTER(?bottom, "'"), "\""))) * 0.0254,
IF(REGEX(?bottom, "^[0-9.]+'$"), xsd:float(STRBEFORE(?bottom, "'")) * 0.3048,
xsd:float(?bottom))) AS ?bottomMeters)
# Only include a building if its top is lower than its bottom
FILTER(?topMeters < ?bottomMeters)
# Calculate the effective height for sorting
BIND(?topMeters - ?bottomMeters AS ?effectiveHeight)
}
ORDER BY ASC(?effectiveHeight)
oneways with lanes in both directions
# works with defaultView:Map
select distinct ?way ?coordinates where {
{ #### any oneway that has lanes:forward and lanes:backward and neither is 0
values ?oneway { # valid values of oneway
"yes"
"1"
"-1"
"reverse"
"alternating"
"alternate"
"reversible"
}
?way osmt:oneway ?oneway;
osmt:lanes:forward ?forward;
osmt:lanes:backward ?backward;
osmm:loc ?coordinates.
filter(?forward != "0" && ?backward != "0") # neither is 0
} union { # any oneway=[yes|1|reverse|-1] that has more lanes than lanes:[forward|backward]
values (?oneway ?direction) { # mapping of oneway values to :forward and :backward
("yes" osmt:lanes:forward)
("reverse" osmt:lanes:backward)
("1" osmt:lanes:forward)
("-1" osmt:lanes:backward)
}
?way osmt:oneway ?oneway;
osmt:lanes ?total;
?direction ?alsoTotal;
osmm:loc ?coordinates.
filter(?total != ?alsoTotal)
}
}
Pairs of turn restrictions that prohibit a different maneuver over the same geometry
Returns a table of 500 pairs of turn restrictions that indicate two different turn restrictions over the same geometry. For example, it should not be possible for a maneuver from the same roadway, via the same intersection, to the same roadway to be simultaneously a left turn and a right turn.
SELECT * WHERE {
?relation1 osmt:restriction ?restriction1;
osmm:type "r";
osmm:has ?from;
?from "from";
osmm:has ?via;
?via "via";
osmm:has ?to;
?to "to".
?relation2 osmt:restriction ?restriction2;
osmm:type "r";
osmm:has ?from;
?from "from";
osmm:has ?via;
?via "via";
osmm:has ?to;
?to "to".
FILTER(?restriction1 != ?restriction2)
}
LIMIT 500
Redundant or illegal U-turn restrictions
Displays an interactive map of malformed, U-turn-related restriction relations. Most are no U-turn restrictions that prohibit U-turning from a one-way street onto the same street, which would be implied by the oneway=yes tag. Others supposedly require the driver to make this illegal and unsafe maneuver. This error usually occurs when dividing a road into a dual carriageway but neglecting to replace the turn restriction's from or to member with the new parallel one-way.
#defaultView:Map
SELECT * WHERE {
# Query OpenStreetMap for no U-turn and U-turn only restrictions
VALUES (?restrictions ?layer) { ("no_u_turn" "No U-turn") ("only_u_turn" "U-turn only") }
?restriction osmt:restriction ?restrictions;
# Where the maneuver goes from a way to the same way
osmm:has ?way;
?way "from";
osmm:has ?way;
?way "to".
# And the way is a one-way
?way osmt:oneway "yes";
osmm:loc ?coordinates.
}
Unclosed areas
Displays an interactive map of ways that are explicitly tagged as areas using area=yes but are not closed ways.
#defaultView:Map
SELECT * WHERE {
?area osmt:area "yes";
osmm:isClosed false;
osmm:loc ?coordinates.
}
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)
Overwritten GNIS post office names
Displays an interactive map of United States Post Office locations that were imported from the Geographic Names Information System (GNIS), then retagged according to the Name Suggestion Index, overwriting the branch name with a generic name. Select a result, follow the link to the GNIS feature page, and set name=* back to the name listed on that page. If the page also lists an address, also tag the post office with that address.
#defaultView:Map
SELECT * WHERE {
?osm osmt:brand:wikidata wd:Q668687;
osmt:name "United States Post Office";
osmt:gnis:feature_id ?featureID;
osmm:loc ?coordinates.
BIND(IRI(CONCAT("https://geonames.usgs.gov/apex/f?p=GNISPQ:3:::NO::P3_FID:", ?featureID)) AS ?gnis)
}
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)
}