User:Jneubert/Special queries

It seems that some queries run into timeouts via REST, but can be executed via the GUI.

Count qualifiers of a property

SELECT ?qual ?qualLabel (COUNT(DISTINCT ?item) AS ?count) WHERE {

     hint:Query hint:optimizer "None" .
     ?item p:P214 ?statement .     # for example, VIAF ID
     ?statement ?pq_qual ?pq_obj . # a triple involving the qualified statement ...     
     ?qual wikibase:qualifier ?pq_qual . # ... with a predicate ?pq_pred that is a qualifier

     SERVICE wikibase:label {
         bd:serviceParam wikibase:language "en" .
     }

}  GROUP BY ?qual ?qualLabel
ORDER BY DESC (?count)

Try it!

Reports for the maintenance of vocabulary mappings

For vocabulary mappings to Wikidata which use the mapping relation type (P4390) qualifier, the regular constraint reports do not cover the various occurring cases well. The "single value" and "different values" constraint reports, in particular, imply 1:1 relationships, while with qualified relations, n:m relationships may occur and deserve special attention.

Meaningful reports have to include labels and thus require information both from WDQS and a SPARQL endpoint for the linked external vocabulary. Federated queries from WDQS are limited to a list of named endpoints. Therefore, a general solution currently seems not to be achievable. For a particular property with mandatory P4390 qualifier, STW Thesaurus for Economics ID (P3911), there are example reports, using a custom endpoint.

Report	load query and execute
Full mapping, with relation types	wikidata_mapping
Single value violations (multiple exact matches from an item)	exact_single_violations
Distinct values violations (multiple exact matches to different items)	exact_distinct_violations
Multiple ID values per item (which may or may not indicate an error)	multiple_id_values
Same ID value in different items (which may or may not indicate an error)	same_id_value
All non-exact relationships, with a link to search Wikidata for possible newly introduced better matches	inexact_wikidata_mapping

"Overlay" Wikidata items with skos:broader relations imported from STW

select ?wd ?wdLabel ?wdBroader ?wdBroaderLabel
where {
  # seems to do not work with query optimization
  hint:Query hint:optimizer "None" .

  # sparql endpoint of external thesaurus
  service <http://zbw.eu/beta/sparql/stw/query> {
    select ?stwId ?stwBroaderId
    where {
      ?stw skos:broader ?stwBroader .
      filter(strstarts(str(?stw), 'http://zbw.eu/stw/descriptor/'))
      filter(strstarts(str(?stwBroader), 'http://zbw.eu/stw/descriptor/'))
      bind(strafter(str(?stw), 'http://zbw.eu/stw/descriptor/') as ?stwId)
      bind(strafter(str(?stwBroader), 'http://zbw.eu/stw/descriptor/') as ?stwBroaderId)
    }  
  }
  ?wd wdt:P3911 ?stwId .
  ?wdBroader wdt:P3911 ?stwBroaderId .
  service wikibase:label {
    bd:serviceParam wikibase:language "en" .
  }
}

Try it!

List of all economists in PM20

select ?item ?itemLabel ?pm20
where {
  
  # items with PM20 id
  ?item wdt:P4293 ?pm20Id ;
        wdt:P106 ?occupation .
  
  # occupation: economist or business economist
  filter(?occupation in (wd:Q188094, wd:Q1860032))

  # restrict to items with online accessible articles
  ?item p:P4293/pq:P5592 ?workCount .
  filter(?workCount > 0)

  # add folder link
  bind(uri(concat('http://purl.org/pressemappe20/folder/', ?pm20Id)) as ?pm20)
  
  # add labels
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en, de, fr, es, nl, pl, ru". }
}

Try it!

Map of economists in PM20 by place of birth

#defaultView:Map{"hide":"?geo"}
select ?image ?item ?itemLabel ?pobLabel ?viewer ?geo
where {
  ?item wdt:P106 ?occupation ;
        # with PM20 id
        wdt:P4293 ?pm20Id ;
        # with place of birth
        wdt:P19 ?pob .
  # occupation: economist or business economist
  filter(?occupation in (wd:Q188094, wd:Q1860032))
  # restrict to items with online accessible articles
  ?item p:P4293/pq:P5592 ?workCount .
  filter(?workCount > 0)
  # geo coordinates for pob
  ?pob wdt:P625 ?geo .
  # optional image
  optional { ?item wdt:P18 ?image . }
  # viewer link
  bind(substr(?pm20Id, 4, 4) as ?numStub)
  bind(substr(?pm20Id, 4, 6) as ?num)
  bind(uri(concat('http://dfg-viewer.de/show/?tx_dlf[id]=http://zbw.eu/beta/pm20mets/pe/', ?numStub, 'xx/', ?num, '.xml')) as ?viewer)
  # add labels
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en, de, fr, es, nl, pl, ru". }
}

Try it!

Update: Restricted to folders with online available articles. Jneubert (talk) 06:52, 7 August 2018 (UTC) Update: Include business economist (Q1860032). Jneubert (talk) 12:44, 14 June 2019 (UTC)

PM20 type "co/" which are not an organization

select ?item ?itemLabel
where {
  ?item wdt:P4293 ?pm20Id .
  filter(regex(?pm20Id, "^co/"))
  filter (not exists {
      ?item p:P31/ps:P31/wdt:P279* wd:Q43229 .
    })
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE,de,en,es,fr]". }
}

Try it!

Extended version: No mapping relation, instance-of, no organization

select ?item ?itemLabel
where {
  # pm20 id starting with "co/"
  ?item wdt:P4293 ?pm20Id .
  filter(regex(?pm20Id, "^co/"))
  # existing instance-of relationship
  filter (exists {
    ?item wdt:P31 [] .
  })
  # not an instance of (subclass of) organization
  filter (not exists {
      ?item p:P31/ps:P31/wdt:P279* wd:Q43229 .
    })
  # exclude items with mapping relations
  filter (not exists {
    ?item p:P4293/pq:P4390 ?relation .
    })
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE,de,en,es,fr]". }
}

Try it!

Economists with ISNI without GND

select ?item ?itemLabel ?value
where {
  ?item wdt:P106 wd:Q188094 .
  ?item wdt:P213 ?value .
  filter (
    not exists {
      ?item wdt:P227 [] .
    }
  )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en"  }    
}

Try it!

Wikidata properties mapped to schema.org

select ?wdProperty ?wdPropertyLabel ?target
where {
  
  # get all properties
  ?wdProperty rdf:type wikibase:Property .

  # with exactly matching external URIs
  ?wdProperty wdt:P2888 ?target .
  
  # restrict to schema.org
  filter(strstarts(str(?target), 'https://schema.org/'))
  
  # get wikidata labels
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!