SPARQL examples: Difference between revisions

From clb-lod
 
(6 intermediate revisions by the same user not shown)
Line 21: Line 21:
   } group by ?wikibase_item ?pubtype ?1stauthor ?titles  
   } group by ?wikibase_item ?pubtype ?1stauthor ?titles  
</sparql>
</sparql>
=== Which records contain unreconciled creator statements? ===
=== Which records contain unreconciled creator statements? ===
This lists the records containing creators as literal values (and not as proper entity links), and which those literal values are. The result can be used for the OpenRefine software.
<sparql tryit=1">
<sparql tryit=1">
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
Line 69: Line 69:
</sparql>
</sparql>
=== Distribution of MARC creator roles ===
=== Distribution of MARC creator roles ===
This will list MARC creator roles existing in the data, and number of creator statements.
This will list MARC creator roles existing in the data, and number of creator statements for each role. Change visualization type to "table" for seeing the source data used for the bar chart visualization.
<sparql tryit="1">
<sparql tryit="1">
#defaultView:BarChart
#defaultView:BarChart
Line 92: Line 92:
order by desc(?number_of_creator_statements)
order by desc(?number_of_creator_statements)
</sparql>
</sparql>
=== How many NKCR creators don't were not found on Wikidata? ===
 
This gives the number of NKCR-disambiguated authors not found on Wikidata.
=== How many NKCR creators were not found on Wikidata? ===
This gives the number of NKCR-disambiguated creators vs. the number of these found on Wikidata.
<sparql Tryit="1">
<sparql Tryit="1">
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
Line 110: Line 111:
   ?wikibase_item clbdp:P5 clbwb:Q5;
   ?wikibase_item clbdp:P5 clbwb:Q5;
                 clbdp:P8 ?nkcr .
                 clbdp:P8 ?nkcr .
   optional {?wikibase_item clbdp:P1 ?wikidata .}
   optional {?wikibase_item clbdp:P1 ?wikidata .}}
</sparql>
</sparql>
=== How many creator statements are not reconciled? ===
=== How many creator statements are not reconciled? ===
Line 133: Line 134:
   filter not exists {?creatorstatement ?stprop [].}
   filter not exists {?creatorstatement ?stprop [].}
   } group by ?number_of_unreconciled
   } group by ?number_of_unreconciled
</sparql>
===Co-authors of one author===
This shows the co-authors of one author as graph visualization (change visualization type to "table" for seeing the source data used for the graph visualization)
<sparql tryit="1">
#defaultView:Graph
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>
select ?author ?authorLabel ?coauthor ?coauthorLabel (count(?bibItem) as ?count)
where {
  bind(clbwb:Q204 as ?author) # author we want to see the co-authors (e.g. Q204, V. Malínek)
  ?author rdfs:label ?authorLabel. filter(lang(?authorLabel) = "cs")
  ?bibItem clbdp:P5 clbwb:Q3; clbdp:P12 ?author. # all bibitems authored by that author
  ?bibItem clbdp:P12 ?coauthor. filter(?coauthor != ?author).
  ?coauthor rdfs:label ?coauthorLabel. filter(lang(?coauthorLabel) = "cs")
  } group by ?author ?authorLabel ?coauthor ?coauthorLabel ?count order by desc(?count)
</sparql>
</sparql>


== UDC Classificators ==
=== UDC Classificators in CLB-LOD ===
UDC Classificators, at the moment, are represented as literals as in MARC. A conceptual mapping, i.e. linking classificators to concepts, is planned.
<sparql tryit="1">
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>
select ?udc_literal (count(?wikibase_item) as ?bibitems)
       
where {
  ?wikibase_item clbp:P86 [clbpq:P87 ?udc_literal] .
 
} group by ?udc_literal ?bibitems order by desc(?bibitems)
</sparql>
== CLB-LOD Ontology ==
== CLB-LOD Ontology ==
=== Overview of items reserved for the CLB-LOD Ontology (Q1 to Q200) ===
=== Overview of items reserved for the CLB-LOD Ontology (Q1 to Q200) ===
<sparql tryit="1">
<sparql tryit="1">
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>

Latest revision as of 11:33, 13 October 2022

SPARQL Query Examples

MARC records

MARC records in CLB-LOD

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?wikibase_item ?pubtype ?1stauthor (group_concat(?title;SEPARATOR=" == ") as ?titles)
       
where { 
  ?wikibase_item clbdp:P5 clbwb:Q3; clbdp:P58 ?type; clbdp:P6 ?title.
  ?type rdfs:label ?pubtype . filter(lang(?pubtype)="en")
  optional {?wikibase_item clbp:P12 [clbps:P12 ?1stau; clbpq:P14 "1"] .
           ?1stau clbdp:P48 ?1stauthor.}
  } group by ?wikibase_item ?pubtype ?1stauthor ?titles

Try it!

Which records contain unreconciled creator statements?

This lists the records containing creators as literal values (and not as proper entity links), and which those literal values are. The result can be used for the OpenRefine software.

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?bibItem (iri(concat("https://vufind.ucl.cas.cz/Record/",?record)) as ?clb_link) ?creatorstatement ?firstname ?lastname
where {
  ?marc_creator_prop clbdp:P5 clbwb:Q33; rdfs:label ?marc_creator_role_label . filter (lang(?marc_creator_role_label)="en")
  bind(strafter(str(?marc_creator_prop),"https://clb-lod.wikibase.cloud/entity/") as ?pid)
  bind(iri(concat("https://clb-lod.wikibase.cloud/prop/statement/",?pid)) as ?stprop)
  bind(iri(concat("https://clb-lod.wikibase.cloud/prop/",?pid)) as ?prop)
  ?bibItem clbp:P5 [clbps:P5 clbwb:Q3; clbpq:P7 ?record]; ?prop ?creatorstatement . 
  ?creatorstatement clbpq:P48 ?lastname . optional {?creatorstatement clbpq:P47 ?firstname.}
  filter not exists {?creatorstatement ?stprop [].}
  } order by ?lastname

Try it!


Creators

Creators in CLB-LOD

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?wikibase_item ?label
  (iri(concat("http://www.wikidata.org/entity/",?wikidata)) as ?wikidata_link) 
  (iri(concat("https://aleph.nkp.cz/F/?func=find-c&local_base=aut&ccl_term=ica=",?nkcr)) as ?nkcr_link)
  (count(?bibitem) as ?author_roles)
       
where { 
  ?wikibase_item clbdp:P5 clbwb:Q5; rdfs:label ?label. filter (lang(?label)="en")
  ?wikibase_item clbdp:P8 ?nkcr .
  optional {?wikibase_item clbdp:P1 ?wikidata .}
  optional {?bibitem clbdp:P12 ?wikibase_item .}
  
} group by ?wikibase_item ?label ?nkcr ?wikidata ?author_roles order by desc(?author_roles)

Try it!

Distribution of MARC creator roles

This will list MARC creator roles existing in the data, and number of creator statements for each role. Change visualization type to "table" for seeing the source data used for the bar chart visualization.

#defaultView:BarChart
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?marc_creator_role ?marc_creator_role_label (count(?bibItem) as ?number_of_creator_statements)
                           
where {
                           
?marc_creator_prop clbdp:P5 clbwb:Q33; wikibase:directClaim ?marc_creator_role; rdfs:label ?marc_creator_role_label .
  filter (lang(?marc_creator_role_label)="en")
?bibItem ?marc_creator_role [] .                

}
group by ?marc_creator_role ?marc_creator_role_label ?number_of_creator_statements        
order by desc(?number_of_creator_statements)

Try it!


How many NKCR creators were not found on Wikidata?

This gives the number of NKCR-disambiguated creators vs. the number of these found on Wikidata.

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select   
  (count(?nkcr) as ?nkcr_count)
  (count(?wikidata) as ?wikidata_count)
       
where { 
  ?wikibase_item clbdp:P5 clbwb:Q5;
                 clbdp:P8 ?nkcr .
  optional {?wikibase_item clbdp:P1 ?wikidata .}}

Try it!

How many creator statements are not reconciled?

This returns the number of creator statements (all roles) that do not point to a creator item (but to name literals instead)

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select (count (distinct ?creatorstatement) as ?number_of_unreconciled)
where {
  ?marc_creator_prop clbdp:P5 clbwb:Q33; rdfs:label ?marc_creator_role_label . filter (lang(?marc_creator_role_label)="en")
  bind(strafter(str(?marc_creator_prop),"https://clb-lod.wikibase.cloud/entity/") as ?pid)
  bind(iri(concat("https://clb-lod.wikibase.cloud/prop/statement/",?pid)) as ?stprop)
  bind(iri(concat("https://clb-lod.wikibase.cloud/prop/",?pid)) as ?prop)
  ?bibItem clbdp:P5 clbwb:Q3; ?prop ?creatorstatement . 
  ?creatorstatement clbpq:P48 ?lastname . optional {?creatorstatement clbpq:P47 ?firstname.}
  filter not exists {?creatorstatement ?stprop [].}
  } group by ?number_of_unreconciled

Try it!

Co-authors of one author

This shows the co-authors of one author as graph visualization (change visualization type to "table" for seeing the source data used for the graph visualization)

#defaultView:Graph
PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?author ?authorLabel ?coauthor ?coauthorLabel (count(?bibItem) as ?count)
where {
  bind(clbwb:Q204 as ?author) # author we want to see the co-authors (e.g. Q204, V. Malínek)
  ?author rdfs:label ?authorLabel. filter(lang(?authorLabel) = "cs")
  ?bibItem clbdp:P5 clbwb:Q3; clbdp:P12 ?author. # all bibitems authored by that author
  ?bibItem clbdp:P12 ?coauthor. filter(?coauthor != ?author).
  ?coauthor rdfs:label ?coauthorLabel. filter(lang(?coauthorLabel) = "cs")
  } group by ?author ?authorLabel ?coauthor ?coauthorLabel ?count order by desc(?count)

Try it!


UDC Classificators

UDC Classificators in CLB-LOD

UDC Classificators, at the moment, are represented as literals as in MARC. A conceptual mapping, i.e. linking classificators to concepts, is planned.

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?udc_literal (count(?wikibase_item) as ?bibitems)
         
where { 
  ?wikibase_item clbp:P86 [clbpq:P87 ?udc_literal] .
  
} group by ?udc_literal ?bibitems order by desc(?bibitems)

Try it!

CLB-LOD Ontology

Overview of items reserved for the CLB-LOD Ontology (Q1 to Q200)

PREFIX clbwb: <https://clb-lod.wikibase.cloud/entity/>
PREFIX clbdp: <https://clb-lod.wikibase.cloud/prop/direct/>
PREFIX clbp: <https://clb-lod.wikibase.cloud/prop/>
PREFIX clbps: <https://clb-lod.wikibase.cloud/prop/statement/>
PREFIX clbpq: <https://clb-lod.wikibase.cloud/prop/qualifier/>
PREFIX clbpr: <https://clb-lod.wikibase.cloud/prop/reference/>
PREFIX clbno: <https://clb-lod.wikibase.cloud/prop/novalue/>

select ?wikibase_item ?label
       
where { 
  ?wikibase_item rdfs:label ?label. filter (lang(?label)="en")
  values ?wikibase_item {clbwb:Q1 clbwb:Q2 clbwb:Q3 clbwb:Q4 clbwb:Q5 clbwb:Q6 clbwb:Q7 clbwb:Q8 clbwb:Q9 clbwb:Q10 clbwb:Q11 clbwb:Q12 clbwb:Q13 clbwb:Q14 clbwb:Q15 clbwb:Q16 clbwb:Q17 clbwb:Q18 clbwb:Q19 clbwb:Q20 clbwb:Q21 clbwb:Q22 clbwb:Q23 clbwb:Q24 clbwb:Q25 clbwb:Q26 clbwb:Q27 clbwb:Q28 clbwb:Q29 clbwb:Q30 clbwb:Q31 clbwb:Q32 clbwb:Q33 clbwb:Q34 clbwb:Q35 clbwb:Q36 clbwb:Q37 clbwb:Q38 clbwb:Q39 clbwb:Q40 clbwb:Q41 clbwb:Q42 clbwb:Q43 clbwb:Q44 clbwb:Q45 clbwb:Q46 clbwb:Q47 clbwb:Q48 clbwb:Q49 clbwb:Q50 clbwb:Q51 clbwb:Q52 clbwb:Q53 clbwb:Q54 clbwb:Q55 clbwb:Q56 clbwb:Q57 clbwb:Q58 clbwb:Q59 clbwb:Q60 clbwb:Q61 clbwb:Q62 clbwb:Q63 clbwb:Q64 clbwb:Q65 clbwb:Q66 clbwb:Q67 clbwb:Q68 clbwb:Q69 clbwb:Q70 clbwb:Q71 clbwb:Q72 clbwb:Q73 clbwb:Q74 clbwb:Q75 clbwb:Q76 clbwb:Q77 clbwb:Q78 clbwb:Q79 clbwb:Q80 clbwb:Q81 clbwb:Q82 clbwb:Q83 clbwb:Q84 clbwb:Q85 clbwb:Q86 clbwb:Q87 clbwb:Q88 clbwb:Q89 clbwb:Q90 clbwb:Q91 clbwb:Q92 clbwb:Q93 clbwb:Q94 clbwb:Q95 clbwb:Q96 clbwb:Q97 clbwb:Q98 clbwb:Q99 clbwb:Q100 clbwb:Q101 clbwb:Q102 clbwb:Q103 clbwb:Q104 clbwb:Q105 clbwb:Q106 clbwb:Q107 clbwb:Q108 clbwb:Q109 clbwb:Q110 clbwb:Q111 clbwb:Q112 clbwb:Q113 clbwb:Q114 clbwb:Q115 clbwb:Q116 clbwb:Q117 clbwb:Q118 clbwb:Q119 clbwb:Q120 clbwb:Q121 clbwb:Q122 clbwb:Q123 clbwb:Q124 clbwb:Q125 clbwb:Q126 clbwb:Q127 clbwb:Q128 clbwb:Q129 clbwb:Q130 clbwb:Q131 clbwb:Q132 clbwb:Q133 clbwb:Q134 clbwb:Q135 clbwb:Q136 clbwb:Q137 clbwb:Q138 clbwb:Q139 clbwb:Q140 clbwb:Q141 clbwb:Q142 clbwb:Q143 clbwb:Q144 clbwb:Q145 clbwb:Q146 clbwb:Q147 clbwb:Q148 clbwb:Q149 clbwb:Q150 clbwb:Q151 clbwb:Q152 clbwb:Q153 clbwb:Q154 clbwb:Q155 clbwb:Q156 clbwb:Q157 clbwb:Q158 clbwb:Q159 clbwb:Q160 clbwb:Q161 clbwb:Q162 clbwb:Q163 clbwb:Q164 clbwb:Q165 clbwb:Q166 clbwb:Q167 clbwb:Q168 clbwb:Q169 clbwb:Q170 clbwb:Q171 clbwb:Q172 clbwb:Q173 clbwb:Q174 clbwb:Q175 clbwb:Q176 clbwb:Q177 clbwb:Q178 clbwb:Q179 clbwb:Q180 clbwb:Q181 clbwb:Q182 clbwb:Q183 clbwb:Q184 clbwb:Q185 clbwb:Q186 clbwb:Q187 clbwb:Q188 clbwb:Q189 clbwb:Q190 clbwb:Q191 clbwb:Q192 clbwb:Q193 clbwb:Q194 clbwb:Q195 clbwb:Q196 clbwb:Q197 clbwb:Q198 clbwb:Q199 clbwb:Q200 }
bind(xsd:integer(strafter(str(?wikibase_item),"https://clb-lod.wikibase.cloud/entity/Q")) as ?order) 
} order by ?order

Try it!