query-a4cd24e5dd2afc37108e658555e82516
Query all paintingsHi, i'm currently working on an open source university project. We query different datasets from wikidata about art. In our ETL process we get the data we want with help of the pywikibot libary (currently only from wikidata). This libary loads the wikidata sites by their qId's which we query beforehand. For the result set of around 150.000 entries this process takes very long time (47 hours) and my idea to improve it was to write a query which returns all rows and columns we want at once. I wrote this query:
Use at
- https://query.wikidata.org/sparql
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX bd: <http://www.bigdata.com/rdf#>
SELECT
?artwork
(CONCAT('[', GROUP_CONCAT(DISTINCT ?class;SEPARATOR=', '), ']') as ?classes)
?artworkLabel
?artworkDescription
?image
(CONCAT('[',GROUP_CONCAT(DISTINCT ?creator;SEPARATOR=', '), ']') as ?creators)
(CONCAT('[',GROUP_CONCAT(DISTINCT ?location;SEPARATOR=', '), ']') as ?locations)
(CONCAT('[',GROUP_CONCAT(DISTINCT ?genre;SEPARATOR=', '), ']') as ?genres)
(CONCAT('[',GROUP_CONCAT(DISTINCT ?movement;SEPARATOR=', '), ']') as ?movements)
(YEAR(xsd:dateTime(?inception)) AS ?inceptionYear)
(CONCAT('[',GROUP_CONCAT(DISTINCT ?material;SEPARATOR=', '), ']') as ?materials)
(CONCAT('[',GROUP_CONCAT(DISTINCT ?depicts;SEPARATOR=', '), ']') as ?depictions)
?countryLabel
?height
?width
WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
?class (wdt:P279*) wd:Q3305213 .
?artwork wdt:P31 ?class ;
wdt:P170 ?creator ;
wdt:P18 ?image .
OPTIONAL { ?artwork wdt:P276 ?location . }
OPTIONAL { ?artwork wdt:P136 ?genre . }
OPTIONAL { ?artwork wdt:P135 ?movement . }
OPTIONAL { ?artwork wdt:P571 ?inception . }
OPTIONAL { ?artwork wdt:P186 ?material . }
OPTIONAL { ?artwork wdt:P180 ?depicts . }
OPTIONAL { ?artwork wdt:P17 ?country . }
OPTIONAL { ?artwork wdt:P2048 ?height . }
OPTIONAL { ?artwork wdt:P2049 ?width . }
} GROUP BY ?artwork ?artworkLabel ?artworkDescription ?image ?inception ?countryLabel ?height ?width
Query found at
graph TD
classDef projected fill:lightgreen;
classDef literal fill:orange;
classDef iri fill:yellow;
v14("?_anon_42b5416610844a34809f33a099ae9de12198"):::projected
v15("?_anon_42b5416610844a34809f33a099ae9de12199"):::projected
v16("?_anon_42b5416610844a34809f33a099ae9de12200"):::projected
v17("?_anon_42b5416610844a34809f33a099ae9de12201"):::projected
v18("?_anon_42b5416610844a34809f33a099ae9de12202"):::projected
v19("?_anon_42b5416610844a34809f33a099ae9de12203"):::projected
v20("?_anon_42b5416610844a34809f33a099ae9de12204"):::projected
v2("?artwork"):::projected
v1("?class")
v21("?classes")
v11("?country")
v3("?creator")
v22("?creators")
v28("?depictions")
v10("?depicts")
v6("?genre")
v24("?genres")
v12("?height"):::projected
v4("?image"):::projected
v8("?inception"):::projected
v26("?inceptionYear")
v5("?location")
v23("?locations")
v9("?material")
v27("?materials")
v7("?movement")
v25("?movements")
v13("?width"):::projected
c4(["en"]):::literal
c2(["bd:serviceParam"]):::iri
c6(["wd:Q3305213"]):::iri
subgraph s1["http://wikiba.se/ontology#label"]
style s1 stroke-width:4px;
c2 --"wikibase:language"--> c4
end
v1 --"wdt:P279"--> c6
v2 --"wdt:P31"--> v1
v2 --"wdt:P170"--> v3
v2 --"wdt:P18"--> v4
subgraph optional0["(optional)"]
style optional0 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P276".-> v5
end
subgraph optional1["(optional)"]
style optional1 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P136".-> v6
end
subgraph optional2["(optional)"]
style optional2 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P135".-> v7
end
subgraph optional3["(optional)"]
style optional3 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P571".-> v8
end
subgraph optional4["(optional)"]
style optional4 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P186".-> v9
end
subgraph optional5["(optional)"]
style optional5 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P180".-> v10
end
subgraph optional6["(optional)"]
style optional6 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P17".-> v11
end
subgraph optional7["(optional)"]
style optional7 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P2048".-> v12
end
subgraph optional8["(optional)"]
style optional8 fill:#bbf,stroke-dasharray: 5 5;
v2 -."wdt:P2049".-> v13
end
bind7[/"?class"/]
v1 --o bind7
bind7 --as--o v14
bind8[/"?creator"/]
v3 --o bind8
bind8 --as--o v15
bind9[/"?location"/]
v5 --o bind9
bind9 --as--o v16
bind10[/"?genre"/]
v6 --o bind10
bind10 --as--o v17
bind11[/"?movement"/]
v7 --o bind11
bind11 --as--o v18
bind12[/"?material"/]
v9 --o bind12
bind12 --as--o v19
bind13[/"?depicts"/]
v10 --o bind13
bind13 --as--o v20
bind14[/"concat('#91;',,'#93;')"/]
null --o bind14
bind14 --as--o v21
bind15[/"concat('#91;',,'#93;')"/]
null --o bind15
bind15 --as--o v22
bind16[/"concat('#91;',,'#93;')"/]
null --o bind16
bind16 --as--o v23
bind17[/"concat('#91;',,'#93;')"/]
null --o bind17
bind17 --as--o v24
bind18[/"concat('#91;',,'#93;')"/]
null --o bind18
bind18 --as--o v25
bind19[/"year-from-dateTime(http://www.w3.org/2001/XMLSchema#dateTime(?inception))"/]
v8 --o bind19
bind19 --as--o v26
bind20[/"concat('#91;',,'#93;')"/]
null --o bind20
bind20 --as--o v27
bind21[/"concat('#91;',,'#93;')"/]
null --o bind21
bind21 --as--o v28