Project:SPARQL/admin: Difference between revisions

Revision as of 07:27, 6 September 2024

Other queries

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!

Married names and Marriage dates

#title:Query for married name/married dates discrepancies.
# some women have a married name but no marriage date, or vice versa. This query should help with checking for the missing information...
# most have very few statements and/or are just "Mrs". But there are some with 20+ statements.
# so there's a FILTER statements line that can be adjusted; it's currently at 10

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 ?statements ?married_name ?married_in

WHERE {
   ?person bnwdt:P3 bnwd:Q3 ;
         wikibase:statements ?statements .
  
  FILTER (?statements > 10) .
  
  OPTIONAL {?person bnwdt:P141 ?married_name .} #look for married name  (bnwdt:P130 | bnwdt: P132) 
  optional {?person (bnwdt:P130 | bnwdt:P132)  ?married_in . }
  
  FILTER  ( 
    (NOT EXISTS { ?person bnwdt:P141 ?marriedname .} && EXISTS { ?person (bnwdt:P130 | bnwdt:P132 ) ?married .  } )
       ||
    (EXISTS { ?person bnwdt:P141 ?marriedname .} && NOT EXISTS { ?person (bnwdt:P130 | bnwdt:P132 ) ?married .  } )          
          ) .
   
  SERVICE wikibase:label {
      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb".
  }
  
} # /where

ORDER BY ?personLabel

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!

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!

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!

Preparation for SAL spreadsheet export

# this is probably as close as I can get with just SPARQL.  (it's quite slow but does run)
# variables with multiple values are either grouped with GROUP_CONCAT or reduced to a single value
# *BUT* it doesn't seem to be possible to control order of results inside a group_concat (srsly wtf)
# haven't included unsuccessful FSAs in is_FSA (not quite sure how to go about this in sparql)

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 distinct ?person ?personLabel ?statements 
?is_FSA  ?is_RAI 
(min(year(?dob)) as ?birth) (min(year(?dod)) as ?death) # if multi, just get the earliest one
?birth_name  (group_concat(distinct(?married_name); separator=" || ") as ?married_names)
(group_concat(distinct(?resided_label); separator=" || ") as ?residences) 
(group_concat(distinct(?fellow_of_label); separator=" || " ) as ?fellows)
(group_concat(distinct(?educated_label); separator=" || " ) as ?education)

?isni_ID ?viaf_ID (min(?wikidata) as ?wikidata_ID)
(group_concat(distinct(?ads); separator=" || ") as ?ads_IDs)

WHERE {
  ?person bnwdt:P3 bnwd:Q3 ;
         wikibase:statements ?statements .
   FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} 
  
# elected FSA? [excludes unsuccessful]
   optional {
   ?person bnp:P16 ?SALstatement .
    ?SALstatement bnpq:P22 ?SALelected .
    ?SALstatement bnpq:P22 bnwd:Q36 .  # P22 Q36 successful
    BIND(BOUND(?SALelected) AS ?is_FSA).
  }
 
# elected RAI? [excludes unsuccessful]
  optional {
  ?person bnp:P7 ?RAIstatement .
    ?RAIstatement bnpq:P22 ?RAIelected .
    ?RAIstatement bnpq:P22 bnwd:Q36 .
    BIND(BOUND(?RAIelected) AS ?is_RAI).
  }
  
# dates of birth and death. can be multi.
   optional { ?person bnwdt:P26 ?dob .   }
   optional { ?person bnwdt:P15 ?dod .  }
  
  # birth name P140
  # married name P141. multi.
  optional { ?person bnwdt:P140 ?birth_name . }
  optional { ?person bnwdt:P141 ?married_name . }
  
  # P29 resided at. multi.
  optional { ?person bnwdt:P29 ?resided .
            SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". 
                            ?resided rdfs:label ?resided_label. 
                     } # / label service
           }
  
  # P94 educated at. multi.
  optional { ?person bnwdt:P94 ?educated . 
               SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". 
                      ?educated rdfs:label ?educated_label. 
                     } # / label service          
           }

  
  # P75 was fellow of. multi.
    optional { ?person bnwdt:P75 ?fellow_of . 
              SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". 
                      ?fellow_of rdfs:label ?fellow_of_label. 
                     } # / label service
           }
  # IDs.    
    OPTIONAL {?person bnwdt:P117 ?wikidata .}  # one multi! so just keep one   
    OPTIONAL {?person bnwdt:P119 ?viaf_ID .}         
    OPTIONAL {?person bnwdt:P34 ?ads .}     # multi            
    OPTIONAL {?person bnwdt:P125 ?isni_ID .}        
 

  SERVICE wikibase:label {
      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".
    }
}
group by ?person ?personLabel ?statements ?is_FSA ?is_RAI ?death ?dod ?birth_name ?isni_ID ?viaf_ID ?wikidata_ID 

# order by doesn't work for the group_concats
order by ?person ?residence_label ?fellow_of_label

Try it!

# WORK IN PROGRESS 
# original version with no grouping

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
?is_FSA ?is_RAI 
?dob ?dod 
?birth_name ?married_name 
?educatedLabel
?residedLabel
?fellow_ofLabel
?wikidata_ID ?viaf_ID ?ads_ID ?isni_ID

WHERE {
  ?person bnwdt:P3 bnwd:Q3 ;
         wikibase:statements ?statements .
   FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} 
  
  optional {
    ?person bnp:P16 ?SALstatement .
    ?SALstatement bnpq:P22 ?SALelected .
    ?SALstatement bnpq:P22 bnwd:Q36 .  # P22 Q36 successful
    BIND(BOUND(?SALelected) AS ?is_FSA).
  }
  
  optional {
  ?person bnp:P7 ?RAIstatement .
  ?RAIstatement bnpq:P22 ?RAIelected .
    ?RAIstatement bnpq:P22 bnwd:Q36 .
    BIND(BOUND(?RAIelected) AS ?is_RAI).
  }
  
   optional { ?person bnwdt:P26 ?dob .  }
   optional { ?person bnwdt:P15 ?dod .  }
  
  # birth name P140
  # married name P141
  optional { ?person bnwdt:P140 ?birth_name . }
  optional { ?person bnwdt:P141 ?married_name .}
  
  # P94 educated at
  optional { ?person bnwdt:P94 ?educated . }
  
  # P29 resided at
  optional { ?person bnwdt:P29 ?resided . }
  
  # P75 was fellow of
  optional { ?person bnwdt:P75 ?fellow_of . }
  
    OPTIONAL {?person bnwdt:P117 ?wikidata_ID .}             #return wikidata ID
    OPTIONAL {?person bnwdt:P119 ?viaf_ID .}                 #return VIAF ID
    OPTIONAL {?person bnwdt:P34 ?ads_ID .}                   #return Archaeological Data Service ID
    OPTIONAL {?person bnwdt:P125 ?isni_ID .}             #return ISNI ID
  
  
  SERVICE wikibase:label {
      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".
    }
}

Try it!

Place

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!

Reviewing instance of locality

# how many i/o locality don't have a wikidata id?
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/>

#title:i/o locality
SELECT distinct ?itemLabel ?item ?wd_id ?adminLabel ?bn_geo 
WHERE {  
  
  ?item bnwdt:P12 bnwd:Q2147 . # has i/o locality (could have other i/o as well?)
  
  #optional { ?item bnwdt:P12 ?io . 
  #         #filter not exists { ?item bnwdt:P12 bnwd:Q2147 . } # this doesn't work... ??? should be only a handful.
  #         } # check io other than locality
  
  # what props do they have: 945 P117 wikidata id; 919 P33 admin territory; 9 P153 coord location
  #?item ?p ?s .
  #    ?prop wikibase:claim ?p.
  
  optional { ?item bnwdt:P117 ?wd_id . }
  optional { ?item bnwdt:P14 ?described . } # to check for described at url instead of wd. 
  optional { ?item bnwdt:P33 ?admin . } # admin territory; some multi
  optional { ?item bnwdt:P153 ?bn_geo . }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}

ORDER BY ?itemLabel ?item

Try it!

Localities in the UK that are in more than one P33 admin territory

#title:UK localities in multiple P33 admin territories
# may be problematic for aggregation

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  (count (distinct ?admin) as ?count) (group_concat(distinct(?admin_label); separator="; ") as ?admins)

where {
  values ?region { bnwd:Q1231 bnwd:Q67 bnwd:Q75 bnwd:Q200 bnwd:Q1202 bnwd:Q85 bnwd:Q204 bnwd:Q1215 bnwd:Q72 bnwd:Q425 bnwd:Q1224 bnwd:Q1740 }
   # english regions; wales q425; scotland q1224; n.ire q1740

  ?item bnwdt:P12 bnwd:Q2147 . # i/o locality
  ?item bnwdt:P33* ?region. # in ?region 
  
  ?item bnwdt:P33 ?admin .  
    ?admin rdfs:label ?admin_label. filter(lang(?admin_label)="en") .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
group by ?item ?itemLabel 
having (?count>1)
order by ?itemLabel

Try it!

Localities that are in more than one UK region

#title:UK localities that are in more than one UK region
# very problematic for aggregation

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   (count (distinct ?region) as ?count)  (group_concat(?region_label; separator="; ") as ?regions)

where {
  values ?region { bnwd:Q1231 bnwd:Q67 bnwd:Q75 bnwd:Q200 bnwd:Q1202 bnwd:Q85 bnwd:Q204 bnwd:Q1215 bnwd:Q72 bnwd:Q425 bnwd:Q1224 bnwd:Q1740 }

  ?item bnwdt:P12 bnwd:Q2147 . # i/o locality
  ?item bnwdt:P33* ?region.    
    ?region rdfs:label ?region_label. filter(lang(?region_label)="en") .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
group by ?item ?itemLabel 
having (?count>1)
order by ?itemLabel

Try it!

UK localities that have a UK region as P33 admin territory

#title:UK localities with P33 that is a UK region
# check for anything that shouldn't be directly in a region.

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 ?regionLabel ?region

where {
  values ?region { bnwd:Q1231 bnwd:Q67 bnwd:Q75 bnwd:Q200 bnwd:Q1202 bnwd:Q85 bnwd:Q204 bnwd:Q1215 bnwd:Q72 bnwd:Q425 bnwd:Q1224 bnwd:Q1740 }

  ?item bnwdt:P12 bnwd:Q2147 .
  ?item bnwdt:P33 ?region. 

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
order by ?regionLabel

Try it!

BN administrative territories in the UK with GAS

#title:chaining P33s in the UK
# the "chain" starts at Q1741 United Kingdom and then follows all the P33 links.
# each row is a pair of linked P33s; depth indicates the level in the P33 hierarchy. (depth=1 is the four countries)
# however if there's more than one P33 I think it doesn't fetch all of them [TODO check if that can be adjusted]
# https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/query_optimization#GAS_Service
# https://github.com/blazegraph/database/wiki/RDF_GAS_API

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 ?depth  ?loc1Label ?loc2Label ?loc1 ?loc2
WHERE {
      SERVICE gas:service {
           gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ; 
                       gas:in bnwd:Q1741 ; 
                       gas:linkType bnwdt:P33 ; 
                       gas:traversalDirection "Reverse"; 
                       gas:out ?loc2 ; 
                       gas:out1 ?depth ; 
                       gas:out2 ?loc1 . 
      }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    } 
order by ?depth ?loc1Label ?loc2Label

Try it!

Excavations

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!

Excavation directors whose linked excavation isn't an instance of excavation

# tends to be because of a lack of information about the excavation but possible that some need reviewing
# NB an excavation that does have i/o excavation could still be listed here if it has more than one i/o
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/>
#title:Excavation directors without excavations

SELECT distinct ?person ?personLabel  ?excavationLabel ?excavation ?ioLabel 
WHERE {  
  
  ?person bnwdt:P12 bnwd:Q2137 .
  ?person (bnwdt:P36 | bnwdt:P37 ) ?excavation . 
  
    ?excavation bnwdt:P12 ?io .
    filter not exists { ?excavation bnwdt:P12 bnwd:Q38 . }  # io not excavation
  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
ORDER BY ?ioLabel ?personLabel

Try it!

Excavations that lack a P2 location

## several of these have some kind of location information in their description or other notes
#title:BN excavations without locations
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 bnwdt:P12 bnwd:Q38 . 
  optional { 
    ?item bnwdt:P2 ?location . # no excavations have a P33
  }
  
  filter not exists { ?item bnwdt:P2 ?location .  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
order by ?itemLabel

Try it!

Excavations that lack Directors

#title:BN excavation pages without directors 
#AFAICT there are also no person pages with directors for these excavations
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 bnwdt:P12 bnwd:Q38 . 
  
  optional { 
    ?item bnwdt:P36 ?director . 
  }

  filter not exists { ?item bnwdt:P36 ?director .  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
order by ?itemLabel

Try it!

Excavation vs Person page discrepancies

Excavation Directors

#title:excavation directors - person vs excavation pages
# excavation directors who are documented in only one of excavation / person page
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 ?excavation ?excavationLabel ?director ?directorLabel
#(if(bound(?is_director), "yes", "") as ?person_on_excav)
#(if(bound(?dir_excavation), "yes", "") as ?excav_on_person)


WHERE {  
  {
    ?person bnwdt:P3 bnwd:Q3 .
    ?person bnp:P36 ?s .
      ?s bnps:P36 ?excavation .
  
    # does the excavation have a director matching ?person ? 
        optional { ?excavation bnwdt:P36 ?is_director . filter(?is_director = ?person ). }
    # does the excavation have *any* named directors? 
    #    optional { ?excavation bnwdt:P36 ?has_directors . 
    #              SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". 
    #                                     ?has_directors rdfs:label ?has_directors_label .}
    #             }
    filter(!bound(?is_director)).
  }
  union
  {
    
     # instance of excavation
   ?excavation bnwdt:P12 bnwd:Q38 .
  
  optional { ?excavation bnwdt:P36 ?director .
           # does the director have P36 matching excavation on their page...  filter might fail if you fiddle around with prefixes
            optional { ?director bnwdt:P36 ?dir_excavation . filter( ?dir_excavation = ?excavation ). }
           } 
    filter(!bound(?dir_excavation)).
   }
    
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
#group by ?person ?personLabel ?excavation ?excavationLabel ?director ?directorLabel 
ORDER BY  ?excavationLabel ?person ?director

Try it!

Excavation Members

#title:excavation members - people vs excavation pages
# excavation members who are documented in only one of excavation / person page
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 ?excavation ?excavationLabel ?member ?memberLabel
 #(if(bound(?is_member), "yes", "") as ?person_on_excav)
 #(if(bound(?mem_excavation), "yes", "") as ?excav_on_person)

WHERE {  
  {
    # person P37 member of excavation
    ?person bnwdt:P3 bnwd:Q3 .
    ?person bnp:P37 ?s .
      ?s bnps:P37 ?excavation .
  
    # does the excavation have a member matching ?person ? 
        optional { ?excavation bnwdt:P37 ?is_member . filter(?is_member = ?person ). }
    filter(!bound(?is_member)).
  }
  union
  {
    
    # instance of excavation
   ?excavation bnwdt:P12 bnwd:Q38 .
  
   ?excavation bnwdt:P37 ?member .
           # does the director have P36 matching excavation on their page...  filter might fail if you fiddle around with prefixes
            optional { ?member bnwdt:P37 ?mem_excavation . filter( ?mem_excavation = ?excavation ). }
            
    filter(!bound(?mem_excavation)).
   }
    
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
ORDER BY  ?excavationLabel ?person ?member

Try it!

Excavation dates issues

Date inconsistences

#title:BN excavation pages dates inconsistencies
# inconsistencies about where dates are recorded: some are in a main level P1 and some are in the instance 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/>

SELECT distinct ?item ?itemLabel  ?p1_date ?io_date
WHERE {   
  
  ?item  bnwdt:P12 bnwd:Q38 .

   optional {?item bnwdt:P1 ?p1_date . }
    
   optional {?item ?p ?s . ?s (bnpq:P1|bnpq:P27|bnpq:P28) ?io_date . }

  filter ( bound(?p1_date) || bound(?io_date)  ) .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
order by ?itemLabel

Try it!

Dates missing

to follow

Dates

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!

Women with no date of death in BN wikibase who have a date of death on Wikidata

# Women who have no date of death in our wikibase and have a date of death on Wikidata 

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  ?wd_dod
WHERE {  
  ?person bnwdt:P3 bnwd:Q3 . #select women
  FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team
 
  filter not exists {?person bnwdt:P15 ?bn_dod . } # *don't* have P15 date of death
  
  ?person bnwdt:P117 ?wikidata_ID . #look for wikidata ID on person page
    bind(iri(concat("http://www.wikidata.org/entity/", str(?wikidata_ID))) as ?item) .

  
  SERVICE <https://query.wikidata.org/sparql> {
        ?item wdt:P21 ?WD_gender. #get gender of person
        ?item wdt:P570 ?wd_dod .  #date of death on Wikidata P570
      }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". } 
}
ORDER BY ?personLabel

Try it!

Women with no date of birth in our wikibase who have a date of birth in Wikidata

# Women who have no date of birth in BN wikibase who have a date of birth on Wikidata 

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  ?wd_dob 
WHERE {  
  ?person bnwdt:P3 bnwd:Q3 . #select women
  FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team
 
  filter not exists {?person bnwdt:P26 ?bn_dob . } # *don't* have P15 date of death
  
  ?person bnwdt:P117 ?wikidata_ID . #look for wikidata ID on person page
    bind(iri(concat("http://www.wikidata.org/entity/", str(?wikidata_ID))) as ?item) .

  SERVICE <https://query.wikidata.org/sparql> {
        ?item wdt:P21 ?WD_gender. #get gender of person
        ?item wdt:P569 ?wd_dob .  #date of birth on Wikidata P569
      }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb". } 
}
ORDER BY ?personLabel

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!

Degrees lacking conferred by information

#title:academic degree Oxford/Cambridge potential issues (again!)
#found at least one that was not picked up by previous queries because it lacked conferred by
#so this is simply a list of all academic degree with no conferred by.
#many will simply lack enough information (including date) to make any decision.
 
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   ?date  ?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
  optional {  ?s bnpq:P61 ?by .  }
  
  #filter not exists { ?s bnpq:P61 ?by .  }
  filter(!bound(?by)) . # filter not exists doesn't work for this; idk why.
  
  # date 
   optional { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date . }
  #filter not exists { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date .}
   
  
   # 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 ?degreeLabel

Try it!

Start dates that might not have accompanying end dates

#title:Women with start dates that might not have accompanying end 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/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>

SELECT distinct ?personLabel ?prop_label ?start_date  (if(bound(?end_date), "yes", "no") as ?has_end_date) ?person ?s  
#(group_concat(?other_date_label; SEPARATOR=" | ") as ?other_dates)

WHERE {
  ?person bnwdt:P3 bnwd:Q3 .
   
  # need to find start times anywhere and get prop label so use claim. 
  ?person ?p ?s . 
  ?claim wikibase:claim ?p;       
         rdfs:label ?prop_label. filter(lang(?prop_label)="en") .  
    
# pretty sure start time is qualifier only...  
 # get start dates 
 # then optional end dates 
 # can only see end time/pit associated, and the only pit with start time is not relevant, but can't be sure that'll always be true.
 # optional other that excludes both start/end but lists any other date types
  
     ?s bnpq:P27 ?start_date . # get start dates
  
    optional {?s bnpq:P28 ?end_date .} # check for end dates

 #   optional {
 #       ?s ?pq ?other_date . # could there be any other associated dates?
      
 #       ?qual_prop wikibase:qualifier ?pq;      
 #             wikibase:propertyType wikibase:Time ; # nb *does not* include edtf dates but AFAICT there are none in qualifiers.
 #             rdfs:label ?other_date_label . filter(lang(?other_date_label)="en-gb") . # what kind of date is it.
  
        # drop both start and end this time. idk if there could be a faster way to do this?
 #       filter not exists { ?s (bnpq:P27 | bnpq:P28) ?other_date .  }
  
 #  } # / optional other dates
  
  # filter(!bound(?end_date)). # to filter out rows with an end date
  
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". } 
  
} 

ORDER BY ?personLabel ?prop_label ?start_date

Try it!

End dates that might not have accompanying start dates

#title:Women with end dates that might not have accompanying start 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/>
PREFIX bnpsv: <https://beyond-notability.wikibase.cloud/prop/statement/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>

SELECT distinct ?personLabel ?prop_label ?end_date  
(if(bound(?start_date), "yes", "no") as ?has_start_date)  
(group_concat(?other_date_label; SEPARATOR=" | ") as ?other_dates) 
?person ?s

WHERE {
  ?person bnwdt:P3 bnwd:Q3 .
  ?person ?p ?s . 
  ?claim wikibase:claim ?p;       
         rdfs:label ?prop_label. filter(lang(?prop_label)="en") .  
    
 # get end dates 
 # then optional start dates 
 # can only see start time/pit associated, and in fact the only pit with start time is not relevant, but can't be sure that'll always be true.
 # so an optional other that excludes both start/end but lists any other date types with group_concat
  
     ?s bnpq:P28 ?end_date . # get end dates
  
    optional {?s bnpq:P27 ?start_date .} # check for start dates

    optional {
        ?s ?pq ?other_date . # could there be any other associated dates?
        ?qual_prop wikibase:qualifier ?pq;      
              wikibase:propertyType wikibase:Time ; # nb *does not* include edtf dates but AFAICT there are none in qualifiers.
              rdfs:label ?other_date_label . filter(lang(?other_date_label)="en-gb") . # what kind of date 
        # drop both start and end. idk if there could be a faster way to do this?
        filter not exists { ?s (bnpq:P27 | bnpq:P28) ?other_date .  }
  
   } # / optional other dates
  
 #  filter(!bound(?start_date)). # to filter out rows with a start date (77 atm)
  
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". } 
  
} 

group by  ?personLabel ?prop_label ?end_date ?start_date ?has_start_date ?person ?s  

ORDER BY ?personLabel ?prop_label ?end_date

Try it!

Item / free text

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!

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!

Wikidata

Places that link to wikidata AND have wikidata P625 geo coordinates

#title:wikidata geo coords P625
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 # ?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 ?  "P33 but only if i/o arch site or historic house" historic house Q2961  arch site Q86
  union
  {?item bnwdt:P33 ?location .} # admin territory ADDS ONE ROW. lol.
    
       ?location bnwdt:P117 ?ws .  # wikidata id        
         
         bind(iri(concat("http://www.wikidata.org/entity/", str(?ws))) as ?wikidata) .
         SERVICE <https://query.wikidata.org/sparql> {
             ?wikidata wdt:P625 ?wd_geo .  # does the wikidata page have geocoords?
         } # /wikidata service   
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". } 
}
ORDER BY ?wikidata ?locationLabel

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!

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!

Wikidata instance ofs (P31) for BN localities

#title:counts of wikidata instance of (P31) for UK localities 

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  ?wikidata_io_label  (count(distinct *) as ?count)  (group_concat(distinct(?country_label); separator="; ") as ?countries)

where {
  { # subquery for order of countries in group_concat.
  select * 
   where {
    values ?country { bnwd:Q617 bnwd:Q425 bnwd:Q1224 bnwd:Q1740 }

  ?item bnwdt:P12 bnwd:Q2147 . # i/o locality
  ?item bnwdt:P33+ ?country . 
      ?country rdfs:label ?country_label . filter(lang(?country_label)="en") .
     
  ?item bnwdt:P117 ?wd.
      bind(iri(concat("http://www.wikidata.org/entity/", str(?wd))) as ?wikidata) .
      SERVICE <https://query.wikidata.org/sparql> {   
          ?wikidata wdt:P31 ?wd_io.
              SERVICE wikibase:label { 
                bd:serviceParam wikibase:language "en".  
                #?wikidata rdfs:label ?wd_label . 
                ?wd_io rdfs:label ?wikidata_io_label . 
                }        
        }
  
  } # end of subquery where
  order by ?country_label
 } # end of subquery
  
} # end of main where
group by ?wikidata_io_label 
order by desc(?count)

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!

Women who are listed as Fellows of the Society of Antiquaries of London (FSAs) on our wikibase and who are on Wikidata, but are not listed as FSAs on Wikidata

# Women who are listed as Fellows of the Society of Antiquaries of London (FSAs) on our wikibase and who are on Wikidata, but are not listed as FSAs on Wikidata

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 bnwd:Q3 . #select women
  FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team
  
  ?person bnwdt:P75 bnwd:Q8. # find only BN subjects having a FSA (h/t @Tagishsimon)

  ?person bnwdt:P117 ?wikidata_ID . #look for wikidata ID on person page
  bind(iri(concat("http://www.wikidata.org/entity/", str(?wikidata_ID))) as ?item) .

  SERVICE <https://query.wikidata.org/sparql> {
        ?item wdt:P21 ?WD_gender. #get gender of person
        FILTER NOT EXISTS {?item wdt:P166 wd:Q26196499 . } #not FSA on Wikidata
      }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en". } 
}
ORDER BY ?personLabel

Try it!

Women with Wikimedia commons images not yet added to the wikibase

# credit: tagishsimon
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 ?wikidata ?image

WHERE {
    ?person bnwdt:P3 bnwd:Q3 . #select women   
    #?person bnwdt:P75 bnwd:Q8. # limit to FSAs if the full set times out.
   
    ?person bnwdt:P117 ?wikidata_ID . #get wikidata ID
    bind(iri(concat("http://www.wikidata.org/entity/", str(?wikidata_ID))) as ?wikidata) .

   # does not have a P159 image in the wikibase
    filter not exists {?person bnwdt:P159 [] . }

   # but does have an image P18 in wikidata
    SERVICE <https://query.wikidata.org/sparql> {
      ?wikidata wdt:P18 ?image. 
     }  
       
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en, en-gb". } 
}
             
order by ?personLabel ?id_label ?id_no

Try it!

Reference queries

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!

Reference query for RHS

#title:statements with references to RHS archive
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  enhancement
# stated in (P56) / 
# RHS Q2519 (see eg Katherine Routledge academic degree)
# looking for all Q315 references for person EXCEPT the ones in the election statement itself [P155]
# 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. however this is not as useful as the blue papers

SELECT distinct ?personLabel ?propLabel ?valueLabel
(group_concat(?evidence; separator=" | ") as ?evidence_text)
 ?link  
#?s

WHERE {  
  ?person bnwdt:P3 bnwd:Q3 .
  ?person ?p ?s .
      ?prop wikibase:claim ?p;      
         wikibase:statementProperty ?ps.      
   
  # *not* in p155 rhs election  
  filter not exists { ?s bnps:P155 ?anything . }
  
    ?s ?ps ?value .
  
         # 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   RHS Q2519
    ?s prov:wasDerivedFrom ?refnode.
        ?refnode bnpr:P56 bnwd:Q2519 . 
  
  
  optional { ?person bnp:P155 ?ss.
               ?ss bnps:P155 ?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!

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

Anonymous

Search

Project:SPARQL/admin: Difference between revisions

Revision as of 07:27, 6 September 2024

Other queries

Admin

Women about whom we have recorded fewer than 3 pieces of information

People for whom their item records is given as 'Mrs'

All triples in the wiki, limited to 1000

All triples in the wiki, represented as a crude graph

People whose assigned gender we have been unable to determine and/or confirm

People who have a spouse statement and - optionally - marriage date statements for checking if there is any misalignment with 'Spouse not in wikibase' statements

Married names and Marriage dates

Missing language labels

Date of latest edit for every item, according to WQS

Questions

Preparation for SAL spreadsheet export

Place

Places of residence that lack instance of locality

Reviewing instance of locality

Localities in the UK that are in more than one P33 admin territory

Localities that are in more than one UK region

UK localities that have a UK region as P33 admin territory

BN administrative territories in the UK with GAS

Excavations

Archaeological sites that don't have National Heritage England list numbers

Excavation directors whose linked excavation isn't an instance of excavation

Excavations that lack a P2 location

Excavations that lack Directors

Excavation vs Person page discrepancies

Excavation Directors

Excavation Members

Excavation dates issues

Date inconsistences

Dates missing

Dates

Statements that contain both a date *and* an unknown value date

Women with no dates

Women with more than X statements but no date of birth/death

Women with no date of death in BN wikibase who have a date of death on Wikidata

Women with no date of birth in our wikibase who have a date of birth in Wikidata

Oxford and Cambridge academic degrees potential date problems

More potential problems with dates

Degrees lacking conferred by information

Start dates that might not have accompanying end dates

End dates that might not have accompanying start dates

Item / free text

Compare uses of item v free text

Position held: item v free text

Women with position held free text

Wikidata

Places that link to wikidata AND have wikidata P625 geo coordinates

Locations that don't have Wikidata links or their Wikidata pages lack geocoordinates

All places (by coordinate location on wikidata), filtering out places already listed as instance of locality

Wikidata instance ofs (P31) for BN localities

Wikidata ID is a URL

Wikidata URL is not a URL

Women who are listed as Fellows of the Society of Antiquaries of London (FSAs) on our wikibase and who are on Wikidata, but are not listed as FSAs on Wikidata

Women with Wikimedia commons images not yet added to the wikibase

Reference queries

Reference query for Blue Papers

References using P11

Reference query for RHS

Tools for batch processing and quality assurance work

All people (by assigned gender), filtering out those already listed as instance of human

All items without a P12 (instance of) triple

Counts of items for each value of the property "instance of" (P12)

Get all items (with 'instance of' statement) for duplicate checking in OpenRefine

All uses of <unknown value>

Items without any statements

Navigation

Wiki tools

Page tools

Other projects

Statements that contain both a date and an unknown value date