User:Jarekt/queries

From Wikidata
Jump to navigation Jump to search
Scratch pad with SPARQL I want to keep or find again.

The following query uses these:

The following query uses these:

  • Properties: creator (P170) View with Reasonator View with SQID, author (P50) View with Reasonator View with SQID
     1 SELECT ?item ?creatorLabel ?authorLabel
     2 { 
     3   VALUES ?item { 
     4 
     5 wd:Q956541
     6 wd:Q9639014
     7 
     8   } . 
     9   optional{?item wdt:P170 ?creator }.
    10   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
    11   optional{?item wdt:P50 ?author } .
    

}

Contents

Taxon related[edit]

harvest BugGuideID[edit]

The following query uses these:

bad BugGuideID[edit]

The following query uses these:

  • Properties: BugGuide ID (P2464) View with Reasonator View with SQID, parent taxon (P171) View with Reasonator View with SQID, taxon rank (P105) View with Reasonator View with SQID
    1 SELECT ?item ?BugGuideID 
    2 {
    3     ?item wdt:P2464 ?BugGuideID .      # has BugGuideID 
    4     ?item wdt:P171  ?pItem .           # has parent item
    5     MINUS {?item wdt:P171+ wd:Q1360. } # exclude items in Arthropoda tree
    6     MINUS {?item wdt:P105 wd:Q38348. } # exclude Arthropoda (Q1360) itself
    7 }
    
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:

  • Properties: LepIndex ID (P3064) View with Reasonator View with SQID, taxon name (P225) View with Reasonator View with SQID, taxon rank (P105) View with Reasonator View with SQID, BugGuide ID (P2464) View with Reasonator View with SQID
    1 SELECT DISTINCT ?taxon ?item ?rankLabel
    2 {
    3 	?item wdt:P3064 ?value .
    4 	?item wdt:P225  ?taxon .       # taxon name
    5     ?item wdt:P105  ?rank .
    6     BIND ( wd:Q34740 as ?rank).
    7 	MINUS { ?item wdt:P2464 [] } . # BugGuideID 
    8     SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
    9 }
    

find BugGuideID candidates based on name[edit]

The following query uses these:

  • Properties: taxon name (P225) View with Reasonator View with SQID, taxon rank (P105) View with Reasonator View with SQID
     1 SELECT  ?taxon ?item ?rank WHERE {
     2 	?item wdt:P225  ?taxon .       # taxon name
     3     ?item wdt:P105  ?rank .
     4 
     5   VALUES ?taxon {"Glycyphagoidea"
     6 "Acaroidea"
     7 "Hemisarcoptoidea"
     8 } .
     9 
    10 }
    

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) View with Reasonator View with SQID, Commons category (P373) View with Reasonator View with SQID, Commons gallery (P935) View with Reasonator View with SQID
     1 SELECT ?item ?article
     2 WHERE
     3 {
     4     ?item wdt:P225 ?taxonName .
     5     OPTIONAL {?item wdt:P373 ?commonscat} . 
     6     #OPTIONAL {?item wdt:P935 ?commonsgal} .   
     7     ?article schema:about ?item .
     8     ?article schema:isPartOf <https://commons.wikimedia.org/> .
     9     FILTER (STRSTARTS(STR(?article), "https://commons.wikimedia.org/wiki/Category"))
    10     FILTER (!bound(?commonscat))
    11 }
    

Taxon with a file but without P373.[edit]

The following query uses these:

Related to categories[edit]

extra P373[edit]

The following query uses these:

  • Properties: Commons category (P373) View with Reasonator View with SQID, topic's main category (P910) View with Reasonator View with SQID, category's main topic (P301) View with Reasonator View with SQID
     1 SELECT DISTINCT ?categoryItem WHERE {
     2     # ?article and ?category share the same P373 ("Commons Category")
     3     ?articleItem  wdt:P373 ?commonsCategory .
     4     ?categoryItem wdt:P373 ?commonsCategory .
     5   
     6     # P301 and P910 are set properly
     7     ?articleItem  wdt:P910 ?categoryItem .
     8     ?categoryItem wdt:P301 ?articleItem  .
     9   
    10     # sitelink to Commons points to the same category as property P373
    11     ?commonsSitelink schema:about ?categoryItem .
    12     FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category"))
    13     FILTER(STRENDS(STR(?commonsSitelink), ENCODE_FOR_URI(?commonsCategory)))
    14 } limit 100
    

missing P373[edit]

The following query uses these:

  • Properties: Commons category (P373) View with Reasonator View with SQID, instance of (P31) View with Reasonator View with SQID
    1 SELECT (Count(?item) as ?count) WHERE {
    2     # ?article and ?category share the same P373 ("Commons Category")
    3     MINUS{ ?item  wdt:P373 ?commonsCategory . }
    4     MINUS{ ?item wdt:P31 wd:Q4167836 . }
    5   
    6     # sitelink to Commons points to the same category as property P373
    7     ?commonsSitelink schema:about ?item .
    8     FILTER(STRSTARTS(STR(?commonsSitelink), "https://commons.wikimedia.org/wiki/Category"))
    9 }
    

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!

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!

People related[edit]

City of birth[edit]

The following query uses these:

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) View with Reasonator View with SQID
     1 SELECT (IRI(concat("https://commons.wikimedia.org/wiki/", ?template)) as ?templateLink) ?templateName ?creatorItem ?creatorItemLabel {
     2   SERVICE wikibase:mwapi {
     3 	 bd:serviceParam wikibase:api "Generator" .
     4      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     5      bd:serviceParam mwapi:gcmtitle "Category:Creator templates without Wikidata link" .
     6      bd:serviceParam mwapi:generator "categorymembers" .
     7      bd:serviceParam mwapi:gcmtype "page" .
     8      bd:serviceParam mwapi:gcmlimit "max" .
     9      bd:serviceParam mwapi:gcmsort "timestamp" .
    10      bd:serviceParam mwapi:gcmdir "descending" .
    11      ?template wikibase:apiOutput mwapi:title  .
    12   }
    13   BIND(substr(?template,9) as ?templateName) .
    14   OPTIONAL { 
    15     ?creatorItem wdt:P1472 ?templateName .
    16     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
    17   }
    18   FILTER ( BOUND(?creatorItem) ) .
    19 }
    
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:
  • Properties: Commons category (P373) View with Reasonator View with SQID
     1 SELECT (IRI(concat("https://commons.wikimedia.org/wiki/", ?creatorTemplate)) as ?creatorLink) ?creatorName ?categoryName ?commonsCatItem ?commonsCatItemLabel {
     2   SERVICE wikibase:mwapi { # list of all creator templates without Wikidata link
     3      bd:serviceParam wikibase:api "Generator" .
     4      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     5      bd:serviceParam mwapi:gcmtitle "Category:Creator templates without Wikidata link" .
     6      bd:serviceParam mwapi:generator "categorymembers" .
     7      bd:serviceParam mwapi:gcmtype "page" .
     8      bd:serviceParam mwapi:gcmlimit "max" .
     9      bd:serviceParam mwapi:gcmsort "timestamp" .
    10      bd:serviceParam mwapi:gcmdir "descending" .
    11      ?creatorTemplate wikibase:apiOutput mwapi:title  .
    12   }
    13   hint:Prior hint:runFirst 1 . 
    14   SERVICE wikibase:mwapi { # get home category
    15      bd:serviceParam wikibase:api "Categories" .
    16      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
    17      bd:serviceParam mwapi:titles ?creatorTemplate .
    18      bd:serviceParam mwapi:clshow "!hidden" .
    19      ?category wikibase:apiOutput mwapi:category  .
    20   }
    21   BIND(substr(?creatorTemplate,9) as ?creatorName ) .
    22   BIND(substr(?category,10)       as ?categoryName) .
    23   OPTIONAL { 
    24     ?commonsCatItem wdt:P373 ?categoryName . # category is linked from Wikidata
    25     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
    26   }
    27   FILTER ( BOUND(?commonsCatItem) ) .
    28   FILTER ( ?commonsCatItem!=wd:Q24731821 ) .
    29 }
    
The following query uses these:
  • Properties: Commons category (P373) View with Reasonator View with SQID
     1 SELECT (IRI(concat("https://commons.wikimedia.org/wiki/", ?homeCategory)) as ?homeCategoryLink) ?homeCategory ?commonsCatItem ?commonsCatItemLabel {
     2   SERVICE wikibase:mwapi { # list of all creator templates without Wikidata link
     3      bd:serviceParam wikibase:api "Generator" .
     4      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     5      bd:serviceParam mwapi:gcmtitle "Category:Creator template home categories without Wikidata link" .
     6      bd:serviceParam mwapi:generator "categorymembers" .
     7      bd:serviceParam mwapi:gcmtype "page" .
     8      bd:serviceParam mwapi:gcmlimit "max" .
     9      bd:serviceParam mwapi:gcmsort "timestamp" .
    10      bd:serviceParam mwapi:gcmdir "descending" .
    11      ?homeCategory wikibase:apiOutput mwapi:title  .
    12   }
    13 
    14   BIND(substr(?homeCategory,10) as ?categoryName) .
    15   OPTIONAL { 
    16     ?commonsCatItem wdt:P373 ?categoryName . # category is linked from Wikidata
    17     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
    18   }
    19   FILTER ( BOUND(?commonsCatItem) ) .
    20 }
    

Creator Home categories used be multiple items[edit]

The following query uses these:

  • Properties: Commons Creator page (P1472) View with Reasonator View with SQID, Commons category (P373) View with Reasonator View with SQID, instance of (P31) View with Reasonator View with SQID
    1 SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label ?value
    2 {
    3     ?item1 wdt:P1472 [] .
    4 	?item1 wdt:P373 ?value .
    5 	?item2 wdt:P373 ?value .
    6 	FILTER(?item1 != ?item2) .
    7     MINUS{ ?item2 wdt:P31 wd:Q4167836 . }
    8 	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" } .
    9 } LIMIT 500
    

The following query uses these:

  • Properties: Commons Creator page (P1472) View with Reasonator View with SQID, Commons category (P373) View with Reasonator View with SQID, date of birth (P569) View with Reasonator View with SQID, date of death (P570) View with Reasonator View with SQID, instance of (P31) View with Reasonator View with SQID
     1 SELECT DISTINCT ?item1 ?item1Label ?dob1 ?dod1 ?item2 ?item2Label ?dob2 ?dod2 ?value
     2 {
     3     ?item1 wdt:P1472 [] .
     4 	?item1 wdt:P373 ?value .
     5 	?item2 wdt:P373 ?value .
     6     OPTIONAL {?item1 wdt:P569 ?dob1} .
     7     OPTIONAL {?item1 wdt:P570 ?dod1} .
     8     OPTIONAL {?item2 wdt:P569 ?dob2} .
     9     OPTIONAL {?item2 wdt:P570 ?dod2} .
    10     FILTER(?item1 != ?item2) .
    11     MINUS{ ?item2 wdt:P31 wd:Q4167836 . }
    12 	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" } .
    13 } LIMIT 500
    

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

The following query uses these:

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]

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!

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!