View source for Project:SPARQL/admin

== Other queries ==

[[Project:SPARQL/examples|main queries page]]

==Admin==

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

<sparql tryit="1">
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".
  }
} 
</sparql>

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

<sparql tryit="1">
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". } 
}
</sparql>

===All triples in the wiki, limited to 1000===

<sparql tryit="1">
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!
</sparql>

===All triples in the wiki, represented as a crude graph===

<sparql tryit="1">
#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!
</sparql>

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

<sparql tryit="1">
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
</sparql>

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

<sparql tryit="1">
# 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)
</sparql>

=== Married names and Marriage dates ===

<sparql tryit="1">
#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
</sparql>



=== Missing language labels ===

<sparql tryit="1">

# 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 

</sparql>




=== Date of latest edit for every item, according to WQS ===

<sparql tryit="1">
#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

</sparql>





=== Questions ===

<sparql tryit="1">

#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
</sparql>


=== Preparation for SAL spreadsheet export ===

<sparql tryit="1">
# WORK IN PROGRESS - not ready to use!
# not done any 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".
    }
}
</sparql>

== Place ==

=== Places of residence that lack instance of locality===

<sparql tryit="1">
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
</sparql>



=== Reviewing instance of locality ===

<sparql tryit="1">
# 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
</sparql>

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

<sparql tryit="1">
#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
</sparql>

=== Localities that are in more than one UK region ===

<sparql tryit="1">
#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
</sparql>

=== UK localities that have a UK region as P33 admin territory ===

<sparql tryit="1">
#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
</sparql>


=== BN administrative territories in the UK with GAS ===

<sparql tryit="1">
#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
</sparql>

== Excavations ==

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

<sparql tryit="1">
#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 

</sparql>



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

<sparql tryit="1">
# 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
</sparql>

=== Excavations that lack a P2 location ===

<sparql tryit="1">
## 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
</sparql>

=== Excavations that lack Directors ===

<sparql tryit="1">
#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
</sparql>

=== Excavation vs Person page discrepancies ===

==== Excavation Directors ====

<sparql tryit="1">
#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
</sparql>

==== Excavation Members ====

<sparql tryit="1">
#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
</sparql>

=== Excavation dates issues ===

==== Date inconsistences ====

<sparql tryit="1">
#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
</sparql>

==== Dates missing ====

to follow

== Dates ==

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

<sparql tryit="1">
# 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

</sparql>


=== Women with no dates===

<sparql tryit="1">

# 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

</sparql>


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

<sparql tryit="1">

#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

</sparql>

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

<sparql tryit="1">
# 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
</sparql>

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

<sparql tryit="1">
# 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
</sparql>

=== Oxford and Cambridge academic degrees potential date problems ===

<sparql tryit="1">
#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
</sparql>

=== More potential problems with dates ===

<sparql tryit="1">
#title:academic degree Oxford/Cambridge potential date issues (again)
# looking for cases where there might be a date problem but there isn't a queryable date
 
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>

SELECT distinct ?personLabel ?degreeLabel ?note ?byLabel ?link

WHERE {  
  ?person bnwdt:P3 bnwd:Q3 . #select women
  
  # academic degree = P59. 
  ?person bnp:P59 ?s .  
    ?s bnps:P59 ?degree . # type of degree
  
  # optional qualifiers for degree
  
  # P60 subject
  #  optional { ?s bnpq:P60 ?subject}
  
  # P61 conferred by
    ?s bnpq:P61 ?by .  
  
  filter ( ?by=bnwd:Q1181 || ?by=bnwd:Q364 ).
  
  # date 
   optional { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date . }
  filter not exists { ?s (bnpq:P1 | bnpq:P27 | bnpq:P28 ) ?date .}
   
  ?s bnpq:P47 ?note .  
  
   # direct link to relevant statement
   BIND (REPLACE(STR(?s), "^.*/([^/]*)$", "$1") as ?sid).
     # for some grrr reason the first dash is replaced with a $ in the html id.
   bind(replace(?sid, "^(Q\\d+)-", "$1\\$") as ?pid) .
   bind(iri(concat(str(?person), "#", ?pid)) as ?link ) 
         
  SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en".}
}
order by ?personLabel ?date ?degreeLabel
</sparql>

=== Degrees lacking conferred by information ===

<sparql tryit="1">
#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
</sparql>


=== Start dates that might not have accompanying end dates ===

<sparql tryit="1">
#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
</sparql>


=== End dates that might not have accompanying start dates ===

<sparql tryit="1">
#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
</sparql>

== Item / free text ==

=== Compare uses of item v free text ===

<sparql tryit="1">
# 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

</sparql>


=== Position held: item v free text ===

<sparql tryit="1">
# 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))
</sparql>

=== Women with position held free text ===

<sparql tryit="1">
# 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
</sparql>


== Wikidata ==

=== Places that link to wikidata AND have wikidata P625 geo coordinates ===

<sparql tryit="1">
#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
</sparql>

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

<sparql tryit="1">
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

</sparql>


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

<sparql tryit="1">
# 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
</sparql>

=== Wikidata instance ofs (P31) for BN localities ===

<sparql tryit="1">
#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) 
</sparql>

=== Wikidata ID is a URL ===

<sparql tryit="1">

#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 
</sparql>

=== Wikidata URL is not a URL ===

<sparql tryit="1">
#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
</sparql>

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

<sparql tryit="1">
# 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
</sparql>

=== Women with Wikimedia commons images not yet added to the wikibase ===

<sparql tryit="1">
# 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
</sparql>

== Reference queries ==

=== Reference query for Blue Papers ===

<sparql tryit="1">

#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
</sparql>

=== References using P11 ===

<sparql tryit="1">
#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
</sparql>

=== Reference query for RHS ===

<sparql tryit="1">
#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
</sparql>

==Tools for batch processing and quality assurance work==

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

<sparql tryit="1">
# 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
</sparql>



===All items without a P12 (instance of) triple===

<sparql tryit="1">
#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".
  }
}
</sparql>

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

<sparql tryit="1">

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

</sparql>

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

<sparql tryit="1">
# 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
</sparql>

===All uses of <unknown value>===

<sparql tryit="1">
# 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
</sparql>


=== Items without any statements ===

<sparql tryit="1">
#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
</sparql>