query-a46729fc33e0ab476bb8042897f785bd
Query to show England administrative hierarchy # -- regions, ceremonial counties, administrative counties, and districts # Compare [[:en:Subdivisions of England]]
SELECT ?region ?regionLabel ?cerem ?ceremLabel ?adm_county ?adm_countyLabel
(GROUP_CONCAT(DISTINCT(?dist_label); separator="; ") AS ?dists)
WHERE {
?region p:P31/ps:P31 wd:Q48091 . # region
# ?region p:P131/ps:P131 wd:Q21 . # England
{?cerem p:P131/ps:P131 ?region . ?cerem p:P31/ps:P31 wd:Q180673 }
UNION
{?region p:P31/ps:P31 wd:Q180673 . BIND(?region AS ?cerem)} .
FILTER NOT EXISTS {?cerem p:P31/ps:P31 wd:Q19953632} .
?adm_county (p:P131/ps:P131)? ?cerem .
{ VALUES ?allowed_adm_county_type { wd:Q769628 wd:Q769603 wd:Q1136601 } .
# metropolitan county, non-metropolitan county, unitary authority
?adm_county p:P31/ps:P31 ?allowed_adm_county_type }
UNION
{VALUES ?adm_county { wd:Q23306 wd:Q23311 } } . # City of London & Greater London
VALUES ?allowed_district_type { wd:Q1187580 wd:Q1002812 wd:Q211690 }
OPTIONAL {
?adm_county p:P31/ps:P31 wd:Q1136601 .
BIND ('' AS ?dist)
BIND (' (unitary) ' AS ?dist_label)
}
OPTIONAL {
?dist p:P131/ps:P131 ?adm_county .
?dist p:P31/ps:P31 ?allowed_district_type .
?dist rdfs:label ?dist_label filter (lang(?dist_label) = "en") .
}
FILTER (!(sameTerm(?adm_county, wd:Q1342914) && sameTerm(?region, wd:Q47994))) . # North East Lincs
FILTER (!(sameTerm(?adm_county, wd:Q175945) && sameTerm(?region, wd:Q47994))) . # North Lincs
FILTER (!(sameTerm(?adm_county, wd:Q21269047) && sameTerm(?region, wd:Q48063))) . # Lincs council area
FILTER (!(sameTerm(?adm_county, wd:Q171866) && sameTerm(?region, wd:Q48063))) . # Middlesbrough
FILTER (!(sameTerm(?adm_county, wd:Q1434448) && sameTerm(?region, wd:Q48063))) . # Redcar & Cleveland
FILTER (!(sameTerm(?adm_county, wd:Q894094) && sameTerm(?region, wd:Q48063))) . # Stockton
FILTER (!(sameTerm(?adm_county, wd:Q21241814) && sameTerm(?region, wd:Q47983))) . # North Yorkshire
FILTER (!(sameTerm(?adm_county, wd:Q42462) && sameTerm(?region, wd:Q47983))) . # York
SERVICE wikibase:label {
bd:serviceParam wikibase:language "en" .
}
} GROUP BY ?region ?regionLabel ?cerem ?ceremLabel ?adm_county ?adm_countyLabel
ORDER BY ?regionLabel ?ceremLabel ?adm_countyLabel
Use at
- https://query.wikidata.org/sparql
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
# Query to show England administrative hierarchy
# -- regions, ceremonial counties, administrative counties, and districts
# Compare [[:en:Subdivisions of England]]
SELECT ?region ?regionLabel ?cerem ?ceremLabel ?adm_county ?adm_countyLabel
(GROUP_CONCAT(DISTINCT(?dist_label); separator="; ") AS ?dists)
WHERE {
?region p:P31/ps:P31 wd:Q48091 . # region
# ?region p:P131/ps:P131 wd:Q21 . # England
{?cerem p:P131/ps:P131 ?region . ?cerem p:P31/ps:P31 wd:Q180673 }
UNION
{?region p:P31/ps:P31 wd:Q180673 . BIND(?region AS ?cerem)} .
FILTER NOT EXISTS {?cerem p:P31/ps:P31 wd:Q19953632} .
?adm_county (p:P131/ps:P131)? ?cerem .
{ VALUES ?allowed_adm_county_type { wd:Q769628 wd:Q769603 wd:Q1136601 } .
# metropolitan county, non-metropolitan county, unitary authority
?adm_county p:P31/ps:P31 ?allowed_adm_county_type }
UNION
{VALUES ?adm_county { wd:Q23306 wd:Q23311 } } . # City of London & Greater London
VALUES ?allowed_district_type { wd:Q1187580 wd:Q1002812 wd:Q211690 }
OPTIONAL {
?adm_county p:P31/ps:P31 wd:Q1136601 .
BIND ('' AS ?dist)
BIND (' (unitary) ' AS ?dist_label)
}
OPTIONAL {
?dist p:P131/ps:P131 ?adm_county .
?dist p:P31/ps:P31 ?allowed_district_type .
?dist rdfs:label ?dist_label filter (lang(?dist_label) = "en") .
}
FILTER (!(sameTerm(?adm_county, wd:Q1342914) && sameTerm(?region, wd:Q47994))) . # North East Lincs
FILTER (!(sameTerm(?adm_county, wd:Q175945) && sameTerm(?region, wd:Q47994))) . # North Lincs
FILTER (!(sameTerm(?adm_county, wd:Q21269047) && sameTerm(?region, wd:Q48063))) . # Lincs council area
FILTER (!(sameTerm(?adm_county, wd:Q171866) && sameTerm(?region, wd:Q48063))) . # Middlesbrough
FILTER (!(sameTerm(?adm_county, wd:Q1434448) && sameTerm(?region, wd:Q48063))) . # Redcar & Cleveland
FILTER (!(sameTerm(?adm_county, wd:Q894094) && sameTerm(?region, wd:Q48063))) . # Stockton
FILTER (!(sameTerm(?adm_county, wd:Q21241814) && sameTerm(?region, wd:Q47983))) . # North Yorkshire
FILTER (!(sameTerm(?adm_county, wd:Q42462) && sameTerm(?region, wd:Q47983))) . # York
SERVICE wikibase:label {
bd:serviceParam wikibase:language "en" .
}
} GROUP BY ?region ?regionLabel ?cerem ?ceremLabel ?adm_county ?adm_countyLabel
ORDER BY ?regionLabel ?ceremLabel ?adm_countyLabel
Query found at
graph TD
classDef projected fill:lightgreen;
classDef literal fill:orange;
classDef iri fill:yellow;
v9("?adm_county"):::projected
v3("?adm_countyLabel"):::projected
v8("?allowed_adm_county_type")
v9("?allowed_district_type")
v8("?cerem"):::projected
v2("?ceremLabel"):::projected
v10("?dist")
v11("?dist_label"):::projected
v11("?dists")
v5("?region"):::projected
v1("?regionLabel"):::projected
a2((" "))
a3((" "))
a4((" "))
a5((" "))
a1((" "))
a6((" "))
a7((" "))
a8((" "))
a9((" "))
a10((" "))
c20(["wd:Q1136601"]):::iri
c12(["en"]):::literal
c19(["wd:Q180673"]):::iri
c15(["wd:Q19953632"]):::iri
c23(["bd:serviceParam"]):::iri
c16(["wd:Q48091"]):::iri
f0[["not sameterm(?adm_county,'wd:Q42462')sameterm(?region,'wd:Q47983')"]]
f0 --> v9
f0 --> v5
f1[["not sameterm(?adm_county,'wd:Q21241814')sameterm(?region,'wd:Q47983')"]]
f1 --> v9
f1 --> v5
f2[["not sameterm(?adm_county,'wd:Q894094')sameterm(?region,'wd:Q48063')"]]
f2 --> v9
f2 --> v5
f3[["not sameterm(?adm_county,'wd:Q1434448')sameterm(?region,'wd:Q48063')"]]
f3 --> v9
f3 --> v5
f4[["not sameterm(?adm_county,'wd:Q171866')sameterm(?region,'wd:Q48063')"]]
f4 --> v9
f4 --> v5
f5[["not sameterm(?adm_county,'wd:Q21269047')sameterm(?region,'wd:Q48063')"]]
f5 --> v9
f5 --> v5
f6[["not sameterm(?adm_county,'wd:Q175945')sameterm(?region,'wd:Q47994')"]]
f6 --> v9
f6 --> v5
f7[["not sameterm(?adm_county,'wd:Q1342914')sameterm(?region,'wd:Q47994')"]]
f7 --> v9
f7 --> v5
f8[["not "]]
subgraph f8e0["Exists Clause"]
e0v1 --"p:P31"--> e0a1
e0a1 --"p:statement/P31"--> e0c3
e0v1("?cerem"):::projected
e0a1((" ")):::projected
e0c3(["wd:Q19953632"]):::iri
end
f8--EXISTS--> f8e0
f8 --> v8
f8 --> c13
f8 --> a1
f8 --> c14
f8 --> c15
v8 --"p:P31"--> a1
a1 --"p:statement/P31"--> c15
v5 --"p:P31"--> a2
a2 --"p:statement/P31"--> c16
subgraph union0[" Union "]
subgraph union0l[" "]
style union0l fill:#abf,stroke-dasharray: 3 3;
v5 --"p:P31"--> a5
a5 --"p:statement/P31"--> c19
bind9[/"?region"/]
v5 --o bind9
bind9 --as--o v8
end
subgraph union0r[" "]
style union0r fill:#abf,stroke-dasharray: 3 3;
v8 --"p:P131"--> a3
a3 --"p:statement/P131"--> v5
v8 --"p:P31"--> a4
a4 --"p:statement/P31"--> c19
end
union0r <== or ==> union0l
end
subgraph union1[" Union "]
subgraph union1l[" "]
style union1l fill:#abf,stroke-dasharray: 3 3;
v9 --"p:P131"--> a6
a6 --"p:statement/P131"--> v8
end
subgraph union1r[" "]
style union1r fill:#abf,stroke-dasharray: 3 3;
end
union1r <== or ==> union1l
end
subgraph union2[" Union "]
subgraph union2l[" "]
style union2l fill:#abf,stroke-dasharray: 3 3;
bind10[/VALUES ?adm_county/]
bind10-->v9
bind100(["wd:Q23306"])
bind100 --> bind10
bind101(["wd:Q23311"])
bind101 --> bind10
end
subgraph union2r[" "]
style union2r fill:#abf,stroke-dasharray: 3 3;
bind11[/VALUES ?allowed_adm_county_type/]
bind11-->v8
bind110(["wd:Q769628"])
bind110 --> bind11
bind111(["wd:Q769603"])
bind111 --> bind11
bind112(["wd:Q1136601"])
bind112 --> bind11
v9 --"p:P31"--> a7
a7 --"p:statement/P31"--> v8
end
union2r <== or ==> union2l
end
bind12[/VALUES ?allowed_district_type/]
bind12-->v9
bind120(["wd:Q1187580"])
bind120 --> bind12
bind121(["wd:Q1002812"])
bind121 --> bind12
bind122(["wd:Q211690"])
bind122 --> bind12
subgraph optional0["(optional)"]
style optional0 fill:#bbf,stroke-dasharray: 5 5;
v9 -."p:P31".-> a8
a8 --"p:statement/P31"--> c20
bind13[/"''"/]
bind13 --as--o v10
bind14[/"' (unitary) '"/]
bind14 --as--o v11
end
subgraph optional1["(optional)"]
style optional1 fill:#bbf,stroke-dasharray: 5 5;
v10 -."p:P131".-> a9
a9 --"p:statement/P131"--> v9
v10 --"p:P31"--> a10
a10 --"p:statement/P31"--> v9
v10 --"rdfs:label"--> v11
end
subgraph s1["http://wikiba.se/ontology#label"]
style s1 stroke-width:4px;
c23 --"wikibase:language"--> c12
end
bind16[/"?dist_label"/]
v11 --o bind16
bind16 --as--o v11