Project:SPARQL/admin

From Beyond Notability

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".
  }
}

Try it!


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". } 
}

Try it!


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!

Try it!


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!

Try it!


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

Try it!


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)

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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))

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!


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".
  }
}

Try it!


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

Try it!


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

Try it!


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

Try it!


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

Try it!

Retrieved from "https://beyond-notability.wikibase.cloud/w/index.php?title=Project:SPARQL/admin&oldid=57286"