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
[edit]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)
Reports for the maintenance of vocabulary mappings
[edit]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
[edit]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" .
}
}
List of all economists in PM20
[edit]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". }
}
Map of economists in PM20 by place of birth
[edit]#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". }
}
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
[edit]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]". }
}
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]". }
}
Economists with ISNI without GND
[edit]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" }
}
Wikidata properties mapped to schema.org
[edit]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". }
}