Project:SPARQL/examples: Difference between revisions

       bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

       bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

       bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

       bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

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

       bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb,en".

 

 

=== Women's interactions with SAL (Q1 and Q8)===

#title:Women's interactions with SAL (Q1 and Q8)

# CAVEAT: this query may overlook a significant number of interactions and should probably not be reference/archived in its current state

SELECT distinct ?person ?personLabel   ?propLabel  ?qual_value  ?qual_propLabel  ?bnwdLabel # ?s ?prop ?qual_prop ?bnwd

 

   ?person bnwdt:P3 bnwd:Q3 . # women

   ?person ?p ?s .

      ?prop wikibase:claim ?p;     

        wikibase:statementProperty ?ps.  

      { ?s ?ps bnwd:Q8 . }  union { ?s ?ps bnwd:Q1 . }

      ?s ?ps ?bnwd. #which type

      optional {  

        ?s ?pq ?qual_value .  

        ?qual_prop wikibase:qualifier ?pq .

      } # /qualifiers

 

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

ORDER BY ?personLabel ?propLabel ?s

=== Women's interactions with RAI (Q35 and Q29) ===

SELECT distinct ?person ?personLabel  ?propLabel  ?qual_value ?qual_propLabel ?bnwdLabel # ?s ?prop ?qual_prop

         wikibase:statementProperty ?ps.     

       # to get both q35 and q29

       { ?s ?ps bnwd:Q35 . }  union { ?s ?ps bnwd:Q29 . }

       ?s ?ps ?bnwd.

 

         ?s ?pq ?qual_value .   

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

ORDER BY ?personLabel

===FSAs who didn't interact with SAL [in Q1 or Q8] apart from record of election ===

SELECT distinct ?person ?personLabel

(if(bound(?sal), "yes", "no") as ?q1_q8_interaction)

   ?person bnwdt:P16 ?whatever . # FSAs

    ?person ?p ?s .

       ?prop wikibase:claim ?p;       

         wikibase:statementProperty ?ps.     

      # union query to get both q8 and q1  but *drop* fellow of q8 p75 and p5 q8 archives at (Q712; catalogue says archives bequeathed by another person)

      { ?s ?ps bnwd:Q8 . filter not exists { ?s (bnps:P75|bnps:P5) ?anything . } } 

      { ?s ?ps bnwd:Q1 . }

      ?s ?ps ?sal.

  } #/optional union.  

  # filter(!bound(?sal)).

ORDER BY ?q1_q8_interaction  ?personLabel

=== RAI members who didn't interact with RAI [in Q29 or Q35] apart from record of election ===

#title: RAIs with no record of interacting with RAI in Q29/Q35

# only active engagements like spoke at, corresponded with, etc, and excluding P67 "was member of"  

# CAVEATS: I think this may not be working as intended and it shouldn't be referenced or archived in its current state

SELECT distinct ?person ?personLabel  

(if(bound(?rai), "yes", "no") as ?interaction)

   ?person bnwdt:P7 ?rai_elected .

   # optional query RAIs who do have record of interaction.

       # union query to get both q8 and q1  but *drop* member of p67. a couple of rogue P75 # to get both q35 and q29

       { ?s ?ps bnwd:Q35 . filter not exists { ?s (bnps:P67|bnps:P75) ?member . } }   

       { ?s ?ps bnwd:Q29 . }

       ?s ?ps ?rai.

   # to filter out the interacters completely uncomment the next line and comment out if(bound) in select

   # filter(!bound(?rai)).

 

ORDER BY ?interaction ?personLabel

==Work==

 

===People, the positions that they held, and when they held them (if known)===

# People, the positions that they held, and when they held them (if known)

   ?person bnp:P17 ?hadposition . #using 'held position' for roles not at institutions that typically employed people on a formal, longstanding basis.

   ?hadposition bnps:P17 ?position

   OPTIONAL {?hadposition bnpq:P1 ?date .}

   OPTIONAL {?hadposition bnpq:P27 ?startdate .}

  OPTIONAL {?hadposition bnpq:P28 ?enddate .}

ORDER BY ?positionLabel ?dateLabel

===People, the positions that they held, the institution that employed them (if known), and date information (if known)===

# People, the positions that they held, the institution that employed them (if known), and date information (if known)

SELECT ?person ?personLabel ?position ?positionLabel ?employer ?employerLabel ?date ?startdate ?enddate

   ?person bnp:P105 ?hadposition . #using 'employed as' for roles at institutions that typically employed people on a formal, longstanding basis.

   ?hadposition bnps:P105 ?position

SELECT distinct ?personLabel ?workLabel ?positionLabel ?date_pLabel ?date  

?employerLabel ?organisedLabel ?ofLabel ?person

#?s ?position  ?of  ?work ?employer ?organised

   ?work wikibase:claim ?work_p .   

       ?date_p wikibase:qualifier ?pq;       

               wikibase:propertyType wikibase:Time . # nb *does not* include edtf dates but AFAICT there are none in qualifiers.

               #rdfs:label ?date_label . filter(lang(?date_label)="en-gb") . # what kind of date is it.

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

=== Lecturers ===

SELECT distinct ?person ?personLabel ?positionLabel

?organisedLabel ?ofLabel

?qual_propLabel ?qualLabel

#?position ?of  ?work ?organised ?date_precision ?qual_prop ?date ?qual ?workLabel

?s

 

WHERE {

   ?person bnwdt:P3 bnwd:Q3 . # women

    

 

    { ?s ?bnps bnwd:Q701  . } union { ?s ?bnps bnwd:Q3021 .  }

#        ?pqv wikibase:timePrecision ?date_precision .

      } # /dates

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

} # /where

ORDER BY ?person ?s

=== Excavations ===

#title:BN excavations

# ?member ?director  ?location ?part ?funder ?facilitated ?organised

 

  ?excavation bnwdt:P12 bnwd:Q38 .

     optional { ?excavation bnwdt:P2 ?location .}  

     optional { ?excavation bnwdt:P27 ?start . }  

 

     optional { ?excavation bnwdt:P28 ?end . }  

 

     optional { ?excavation bnwdt:P1 ?pit. }  

     optional { ?excavation bnwdt:P4 ?part . }  

     optional { ?excavation bnwdt:P36 ?director . }

     optional { ?excavation bnwdt:P37 ?member . }

     optional { ?excavation bnwdt:P109 ?organised .}  

     optional { ?excavation bnwdt:P121 ?facilitated . }  

 

    optional { ?excavation bnwdt:P145 ?funder . }

    

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

== Professional Activities and Organisations ==

 

=== Women's service on committees etc (P102) ===

 

SELECT distinct ?personLabel ?serviceLabel ?qual_propLabel  ?qualLabel  ?qual ?person ?s # ?service

   ?person bnwdt:P3 bnwd:Q3 . # select women

   FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team

   ?person bnp:P102 ?s .   # served on P102

    ?s bnps:P102 ?service .

      

  optional { #qualifiers, if any

    ?s ?pq ?qual .    

        ?qual_prop wikibase:qualifier ?pq.    

      } # /quals

 

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

 

ORDER BY ?personLabel ?s

 

=== Women corresponding with individuals ===

#title:women corresponding with individuals

select ?person ?personLabel ?correspondent ?correspondentLabel  ?qualifier_label

?qualifier ?qualifierLabel

 

{

   ?person bnwdt:P3 bnwd:Q3 .

 

   ?person bnp:P73 ?s .

    ?s bnps:P73 ?correspondent . # corresponded with 

     ?correspondent bnwdt:P12 bnwd:Q2137 . # correspondent is i/o human

      { ?s ?qual_p ?qualifier . 

        ?qual_prop wikibase:qualifier ?qual_p.

        ?qual_prop rdfs:label ?qualifier_label. filter(lang(?qualifier_label)='en') .

      }

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

 

ORDER BY ?person

=== Women's service: information about "of" qualifier, including alternatives for locations===

#title: query for served on "of" info

# where a women served on committee *of* organisation, need to link to info about the organisation, including location.

# todo: check whether there are orgs that don't have location info in BN but do have it in wikidata

SELECT distinct ?personLabel ?serviceLabel ?of ?ofLabel ?of_location ?of_locationLabel ?of_geo ?of_long ?of_lat ?person #?s

 

   ?person bnwdt:P3 bnwd:Q3 . # select women

  FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .}  

  ?person  bnp:P102 ?s .  

     ?s bnps:P102 ?service .

     ?s bnpq:P78 ?of .

      #optional { ?of bnwdt:P117 ?of_wikidata. }  #todo

 

      optional { ?of bnwdt:P2 ?of_location.  

 

 

 

 

=== All activities for women that have instance of public and professional activities===

SELECT ?person ?personLabel ?ppa_label ?ppa ?ppa_valueLabel ?ppa_value ?s

WHERE {

  ?person bnwdt:P3 bnwd:Q3 .

      ?ppa wikibase:claim ?p;    

         wikibase:statementProperty ?ps.    

      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

                ?ppa rdfs:label ?ppa_label.

            } # /label service

    

      ?ppa bnwdt:P12 bnwd:Q151 . # instance of PPA     

      ?s ?ps ?ppa_value.

 

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

}

 

order by ?personLabel ?ppa_label

#title:counting women's instance of PPA

SELECT ?ppa_label ?ppa  (count(?ppa) as ?count)

   ?person bnwdt:P3 bnwd:Q3 .

   ?person ?p ?s . 

      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

                      ?ppa rdfs:label ?ppa_label.       # what kind of thing

                } # /label service

    

      ?ppa bnwdt:P12 bnwd:Q151 . # ahhhh at last     

      #?s ?ps ?ppa_value.

 

group by ?ppa_label ?ppa

 

order by desc(?count)

=== PPA Buckets for Women ===

#title:ppa buckets (revised February 2024)

# broad PPA grouping categories to use for overviews and analysis within categories

SELECT distinct ?person ?personLabel ?ppa_label ?ppa ?bucket

   ?person bnwdt:P3 bnwd:Q3 .

   ?person ?p ?s . 

 

      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb".

                      ?ppa rdfs:label ?ppa_label.  

                } # /label service

    

      ?ppa bnwdt:P12 bnwd:Q151 . # i/o ppa     

      ?s ?ps ?ppa_value.

    # BUT then it would be much more opaque and difficult to edit if (when!) we make any changes to the buckets.

),   "fieldwork" ,

                if( str(?ppa_label) in ("acknowledged for role", "applicant to", "employed as", "held position", "held position (free text)", "served on"),  "positions" ,

    as ?bucket).

 

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

 

order by ?personLabel ?ppa_label

=== Suffrage-related activities ===

#title:initial query for suffrage activities

SELECT distinct ?person ?personLabel ?mainLabel ?sub_Label ?qualLabel ?locationLabel ?date

# ?pq ?sub_ ?qual ?main

 

 

    ?person bnwdt:P3 bnwd:Q3 . #select women

    FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team

 

   # sub_ i/o - some do have suffrage activity

  #  optional { ?sub_ bnwdt:P12 ?sub_io . ?sub_ bnwdt:P12 bnwd:Q1166 . }

 

    # a qualifier (anywhere) has i/o suffrage activity

 

#   { # this might find extra linked i/o suffrage but i'm not sure it works.

#    ?sub_ bnwdt:P12 bnwd:Q1166 .

 

    # does *not* have i/o suffrage activity in any qualifiers  ... slows things right down and doesn't seem to make any difference to results?

    #filter not exists { ?s bnpq:P12 bnwd:Q1166. }

    # ... but a qualifier *does* have i/o suffrage activity. i think!

 

   optional { ?s bnpq:P78 ?of. }  # outside the union seems much speedier

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

 

order by ?personLabel ?mainLabel

==Place==

 

=== All the things in BN that can be mapped ===

#title:map of BN locations using wikidata P625

SELECT distinct ?locationLabel ?location ?wikidata ?wd_geo

 

    

      ?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

===People not in London linked as a graph by a NUTS region or country (excluding England) at which they were once resident in the UK===

# People not in London linked as a graph by a NUTS region or country (excluding England) at which they were once resident in the UK

SELECT ?resident ?residentLabel ?region ?regionLabel

   ?resident bnwdt:P29 ?residence .

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2038 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2040 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2041 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2042 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q39.}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2534.}

   ?residence bnwdt:P33 ?district .

   ?district bnwdt:P33 ?county .

===People whose residence was at one time in the east or south east of England, excluding London===

SELECT ?resident ?residentLabel ?residenceLabel ?districtLabel ?countyLabel ?regionLabel

   ?resident bnwdt:P29 ?residence .

   ?residence bnwdt:P33 ?district .

   ?district bnwdt:P33 ?county .

   ?county bnwdt:P33 ?region .

    {?county bnwdt:P33 bnwd:Q67 .}

    {?county bnwdt:P33 bnwd:Q85 .}

ORDER BY ?regionLabel ?countyLabel ?districtLabel ?residenceLabel

===People whose residence was at one time in North East England or Yorkshire and the Humber===

# People whose residence was at one time in North East England or Yorkshire and the Humber

SELECT ?resident ?residentLabel ?residenceLabel ?districtLabel ?countyLabel ?regionLabel

   ?resident bnwdt:P29 ?residence .

   ?residence bnwdt:P33 ?district .

   ?district bnwdt:P33 ?county .

   ?county bnwdt:P33 ?region .

    {?county bnwdt:P33 bnwd:Q72 .}

    {?county bnwdt:P33 bnwd:Q200 .}

ORDER BY ?regionLabel ?countyLabel ?districtLabel ?residenceLabel

===People not in London with the district, county, and region at which they were once resident (in the UK) listed===

# People not in London with the district, county, and region at which they were once resident (in the UK) listed

SELECT ?resident ?residentLabel ?district ?districtLabel ?county ?countyLabel ?region ?regionLabel

   ?resident bnwdt:P29 ?residence .

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2040 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2041 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2042 .}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q39.}

    FILTER NOT EXISTS {?resident bnwdt:P29 bnwd:Q2534.}

   ?residence bnwdt:P33 ?district .

   ?district bnwdt:P33 ?county .

ORDER BY ?regionLabel ?countyLabel ?districtLabel ?residentLabel

===People sorted by the number of places they lived, including the number of cites/towns/villages they lived in===

  FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team

  SERVICE wikibase:label {

      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en-gb".

} group by ?person ?personLabel ?statements order by desc(?residence_count)

 

===Places at which women resided, sorted by how many residences all women had at a given village/town/city/London area===

 

<sparql tryit="1">

# Places at which women resided, sorted by how many residences all women had at a given village/town/city/London area

 

PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>

SELECT ?residence ?residenceLabel (count(?residence) as ?residence_count) #count number of places at which women lived

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

}

===Districts in which women resided, sorted by how many residences all women had in that district (or equivalent sub-level for non-UK residences)===

SELECT ?district ?districtLabel (count(?district) as ?district_count) #count number of districts in which women lived

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

}

<sparql tryit="1">

# Counties in which women resided, sorted by how many residences all women had in that county

SELECT ?county ?countyLabel (count(?county) as ?county_count) #count number of counties in which women lived

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