Project:SPARQL/examples: Difference between revisions

From Beyond Notability

Project:SPARQL/examples (view source)

Revision as of 12:51, 1 July 2024

4,298 bytes added ,  4 months ago
m
→‎Women and their work activities, with employers/organisations and dates
(18 intermediate revisions by the same user not shown)
Line 170: Line 170:
</sparql>
</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's interactions with SAL (Q1 and Q8)===
=== Women's interactions with SAL (Q1 and Q8)===


<sparql tryit="1">
<sparql tryit="1">
#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
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
Line 215: Line 184:
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd:  <http://www.wikidata.org/entity/>


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


WHERE {   
WHERE {   
Line 225: Line 192:


       ?prop wikibase:claim ?p;       
       ?prop wikibase:claim ?p;       
         wikibase:statementProperty ?ps; 
         wikibase:statementProperty ?ps.     
        wikibase:statementValue ?psv;
        wikibase:propertyType ?prop_type.     
      
      
       # union query to get both q8 and q1  
       # union query to get both q8 and q1  
       { ?s ?ps bnwd:Q8 . }  union { ?s ?ps bnwd:Q1 . }
       { ?s ?ps bnwd:Q8 . }  union { ?s ?ps bnwd:Q1 . }
       ?s ?ps ?sal.
       ?s ?ps ?bnwd. #which type
    
    
      optional {  
      optional {  
         ?s ?pq ?pq_value .   
         ?s ?pq ?qual_value .   
          
          
         ?qual_prop wikibase:qualifier ?pq .  
         ?qual_prop wikibase:qualifier ?pq .
        #?qual_prop rdfs:label ?qual_prop_label. filter(lang(?qual_prop_label)="en-gb") .
        #?qual_prop wikibase:propertyType ?qual_prop_type
          
          
       } # /qualifiers
       } # /qualifiers
Line 250: Line 213:


<sparql tryit="1">
<sparql tryit="1">
#title:Women's interactions with RAI (Q35 and Q29)
# CAVEAT: this query may overlook a significant number of interactions and should probably not be reference/archived in its current state
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
PREFIX bnwds: <https://beyond-notability.wikibase.cloud/entity/statement/>
Line 257: Line 222:
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnps: <https://beyond-notability.wikibase.cloud/prop/statement/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX bnpq: <https://beyond-notability.wikibase.cloud/prop/qualifier/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd:  <http://www.wikidata.org/entity/>


SELECT distinct ?person ?personLabel  ?prop ?propLabel ?prop_type ?pq_value ?pq_valueLabel ?qual_propLabel ?qual_prop_type ?rai ?s
SELECT distinct ?person ?personLabel  ?propLabel  ?qual_value ?qual_propLabel ?bnwdLabel # ?s ?prop ?qual_prop


WHERE {   
WHERE {   
Line 267: Line 230:


       ?prop wikibase:claim ?p;       
       ?prop wikibase:claim ?p;       
         wikibase:statementProperty ?ps; 
         wikibase:statementProperty ?ps.     
        wikibase:statementValue ?psv;
        wikibase:propertyType ?prop_type.     
      
      
       # to get both q35 and q29  
       # to get both q35 and q29  
       { ?s ?ps bnwd:Q35 . }  union { ?s ?ps bnwd:Q29 . }
       { ?s ?ps bnwd:Q35 . }  union { ?s ?ps bnwd:Q29 . }
       ?s ?ps ?rai.
       ?s ?ps ?bnwd.


       # qualifiers.  
       # qualifiers.  
       optional {  
       optional {  
         ?s ?pq ?pq_value .   
         ?s ?pq ?qual_value .   
          
          
         ?qual_prop wikibase:qualifier ?pq .   
         ?qual_prop wikibase:qualifier ?pq .   
        #?qual_prop rdfs:label ?qual_prop_label. filter(lang(?qual_prop_label)="en-gb") .
        ?qual_prop wikibase:propertyType ?qual_prop_type .
          
          
       } # /qualifiers
       } # /qualifiers
Line 296: Line 255:
#title: FSAs with no record of interacting with SAL in Q1 / Q8
#title: FSAs with no record of interacting with SAL in Q1 / Q8
# only active engagements like spoke at, corresponded with, etc, and excluding P75 "was fellow of" and P5 "archives at".
# only active engagements like spoke at, corresponded with, etc, and excluding P75 "was fellow of" and P5 "archives at".
# WIP: to update with other SAL records as I work through queries for them. Plus caveat that there may be other sources of interaction not recorded in the database!
# CAVEATS: I think this is not working as intended and it shouldn't be referenced or archived


PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
Line 344: Line 303:
#title: RAIs with no record of interacting with RAI in Q29/Q35
#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"  
# only active engagements like spoke at, corresponded with, etc, and excluding P67 "was member of"  
# WIP: to update with other RAI records as I work through queries for them. Plus caveat that there may be other sources of interaction not recorded in the database.
# CAVEATS: I think this may not be working as intended and it shouldn't be referenced or archived in its current state


PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
PREFIX bnwd: <https://beyond-notability.wikibase.cloud/entity/>
Line 455: Line 414:
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>
PREFIX bnpqv: <https://beyond-notability.wikibase.cloud/prop/qualifier/value/>


SELECT distinct ?personLabel ?workLabel ?positionLabel ?date_pLabel ?date  
SELECT distinct ?personLabel ?workLabel ?positionLabel ?date_propLabel ?date  
?employerLabel ?organisedLabel ?ofLabel ?person
?employerLabel ?organisedLabel ?ofLabel ?locationLabel ?person
#?s  ?position  ?of  ?work ?employer ?organised
#?s  ?position  ?of  ?work ?employer ?organised ?location ?date_prop


WHERE {
WHERE {
Line 471: Line 430:
     # more about the position
     # more about the position
     ?s ( bnps:P17|bnps:P48|bnps:P105 ) ?position .   
     ?s ( bnps:P17|bnps:P48|bnps:P105 ) ?position .   
     # employer / organised by / of (incl free text). TODO: check whether these are mutually exclusive; could make this a union.
     # employer / organised by / of (incl free text).  
     # for position held, employer>of [ontology day discussion 13/9/23]
     # for position held, employer>of [ontology day discussion 13/9/23]
     OPTIONAL { ?s bnpq:P18 ?employer .}   
     OPTIONAL { ?s bnpq:P18 ?employer .}   
Line 477: Line 436:
     OPTIONAL { ?s ( bnpq:P78 | bnpq:P66 ) ?of .}
     OPTIONAL { ?s ( bnpq:P78 | bnpq:P66 ) ?of .}
   
   
  # optional { ?s bnpq:P2 ?location . } # sometimes there is location and no employer/org/of
    optional { ?s bnpq:P2 ?location . }
      
      
   # dates
   # dates with property labels.
     optional {
     optional {
     ?s ?pq ?date .  
     ?s ?pq ?date .
     
       ?date_prop wikibase:qualifier ?pq;       
       ?date_p wikibase:qualifier ?pq;       
               wikibase:propertyType wikibase:Time .
              #wikibase:propertyType ?date_prop_type; # can check date property types if needed
       } # /dates
               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.
       } # /dates
    
    
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }  
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }  
Line 614: Line 570:


ORDER BY ?person ?s   
ORDER BY ?person ?s   
</sparql>
=== Excavations ===
<sparql tryit="1">
#title:BN 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 ?excavationLabel ?excavation ?locationLabel ?start ?end ?pit
?partLabel ?funderLabel ?organisedLabel ?facilitatedLabel ?directorLabel ?memberLabel
# ?member ?director  ?location ?part ?funder ?facilitated ?organised
WHERE { 
  # instance of excavation  128
  ?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". }
}
ORDER BY ?excavationLabel
</sparql>
</sparql>


Line 845: Line 847:
       ?s ?ps ?ppa_value.
       ?s ?ps ?ppa_value.
    
    
     # note: it would be possible to write this to use ppa rather than ppa_label
     # note: it would be possible to write this to use ppa rather than ppa_label. there's a slight risk that property names might change.
     # BUT then it would be much more opaque and difficult to edit if (when!) we make any changes to the buckets.
     # BUT then it would be much more opaque and difficult to edit if (when!) we make any changes to the buckets.
     bind(
     bind(
Line 867: Line 869:
order by ?personLabel ?ppa_label
order by ?personLabel ?ppa_label
</sparql>
</sparql>


=== Suffrage-related activities ===
=== Suffrage-related activities ===
Line 1,340: Line 1,341:
#title:Counties of residence for women recorded in CAS Indexes of Archaeological Papers
#title:Counties of residence for women recorded in CAS Indexes of Archaeological Papers


SELECT distinct ?person ?personLabel ?county ?countyLabel ?wikidata ?geo   
SELECT distinct ?person ?personLabel ?residence ?residenceLabel ?county ?countyLabel ?wikidata ?geo   
WHERE {   
WHERE {   
   ?person bnwdt:P3 bnwd:Q3 . # select women
   ?person bnwdt:P3 bnwd:Q3 . # select women
Line 1,346: Line 1,347:
   FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team
   FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .} #filter out project team
   ?person bnwdt:P29 ?residence . # select residences
   ?person bnwdt:P29 ?residence . # select residences
   ?residence bnwdt:P33 ?district . #select districts
 
   ?district bnwdt:P33 ?county . #select counties
   ?residence bnwdt:P33 ?district . #select p33 of residence
      optional { ?county bnwdt:P153 ?geo2 .  } # for geocoords added to BN
   ?district bnwdt:P33 ?next . #select p33 of district, mostly counties but a few region/outside uk
    
    
       optional {     
  optional {
       ?county bnp:P117 ?ws .  # wikidata id P117         
    # is ?next a uk region?
    ?next bnwdt:P12 bnwd:Q4207 .
    BIND(BOUND(?next) AS ?region).
    }
  optional {
    # is ?next in the UK?
    ?next bnwdt:P33* bnwd:Q1741 .
    BIND(BOUND(?next) AS ?uk).
    }
 
  # if next=q4207 uk region, then use ?district for county, ifelse in uk use ?next, else not in uk use ?district.
  bind(
       if( bound(?region), ?district, # region first (because also uk)
        if( bound(?uk), ?next, # then rest of uk 
      ?district)) # then anything not in uk.
    as ?county).
 
    optional { ?county bnwdt:P153 ?geo2 .  } # for geocoords added to BN
 
    optional {     
       ?county bnp:P117 ?ws .  # wikidata id P117         
         ?ws bnpq:P14 ?wikidata_url .
         ?ws bnpq:P14 ?wikidata_url .
         BIND(IRI(REPLACE(?wikidata_url, "https://www.wikidata.org/wiki/", "http://www.wikidata.org/entity/")) as ?wikidata )  
         BIND(IRI(REPLACE(?wikidata_url, "https://www.wikidata.org/wiki/", "http://www.wikidata.org/entity/")) as ?wikidata )  
Line 2,282: Line 2,303:
group by ?wd_io ?wd_io_label
group by ?wd_io ?wd_io_label
order by ?wd_io_label
order by ?wd_io_label
</sparql>
=== Localities not in the UK ===
<sparql tryit="1">
##admin_paths lists the linked P33 for the item. Unfortunately, they aren't reliably returned in ascending order.
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:localities not in the UK
select  ?item ?itemLabel (group_concat(distinct ?admin_label; separator=", " ) as ?admin_path)
where {
  ?item bnwdt:P12 bnwd:Q2147 . # i/o locality
  ?item bnwdt:P33* ?admin.   
    ?admin rdfs:label ?admin_label. filter(lang(?admin_label)="en") .
 
  filter not exists { ?item bnwdt:P33* bnwd:Q1741.  } # filter out anything in the UK
  filter (?item not in (bnwd:Q619, bnwd:Q2961, bnwd:Q4207, bnwd:Q86) ). # and a few things that are not specific places
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
group by ?item ?itemLabel
order by ?itemLabel
</sparql>
</sparql>


Line 3,729: Line 3,779:
ORDER BY ?item ?distance
ORDER BY ?item ?distance
</sparql>
</sparql>
=== Traversing instance of with GAS ===
<sparql tryit="1">
#title:GAS
# The Gather, Apply, and Scatter (GAS) service provides graph traversal, graph mining, and similar classes of algorithms for SPARQL.
# In practical terms, it enables a series of relations to be followed through the graph; for instance the chain of father, grandfather, great-grandfather &c - tracing the father (P22) line - of a subject item
# 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  ?ioLabel ?itemLabel ?io ?item
WHERE {
      SERVICE gas:service {
          gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ;
                      gas:in bnwd:Q3099 ;
                      gas:linkType bnwdt:P12 ;
                      gas:traversalDirection "Reverse";
                      gas:out ?item ;
                      gas:out1 ?depth ;
                      gas:out2 ?io .
      }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    }
order by ?depth ?ioLabel ?itemLabel
</sparql>


=== Working with property paths: instance of [CAVEAT: experimental, probably wrong]===
=== Working with property paths: instance of [CAVEAT: experimental, probably wrong]===
Line 3,978: Line 4,062:
   UNION
   UNION
    
    
   { # qualifier dates
   { # FIXME? qualifier dates. hmm... how is this different from above? I think something is missing.
      
      
     ?person bnwdt:P3 bnwd:Q3 .   
     ?person bnwdt:P3 bnwd:Q3 .   
Line 4,171: Line 4,255:


order by ?person ?date_value
order by ?person ?date_value
</sparql>
=== Women who were adults in 1911 ===
<sparql tryit="1">
#title:Women who were adults in 1911 and their calculated ages
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 ?age1911
WHERE {
  ?person bnwdt:P3 bnwd:Q3 ;
        wikibase:statements ?statements .
  FILTER NOT EXISTS {?person bnwdt:P4 bnwd:Q12 .}
  ?person bnwdt:P26 ?dob . 
  #optional {
      ?person bnwdt:P15 ?dod . 
  #}
 
  filter(year(?dod) > 1911 && year(?dob) <=1893 ) .
  #if dod is made optional a more complex filter to include some women who were likely to still be alive in 1911. but doesnt add very many.
  #filter( ( year(?dob) <= 1893  && year(?dod) > 1911 ) || ( year(?dob) <= 1893 && year(?dob) >1870 ) ) .
 
  #Date literals can be written by adding ^^xsd:dateTime to an ISO 8601 date string: "2012-10-29"^^xsd:dateTime
 
  bind(year("1911-07-01"^^xsd:dateTime) - year(?dob) as ?age1911 ).
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,en-gb". }
}
ORDER BY ?personLabel
</sparql>
</sparql>
Sharon
3,427

edits

Retrieved from "https://beyond-notability.wikibase.cloud/wiki/Special:MobileDiff/58690...61131"