User:SJK/SPARQL Queries

On this page I keep notes on the SPARQL Queries I write against Wikidata. Feel free to use them as a basis for your own queries.

Find planets, their mass in kilograms, and planet type info

SELECT ?item ?itemLabel ?kilograms ?isaLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q634 .
  ?item p:P2067/psn:P2067/wikibase:quantityAmount ?kilograms .
  ?item wdt:P31 ?isa .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Find all operating systems (Q9135) without English labels

Can easily be modified to find entities of any particular class which lack English labels. Will show all labels in other (non-English) languages

SELECT ?item ?anyLabel (LANG(?anyLabel) AS ?labelLang)
WHERE
{
	?item wdt:P31/wdt:P279* wd:Q9135 .
  	OPTIONAL { ?item rdfs:label ?label .
        FILTER (LANGMATCHES(LANG(?label),"EN"))  } .
  	FILTER (!BOUND(?label)) .
        OPTIONAL { ?item rdfs:label ?anyLabel }
}

Same as above but exclude Linux distributions (Q131669):

SELECT ?item ?anyLabel (LANG(?anyLabel) AS ?labelLang)
WHERE
{
        ?item wdt:P31/wdt:P279* wd:Q9135 .
  	FILTER NOT EXISTS { ?item wdt:P31/wdt:P279* wd:Q131669 } .
        OPTIONAL { ?item rdfs:label ?label .
        FILTER (LANGMATCHES(LANG(?label),"EN"))  } .
        FILTER (!BOUND(?label)) .
        OPTIONAL { ?item rdfs:label ?anyLabel }
}

Find self-referential uses of a property

SELECT DISTINCT ?item (str(?itemLabel) AS ?itemLabelStr)
WHERE
{
	?item wdt:P279 ?item . 
	OPTIONAL { ?item rdfs:label ?itemLabel . FILTER (LANGMATCHES(LANG(?itemLabel),"EN"))  } .
}

List all Properties in numeric ID order

SELECT DISTINCT ?property ?propertyLabel ?propertyID
WHERE {
  	?property rdf:type wikibase:Property .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
  	BIND(xsd:integer(REPLACE(STR(?property),"^http://www.wikidata.org/entity/P","")) AS ?propertyID)
}
ORDER BY ?propertyID

For those with item type only:

SELECT DISTINCT ?property ?propertyLabel ?propertyID
WHERE {
        ?property rdf:type wikibase:Property .
        SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
        BIND(xsd:integer(REPLACE(STR(?property),"^http://www.wikidata.org/entity/P","")) AS ?propertyID) .
  		?property wikibase:propertyType wikibase:WikibaseItem
}
ORDER BY ?propertyID

Find where A is both a subclass and an instance of B

Many of these are errors, although there might be some cases where it could be acceptable. For example, Pseudo-Plutarch is listed as both an instance of human and a subclass of human. While at first I thought that must an error, it could also be reasonably interpreted as a reflection of the uncertainty as to whether Pseudo-Plutarch was a single individual or a group of individuals.

SELECT ?a ?aLabel ?b ?bLabel
WHERE {
  	?a wdt:P279 ?b .
	?a wdt:P31 ?b .
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Find entities having GeoNames IDs but lacking English labels

You can copy the English name from Geonames into Wikidata. (Check it is correct first though, since sometimes the Geonames IDs are wrong; compare the claims about the entity and the other language labels against the Geonames data to confirm they are talking about the same thing.)

SELECT DISTINCT ?item ?geonamesURL
WHERE
{
	?item wdt:P1566 ?geonamesID .
    	OPTIONAL { ?item rdfs:label ?label .
        FILTER (LANGMATCHES(LANG(?label),"EN"))  } .
        FILTER (!BOUND(?label)) .
        OPTIONAL { ?item rdfs:label ?anyLabel } .
  		BIND(IRI(CONCAT("http://sws.geonames.org/",?geonamesID)) as ?geonamesURL)
}
LIMIT 100

Entities associated with Australia, lacking English labels, but having a label in another language

Often the other language label is actually English, so can just be set to the English label. This does require manual review, however, since in other cases the non-English label clearly isn't in English.

SELECT DISTINCT ?item ?label
WHERE {
  ?item wdt:P17  wd:Q408 .
  FILTER NOT EXISTS {
    ?item rdfs:label ?englishLabel .
    FILTER(LANGMATCHES(LANG(?englishLabel),"EN"))
  } .
  ?item rdfs:label ?label
}

Things that are caves but also something else you might not expect a cave to be

Basically, this finds everything that is an instance of cave but which is also an instance of something else. Then it filters out the other things you'd expect caves to be instances of (e.g. natural heritage or archaeological sites). Leaving behind caves that are also other things that are possibly odd things for caves to be. Some of these are entirely sensible, others maybe not.

SELECT ?item ?other ?itemLabel ?otherLabel
WHERE {
  ?item wdt:P31 wd:Q35509 .
  ?item wdt:P31 ?other .
  FILTER (?other != wd:Q35509 ) .
  FILTER (?other != wd:Q839954 ) .
  FILTER (?other != wd:Q386426 ) .
  FILTER (?other != wd:Q358) .
  FILTER (?other != wd:Q863404 ).
  FILTER NOT EXISTS { ?other wdt:P279* wd:Q35509 } .
  FILTER NOT EXISTS { ?other wdt:P279* wd:Q839954 } .
  FILTER NOT EXISTS { ?other wdt:P279* wd:Q386426 } .
  FILTER NOT EXISTS { ?other wdt:P279* wd:Q358 } .
  FILTER NOT EXISTS { ?other wdt:P279* wd:Q863404 } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Things in the UK, without any instance or subclass, but their name ends in "Power Station"

It is fair to presume these things actually are power stations.

SELECT ?item  ?itemLabel 
WHERE {
  ?item wdt:P17 wd:Q145 .
  FILTER NOT EXISTS { ?item wdt:P31 ?class1 } .
  FILTER NOT EXISTS { ?item wdt:P279 ?class2 } . 
  ?item rdfs:label ?itemLabel .
  FILTER(LANGMATCHES(LANG(?itemLabel),"EN")) .
  FILTER(REGEX(?itemLabel,"Power Station$"))
}

Power stations not said to be hydroelectric but they have "Hydroelectric" in their name

SELECT ?item  ?itemLabel 
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q159719 .
  FILTER NOT EXISTS { ?item wdt:P31/wdt:P279* wd:Q15911738 } .
  ?item rdfs:label ?itemLabel .
  FILTER(LANGMATCHES(LANG(?itemLabel),"EN")) .
  FILTER(REGEX(?itemLabel,"Hydroelectric"))
}

Having Australian Heritage Database ID but not having Country of Australia

Note: this database contains a small number of items outside Australia, e.g. ANZAC Cove in Turkey. Those items are not supposed to have the country of Australia.

SELECT ?item ?itemLabel
WHERE {
  ?item wdt:P3008 ?ausHeritageID .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
  FILTER NOT EXISTS { ?item wdt:P17 wd:Q408 }
}

Theological notes having English labels but lacking Latin labels

SELECT ?item ?itemEnglishLabel
WHERE {
  ?item wdt:P31 wd:Q28119403 .
  ?item rdfs:label ?itemEnglishLabel .
  FILTER(LANGMATCHES(LANG(?itemEnglishLabel),"EN")) .
  FILTER NOT EXISTS {
    ?item rdfs:label ?itemLatinLabel .
    FILTER(LANGMATCHES(LANG(?itemLatinLabel),"LA"))
  }
}

Items having Twitter hashtag starting with #

This is a common mistake. Even though we normally write Twitter hashtag with an initial hash, the usage instructions for hashtag (P2572) say to omit it.

SELECT ?item ?hashtag
WHERE {
  	?item wdt:P2572 ?hashtag .
  	FILTER(REGEX(?hashtag,'^#'))
}

Find all VICNAMES IDs, generate URLs

SELECT ?item ?itemLabel ?vicnames ?uri
WHERE
{
	?item wdt:P3472 ?vicnames .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
  	BIND(IRI(CONCAT("https://maps.land.vic.gov.au/lassi/VicnamesUI.jsp?placeId=",?vicnames)) AS ?uri) 
}

Railway station in Victoria without VICNAMES

SELECT DISTINCT ?item ?itemLabel ?vicnames
{
	?item wdt:P31 wd:Q55488 .
  	?item wdt:P131/wdt:P131* wd:Q36687 .
  	FILTER NOT EXISTS { ?item wdt:P3472 ?vicnames } .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Find 500 items for which Swedish and Cebuano labels are same but English label is lacking

Almost every such item was is based on an article created by Lsjbot (Q17430942), and if the Swedish and Cebuano labels are identical the English will be the same too. Note the below query excludes disambiguation pages–not sure if that is really needed or not but I did it for now.

SELECT ?item ?svLabel
WHERE
{
	?item rdfs:label ?svLabel .
    FILTER(LANG(?svLabel) = "sv") .
	?item rdfs:label ?cebLabel .
    FILTER(LANG(?cebLabel) = "ceb") .
  	FILTER(xsd:string(?svLabel) = xsd:string(?cebLabel)) .
  	OPTIONAL {
      	?item rdfs:label ?enLabel .
        FILTER (LANGMATCHES(LANG(?enLabel),"EN"))
    } .
    FILTER (!BOUND(?enLabel)) .
  	FILTER NOT EXISTS {
      ?item wdt:P31/wdt:P279* wd:Q4167410
    }
}
LIMIT 500

Find middle schools without country and with English label

SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q149566 .
  FILTER NOT EXISTS { ?item wdt:P17 ?country } .
  ?item rdfs:label ?label .
  FILTER (LANGMATCHES(LANG(?label),"EN")) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Find schools with no country but website in Japan (.jp domain)

SELECT ?item ?itemLabel ?website
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q3914 .
  FILTER NOT EXISTS { ?item wdt:P17 ?country } .
  ?item wdt:P856 ?website .
  FILTER CONTAINS(xsd:string(?website),".jp/") .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}