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

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

harvest BugGuideID

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

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

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

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

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

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

Taxon with sitelink to Commons but without P373.

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.

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

extra P373

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

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

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)

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

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

Try it!

non-unique P373

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

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

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

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

City of birth

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

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

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

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

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

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

qualifiers

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

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

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

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

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

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)

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)

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

Commons files with multiple locations

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

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

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?

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

Get Item, collection and inventory ID

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

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

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

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

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 '"'

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

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

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

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

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

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

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

Try it!

thickness -> horizontal depth

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

PD works without qualifiers

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

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

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

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

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

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 or standard (P459)

The following query uses these:

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

Properties: copyright status (P6216)  

, applies to jurisdiction (P1001)  

, determination method or standard (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)

The following query uses these:

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

Properties: copyright status (P6216)  

, determination method or standard (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

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

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

The following query uses these:

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

Properties: copyright status (P6216)  

, applies to jurisdiction (P1001)  

, determination method or standard (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

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

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

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

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

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

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

Taxon related

harvest BugGuideID

bad BugGuideID

look for candidates

find BugGuideID candidates

find BugGuideID candidates based on name

find BugGuideID candidates based on label

Taxons without commonscat

Taxon with sitelink to Commons but without P373.

Taxon with a file but without P373.

Related to categories

extra P373

missing P373

Labels ≠ Sitelink

Number of Sitelink per project (not working)

Sitelinks to old wikisource

non-unique P373

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

videos as images

P18s from commons category

People related

City of birth

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

Creator Home categories used be multiple items

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

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

frequency of nationalities or ethnic groups among Creators

Date related

qualifiers

Example of date using Millennium precision

items with P1472 and P650 that have year DOB

multi precision dates query

Multicalendar DOB

Multicalendar DOB

find burial date based on place of burial (P119)

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

Coordinate related

Commons files with multiple locations

Correct coordinate location precision

Institutions with double locations

What is Lipovica's current country?

Related to artworks

Get Item, collection and inventory ID

paintings without image but with a sitelink to commons

paintings pairs using the same image

explore significant event

get labels and descriptions

get Witkacy artworks with labels starting with '"'

=PD-Art images

Redirects

Property exploration

value frequency

histogram of use

item with the most properties of some kind

thickness -> horizontal depth

copyright related

PD works without qualifiers

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

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

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

publications from before 1800 without copyright statement

paintings from before 1800 without copyright statement

Missing determination method or standard (P459)

Missing applies to jurisdiction (P1001)

license-template items

explore license to template connection

Count unique P1001/P459 pairs for PD items

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

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

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

X

Dured woodcuts

Append to descriptions of an item

Navigation menu

Search