SPARQL examples/Broken Queries

From OpenStreetMap Wiki
Jump to: navigation, search

These queries need some fixing - using Wikidata federated service.

OSM objects linking to Wikipedia disambiguation pages

#defaultView:Map
SELECT ?osmId ?wdLabel ?wd ?wpTag ?loc WHERE {
  # Limit to subjects that have a tag called "wikidata"
  ?osmId osmt:wikidata ?wd ;
         osmm:loc ?loc .

  # Include Wikipedia tag if it exists
  OPTIONAL { ?osmId osmt:wikipedia ?wpTag . }
  # Pick the first available language for the wikidata entry (creates ?wdLabel value)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" }

  # ?wd must be an "instance of" a disambiguation page, or an instance
  # of some type, which itself is a (sub-)*subclass of a disambig page.
  ?wd wdt:P31/wdt:P279* wd:Q4167410 .

  # Optionally, find pl:* wikipedia tags (point to Polish wiki)
  # For performance, remove the "OPTIONAL {" and "}" part above
  #  FILTER( STRSTARTS(STR(?wpTag), 'https://pl.wikipedia')) .

  # Or, instead, only show Wikidata items that have a Polish WP article
  # You may also want to add ?article to the list of fields returns by SELECT statement
  #  ?article schema:about ?wd .
  #  ?article schema:isPartOf <https://pl.wikipedia.org/>.

  # Optionally, limit to just ways (can be 'n', 'r', 'w')
  # ?osmId osmm:type 'w' .

  # Optionally, restrict OSM objects to those that have a specific tag (and value)
  #  ?osmId osmt:place 'city' .        # exact string matching
  #  ?osmId osmt:name:en ?nameen .     # unless filtered, matches all objects with this tag
  #  FILTER( regex(?nameen, "A.b") )   # filter name:en to match a regex. Not very efficient

  # Limit locations to a given bounding box (USA in this case)
  # For some reason, wikibase:box is timing out - tinyurl.com/yc2kd4xs
  # ?osmId osmm:loc ?loc .
  # BIND( geof:longitude(?loc) as ?longitude )
  # BIND( geof:latitude(?loc) as ?latitude )
  # FILTER( ?longitude > -126.2 && ?longitude < -64 && ?latitude > 25 && ?latitude < 49.6)
}

Run it (edit query)

OSM objects linking to Wikipedia "list" pages

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

  # Limit to subjects that have a tag called "wikidata"
  ?osmId osmt:wikidata ?wd .

  # Optionally, restrict OSM objects to those that have a specific tag (and value)
  # ?osmId osmt:place 'city' .        # exact string matching
  # ?osmId osmt:name:en ?nameen .     # unless filtered, matches all objects with this tag
  # FILTER( regex(?nameen, "A.b") )    # not very efficient

  # ?wd must be "instance of" list page, or instance of an item which is a subclass(es) of it.
  ?wd wdt:P31/wdt:P279* wd:Q13406463 .

  # Pick the first available language for the wikidata entry (creates ?wdLabel value)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,it,pl,ru,es,sv,nl" . }
}

Run it (edit query)

Find places located too far from Wikidata's

This query shows OSM objects, whose location is more than 50km from where corresponding Wikidata item is located. The query orders by distance, and only shows top 50 results that are furthest away. See also distance function.

SELECT ?osmId ?wd ?wdLabel ?dist WHERE {
  # ?osmId osmm:type 'n' .       # Optionally filter by the type of OSM object
  ?osmId osmm:loc ?osmLoc .    # With location value
  ?osmId osmt:wikidata ?wd .   # With "wikidata" tag
  ?wd wdt:P625 ?wdLoc .        # Corresponding Wikidata must also have location
  BIND(geof:distance(?wdLoc, ?osmLoc) as ?dist)   # Calculate distance between OSM's and WD's
  FILTER(?dist > 50)  # Filter by minimum distance. Some large objects might be correct with even greater distance

  # For the Wikidata entry, get its label in current user's language, or in the first available
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" . }
}
ORDER BY DESC(?dist)
LIMIT 50

Run it (edit query)

Objects linking to place of business with "wikidata" tag instead of "operator:wikidata" tag

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

  # Limit to subjects that have a tag called "wikidata"
  ?osmId osmt:wikidata ?wd .

  # Filter out entries that have identical wikidata and operator:wikidata
  # In theory there should only be one WP tag
  FILTER NOT EXISTS { ?osmId osmt:operator:wikidata ?wd . }

  # Include Wikipedia tag if it exists
  OPTIONAL { ?osmId osmt:wikipedia ?wpTag . }

  # Optionally, find pl:* wikipedia tags (point to Polish wiki)
  # For performance, remove the "OPTIONAL {" and "}" part above
  # FILTER( STRSTARTS(STR(?wpTag), 'https://pl.wikipedia')) .

  # Or, instead, only show Wikidata items that have a Polish WP article
  # You may also want to add ?article to the list of fields returns by SELECT statement
  #  ?article schema:about ?wd .
  #  ?article schema:isPartOf <https://pl.wikipedia.org/>.

  # Optionally, restrict OSM objects to those that have a specific tag (and value)
  #  ?osmId osmt:place 'city' .        # exact string matching
  #  ?osmId osmt:name:en ?nameen .     # unless filtered, matches all objects with this tag
  #  FILTER( regex(?nameen, "A.b") )    # filter name:en to match a regex. Not very efficient

  # ?wd must be "instance of" an enterprise, or instance of an item which is a subclass(es) of it.
  ?wd wdt:P31/wdt:P279* wd:Q4830453 .

  # Pick the first available language for the wikidata entry (creates ?wdLabel value)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" . }
}
LIMIT 50

Run it (edit query)

Show the top most common OSM objects that represent places of business

Same as the above, but counts unique Wikidata IDs, and shows the most common ones

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

  # Limit to subjects that have a tag called "wikidata"
  ?osmId osmt:wikidata ?wd .

  # Filter out entries that have identical wikidata and operator:wikidata
  # In theory there should only be one WP tag
  FILTER NOT EXISTS { ?osmId osmt:operator:wikidata ?wd . }

  # Include Wikipedia tag if it exists
  OPTIONAL { ?osmId osmt:wikipedia ?wpTag . }

  # Optionally, find pl:* wikipedia tags (point to Polish wiki)
  # For performance, remove the "OPTIONAL {" and "}" part above
  # FILTER( STRSTARTS(STR(?wpTag), 'https://pl.wikipedia')) .

  # Or, instead, only show Wikidata items that have a Polish WP article
  # You may also want to add ?article to the list of fields returns by SELECT statement
  #  ?article schema:about ?wd .
  #  ?article schema:isPartOf <https://pl.wikipedia.org/>.

  # Optionally, restrict OSM objects to those that have a specific tag (and value)
  #  ?osmId osmt:place 'city' .        # exact string matching
  #  ?osmId osmt:name:en ?nameen .     # unless filtered, matches all objects with this tag
  #  FILTER( regex(?nameen, "A.b") )    # filter name:en to match a regex. Not very efficient

  # ?wd must be "instance of" an enterprise, or instance of an item which is a subclass(es) of it.
  ?wd wdt:P31/wdt:P279* wd:Q4830453 .

  # Pick the first available language for the wikidata entry (creates ?wdLabel value)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" . }
}
group by ?wd
ORDER BY DESC(?count)
LIMIT 50

Run it (edit query)


Find Irish places with name != name:ga or name:en

#defaultView:Map
SELECT ?osmid ?name ?nameen ?namega ?loc {
  # subjects must have all 3 tags: name, name:en, name:ga
  ?osmid osmt:name ?name ;
         osmt:name:en ?nameen ;
         osmt:name:ga ?namega .

  # "name" is not the same as "name:ga" or "name:en"
  FILTER (?name != ?nameen && ?name != ?namega)

  # Limit to those that are within 500km around Ireland "center"
  # The center is taken from Wikidata:  Ireland (Q27)  location (P625)
  wd:Q27 wdt:P625 ?irelandCenter .
  SERVICE wikibase:around {
    ?osmid osmm:loc ?loc .
    bd:serviceParam wikibase:center ?irelandCenter .
    bd:serviceParam wikibase:radius "500" .
  }
} LIMIT 100

Run it (edit query)

Find all US states, and show their flags

#defaultView:Map
SELECT * WHERE {

  # This sub-query gets all top-level subdivisions of a given country

{SELECT REDUCED ?id ?idLabel
	   (SAMPLE(?flagImg) as ?flagImg)
	   (SAMPLE(?page) as ?page)
WHERE {
  # Configurable parameters:
  # ?country is a country
  BIND(wd:Q30 as ?country)
  # ?types is administrative subdivisions we seek for this country
  VALUES ?types { wd:Q34876 wd:Q7275 }
  # No serviceable parts beyond this point
  # Located in the administrative territorial entity of the whole country, but not sub-entity
  ?id wdt:P131 ?country .
  # We want it to be of this type in a country, and have a capital
  FILTER EXISTS { ?id (wdt:P31/wdt:P279*) ?types ;
                         wdt:P17 ?country . }
  # Excluding the country itself
  FILTER(?id != ?country)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?id wdt:P41 ?flagImg }
  OPTIONAL {
      ?page schema:about ?id .
      ?page schema:isPartOf <https://en.wikipedia.org/> .
  }

} GROUP BY ?id ?idLabel}


  # For each found id, find osm object that links to it, and find its location
  ?osmid osmt:wikidata ?id .
  ?osmid osmm:loc ?loc .
}

Run it (edit query)


Show a map of all schools, universities, and kindergartens near a place

#defaultView:Map
# "layer" keyword breaks all results into multiple groups
# Use the layers button in the upper right corner of the map to filter
SELECT ?marketLoc ?marketName (?amenity as ?layer) ?osmid WHERE {

  # We are only interested in these types of amenities
  VALUES ?amenity { "kindergarten" "school" "university" "college" }

  # Find anything with tag "amenity", and that has a name and location
  ?osmid osmt:amenity ?amenity ;
         osmt:name ?marketName ;
         osmm:loc ?marketLoc .

  # Get the center of Jersey City from Wikidata
  wd:Q26339 wdt:P625 ?myLoc .

  # Calculate the distance,
  # and filter to just those within 5km
  BIND(geof:distance(?myLoc, ?marketLoc) as ?dist)
  FILTER(?dist < 5)
}

Run it (edit query)

Find "Featured" wiki articles with location, but without OSM connection

SELECT ?sitelink ?wdLabel WHERE {

  # Wikidata item must have a location within the given circle
  # Use the location value from WD Poland entry (Q36) for the center
  # Note that if bounding box is available, it might be faster
  wd:Q36 wdt:P625 ?center .
  SERVICE wikibase:around {
    ?wd wdt:P625 ?loc .
    bd:serviceParam wikibase:center ?center .
    bd:serviceParam wikibase:radius "300" . #km
  }
  
  # There must not be an OSM object with this wikidata id
  FILTER NOT EXISTS { ?osm1 osmt:wikidata ?wd . }

  # There must not be an OSM object with this wikipedia link
  FILTER NOT EXISTS { ?osm2 osmt:wikipedia ?sitelink . }

  # Sitelink must be present in this wiki, with the given badges
  # A much slower query can do multiple badge types. Uncomment this:
  #  VALUES ?badges { wd:Q17437796 wd:Q17437798 }
  # And replace  wd:Q17437796  with  ?badges
  ?sitelink schema:isPartOf <https://pl.wikipedia.org/> ;
            schema:about ?wd ;
            wikibase:badge wd:Q17437796 .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,pl" } .

} LIMIT 10

Run it (edit query)

Find all wikidata items near the specific osm object

#defaultView:Map
SELECT ?loc ?wd ?wdLabel ?distance ?enSite ?esSite ?deSite ?frSite ?ruSite WHERE {
  
  # for convenience, define constant parameter of the central object
  BIND (osmway:157474680 as ?osmId)
  BIND (10 as ?radius)  # in km
  
  # get object's location to use as a center of the search
  ?osmId osmm:loc ?center .

  # find all wikidata items in the close proximity
  SERVICE wikibase:around {
    ?wd wdt:P625 ?loc .
    bd:serviceParam wikibase:center ?center .
    bd:serviceParam wikibase:radius ?radius .
  }

  # Calculate distance between OSM's and WD's
  BIND(geof:distance(?center, ?loc) as ?distance)

  # For the Wikidata entry, get its label in current user's language, or in the first available
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" . }

  # Find common wikipedia sitelinks
  OPTIONAL { ?enSite schema:about ?wd ;
                     schema:isPartOf <https://en.wikipedia.org/> . }
  OPTIONAL { ?esSite schema:about ?wd ;
                     schema:isPartOf <https://es.wikipedia.org/> . }
  OPTIONAL { ?deSite schema:about ?wd ;
                     schema:isPartOf <https://de.wikipedia.org/> . }
  OPTIONAL { ?frSite schema:about ?wd ;
                     schema:isPartOf <https://fr.wikipedia.org/> . }
  OPTIONAL { ?ruSite schema:about ?wd ;
                     schema:isPartOf <https://ru.wikipedia.org/> . }    
}
ORDER BY ?distance
LIMIT 100

Run it (edit query)


Find all wikidata items which have no image, i.e. which may require an illustration, near the specific osm object

#defaultView:Map
SELECT ?loc ?wd ?wdLabel ?distance ?enSite ?esSite ?deSite ?frSite ?ruSite WHERE {
 
  # for convenience, define constant parameter of the central object
  BIND (osmway:79658245 as ?osmId)
  BIND (10 as ?radius)  # in km
 
  # get object's location to use as a center of the search
  ?osmId osmm:loc ?center .

  #Wikidata does not have an Image property
  FILTER NOT EXISTS {
    ?wd wdt:P18 ?img .
  }
 
  # find all wikidata items in the close proximity
  SERVICE wikibase:around {
    ?wd wdt:P625 ?loc .
    bd:serviceParam wikibase:center ?center .
    bd:serviceParam wikibase:radius ?radius .
  }

  # Calculate distance between OSM's and WD's
  BIND(geof:distance(?center, ?loc) as ?distance)

  # For the Wikidata entry, get its label in current user's language, or in the first available
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" . }

 
}
ORDER BY ?distance
LIMIT 100

Run it (edit query)

subject:wikidata pointing to a sculptor - because it might be the artist

#defaultView:Map
SELECT
 ?osmId
 (SAMPLE(?wdLabel) as ?label)
 (SAMPLE(?wd) as ?wd)
 (GROUP_CONCAT(DISTINCT(?occupation); separator=", ") as ?occupation)
 (SAMPLE(?loc) AS ?loc)

WHERE {  
  # Get OSM elements with "subject:wikidata" tag and its location.
  ?osmId osmt:subject:wikidata ?wd ;
         osmm:loc ?loc .
  
  # The subject:wikidata must have occupation="sculptor",
  # or a subclass of sculptor.  Get all occupations of that person.
  ?wd wdt:P106/wdt:P279* wd:Q1281618 ;
      wdt:P106 ?occ .

  # Get labels for the Wikidata entry, and for all occupations
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr,it,pl,ru,es,sv,nl" .
    ?wd rdfs:label ?wdLabel .
    ?occ rdfs:label ?occupation
  }

} GROUP BY ?osmId

Run it (edit query)

Find locations which are used fi.wikivoyage.org but doesn't have OSM relation

Eg, Wikivoyage location maps which doesn't have OSM borders.

SELECT DISTINCT  ?item ?itemLabel ?typeLabel ?coord ?article WHERE { 
  ?item wdt:P625 ?coord . 
  ?item wdt:P31 ?type .
  ?article schema:about ?item ; schema:isPartOf <https://fi.wikivoyage.org/> ; 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi". }         
  FILTER NOT EXISTS { ?osmId osmm:type 'r'; osmt:wikidata ?item . }  
}

Run it (edit query)

Find all brands that belong to Yum! Brands (KFC, TacoBell, ...)

#defaultView:Map
SELECT DISTINCT ?osmId ?wd ?wdLabel ?loc WHERE {
  
  ?osmId osmt:brand:wikidata ?wd ;
         osmm:loc ?loc .
  
  ?wd wdt:P127* wd:Q668737 .
    
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,ru,es,de,zh,ja". }
}

Run it (edit query)

OSM places without name:fr that are most popular in French wiki

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

  # This will limit the results to places which do not have a `name:en` tag:
  FILTER NOT EXISTS { ?osmId osmt:name:fr ?namefr . }

  # Must have wikidata tag
  ?osmId osmt:wikidata ?wd .

  # the wikidata must have a frwiki page, and it must be popular
  # note that some less popular pages may not have a pageview value
  ?sitelink schema:isPartOf <https://fr.wikipedia.org/> ;
            schema:about ?wd ;
            pageviews: ?popularity .

  # Wikidata must have a French label
  ?wd rdfs:label ?label .
  FILTER(lang(?label) = "fr")
}
ORDER BY DESC(?popularity)
LIMIT 50

Run it (edit query)

Objects whose Wikipedia tag does not exist in Wikidata

SELECT ?osmId ?wpTag WHERE {
  # Limit to subjects that have a tag called "wikipedia"
  ?osmId osmt:wikipedia ?wpTag .

  # The article title must not be used in Wikidata
  FILTER NOT EXISTS { ?wpTag schema:isPartOf ?anySite . }

  # OSM item must not have a wikidata tag
  FILTER NOT EXISTS { ?osmId osmt:wikidata ?anyWd . }

  # Optionally, find pl:* wikipedia tags (point to Polish wiki)
  #  FILTER( STRSTARTS(STR(?wpTag), 'https://pl.wikipedia')) .

  # Optionally, limit to just ways (can be 'n', 'r', 'w')
  #  ?osmId osmm:type 'w' .

  # Optionally, restrict OSM objects to those that have a specific tag (and value)
  #  ?osmId osmt:place 'city' .        # exact string matching
  #  ?osmId osmt:name:en ?nameen .     # unless filtered, matches all objects with this tag
  #  FILTER( regex(?nameen, "A.b") )   # filter name:en to match a regex. Not very efficient
}
LIMIT 10

Run it (edit query)

Names and languages

Places without English name tag, and without English label in Wikidata

SELECT ?osmId ?osmType ?place ?wd WHERE {
  ?osmId osmm:type ?osmType .
  ?osmId osmt:place ?place .
  ?osmId osmt:wikidata ?wd .
  FILTER NOT EXISTS { ?osmId osmt:name:en ?nameen . }

  OPTIONAL { ?wd rdfs:label ?label FILTER(lang(?label) = "en") }
  FILTER(!BOUND(?label))
}
LIMIT 50

Run it (edit query)