Project:SPARQL/admin: Difference between revisions

From Beyond Notability
Line 451: Line 451:


== Excavations ==
== 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 ===
=== Excavation directors whose linked excavation isn't an instance of excavation ===

Revision as of 12:04, 21 May 2024

Other queries

main queries page

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!



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!


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!



Oxford and Cambridge academic degrees potential date problems

#title:academic degree Oxford/Cambridge date issues
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwdv: <https://beyond-notability.wikibase.cloud/value/>
PREFIX bnwdt: <https://beyond-notability.wikibase.cloud/prop/direct/>
PREFIX bnp: <https://beyond-notability.wikibase.cloud/prop/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>

SELECT distinct ?personLabel ?degreeLabel ?byLabel ?subjectLabel
?date ?date_label  ?s ?person #?subject ?degree ?by ?university

WHERE {  
  ?person bnwdt:P3 bnwd:Q3 . #select women
  
  # academic degree = P59. 
  ?person bnp:P59 ?s .  
    ?s bnps:P59 ?degree . # type of degree
  
  # optional qualifiers for degree
  
  # P60 subject
    optional { ?s bnpq:P60 ?subject}
  
  # P61 conferred by
    ?s bnpq:P61 ?by .  
  
  # date with type
    ?s ?pq ?date .       
      ?qual_prop wikibase:qualifier ?pq;
              wikibase:propertyType wikibase:Time ; # nb excludes edtf dates
              rdfs:label ?date_label . filter(lang(?date_label)="en") . # what kind of date is it.
      
 # filter: (cambridge (Q1181) and before 1948) OR (oxford (Q364) and before 1920)
   
  filter( ( year(?date)<1920 && ?by=bnwd:Q364 ) || ( year(?date)<1948 && ?by=bnwd:Q1181 ) ) 
  
      
  SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en".}
}
order by ?byLabel ?personLabel ?date ?degreeLabel

Try it!


More potential problems with dates

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

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

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

Try it!


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!




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!

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