User:Jarekt/queries

Scratch pad with SPARQL I want to keep or find again.

The following query uses these:

Items: Universe (Q1)   , Earth (Q2)

Properties: image (P18)  

SELECT ?item ?img 
{ 
  VALUES ?item { 
     wd:Q1 
     wd:Q2 
  } . 
  ?item wdt:P18 ?img
}

Try it!

The following query uses these:

Items: The Death of General Wolfe (Q956541)   , Q9639014

Properties: creator (P170)  

, author (P50)  

SELECT ?item ?creatorLabel ?authorLabel
{ 
  VALUES ?item { 

wd:Q956541
wd:Q9639014

  } . 
  optional{?item wdt:P170 ?creator }.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
  optional{?item wdt:P50 ?author } .

Try it!

}

My items without P31[edit]

The following query uses these:

Properties: instance of (P31)  

, Commons category (P373)  

SELECT ?item ?itemLabel ?commonscat
{ 
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "random" .
    bd:serviceParam mwapi:list "usercontribs" . 
    bd:serviceParam mwapi:ucuser "Jarekt" . 
    bd:serviceParam mwapi:ucprop "title|timestamp|comment" .
    bd:serviceParam mwapi:ucnamespace "0" .
    bd:serviceParam mwapi:ucshow "new" .
    bd:serviceParam mwapi:uclimit "1" .
    ?created wikibase:apiOutput "//api/query/usercontribs/item/@timestamp" .
    ?comment wikibase:apiOutput "//api/query/usercontribs/item/@comment" .
    ?item wikibase:apiOutputItem "//api/query/usercontribs/item/@title" .
    bd:serviceParam wikibase:limitContinuations "249" .
  }
  MINUS { ?item wdt:P31 [] }
  OPTIONAL {?item wdt:P373 ?commonscat} . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

Taxon related[edit]

harvest BugGuideID[edit]

The following query uses these:

Properties: BugGuide taxon ID (P2464)  

, taxon rank (P105)  

, taxon name (P225)  

SELECT DISTINCT ?BugGuideID ?item ?taxon ?rank
{
	?item wdt:P2464 ?BugGuideID .  # BugGuideID 
    ?item wdt:P105  ?rank .
  	?item wdt:P225  ?taxon .       # taxon name
}

Try it!

bad BugGuideID[edit]

The following query uses these:

Items: Arthropoda (Q1360)   , phylum (Q38348)

Properties: BugGuide taxon ID (P2464)  

, parent taxon (P171)  

, taxon rank (P105)  

SELECT ?item ?BugGuideID 
{
    ?item wdt:P2464 ?BugGuideID .      # has BugGuideID 
    ?item wdt:P171  ?pItem .           # has parent item
    MINUS {?item wdt:P171+ wd:Q1360. } # exclude items in Arthropoda tree
    MINUS {?item wdt:P105 wd:Q38348. } # exclude Arthropoda (Q1360) itself
}

Try it!

SELECT  ?item ?BugGuideID 
{
	?item wdt:P2464 ?BugGuideID .  # BugGuideID 
    ?item wdt:P105  ?rank .
    #VALUES ?rank {wd:Q34740 }  
    VALUES ?rank {wd:Q35409 wd:Q36602 wd:Q2455704 wd:Q37517 wd:Q2136103 wd:Q5867959 wd:Q227936 wd:Q38348 wd:Q2889003 wd:Q3965313 wd:Q5868144 wd:Q5867051 wd:Q14817220 wd:Q2981883 wd:Q1153785 wd:Q3504061 wd:Q10861426  } .                #  	taxonomic rank
	?item wdt:P171  ?pItem .       # parent item
  
    MINUS {?item wdt:P171
  				 |wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171  
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171  
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171   
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171  
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171     
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171     
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171 
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171 
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 #|wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 #|wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 wd:Q1360 . }
   # MINUS{ ?item wdt:P105 wd:Q38348. }
}
Limit 20

look for candidates[edit]

SELECT DISTINCT ?taxon ?item ?rank
{
	#?item wdt:P1895 ?value .
	?item wdt:P225  ?taxon .       # taxon name
    ?item wdt:P105  ?rank .
    VALUES ?rank {wd:Q227936 } #wd:Q2455704 wd:Q227936 wd:Q3965313}
	MINUS { ?item wdt:P2464 [] } . # BugGuideID 
    ?item wdt:P171
                 |wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171  
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171  
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171   
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171  
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171     
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171     
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171 
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171 
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 |wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 #|wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 #|wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 wd:Q1390 . #wd:Q1390 . #wd:Q25375 . #wd:Q23005 # wd:Q1360 #Q22651 . # Q37204
}
limit 2000

find BugGuideID candidates[edit]

The following query uses these:

Items: genus (Q34740)

Properties: LepIndex ID (P3064)  

, taxon name (P225)  

, taxon rank (P105)  

, BugGuide taxon ID (P2464)  

SELECT DISTINCT ?taxon ?item ?rankLabel
{
	?item wdt:P3064 ?value .
	?item wdt:P225  ?taxon .       # taxon name
    ?item wdt:P105  ?rank .
    BIND ( wd:Q34740 as ?rank).
	MINUS { ?item wdt:P2464 [] } . # BugGuideID 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

find BugGuideID candidates based on name[edit]

The following query uses these:

Properties: taxon name (P225)  

, taxon rank (P105)  

SELECT  ?taxon ?item ?rank WHERE {
	?item wdt:P225  ?taxon .       # taxon name
    ?item wdt:P105  ?rank .

  VALUES ?taxon {"Glycyphagoidea"
"Acaroidea"
"Hemisarcoptoidea"
} .

}

Try it!

find BugGuideID candidates based on label[edit]

SELECT DISTINCT ?lab ?item WHERE {

 #?item  wdt:P255 ?P255 .
 #?item  wdt:P255 ?P255 .
       
 OPTIONAL {
   ?item rdfs:label ?lab 
   FILTER((LANG(?lab)) = "en")
 }
 MINUS {?item wdt:P31 wd:Q4167836 } .
 VALUES ?lab {"Acacesia hamata"

"Agelenopsis pennsylvanica"

} .

}

Taxons without commonscat[edit]

Taxon with sitelink to Commons but without P373.[edit]

The following query uses these:

Properties: taxon name (P225)  

, Commons category (P373)  

, Commons gallery (P935)  

SELECT ?item ?article
WHERE
{
    ?item wdt:P225 ?taxonName .
    OPTIONAL {?item wdt:P373 ?commonscat} . 
    #OPTIONAL {?item wdt:P935 ?commonsgal} .   
    ?article schema:about ?item .
    ?article schema:isPartOf <https://commons.wikimedia.org/> .
    FILTER (STRSTARTS(STR(?article), "https://commons.wikimedia.org/wiki/Category"))
    FILTER (!bound(?commonscat))
}

Try it!

Taxon with a file but without P373.[edit]

The following query uses these:

Properties: taxon name (P225)  

, image (P18)  

, Commons category (P373)  

SELECT ?item ?taxonName ?image
WHERE
{
    ?item wdt:P225 ?taxonName .
    ?item wdt:P18 ?image .  
    MINUS { ?item wdt:P373 [] } . # commons category
}

Try it!

Related to categories[edit]

extra P373[edit]

The following query uses these:

Properties: Commons category (P373)  

, topic's main category (P910)  

, category's main topic (P301)  

SELECT DISTINCT ?categoryItem WHERE {
    # ?article and ?category share the same P373 ("Commons Category")
    ?articleItem  wdt:P373 ?commonsCategory .
    ?categoryItem wdt:P373 ?commonsCategory .
  
    # P301 and P910 are set properly
    ?articleItem  wdt:P910 ?categoryItem .
    ?categoryItem wdt:P301 ?articleItem  .
  
    # sitelink to Commons points to the same category as property P373
    ?commonsSitelink schema:about ?categoryItem .
    FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category"))
    FILTER(STRENDS(STR(?commonsSitelink), ENCODE_FOR_URI(?commonsCategory)))
} limit 100

Try it!

missing P373[edit]

The following query uses these:

Items: Wikimedia category (Q4167836)

Properties: Commons category (P373)  

, instance of (P31)  

SELECT (Count(?item) as ?count) WHERE {
    # ?article and ?category share the same P373 ("Commons Category")
    MINUS{ ?item  wdt:P373 ?commonsCategory . }
    MINUS{ ?item wdt:P31 wd:Q4167836 . }
  
    # sitelink to Commons points to the same category as property P373
    ?commonsSitelink schema:about ?item .
    FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category"))
}

Try it!

Labels ≠ Sitelink[edit]

SELECT ?item ?wiki_sitelink ?sitelink_label ?label {
  ?wiki_sitelink schema:about ?item; schema:isPartOf <https://it.wikipedia.org/>; schema:name ?sitelink_label .
  ?item rdfs:label ?label .
  FILTER(LANG(?label) = 'it') .
  FILTER(false = (LCASE(SUBSTR(STR(?wiki_sitelink), 31)) = LCASE(ENCODE_FOR_URI(LCASE(STR(?label))))) )
  FILTER(false=CONTAINS(?sitelink_label,")"))
} LIMIT 100

Try it!

Number of Sitelink per project (not working)[edit]

SELECT ?project (count(?item) as ?count) WHERE {
  ?sitelink schema:about ?item ; schema:isPartOf ?project  .
} 
group by ?project 
order by ?count
LIMIT 3000

Try it!

Sitelinks to old wikisource[edit]

SELECT ?item WHERE {
  ?sitelink schema:about ?item ; schema:isPartOf <https://wikisource.org/>; 
}

Try it!

non-unique P373[edit]

SELECT ?itemLabel ?item ?commonscat WHERE { 
  hint:Query hint:optimizer "None" 
    {
      SELECT ?commonscat (COUNT(?item) AS ?count) WHERE {
          {
            SELECT ?commonscat WHERE {
               ?item wdt:P373 ?commonscat .
            }    GROUP BY ?commonscat
            HAVING (COUNT(?item) > 4)
          }
          ?item wdt:P373 ?commonscat 
          FILTER NOT EXISTS {?item wdt:P31 wd:Q4167836} .
       } GROUP BY ?commonscat
       HAVING (COUNT(?item) > 10)
   }    
         
   ?item wdt:P373 ?commonscat .
   FILTER NOT EXISTS {?item wdt:P31 wd:Q4167836} .    
   FILTER NOT EXISTS {?item wdt:P31 wd:Q13406463} .    
   #FILTER EXISTS {?item wdt:P1435 wd:Q31948690} . 

   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
} ORDER BY DESC(?count) ?commonscat

Try it!

SELECT ?item  ?itemLabel ?ad WHERE { # ?itemLabel ?im
    ?item  wdt:P373 "Kolonia Staszica (Ruda Śląska)". 
    optional{ ?item  wdt:P18 ?im}.
    optional{ ?item  wdt:P969 ?ad}.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "pl" . }
}

Try it!

Looking for items where category saved in sitelink to Commons does not match P373[edit]

I observed that often after category rename, new category is placed in a sitelink but P373 is not updated. So I am looking for such cases. --Jarekt (talk) 14:15, 25 October 2017 (UTC)

schema:name returns two parts: value and language. You need to add str() to remove the language info and to get a pure literal value. Only those values you can compare with wdt:P373 which is from the beginning a pure literal value.

SELECT ?item ?commonsCategory ?commonsSitelink ?category WHERE {
  ?item wdt:P373 ?commonsCategory . 
  ?commonsSitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?sitelink_label .
  FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category")) . # sitelink to a category
  BIND(str(substr(?sitelink_label,10)) as ?category) . # strip "Category:" part
  FILTER( false = (?category = ?commonsCategory) )
} LIMIT 10

Try it!

--Pasleim (talk) 15:08, 25 October 2017 (UTC)

Intersect with Wikidata:Database_reports/Constraint_violations/P373#.22Commons_link.22_violations or P373 that do not exist

SELECT ?item ?category ?commonsCategory WHERE {
  ?item wdt:P373 ?commonsCategory . 
  ?commonsSitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?sitelink_label .
  FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category")) . # sitelink to a category
  BIND(str(substr(?sitelink_label,10)) as ?category) . # strip "Category:" part
  FILTER( false = (?category = ?commonsCategory) ).
  VALUES ?item { 
 wd:Q6672
wd:Q37920

}. 
}

Try it!

videos as images[edit]

SELECT ?item ?image WHERE
{
  ?item wdt:P18 ?image .
  FILTER( strends(str(?image),".ogv") ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} 
LIMIT 100

Try it!

P18s from commons category[edit]

trying to find items that include in image (P18) files from c:Category:Graves_without_Wikidata_item

SELECT ?file ?item  {
  SERVICE wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Graves_without_Wikidata_item" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?file wikibase:apiOutput mwapi:title  .
  }
  BIND(IRI(concat("http://commons.wikimedia.org/wiki/Special:FilePath/",ENCODE_FOR_URI(substr(?file,6)))) as ?fileName) .
  OPTIONAL { 
    ?item wdt:P18 ?fileName .
   }
}

Try it!

People related[edit]

City of birth[edit]

The following query uses these:

Items: Pyotr Ilyich Tchaikovsky (Q7315)   , city (Q515)

Properties: place of death (P20)   , located in the administrative territorial entity (P131)   , instance of (P31)   , subclass of (P279)  
```
SELECT DISTINCT ?city { ?city ^(wdt:P20/wdt:P131*) wd:Q7315; wdt:P31/wdt:P279* wd:Q515 . }
```

Try it!

Query to keep Commons Creator page (P1472) property and Commons creator templates in synch[edit]

Wikidata Commons Creator page (P1472) property and Commons creator templates have kind of reciprocal relationship: Commons Creator page (P1472) point from an item to one of Commons Creator templates and each creator template has "Wikidata" field with a q-code of the item. If creator template has "Wikidata" field with a q-code of some item but that item does not have matching Commons Creator page (P1472) property than the creator template is placed in c:Category:Creator templates with Wikidata link: item missing linkback. However if some item has I can not figure out how to write a query or generate a list of q-codes for items with Commons Creator page (P1472) property that point to a templates that do not have "wikidata" field pointing back. Any idea if such a query can be written or if some tool like petscan, etc. could help me? --Jarekt (talk) 02:39, 16 June 2017 (UTC)

If I understand correctly what you're looking for, the new MWAPI for WDQS can be of some help.

The following query uses these:

Properties: Commons Creator page (P1472)  

SELECT (IRI(concat("https://commons.wikimedia.org/wiki/", ?template)) as ?templateLink) ?templateName ?creatorItem ?creatorItemLabel {
  SERVICE wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Creator templates without Wikidata link" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?template wikibase:apiOutput mwapi:title  .
  }
  BIND(substr(?template,9) as ?templateName) .
  OPTIONAL { 
    ?creatorItem wdt:P1472 ?templateName .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
  }
  FILTER ( BOUND(?creatorItem) ) .
}

Try it!

However it may not return all wanted entries, due to limitations with MWAPI call results limit. This limit really doesn't make sense in the SPARQL context in my opinion, especially for generators. @Smalyshev (WMF): any chance the engine can iterate by using continue parameter? -- Nono314 (talk) 19:39, 16 June 2017 (UTC)

Theoretically, it is possible, practically, different APIs seem to do continuations differently, so it may be hard to implement it in generic way. E.g. for this API, continue is in gcmcontinue, but for search it's sroffset, and for querypage it's qpoffset. If I figure out a way how to generalize it, I can implement it.

Though one needs to be careful as result may be too big and lead to timeouts. --Smalyshev (WMF) (talk) 22:54, 16 June 2017 (UTC)

Sure, as for any SPARQL query! @Smalyshev (WMF): Thanks for having a look at it. I think it really makes sense for generators. So we could pass the right parameter in the query, and you would just iterate until you get batchcomplete in result? And maybe a way to specify a maximum number of iterations? -- Nono314 (talk) 23:38, 16 June 2017 (UTC)

query listing creator templates that do not have wikidata id, but for which there is an item with Commons category (P373) pointing to their home category.

The following query uses these:

Items: Commons Creator page (Q24731821)

Properties: Commons category (P373)  

SELECT (IRI(concat("https://commons.wikimedia.org/wiki/", ?creatorTemplate)) as ?creatorLink) ?creatorName ?categoryName ?commonsCatItem ?commonsCatItemLabel {
  SERVICE wikibase:mwapi { # list of all creator templates without Wikidata link
     bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Creator templates without Wikidata link" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?creatorTemplate wikibase:apiOutput mwapi:title  .
  }
  hint:Prior hint:runFirst 1 . 
  SERVICE wikibase:mwapi { # get home category
     bd:serviceParam wikibase:api "Categories" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:titles ?creatorTemplate .
     bd:serviceParam mwapi:clshow "!hidden" .
     ?category wikibase:apiOutput mwapi:category  .
  }
  BIND(substr(?creatorTemplate,9) as ?creatorName ) .
  BIND(substr(?category,10)       as ?categoryName) .
  OPTIONAL { 
    ?commonsCatItem wdt:P373 ?categoryName . # category is linked from Wikidata
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
  }
  FILTER ( BOUND(?commonsCatItem) ) .
  FILTER ( ?commonsCatItem!=wd:Q24731821 ) .
}

Try it!

The following query uses these:

Properties: Commons category (P373)  

SELECT (IRI(concat("https://commons.wikimedia.org/wiki/", ?homeCategory)) as ?homeCategoryLink) ?homeCategory ?commonsCatItem ?commonsCatItemLabel {
  SERVICE wikibase:mwapi { # list of all creator templates without Wikidata link
     bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Creator template home categories without Wikidata link" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?homeCategory wikibase:apiOutput mwapi:title  .
  }

  BIND(substr(?homeCategory,10) as ?categoryName) .
  OPTIONAL { 
    ?commonsCatItem wdt:P373 ?categoryName . # category is linked from Wikidata
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
  }
  FILTER ( BOUND(?commonsCatItem) ) .
}

Try it!

Creator Home categories used be multiple items[edit]

The following query uses these:

Items: Wikimedia category (Q4167836)

Properties: Commons Creator page (P1472)  

, Commons category (P373)  

, instance of (P31)  

SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label ?value
{
    ?item1 wdt:P1472 [] .
	?item1 wdt:P373 ?value .
	?item2 wdt:P373 ?value .
	FILTER(?item1 != ?item2) .
    MINUS{ ?item2 wdt:P31 wd:Q4167836 . }
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" } .
} LIMIT 500

Try it!

The following query uses these:

Items: Wikimedia category (Q4167836)

Properties: Commons Creator page (P1472)  

, Commons category (P373)  

, date of birth (P569)  

, date of death (P570)  

, instance of (P31)  

SELECT DISTINCT ?item1 ?item1Label ?dob1 ?dod1 ?item2 ?item2Label ?dob2 ?dod2 ?value
{
    ?item1 wdt:P1472 [] .
	?item1 wdt:P373 ?value .
	?item2 wdt:P373 ?value .
    OPTIONAL {?item1 wdt:P569 ?dob1} .
    OPTIONAL {?item1 wdt:P570 ?dod1} .
    OPTIONAL {?item2 wdt:P569 ?dob2} .
    OPTIONAL {?item2 wdt:P570 ?dod2} .
    FILTER(?item1 != ?item2) .
    MINUS{ ?item2 wdt:P31 wd:Q4167836 . }
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" } .
} LIMIT 500

Try it!

Look up if an item is a redirect and where is it pointing[edit]

The following query uses these:

Items: Matteo Ponzone (Q1268917)  

, Matthew Henry Wilson (Q26248169)  

, Matthäus Greuter (Q3299661)  

SELECT ?item ?target {
  VALUES ?item { 
    
wd:Q1268917
wd:Q26248169
wd:Q3299661

 } .
  ?item owl:sameAs ?target .
}

Try it!

Items that have Commons Creator page (P1472) but do not have English label[edit]

SELECT ?item ?creator {
  ?item wdt:P1472 ?creator .
  FILTER NOT EXISTS {
    ?item rdfs:label ?label .
    FILTER((LANG(?label) = 'en')) .
  }
}

Try it!

frequency of nationalities or ethnic groups among Creators[edit]

SELECT ?value ?valueLabel (COUNT(DISTINCT ?item) AS ?count) WHERE {
    ?item wdt:P1472 [] .
	?item wdt:P172 ?value .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" } .
} GROUP BY ?value ?valueLabel
ORDER BY DESC (?count)
LIMIT 200

Try it!

Date related[edit]

qualifiers[edit]

SELECT ?person ?personLabel ?start ?end WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P569 ?statement . 
  ?statement pq:P1319 ?start .
  FILTER NOT EXISTS { ?statement pq:P1326 ?end }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 10

Try it!

Example of date using Millennium precision[edit]

I am looking for an example item with some property storing date that use Millennium precision. Is there a query that can help me find one? --Jarekt (talk) 16:00, 27 June 2017 (UTC)

I found one by hand: Q632385 --Jarekt (talk) 17:21, 27 June 2017 (UTC)

If you want a bunch of samples with point in time (P585):

SELECT ?precision ?precisionLabel ?prec ?sample ?sampleLabel ?date
WITH
{
    SELECT ?prec (SAMPLE(?item) as ?sample)
    {
        ?item p:P585/psv:P585/wikibase:timePrecision ?prec
    }
    GROUP BY ?prec
}  as %inc
WHERE      
{
    INCLUDE %inc 
    ?precision wdt:P2803 ?pr .
    ?sample wdt:P585 ?date
    FILTER(?prec = xsd:integer(?pr) )
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,fr,ro". }
}          
ORDER BY DESC(?prec)

Try it!

edited sample above.
--- Jura 18:33, 27 June 2017 (UTC)

items with P1472 and P650 that have year DOB[edit]

SELECT ?item WHERE {
  ?item wdt:P1472 [] .
  ?item wdt:P650 ?rdkid .
  ?item p:P569 ?dobstatement .
  ?dobstatement psv:P569 [
                wikibase:timePrecision "9"^^xsd:integer ;
                wikibase:timeValue ?birth ;
              ]
  MINUS { ?dobstatement prov:wasDerivedFrom ?provenance .
         MINUS { ?provenance pr:P143 [] } .
        }
  }

Try it!

multi precision dates query[edit]

A query where date of birth (P569) (or date of death (P570) ) has 2 values (or more) one a year and one a more precise date with the same year. Wikidata:Requests_for_permissions/Bot/MatSuBot_7

SELECT ?item ?val1 ?prec1 ?val2 ?prec2 {
  ?item p:P569 ?statement1 .
  ?item p:P569 ?statement2 FILTER( ?statement2 != ?statement1 ) . # more than one statement
  MINUS { ?item p:P569/wikibase:rank wikibase:PreferredRank } .
  ?statement1 psv:P569 [ wikibase:timeValue ?val1; wikibase:timePrecision ?prec1 ] .
  ?statement2 psv:P569 [ wikibase:timeValue ?val2; wikibase:timePrecision ?prec2 ] .
  FILTER( ?prec1 < ?prec2 ) . # different precision
  MINUS {
    ?statement1 prov:wasDerivedFrom ?ref1 .
    ?ref1 ?pr1 [] .
    FILTER( ?pr1 != pr:P143 ) . # the less precise statement is without real source
  } .
  ?statement2 prov:wasDerivedFrom ?ref2 .
  ?ref2 ?pr2 [] .
  FILTER( ?pr2 != pr:P143 ) . # the more precise statement does have it
  FILTER( YEAR( ?val1 ) = YEAR( ?val2 ) ) .
  FILTER( ?prec1 = 9 || MONTH( ?val1 ) = MONTH( ?val2 ) ) . # one time value is inside the other one
}

Try it!

Multicalendar DOB[edit]

SELECT ?item ?cal1 ?cal2
{
  ?item p:P569 ?date1 .
  ?item p:P569 ?date2 FILTER( ?date2 != ?date1 ) . # more than one statement
  ?date1 psv:P569 [ wikibase:timeCalendarModel ?cal1; wikibase:timeValue ?time1; wikibase:timePrecision "11"^^xsd:integer; ] .
  ?date2 psv:P569 [ wikibase:timeCalendarModel ?cal2; wikibase:timeValue ?time2; wikibase:timePrecision "11"^^xsd:integer; ] .
  FILTER(?cal1 != ?cal2 ) .
}       
LIMIT 10

Try it!

Multicalendar DOB[edit]

SELECT ?item ?julianDate ?gregorianDate
{
  ?item p:P569 ?date1 .
  ?item p:P569 ?date2 FILTER( ?date2 != ?date1 ) . # more than one statement
  ?date1 psv:P569 [ wikibase:timeValue ?julianDate;    wikibase:timeCalendarModel wd:Q1985786; wikibase:timePrecision "11"^^xsd:integer; ] .
  ?date2 psv:P569 [ wikibase:timeValue ?gregorianDate; wikibase:timeCalendarModel wd:Q1985727; wikibase:timePrecision "11"^^xsd:integer; ] .
  FILTER( ?julianDate = ?gregorianDate )      .
}       
LIMIT 100

Try it!

find burial date based on place of burial (P119)[edit]

SELECT ?person ("P4602" as ?P) ?burial ?dod WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P119/pq:P580 ?burial .
  ?person wdt:P570 ?dod .
  #MINUS { ?person wdt:P570 [] }.
  BIND(ROUND((?burial - ?dod)) AS ?duration).
  FILTER(?duration < 20).
}

Try it!

find burial/baptism date based on significant event (P793)[edit]

SELECT ?person ("P4602" as ?P) ?burial  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P793 [ps:P793 wd:Q331055; pq:P585 ?burial] .
}

Try it!

SELECT ?person ?baptism  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P793 [ps:P793 wd:Q35856; pq:P585 ?baptism] .  
}

Try it!

Objects with a significant event (P793)/baptism (Q35856) where the point in time (P585) is missing in the result.

SELECT ?person ?baptism  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P793 ?mem .
  ?mem ps:P793 wd:Q35856 . 
  OPTIONAL{ ?mem pq:P585 ?baptism }  
}

Try it!

Coordinate related[edit]

Commons files with multiple locations[edit]

SELECT ?file ?coord1 ?coord2
{
	?file wdt:P1259 ?coord1 , ?coord2 .
    FILTER (?coord1 != ?coord2)
} 
LIMIT 100

Try it!

runs in approx. 45 seconds, while

SELECT ?file ?coord1 ?coord2
{
	?file p:P1259 ?coord1 , ?coord2 .
    FILTER (?coord1 != ?coord2)
} 
LIMIT 100

Try it!

runs in 2 seconds.

Correct coordinate location precision[edit]

Many coordinate locations on Wikidata have incorrectly set "precision" parameter. The query below finds ~2000 items where latitude and longitude are integers (measured in degrees) while precision is set to a value less than a degree. Those locations should change the "precision" setting to +-1 degree. Latter we could do similar runs for precissions +- 1 arcminute, etc.

SELECT ?item ?itemLabel ?coord ?prec
WHERE
{
 ?item p:P625 ?coordinate.
 ?coordinate ps:P625 ?coord.
 ?coordinate psv:P625 ?coordinate_node.
 ?coordinate_node wikibase:geoLongitude ?lon.
 ?coordinate_node wikibase:geoLatitude ?lat.  
 ?coordinate_node wikibase:geoPrecision ?prec.  
 FILTER (?lat=ROUND(?lat)) . # integer latitude (in degrees)
 FILTER (?lon=ROUND(?lon)) . # integer longitude (in degrees)
 FILTER (?prec<1) .         # measurement precision less then one degree
 MINUS {?item p:P31 wd:Q146591} # not a circle of latitude
 MINUS {?item p:P31 wd:Q32099 } # not a meridian
 MINUS {?item p:P31 wd:Q17272482 } # not a time zone
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
}

Try it!

Institutions with double locations[edit]

SELECT ?item ?coord1 ?coord2
{
    ?item wdt:P625 ?coord1.      
    ?item wdt:P1612  ?Inst .           
    ?item p:P159  ?statement .   
    ?statement pq:P625 ?coord2.
}

Try it!

What is Lipovica's current country?[edit]

Lipovicë (Q6556803) has belonged to several countries during its history.

My query: What is Lipovica's current country?

An idea is to do a normal wdt:P131*/wdt:P17 but filter out all levels that have a endtime.

The query below unfortunately times out, can you help me fix it?

SELECT ?country WHERE {
 wd:Q6556803 wdt:P131* ?area.
 ?area wdt:P17 ?country.
 FILTER NOT EXISTS {
  wd:Q6556803 p:P131/(ps:P131/p:P131)* ?statement.
  ?statement ps:P131 ?area.
  wd:Q6556803 p:P131/(ps:P131/p:P131)* ?intermediateStatement.
  ?intermediateStatement (ps:P131/p:P131)* ?statement.
  ?intermediateStatement pq:P582 ?endTime.
 }
}

Try it!

Thanks a lot! :-) Syced (talk) 06:56, 15 June 2017 (UTC)

Lipovicë (Q6556803) doesn't have located in the administrative territorial entity (P131) + typo ?country × ?countryId. Matěj Suchánek (talk) 07:40, 15 June 2017 (UTC)

I fixed both problems, thanks! Still getting a timeout, though. Any other idea? :-) Syced (talk) 08:35, 15 June 2017 (UTC)

I've made it to:

SELECT DISTINCT ?country WHERE {
  wd:Q6556803 wdt:P131* ?area .
  ?area wdt:P17 ?country .
  OPTIONAL {
    wd:Q6556803 wdt:P131*/p:P131 [
      pq:P582 ?endTime; ps:P131/wdt:P131* ?area
    ] .
  } .
  FILTER( !BOUND( ?endTime ) ) .
}

Try it!

I'm not a SPARQL expert, though, so I can't guarantee it works for any places. Another problem I can see is with places that had the current value in the past as well. I don't have an example but I'm concerned it wouldn't work. I believe this is the case statements with ranks come in. Matěj Suchánek (talk) 10:19, 15 June 2017 (UTC)

Related to artworks[edit]

Get Item, collection and inventory ID[edit]

SELECT DISTINCT ?item ?collection ?id WHERE {
  ?item wdt:P31 wd:Q3305213 .
  ?item wdt:P571 ?date .
  #minus{ ?item wdt:P18 [] . }
  ?item wdt:P217 ?id .
  ?item p:P217 ?statement . 
  ?statement pq:P195 ?collection .
  FILTER (YEAR(?date) < 1945)
}

Try it!

paintings without image but with a sitelink to commons[edit]

SELECT ?item ?commonsSitelink
{
  ?item wdt:P31 wd:Q3305213 .
  ?commonsSitelink schema:about ?item .
  FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category"))
  MINUS{ ?item wdt:P18 [] . }
}

Try it!

paintings pairs using the same image[edit]

SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label ?image
{
	?item1 wdt:P31 wd:Q3305213 .
	?item2 wdt:P31 wd:Q3305213 .
	?item1 wdt:P18 ?image .
	?item2 wdt:P18 ?image .
	FILTER(?item1 != ?item2) .
	MINUS {?item1 p:P18/pq:P180 [] } .
	MINUS {?item2 p:P18/pq:P180 [] } .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" } .
} 
order by ?image
LIMIT 500

Try it!

explore significant event[edit]

SELECT ?value ?valueLabel ?ct ?sampleitem ?sampleitemLabel ?minitem ?minitemLabel ?maxitem ?maxitemLabel
WHERE
{
  {
    SELECT ?value (count(*) as ?ct) (SAMPLE(?item) as ?sampleitem) (min(?item) as ?minitem) (max(?item) as ?maxitem)
    WHERE
    {
      ?item wdt:P31 wd:Q3305213 . # paintings only
      ?item wdt:P793 ?value .     # sig event            
    }
    GROUP BY ?value
    ORDER BY DESC(?ct)
    LIMIT 20000
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY DESC(?ct) ASC(?value)

Try it!

get labels and descriptions[edit]

SELECT ?item ?Len ?Lpl ?itemDescription WHERE { #?Len ?Lpl ?itemDescription 
 ?item wdt:P170 wd:Q381238 .  
 ?item wdt:P195 wd:Q4801420 .  
 #?item wdt:P186 wd:Q296955  .  
 #?item wdt:P571 ?date .  
 #?item wdt:P217 ?id .  
 #?item wdt:P31 wd:Q14674  .  

 OPTIONAL {
   ?item rdfs:label ?Len 
   FILTER((LANG(?Len)) = "en")
 }
 OPTIONAL {
   ?item rdfs:label ?Lpl 
   FILTER((LANG(?Lpl)) = "pl")
 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "pl". }
}

Try it!

get Witkacy artworks with labels starting with '"'[edit]

SELECT ?item  ?Lpl WHERE { 
 ?item wdt:P170 wd:Q381238 .  
 #?item wdt:P18  ?img .  
 OPTIONAL {
   ?item rdfs:label ?Lpl 
   FILTER((LANG(?Lpl)) = "pl")
 }
 FILTER (STRSTARTS(STR(?Lpl),"\""))
}

Try it!

=PD-Art images[edit]

SELECT distinct ?item  ?itemLabel ?image {
	?item wdt:P31 wd:Q3305213 .
	?item wdt:P18 ?image .
	?item p:P6216/pq:P1001 wd:Q60332278 .
	?item p:P6216/pq:P459  wd:Q29940705 .
	MINUS {?item p:P6216/pq:P518 []} .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} limit 100

Try it!

Redirects[edit]

I have a list of 250 Item ID which I suspect ob being redirects. What would be the easiest way to check if they are and to what page they are redirecting. For example for no label (Q817614) I would like to get Benjamin Marshall (Q15123417). --Jarekt (A) (talk) 11:57, 6 April 2018 (UTC)

SELECT ?source ?target WHERE {
  VALUES ?source { wd:Q817614 }
  ?source owl:sameAs ?target .
}

Try it!

Property exploration[edit]

values used as qualifiers for a property:

SELECT ?qual ?qualLabel ?count WHERE {
  {
    SELECT ?qual (COUNT(DISTINCT ?item) AS ?count) WHERE {
         ?item p:P1684 ?statement .
         ?statement pq:P31 ?qual .
    }  GROUP BY ?qual
  } .

  OPTIONAL {
    ?qual rdfs:label ?qualLabel filter (lang(?qualLabel) = "en") .
  }
}
ORDER BY DESC(?count) ASC(?qualLabel)

Try it!

value frequency[edit]

SELECT ?event ?eventLabel (count(*) as ?count) WHERE {
  ?item wdt:P31 ?value .
  VALUES ?value { wd:Q3305213 wd:Q4502142 wd:Q860861 wd:Q93184 } .
  ?item wdt:P793 ?event .
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
Group by ?event ?eventLabel 
order by desc(?count)
limit 100

Try it!

histogram of use[edit]

SELECT ?count1 (Count(?item) as ?count) (min(?item) as ?example) WHERE {
  SELECT ?item (count(*) as ?count1) WHERE {
    ?item wdt:P195 ?value .
  } group by ?item
} group by ?count1
order by ?count1

Try it!

item with the most properties of some kind[edit]

SELECT ?item  (count(*) as ?count) WHERE {
  ?item wdt:P170 ?event .
}
Group by ?item 
order by desc(?count)
limit 10

Try it!

thickness -> horizontal depth[edit]

SELECT ?item ?itemLabel ?prop ?propLabel 
WHERE
{
	?item p:P793 [ ?pq ?value; wikibase:rank ?rank ] .
	?prop wikibase:qualifier ?pq .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
LIMIT 100

Try it!

SELECT ?item ?typeLabel ?height ?width ?thick ?heightUnitLabel ?widthUnitLabel ?thickUnitLabel WHERE {
  ?item wdt:P31  ?type .
  VALUES ?type { wd:Q179700 wd:Q220659  wd:Q16738862 wd:Q179700 wd:Q26997384  wd:Q48634 wd:Q193475} 
  ?item p:P2048/psv:P2048 ?heightD.
  ?item p:P2049/psv:P2049 ?widthD.
  ?item p:P2610/psv:P2610 ?thickD.
  ?heightD wikibase:quantityAmount ?height.
  ?heightD wikibase:quantityUnit   ?heightUnit.
  ?widthD  wikibase:quantityAmount ?width.
  ?widthD  wikibase:quantityUnit   ?widthUnit.
  ?thickD  wikibase:quantityAmount ?thick.
  ?thickD  wikibase:quantityUnit   ?thickUnit.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

copyright related[edit]

PD works without qualifiers[edit]

SELECT distinct ?item  ?itemLabel {
	?item p:P6216 ?statement .
	?statement ps:P6216 ?pd .                    # get P6216 main value
	VALUES ?pd { wd:Q19652 wd:Q15687061 }        # P6216 main value = public domain
  	FILTER NOT EXISTS { ?statement pq:P1001 [] } # exclude if P1001 present
 	FILTER NOT EXISTS { ?statement pq:P459  [] } # exclude if P459  present
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} LIMIT 100

Try it!

PD works without proper qualifiers whos creators died more than 100 years ago[edit]

SELECT distinct ?item  ?itemLabel {
	?item wdt:P6216 ?copyright .
	VALUES ?copyright { wd:Q19652 wd:Q15687061 }  # P6216 main value = public domain
	# Date of death of the last surviving creator
	{SELECT ?item  (max(?dod) as ?mdod) 
	where {
		?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287 ?creator .
		?creator wdt:P570 ?dod .
	}  group by ?item  }. 
	FILTER(YEAR(?mdod) < 1919)             # exclude if date of death < 1919
	FILTER NOT EXISTS { ?item p:P6216/pq:P1001 wd:Q60332278} # exclude if P1001 for USA present
	FILTER NOT EXISTS { ?item p:P6216/pq:P459  wd:Q29940705} # exclude if P459  for PD-1923 present
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} LIMIT 10

Try it!

PD works without proper qualifiers which were published more than 95 years ago[edit]

SELECT distinct ?item  ?itemLabel {
	?item wdt:P6216 ?copyright .
	VALUES ?copyright { wd:Q19652 wd:Q15687061 }  # P6216 main value = public domain
	?item wdt:P577 ?dop . 
	FILTER(YEAR(?dop) < 1924)              # exclude if date of publication < 1924
	FILTER NOT EXISTS { ?item p:P6216/pq:P1001 wd:Q30}        # exclude if P1001 for USA present
	FILTER NOT EXISTS { ?item p:P6216/pq:P459  wd:Q47246828 } # exclude if P459  for PD-1923 present
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} LIMIT 100

Try it!

PD works without proper qualifiers which were published more than 95 years ago and whose author died more than 100 years ago[edit]

SELECT distinct ?item ?dop ?mdod   ?itemLabel 
where { 
  # look for items of type "work" (or children)
  ?item wdt:P31/wdt:P279* wd:Q386724. 

  ?item wdt:P577 ?dop . # date of publication
  FILTER(?dop  < "1924-01-01"^^xsd:dateTime) .
  FILTER(?mdod < "1918-01-01"^^xsd:dateTime) .
  FILTER NOT EXISTS {?item wdt:P6216 [] } . 
  
  # Date of death of the last surviving creator
  {SELECT ?item  (max(?dod) as ?mdod) 
  where {
    ?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287 ?creator .
    ?creator wdt:P570 ?dod .
  }  group by ?item  }.
              
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

SELECT ?item ?itemLabel ?mdod with 
{ select DISTINCT ?item WHERE 
  {
    # look for items of type "work" or "data" (or children)
    ?item wdt:P31/wdt:P279* ?pq . 
    VALUES ?pq { wd:Q386724  wd:Q42848 } 
    # get date of publication and make sure is before 1924
    ?item wdt:P577 ?dop . 
    hint:Prior hint:rangeSafe true .
    FILTER (?dop < "1924-01-01T00:00:01"^^xsd:dateTime)
    FILTER NOT EXISTS { ?item wdt:P6216 [] }  # item does not have P6216 yet
  } } as %i with {
select ?item (max(?dod) as ?mdod) where  
{ # look through all the authors and other creators of the work and look up their date of death, than find the latest
  include %i  
  ?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287|wdt:P87|wdt:P98 ?creator .
  ?creator wdt:P570 ?dod .
} group by ?item  } as %j
where
{
  include %j
  hint:Prior hint:rangeSafe true .
  FILTER(?mdod < "1918-01-01T00:00:01"^^xsd:dateTime) . # make sure last creator date of death is before 1918
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

publications from before 1800 without copyright statement[edit]

SELECT DISTINCT ?item WHERE {
  # look for items of type "work" or "data" (or children)
  ?item wdt:P31/wdt:P279* ?pq . 
  VALUES ?pq { wd:Q386724  wd:Q42848 } 
  # get date of publication and make sure is old enough that all the creators are dead for more than 100 years.
  ?item wdt:P577 ?dop . 
  hint:Prior hint:rangeSafe true .
  FILTER (?dop < "1800-01-01T00:00:01"^^xsd:dateTime)
  FILTER NOT EXISTS { ?item wdt:P6216 [] }   # item does not have P6216 yet
}

Try it!

paintings from before 1800 without copyright statement[edit]

SELECT DISTINCT ?item WHERE {
  ?item wdt:P31 wd:Q3305213 .
  ?item wdt:P571 ?date . 
  hint:Prior hint:rangeSafe true .
  FILTER (?date < "1800-01-01T00:00:01"^^xsd:dateTime)
  FILTER NOT EXISTS { ?item wdt:P6216 [] }   # item does not have P6216 yet
}

Try it!

#SELECT DISTINCT  concat(?item, 'P6216|Q19652|P1001|Q60332278|P459|Q29940705| /* Based on ", ?author, "s date of death:", year(?dod), " */")
#SELECT DISTINCT  ?item  (concat(?authorLabel, str(YEAR(?dod), " */")) as ?comment)
SELECT DISTINCT  ?item  ?authorLabel (YEAR(?dod) as ?year)
WHERE {
  ?item wdt:P31 wd:Q3305213 .
  FILTER NOT EXISTS { ?item wdt:P6216 [] } .
  ?item wdt:P170 ?author .
  ?author wdt:P570 ?dod .
  FILTER(YEAR(?dod) < 1850)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Limit 20000

Try it!

Missing determination method (P459)[edit]

The following query uses these:

Items: public domain (Q19652)   , public domain (Q15687061)

Properties: copyright status (P6216)  

, applies to jurisdiction (P1001)  

, determination method (P459)  

SELECT distinct ?item ?itemLabel 
WHERE {
	?item p:P6216 ?statement .
	?statement ps:P6216 ?pd .                    # copyright status = public domain
	VALUES ?pd { wd:Q19652 wd:Q15687061 } 
	OPTIONAL {?statement pq:P1001 ?det .} .      # record jurisdiction if present
	FILTER NOT EXISTS { ?statement pq:P459 [] }  # item missing "determination method" statement
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
LIMIT 100

Try it!

Missing applies to jurisdiction (P1001)[edit]

The following query uses these:

Items: public domain (Q19652)   , public domain (Q15687061)

Properties: copyright status (P6216)  

, determination method (P459)  

, applies to jurisdiction (P1001)  

SELECT ?item ?itemLabel ?det ?detLabel 
WHERE {
	?item p:P6216 ?statement .
    ?statement ps:P6216 ?pd .                    # copyright status = public domain
    VALUES ?pd { wd:Q19652 wd:Q15687061 } 
	OPTIONAL {?statement pq:P459 ?det .} .
	FILTER NOT EXISTS { ?statement pq:P1001 [] } 
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} order by ?det
LIMIT 100

Try it!

license-template items[edit]

items related to licenses and associated topic's main template (P1424) properties

The following query uses these:

Items: Q7257715

Properties: instance of (P31)  

, subclass of (P279)  

, topic's main template (P1424)  

, full work available at URL (P953)  

, official website (P856)  

SELECT distinct ?item ?itemLabel ?temp ?tempLabel ?url1 ?url2 WHERE {
  ?item wdt:P31/wdt:P279* wd:Q7257715. 
  Optional { ?item wdt:P1424 ?temp. }
  Optional { ?item wdt:P953  ?url1. }
  Optional { ?item wdt:P856  ?url2. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel

Try it!

explore license to template connection[edit]

The following query uses these:

Items: Wikimedia copyright template (Q110010043)

Properties: instance of (P31)  

, topic's main template (P1424)  

SELECT ?licLabel (count(?item) as ?count) (min(?item) as ?example)
{
    ?item wdt:P31   ?lic .          
    ?item wdt:P1424 ?temp. 
    ?temp wdt:P31   wd:Q110010043 .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
} 
group by ?licLabel
order by desc(?count)

Try it!

Count unique P1001/P459 pairs for PD items[edit]

The following query uses these:

Items: public domain (Q19652)   , public domain (Q15687061)

Properties: copyright status (P6216)  

, applies to jurisdiction (P1001)  

, determination method (P459)  

SELECT ?jLabel ?dLabel ?num ?sampleitem with 
{
  SELECT distinct ?j ?d (count(*) as ?num) (SAMPLE(?item) as ?sampleitem) where
  {
    ?item p:P6216 ?statement .
    ?statement ps:P6216 ?pd .                    # copyright status = public domain
    ?statement pq:P1001 ?j .
    ?statement pq:P459  ?d .
    VALUES ?pd { wd:Q19652 wd:Q15687061 } 
    
} group by ?j ?d } as %i
where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
order by desc(?num)
Limit 100

Try it!

Find items where one of the authors is dead less than 100 years while claiming 100 year pma[edit]

SELECT distinct ?item  ?itemLabel ?creatorLabel ?dod{
	?item wdt:P6216 ?copyright .
	VALUES ?copyright { wd:Q19652 wd:Q15687061 }  # P6216 main value = public domain
	?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287|wdt:P98 ?creator .
	?creator wdt:P570 ?dod 
	FILTER(YEAR(?dod) > 1918)            # exclude if date of death < 1919
	?item p:P6216/pq:P1001 wd:Q60332278 .
	?item p:P6216/pq:P459  wd:Q29940705 .
	MINUS {?item p:P6216/pq:P518 []} .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} 
Order by ?creatorLabel
LIMIT 100

Try it!

Find items where one of the authors is dead less than 70/80/100 years while claiming 70/80/100 year pma[edit]

SELECT distinct ?item  ?itemLabel ?creatorLabel ?dod ?pmaDuration {
	?item wdt:P6216 ?copyright .
	VALUES ?copyright { wd:Q19652 wd:Q15687061 }  # P6216 main value = public domain
  
	?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287|wdt:P98 ?creator .
	?creator wdt:P570 ?dod 
	FILTER(YEAR(?dod) > 2019 - ?pmaDuration)            # exclude if date of death < 1919

	?item p:P6216/pq:P459 ?detmet # determination method 70, 80 or 100 pma
	VALUES ?detmet { wd:Q29870196 wd:Q29940641 wd:Q29940705 }  
	?detmet wdt:P2047 ?pmaDuration
          
	MINUS {?item p:P6216/pq:P518 []} .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} 
Order by ?creatorLabel
LIMIT 100

Try it!

SELECT distinct ?item  ?itemLabel ?creatorLabel ?dod ?pmaDuration {
	?item p:P6216 ?copyrightStatement .
	?copyrightStatement psv:P570 ?copyright
	VALUES ?copyright { wd:Q19652 wd:Q15687061 }  # P6216 main value = public domain
  
	?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287|wdt:P98 ?creator .
	?creator wdt:P570 ?dod 
	FILTER(YEAR(?dod) > 2019 - ?pmaDuration)            # exclude if date of death < 1919

	?copyrightStatement pq:P459 ?detmet # determination method 70, 80 or 100 pma
	VALUES ?detmet { wd:Q29870196 wd:Q29940641 wd:Q29940705 }  
	?detmet wdt:P2047 ?pmaDuration
          
	MINUS {?copyrightStatement pq:P518 []} .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} 
Order by ?creatorLabel
LIMIT 100

Try it!

Find items where one of the authors is dead less than 70 years while claiming 70 year pma[edit]

SELECT distinct ?item  ?itemLabel ?creatorLabel ?dod{
	?item wdt:P6216 ?copyright .
	VALUES ?copyright { wd:Q19652 wd:Q15687061 }  # P6216 main value = public domain
	?item wdt:P50|wdt:P170|wdt:P655|wdt:P84|wdt:P110|wdt:P287|wdt:P98 ?creator .
	?creator wdt:P570 ?dod 
	FILTER(YEAR(?dod) > 1948)            # exclude if date of death < 1919
	?item p:P6216/pq:P1001 wd:Q59542795 .
	?item p:P6216/pq:P459  wd:Q29870196 .
	MINUS {?item p:P6216/pq:P518 []} .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} 
Order by ?creatorLabel
LIMIT 100

Try it!

X[edit]

SELECT DISTINCT ?item, 'P6216', 'Q19652', 'P1001', 'Q60332278', 'P459', 'Q29940705', CONCAT("/* Based on [[", ?dod, "]] */")
WHERE {
  ?item wdt:P31 wd:Q3305213 .
  FILTER NOT EXISTS { ?item wdt:P6216 [] } .
  ?item wdt:P170/wdt:P570 ?dod .
  FILTER(YEAR(?dod) < 1850)
}
Limit 10

Try it!

Dured woodcuts[edit]

SELECT ?item  ?itemLabel ?catcode ?image ?cat WHERE { 
  ?item p:P528 [ pq:P972 wd:Q100320351; ps:P528 ?catcode] .
  ?item wdt:P18 ?image
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
  optional{   ?commonsSitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?cat . }
} order by ?catcode

Try it!

SELECT ?item  ?itemLabel ?catcode ?bartsch ?passavant ?CD ?Kurth ?image ?cat WHERE { 
  ?item p:P528 [ pq:P972 wd:Q100320351; ps:P528 ?catcode] .
  optional{ ?item p:P528 [ pq:P972 wd:Q18327311; ps:P528 ?bartsch] . }
  optional{?item p:P528 [ pq:P972 wd:Q101157511; ps:P528 ?passavant] . }
  optional{?item p:P528 [ pq:P972 wd:Q101181018; ps:P528 ?CD] . }
  optional{ ?item p:P528 [ pq:P972 wd:Q101542418; ps:P528 ?Kurth] . }
  ?item wdt:P18 ?image
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
  optional{   ?commonsSitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?cat . }
} order by ?catcode

Try it!

SELECT ?item ?itemLabel ?fisher ?bartsch ?passavant ?CD ?Kurth ?image ?cat 

WITH { 
  SELECT DISTINCT ?item WHERE {
    ?item wdt:P31 wd:Q18219090.
     {?item p:P528 [ pq:P972 wd:Q100320351; ps:P528 ?fisher] } UNION
     {?item p:P528 [ pq:P972 wd:Q18327311;  ps:P528 ?bartsch] . } UNION
     {?item p:P528 [ pq:P972 wd:Q101157511; ps:P528 ?passavant] . } UNION
     {?item p:P528 [ pq:P972 wd:Q101181018; ps:P528 ?CD] . } UNION
     {?item p:P528 [ pq:P972 wd:Q101542418; ps:P528 ?Kurth] . } 
  }            
} AS %items

WHERE { 
  INCLUDE %items .
  optional{?item p:P528 [ pq:P972 wd:Q100320351; ps:P528 ?fisher] . }
  optional{?item p:P528 [ pq:P972 wd:Q18327311;  ps:P528 ?bartsch] . }
  optional{?item p:P528 [ pq:P972 wd:Q101157511; ps:P528 ?passavant] . }
  optional{?item p:P528 [ pq:P972 wd:Q101181018; ps:P528 ?CD] . }
  optional{?item p:P528 [ pq:P972 wd:Q101542418; ps:P528 ?Kurth] . }
  optional{?item wdt:P18 ?image .}
  optional{?item wdt:P179 ?series .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
  #optional{   ?commonsSitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?cat . }
} ORDER BY ?Kurth ?fisher

Try it!

Append to descriptions of an item[edit]

SELECT ?QID ?Dlang ?desc3 
WHERE 
{
  VALUES ?item { wd:Q88190330 }
  ?item schema:description ?description .
  ?item wdt:P217 ?inv .
  ?item wdt:P571 ?date . 
  ?item wdt:P195 ?gallery . 
  bind(str(YEAR(?date)) as ?year) 
  BIND(LANG(?description) as ?lang)
  
   OPTIONAL {
     ?gallery rdfs:label ?lab 
     FILTER((LANG(?lab)) = ?lang)
   }

  bind(strafter(str(?item),"http://www.wikidata.org/entity/")as ?QID)
  bind(concat("D",?lang) as ?Dlang)
  bind(concat('\"',?description," (",?inv,')\"') as ?desc1)
  bind(concat('\"',?description,", ",?year,'\"') as ?desc2)
  bind(concat('\"',?description," (",?lab, ' ', ?inv,')\"') as ?desc3)
  
}

Try it!

User:Jarekt/queries

My items without P31[edit]

Taxon related[edit]

harvest BugGuideID[edit]

bad BugGuideID[edit]

look for candidates[edit]

find BugGuideID candidates[edit]

find BugGuideID candidates based on name[edit]

find BugGuideID candidates based on label[edit]

Taxons without commonscat[edit]

Taxon with sitelink to Commons but without P373.[edit]

Taxon with a file but without P373.[edit]

Related to categories[edit]

extra P373[edit]

missing P373[edit]

Labels ≠ Sitelink[edit]

Number of Sitelink per project (not working)[edit]

Sitelinks to old wikisource[edit]

non-unique P373[edit]

Looking for items where category saved in sitelink to Commons does not match P373[edit]

videos as images[edit]

P18s from commons category[edit]

People related[edit]

City of birth[edit]

Query to keep Commons Creator page (P1472) property and Commons creator templates in synch[edit]

Creator Home categories used be multiple items[edit]

Look up if an item is a redirect and where is it pointing[edit]

Items that have Commons Creator page (P1472) but do not have English label[edit]

frequency of nationalities or ethnic groups among Creators[edit]

Date related[edit]

qualifiers[edit]

Example of date using Millennium precision[edit]

items with P1472 and P650 that have year DOB[edit]

multi precision dates query[edit]

Multicalendar DOB[edit]

Multicalendar DOB[edit]

find burial date based on place of burial (P119)[edit]

find burial/baptism date based on significant event (P793)[edit]

Coordinate related[edit]

Commons files with multiple locations[edit]

Correct coordinate location precision[edit]

Institutions with double locations[edit]

What is Lipovica's current country?[edit]

Related to artworks[edit]

Get Item, collection and inventory ID[edit]

paintings without image but with a sitelink to commons[edit]

paintings pairs using the same image[edit]

explore significant event[edit]

get labels and descriptions[edit]

get Witkacy artworks with labels starting with '"'[edit]

=PD-Art images[edit]

Redirects[edit]

Property exploration[edit]

value frequency[edit]

histogram of use[edit]

item with the most properties of some kind[edit]

thickness -> horizontal depth[edit]

copyright related[edit]

PD works without qualifiers[edit]

PD works without proper qualifiers whos creators died more than 100 years ago[edit]

PD works without proper qualifiers which were published more than 95 years ago[edit]

PD works without proper qualifiers which were published more than 95 years ago and whose author died more than 100 years ago[edit]

publications from before 1800 without copyright statement[edit]

paintings from before 1800 without copyright statement[edit]

Missing determination method (P459)[edit]

Missing applies to jurisdiction (P1001)[edit]

license-template items[edit]

explore license to template connection[edit]

Count unique P1001/P459 pairs for PD items[edit]

Find items where one of the authors is dead less than 100 years while claiming 100 year pma[edit]

Find items where one of the authors is dead less than 70/80/100 years while claiming 70/80/100 year pma[edit]

Find items where one of the authors is dead less than 70 years while claiming 70 year pma[edit]

X[edit]

Dured woodcuts[edit]

Append to descriptions of an item[edit]

Navigation menu

Search