Project:SPARQL/admin: Difference between revisions
From Beyond Notability
(moved admin/QC queries into a new page) |
|||
Line 597: | Line 597: | ||
} | } | ||
order by ?byLabel ?personLabel ?date ?degreeLabel | order by ?byLabel ?personLabel ?date ?degreeLabel | ||
</sparql> | |||
==== More potential problems with dates ==== | |||
<sparql tryit="1"> | |||
#title:academic degree Oxford/Cambridge potential date issues (again) | |||
# looking for cases where there might be a date problem but there isn't a queryable date | |||
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/> | |||
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/> | |||
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/> | |||
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/> | |||
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/> | |||
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/> | |||
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/> | |||
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/> | |||
SELECT distinct ?personLabel ?degreeLabel ?note ?byLabel ?link | |||
WHERE { | |||
?person bnwdt:P3 bnwd:Q3 . #select women | |||
# academic degree = P59. | |||
?person bnp:P59 ?s . | |||
?s bnps:P59 ?degree . # type of degree | |||
# optional qualifiers for degree | |||
# P60 subject | |||
# optional { ?s bnpq:P60 ?subject} | |||
# P61 conferred by | |||
?s bnpq:P61 ?by . | |||
filter ( ?by=bnwd:Q1181 || ?by=bnwd:Q364 ). | |||
# date | |||
optional { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date . } | |||
filter not exists { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date .} | |||
?s bnpq:P47 ?note . | |||
# direct link to relevant statement | |||
BIND (REPLACE(STR(?s), "^.*/([^/]*)$", "$1") as ?sid). | |||
# for some grrr reason the first dash is replaced with a $ in the html id. | |||
bind(replace(?sid, "^(Q\\d+)-", "$1\\$") as ?pid) . | |||
bind(iri(concat(str(?person), "#", ?pid)) as ?link ) | |||
SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en".} | |||
} | |||
order by ?personLabel ?date ?degreeLabel | |||
</sparql> | </sparql> | ||
Revision as of 13:24, 8 April 2024
Admin
Women about whom we have recorded fewer than 3 pieces of information
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT ?person ?personLabel ?statements WHERE {
?person bnwdt:P3 bnwd:Q3 ;
wikibase:statements ?statements .
FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .}
FILTER (?statements <3)
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb".
}
}
People for whom their item records is given as 'Mrs'
SELECT ?id ?name
WHERE {
?id rdfs:label ?name .
FILTER regex(?name, "mrs *", "i") #this line uses regular expression syntax, described at https://regexper.com/#mrs%5Cs*
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
All triples in the wiki, limited to 1000
SELECT ?a ?aLabel ?b ?c WHERE {
?a ?b ?c
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
LIMIT 1000 #please don't make this number too big as it will slow down the site!
All triples in the wiki, represented as a crude graph
#defaultView:Graph
SELECT ?a ?aLabel ?c ?cLabel WHERE {
?a ?b ?c
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
#note query returns every connection in the wikibase so it may fall over!
People whose assigned gender we have been unable to determine and/or confirm
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
SELECT ?person ?personLabel
WHERE {
?person bnwdt:P3 ?gender .
FILTER NOT EXISTS {?person bnwdt:P3 bnwd:Q3 .} #filter out people identified as women
FILTER NOT EXISTS {?person bnwdt:P3 bnwd:Q10 .} #filter out people identified as men
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
ORDER BY ?personLabel
People who have a spouse statement and - optionally - marriage date statements for checking if there is any misalignment with 'Spouse not in wikibase' statements
# people who have a spouse statement and - optionally - marriage date statements for checking if there is any misalignment with 'Spouse not in wikibase' statements
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT ?person ?personLabel ?spouse ?spouseLabel ?edtfmarriagespouse ?edtfmarriagespouseLabel ?PITmarriagespouse ?PITmarriagespouseLabel WHERE {
?person bnwdt:P41 ?spouse .
OPTIONAL {?person bnp:P132 ?edtfmarriagestatement .
?edtfmarriagestatement bnps:P132 ?edtfmarriagedate .
?edtfmarriagestatement bnpq:P41 ?edtfmarriagespouse .}
OPTIONAL {?person bnp:P130 ?PITmarriagestatement .
?PITmarriagestatement bnps:P130 ?PITmarriagedate .
?PITmarriagestatement bnpq:P41 ?PITmarriagespouse .}
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb".
}
}
ORDER BY DESC(?edtfmarriagespouseLabel) DESC(?PITmarriagespouseLabel)
Compare uses of item v free text
# A number of properties have "item" and "free text" versions; a query to fetches both versions in each case to facilitate comparison of their uses.
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT distinct ?itemLabel ?prop_label ?qual_label ?qual_value ?qual_valueLabel ?item ?qual_prop
# most of these are used in qualifiers - only P72, P71 and P88 found in main (and only a handful of uses of free text?)
#?item ( bnp:P88|bnp:P91 | bnp:P72|bnp:P71) ?s .
## the pairs
#P21 evidence (free text) / P20 evidence (item)
#P79 item exhibited / P80 item exhibited (free text)
#P78 of / P66 of (free text)
#P91 specific reference information / P88 specific reference information (free text)
#P31 street address (free text) / P100 street address (item)
#P72 was delegate at (free text) / P71 was delegate at (item)
WHERE {
?item ?p ?s .
?prop wikibase:claim ?p;
rdfs:label ?prop_label. filter(lang(?prop_label)="en-gb") .
?s ( bnpq:P21|bnpq:P20 | bnpq:P80|bnpq:P79 | bnpq:P66|bnpq:P78 | bnpq:P88|bnpq:P91 | bnpq:P31|bnpq:P100 | bnpq:P72|bnpq:P71 ) ?qual_value .
?s ?qual_p ?qual_value .
?qual_prop wikibase:qualifier ?qual_p;
wikibase:propertyType ?qual_prop_type ;
rdfs:label ?qual_label. filter(lang(?qual_label)='en-gb') .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
order by ?qual_label ?prop_label ?itemLabel
Missing language labels
# query to check for any items/properties which don't have "en" labels. [currently not an issue but retained for reference.]
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT
?item ?itemLabel
(group_concat(distinct ?language) as ?languages)
(count(distinct ?rdfs_label) as ?count) # TIL: where you put distinct matters!
WHERE {
?item ?p ?s.
?item rdfs:label ?rdfs_label . # rdfs item label (1 per language).
BIND(LANG(?rdfs_label) as ?language) . # get 2 letter language codes
#optional { ?item rdfs:label ?engb . filter(lang(?engb)="en-gb"). }
#optional { ?item rdfs:label ?en . filter(lang(?en)="en"). }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en-gb, en". }
}
group by ?item ?itemLabel
HAVING (?count<2)
order by ?item
Statements that contain both a date *and* an unknown value date
# A query to look for statements containing an <unknown value> date *and* a date
# caveat: not yet completely sure if it will work in all cases for main dates
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT ?item ?itemLabel ?main_label ?qualLabel ?subLabel
(group_concat(distinct ?date_prop) as ?dates)
(count (distinct(isBLANK(?t))) as ?tcount)
?s
# using isBlank() in select creates true/false, then count distinct finds any in a group having >1.
WHERE
{
{
# qualifiers. [qualifiers need to include ?s in the group by]
?item ?p ?s.
# labels
?s ?subp ?sub .
?qual wikibase:claim ?p;
wikibase:statementProperty ?subp.
?s ?pq ?t .
?date_prop wikibase:qualifier ?pq ;
wikibase:propertyType ?date_prop_type .
filter(?date_prop_type in (wikibase:Time, wikibase:Edtf) ).
}
UNION
{
# main [excluding ?s from group by this time]
?item ?p ?sm.
?sm ?ps ?t .
?date_prop wikibase:claim ?p.
?date_prop wikibase:statementProperty ?ps.
?date_prop wikibase:propertyType ?date_prop_type .
?date_prop rdfs:label ?main_label . filter(lang(?main_label)="en-gb") .
filter(?date_prop_type in (wikibase:Time, wikibase:Edtf) ).
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en_gb" }
}
group by ?item ?itemLabel ?s ?qualLabel ?subLabel ?main_label
having (?tcount >1 ) # comment out to get everything
ORDER BY ?itemLabel ?main_label ?qualLabel ?subLabel ?date_prop
Women with no dates
# Query for women with no dates
# WIP: at present this includes <unknown value> dates as well as no dates at all [eg Miss Keyser Q2622]
# CAVEAT: the WQS bug means there are likely to be some women in results who do, in fact, have at least one date.
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT distinct ?person ?personLabel (count(?date_value) as ?count)
WHERE {
{ # main dates
?person bnwdt:P3 bnwd:Q3 . # women
optional {
?person ?p ?s .
?s ?date_prop ?wdv .
?wdv wikibase:timeValue ?date_value . }
} # /main dates
UNION
{ # qualifier dates
?person bnwdt:P3 bnwd:Q3 .
optional {
?person ?p ?s .
?s ?date_prop ?pqv .
?pqv wikibase:timeValue ?date_value. }
} # /qual dates
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". }
} # /where
group by ?person ?personLabel
having (?count=0)
ORDER BY ?person
Women with more than X statements but no date of birth/death
#title:women with n>X statements but no date of birth/death
# CAVEAT this currently finds some women who do in fact have dates, because of WQS bug
# updated to add extra info
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?person ?personLabel ?statements ?dob ?dod
(if(bound(?SALproposed), "yes", "") as ?FSA)
(if(bound(?RAIproposed), "yes", "") as ?RAI)
(if(bound(?resided), "yes", "") as ?residence)
(if(bound(?main_date), "yes", "") as ?other_maindate)
(if(bound(?qual_d), "yes", "") as ?qual_date)
WHERE {
?person bnwdt:P3 bnwd:Q3 ;
wikibase:statements ?statements .
FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .}
optional { ?person bnwdt:P15 ?dod . }
optional { ?person bnwdt:P26 ?dob . }
optional { # was elected FSA. comment out optional lines to get FSAs only...
?person bnp:P16 ?s .
?s bnps:P16 ?SALproposed .
?s bnpq:P22 bnwd:Q36 . # successful Q36. will you end up with 2 rows if there's a successful and unsuccessful?
} #/optional fsa
optional { # was elected RAI. only one unsuccessful, so just ignore that bit.
?person bnwdt:P7 ?RAIproposed .
}
optional { ?person bnwdt:P29 ?resided . } # place of residence
optional {
# main dates, apart from birth and death: P133 (widowed) P132 (married edtf) P131 (had child) P130 (married pit)
?person ( bnwdt:P130 | bnwdt:P131 | bnwdt:P132 | bnwdt:P133 ) ?main_date .
} # /optional main dates
optional {
# qualifier dates
?person ?p ?s .
?s ?date_prop ?pqv .
?pqv wikibase:timeValue ?qual_d.
} #/ optional qual dates
FILTER (?statements >10) . # can adjust n statements here...
FILTER (NOT EXISTS { ?person bnwdt:P15 ?dod .} || NOT EXISTS { ?person bnwdt:P26 ?dob . } ) . # EITHER no date of birth OR no date of death. or swap with following line...
#FILTER (NOT EXISTS { ?person bnwdt:P15 ?dod .} && NOT EXISTS { ?person bnwdt:P26 ?dob . } ) . # no date of birth AND no date of death
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb".
}
}
order by ?personLabel
Places of residence that lack instance of locality
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?residence ?residenceLabel ?inst ?instLabel
WHERE {
?resident bnwdt:P29 ?residence . # resided at
filter not exists { ?residence bnwdt:P12 bnwd:Q2147 . } # that doesn't have instance of locality
optional {?residence bnwdt:P12 ?inst .} # does it have instance of anything?
## Note: some have Q618 territory of the British Empire, which also lacks any instance of at present
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
ORDER BY ?residenceLabel
Locations that don't have Wikidata links or their Wikidata pages lack geocoordinates
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?locationLabel ?location ?wikidata ?wd_geo ?instanceLabel ?bn_geo
WHERE {
{?item bnwdt:P29 ?location . } # resided at
union
{?location bnwdt:P12 bnwd:Q2147 .} # OR has instance of locality
union
{?item bnwdt:P2 ?location . } # OR location P2
# any other possibles ?
optional { ?location bnwdt:P12 ?instance . } # check instance of
optional {
?location bnwdt:P117 ?ws . # wikidata id
bind(iri(concat("http://www.wikidata.org/entity/", str(?ws))) as ?wikidata) .
SERVICE <https://query.wikidata.org/sparql> {
optional {?wikidata wdt:P625 ?wd_geo .} # does the wikidata page have geocoords?
} # /wikidata service
} #/wikidata optional
filter (!bound(?wd_geo) ). # no geocoords in wikidata. some may have coords in BN...
optional { ?location bnwdt:P153 ?bn_geo . } # so check for coords in BN
##filter (!bound(?bn_geo)) . # uncomment to filter those out as well
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
ORDER BY ?wikidata ?locationLabel
Archaeological sites that don't have National Heritage England list numbers
#title:archaeological sites without National Heritage numbers
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT ?item ?itemLabel ?location ?locationLabel
(if(bound(?locEngland), "yes", "") as ?inEngland)
WHERE {
?item bnwdt:P12 bnwd:Q86 . # item instance of archaeological site
filter not exists {?item bnwdt:P129 ?thing . } # without NH list number
optional { ?item bnwdt:P2 ?location . } # location if it has one
optional { ?item bnwdt:P2 ?locEngland .
?locEngland bnwdt:P33+ bnwd:Q617.
} # is the location in England
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
order by ?itemLabel
Date of latest edit for every item, according to WQS
#title:latest edits dates for wikibase items
## because of WQS disappearing data bugs this may not always match the latest edit in the page history
SELECT ?item ?date ?dateLabel
WHERE {
?item wikibase:timestamp ?date .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
order by ?date
Wikidata ID is a URL
#title:check for accidental use of URL instead of wikidata ID
## all checked and fixed at 18/12/23 but adding the query in case it recurs
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?item ?itemLabel ?wd_http
WHERE {
?item bnwdt:P117 ?wd_http . # wikidata id
filter(strstarts(str(?wd_http), "http")).
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
ORDER BY ?itemLabel
Wikidata URL is not a URL
#title:check for wikidata URLs that don't start with http. could still be other problems!
## all checked and fixed at 18/12/23 but adding the query in case of any recurring issues
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?item ?itemLabel ?wd_url
WHERE {
?item bnp:P117 ?ws . # wikidata id
?ws bnpq:P14 ?wd_url .
filter(!strstarts(str(?wd_url), "http")).
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
ORDER BY ?itemLabel
Oxford and Cambridge academic degrees potential date problems
#title:academic degree Oxford/Cambridge date issues
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT distinct ?personLabel ?degreeLabel ?byLabel ?subjectLabel
?date ?date_label ?s ?person #?subject ?degree ?by ?university
WHERE {
?person bnwdt:P3 bnwd:Q3 . #select women
# academic degree = P59.
?person bnp:P59 ?s .
?s bnps:P59 ?degree . # type of degree
# optional qualifiers for degree
# P60 subject
optional { ?s bnpq:P60 ?subject}
# P61 conferred by
?s bnpq:P61 ?by .
# date with type
?s ?pq ?date .
?qual_prop wikibase:qualifier ?pq;
wikibase:propertyType wikibase:Time ; # nb excludes edtf dates
rdfs:label ?date_label . filter(lang(?date_label)="en") . # what kind of date is it.
# filter: (cambridge (Q1181) and before 1948) OR (oxford (Q364) and before 1920)
filter( ( year(?date)<1920 && ?by=bnwd:Q364 ) || ( year(?date)<1948 && ?by=bnwd:Q1181 ) )
SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en".}
}
order by ?byLabel ?personLabel ?date ?degreeLabel
More potential problems with dates
#title:academic degree Oxford/Cambridge potential date issues (again)
# looking for cases where there might be a date problem but there isn't a queryable date
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT distinct ?personLabel ?degreeLabel ?note ?byLabel ?link
WHERE {
?person bnwdt:P3 bnwd:Q3 . #select women
# academic degree = P59.
?person bnp:P59 ?s .
?s bnps:P59 ?degree . # type of degree
# optional qualifiers for degree
# P60 subject
# optional { ?s bnpq:P60 ?subject}
# P61 conferred by
?s bnpq:P61 ?by .
filter ( ?by=bnwd:Q1181 || ?by=bnwd:Q364 ).
# date
optional { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date . }
filter not exists { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date .}
?s bnpq:P47 ?note .
# direct link to relevant statement
BIND (REPLACE(STR(?s), "^.*/([^/]*)$", "$1") as ?sid).
# for some grrr reason the first dash is replaced with a $ in the html id.
bind(replace(?sid, "^(Q\\d+)-", "$1\\$") as ?pid) .
bind(iri(concat(str(?person), "#", ?pid)) as ?link )
SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en".}
}
order by ?personLabel ?date ?degreeLabel
Position held: item v free text
# held position job titles item v held position free text
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT
?work_label ?positionLabel ?position (count(*) as ?count)
WHERE {
?person bnwdt:P3 bnwd:Q3 . # women
# get work activities: held position / held position (free text) / employed as
?person ( bnp:P17|bnp:P48 ) ?s .
?person ?work_p ?s . # for work type label
?work wikibase:claim ?work_p;
rdfs:label ?work_label. filter(lang(?work_label)="en") .
# more about the position
?s ( bnps:P17|bnps:P48 ) ?position .
FILTER( !isBLANK(?position) ) . # filter out <unknown value>
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
group by ?work_label ?positionLabel ?position
order by lcase(str(?positionLabel))
Women with position held free text
# held position free text job titles
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT ?person ?personLabel ?positionLabel
WHERE {
?person bnwdt:P3 bnwd:Q3 . # women
# held position (free text)
?person bnwdt:P48 ?position .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
order by ?personLabel ?positionLabel
Questions
#title:asking questions
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
SELECT ?personLabel ?propLabel ?link ?question
WHERE {
?person bnwdt:P3 bnwd:Q3 .
?person ?p ?s .
?prop wikibase:claim ?p;
wikibase:statementProperty ?ps.
# direct link to relevant part of page
BIND (REPLACE(STR(?prop), "^.*/([^/]*)$", "$1") as ?pid).
bind(iri(concat(str(?person), "#", ?pid)) as ?link ) .
# the question
?s bnpq:P157 ?question .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
order by ?personLabel
Reference query for Blue Papers
#title:statements with references to Blue Papers (except SAL election/was fellow of)
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpr: <https://beyond-notability.wikibase.cloud/prop/reference/>
# for QAing work done on SAL Blue Papers enhancement
# stated in (P56) / blue papers Q315
# TODO RHS Q2519 (see eg Katherine Routledge educated at is from RHS)
# looking for all Q315 references for person EXCEPT the ones in the election statement itself [P16]
# nb there can be more than one item in the reference (as well as information that doesn't have a reference)
# so stuff returned might not have anything to do with the Q315, but the referencing isn't specific enough to know which does.
# added evidence text from election statement. group_concat as there's often more than one.
SELECT distinct ?person ?personLabel ?propLabel ?valueLabel
(group_concat(?evidence; separator=" | ") as ?evidence_text)
?link
# ?ref_stated ?ref_text ?ref_itemLabel
#?prop ?qual_prop ?val ?qual_value
#?s
WHERE {
?person bnwdt:P3 bnwd:Q3 .
?person ?p ?s .
?prop wikibase:claim ?p;
wikibase:statementProperty ?ps.
# *not* in p16 sal election. also p75 was fellow of - a handful not sal but may as well exclude that too.
filter not exists { ?s (bnps:P16 | bnps:P75 ) ?anything . }
?s ?ps ?value .
# qualifiers
# optional {
# ?s ?qual_p ?qual_value .
# ?qual_prop wikibase:qualifier ?qual_p .
# }
# direct link to relevant statement
BIND (REPLACE(STR(?s), "^.*/([^/]*)$", "$1") as ?sid).
# for some grrr reason the first dash is replaced with a $ in the html id.
bind(replace(?sid, "^(Q\\d+)-", "$1\\$") as ?pid) .
bind(iri(concat(str(?person), "#", ?pid)) as ?link ) .
# references: stated in = p56. blue papers q315
?s prov:wasDerivedFrom ?refnode.
?refnode bnpr:P56 bnwd:Q315 .
#?refnode bnpr:P56 ?ref_stated . # you'll get extras if multi in the same reference...
# a few q315 have additional stuff in the reference: p91 specific reference information (item) / p88 specific (text) - unrelated to the p56 i think
#optional {?refnode bnpr:P88 ?ref_text.}
#optional {?refnode bnpr:P91 ?ref_item.}
optional { ?person bnp:P16 ?ss.
?ss bnps:P16 ?proposed .
?ss bnpq:P21 ?evidence .
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
group by ?person ?personLabel ?propLabel ?valueLabel ?link
order by ?personLabel ?propLabel
References using P11
#title:references using P11
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpr: <https://beyond-notability.wikibase.cloud/prop/reference/>
select distinct ?person ?personLabel ?propLabel ?reference ?link
where
{
?person ?p ?s .
?prop wikibase:claim ?p;
wikibase:statementProperty ?ps.
# direct link to statement
BIND (REPLACE(STR(?s), "^.*/([^/]*)$", "$1") as ?sid).
# for some grrr reason the first dash is replaced with a $ in the html id.
bind(replace(?sid, "^([Q]\\d+)-", "$1\\$") as ?pid) .
bind(iri(concat(str(?person), "#", ?pid)) as ?link ) .
# references
?s prov:wasDerivedFrom ?refnode.
?refnode bnpr:P11 ?reference .
#?refnode bnpr:P91 ?specific .
# you'll get extras if multi in the same reference...
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
order by ?personLabel
Tools for batch processing and quality assurance work
All people (by assigned gender), filtering out those already listed as instance of human
# All people (by assigned gender), filtering out those already listed as instance of human
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
SELECT ?person ?personLabel ?assigned_genderLabel
WHERE {
?person bnwdt:P3 ?assigned_gender . #select person
FILTER NOT EXISTS {?person bnwdt:P12 bnwd:Q2137 .} #filter out people already listed as instance of human
FILTER NOT EXISTS {?person bnwdt:P12 bnwd:Q12 .} #filter out project team
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
ORDER BY ?personLabel
All places (by coordinate location on wikidata), filtering out places already listed as instance of locality
# All places (by coordinate location on wikidata), filtering out places already listed as instance of locality
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX pr: <http://www.wikidata.org/prop/reference/>
PREFIX prov: <http://www.w3.org/ns/prov#>
SELECT ?place ?placeLabel ?WDitem ?WDcoordinate_location
WHERE {
?place bnwdt:P33 ?admin_area . #select items with 'located in the administrative territorial entity' statements
?place bnwdt:P117 ?Qnumber . #get wikidata ID
FILTER NOT EXISTS {?place bnwdt:P12 bnwd:Q2147 .} #filter out places already listed as instance of locality
#create reference to Wikibase entity
BIND(IRI(concat("http://www.wikidata.org/entity/", ?Qnumber)) as ?WDitem )
#on Wikibase do
SERVICE <https://query.wikidata.org/sparql> {
?WDitem wdt:P625 ?WDcoordinate_location . #get coordinate location on wikidata
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". }
}
GROUP BY ?place ?placeLabel ?WDitem ?WDcoordinate_location
ORDER BY ?placeLabel
All items without a P12 (instance of) triple
#title:nearly all items without P12 (instance of)
#NB: the query will only find items that have at least one statement; this excludes a small number of Qs and a larger number (~35) of Ps.
#see separate query to find items without any statements
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?item ?itemLabel
WHERE {
?item ?b ?c .
?prop wikibase:claim ?b;
wikibase:statementProperty ?ps.
# ?prop wikibase:propertyType wikibase:WikibaseItem. # limit to P & Q Items. not necessary; only the claim is needed.
# weirdness: it still causes some things that are definitely WikibaseItem to be dropped and idk why.
FILTER NOT EXISTS {?item bnwdt:P12 ?instance .}
## FILTER(STRSTARTS(STR(?item), "https://beyond-notability.wikibase.cloud/entity/P")) . # uncomment to limit to P Properties
## FILTER(STRSTARTS(STR(?item), "https://beyond-notability.wikibase.cloud/entity/Q")) . # or limit to Q items
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".
}
}
Counts of items for each value of the property "instance of" (P12)
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT ?value ?valueLabel (count(*) as ?count)
WHERE {
VALUES (?p) { (bnwdt:P12) }
?s ?p ?value.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
GROUP BY ?value ?valueLabel
ORDER BY lcase(?valueLabel) # order by is case sensitive
# or to order by count
# order by ?count
Get all items (with 'instance of' statement) for duplicate checking in OpenRefine
# Get all items (with 'instance of' statement) for duplicate checking in OpenRefine
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT ?item ?itemLabel WHERE {
?item bnwdt:P12 ?anything .
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb".
}
}
ORDER BY ?itemLabel
All uses of <unknown value>
# Query to look for uses of <unknown value> (now including qualifiers)
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
SELECT distinct ?item ?itemLabel ?mainLabel ?sub_Label ?unknown_value_forLabel ?unknown_value_for ?t
WHERE
{
{
# qualifiers
?item ?p ?s.
?s ?sub ?sub_ .
?main wikibase:claim ?p.
?main wikibase:statementProperty ?sub.
?s ?pq ?t .
?unknown_value_for wikibase:qualifier ?pq .
FILTER( isBLANK(?t) ) . # filter for <unknown value>
}
UNION
{
# top level
?item ?p ?s.
?s ?ps ?t .
?unknown_value_for wikibase:claim ?p.
?unknown_value_for wikibase:statementProperty ?ps.
FILTER( isBLANK(?t) ) .
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY ?itemLabel ?unknown_value_forLabel
Items without any statements
#title:items with 0 statements
# NB: because of the WQS disappearing data bugs, a few of the items might in reality have n>0 statements. Some may be intentionally empty.
SELECT distinct ?item ?itemLabel
WHERE {
?item wikibase:statements 0 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
order by ?item