query-212369303e87dc36a197d64191e76f47
number of borders of US states
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 rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX bd: <http://www.bigdata.com/rdf#>
# number of borders of US states
#defaultView:Map{"hide":["?geoshape","?rgb"]}
SELECT ?x ?xLabel ?description ?geoshape ?data ?rgb # determine the max and min values (used to calculate the spread
WHERE {
{
# extract the data
SELECT (COUNT(?border) AS ?data) ?x ?geoshape WHERE {
?x wdt:P31 wd:Q35657 .
?x wdt:P47 ?border .
?border wdt:P31 wd:Q35657 .
?x wdt:P3896 ?geoshape .
} GROUP BY ?x ?geoshape
} {
SELECT (MAX(?data) AS ?max_data) (MIN(?data) AS ?min_data) WHERE {
{
# extract the data
SELECT (COUNT(?border) AS ?data) ?x ?geoshape WHERE {
?x wdt:P31 wd:Q35657 .
?x wdt:P47 ?border .
?border wdt:P31 wd:Q35657 .
?x wdt:P3896 ?geoshape .
} GROUP BY ?x ?geoshape
}
}
}
?x rdfs:label ?xLabel. FILTER (LANG(?xLabel) = "en") .
BIND(IF (?data = 1, " state", " states") AS ?border_text)
BIND (CONCAT("The US state of ", ?xLabel, " is bordered by ", STR(ROUND(?data)), ?border_text ) AS ?description)
BIND ( ?max_data - ?min_data AS ?spread )
BIND ( xsd:integer( 100 * (?data - ?min_data) / ?spread) AS ?percent ) # 0 -> 100
# some example hues 0:red, 20:orange, 50:yellow, 90:green, 180:cyan, 220:blue, 280:indigo, 330:violet, 340:red
# varying hue
# BIND ( 255 * ?percent / 100 + 0 AS ?hue )
# BIND (1 AS ?value) #
# BIND (1 AS ?saturation) . # 0 -> 1
# varying the hue's value
BIND ( (1 - ?percent / 100 + 0) AS ?value ) # to black
BIND (0 AS ?hue) # red
BIND (1 AS ?saturation) . # 0 -> 1
# varying the hue's saturation
# BIND ( (1 - ?percent / 100 + 0) AS ?saturation ) # to -> white
# BIND (90 AS ?hue) # green
# BIND (1 AS ?value) . # 0 -> 1
# the following algorithm based on https://stackoverflow.com/questions/3018313/algorithm-to-convert-rgb-to-hsv-and-hsv-to-rgb-in-range-0-255-for-both
BIND ( ?hue / 60 AS ?hh )
BIND ( FLOOR (?hh) AS ?i ) .
BIND ( ?hh - ?i AS ?ff) .
BIND ( ?value * ( 1 - ?saturation ) AS ?p) .
BIND ( ?value * ( 1 - ( ?saturation * ?ff)) AS ?q ) .
BIND ( ?value * ( 1 - ( ?saturation * (1 - ?ff ))) AS ?t ) .
BIND ( COALESCE(
IF(?i = 0, ?value, 1/0),
IF(?i = 1, ?q, 1/0),
IF(?i = 2, ?p, 1/0),
IF(?i = 3, ?p, 1/0),
IF(?i = 4, ?t, 1/0),
IF(?i = 5, ?value, 1/0),
?value
) AS ?r )
BIND ( COALESCE(
IF(?i = 0, ?t, 1/0),
IF(?i = 1, ?value, 1/0),
IF(?i = 2, ?value, 1/0),
IF(?i = 3, ?q, 1/0),
IF(?i = 4, ?p, 1/0),
IF(?i = 5, ?p, 1/0),
?p
) AS ?g )
BIND ( COALESCE(
IF(?i = 0, ?p, 1/0),
IF(?i = 1, ?p, 1/0),
IF(?i = 2, ?t, 1/0),
IF(?i = 3, ?value, 1/0),
IF(?i = 4, ?value, 1/0),
IF(?i = 5, ?q, 1/0),
?q
) AS ?b )
BIND (FLOOR(255 * ?r) AS ?red)
BIND (FLOOR(255 * ?g) AS ?green)
BIND (FLOOR(255 * ?b) AS ?blue)
# red
BIND ( FLOOR (?red / 16) AS ?red_1 )
BIND ( COALESCE(
IF(?red_1 < 10, STR(?red_1), 1/0),
IF(?red_1 = 10, "a", 1/0),
IF(?red_1 = 11, "b", 1/0),
IF(?red_1 = 12, "c", 1/0),
IF(?red_1 = 13, "d", 1/0),
IF(?red_1 = 14, "e", 1/0),
IF(?red_1 = 15, "f", 1/0),
"ERROR"
) AS ?red_hex1 )
BIND (FLOOR(?red - (16 * xsd:integer( ?red / 16 ))) AS ?red_2)
BIND ( COALESCE(
IF(?red_2 < 10, STR(?red_2), 1/0),
IF(?red_2 = 10, "a", 1/0),
IF(?red_2 = 11, "b", 1/0),
IF(?red_2 = 12, "c", 1/0),
IF(?red_2 = 13, "d", 1/0),
IF(?red_2 = 14, "e", 1/0),
IF(?red_2 = 15, "f", 1/0),
"ERROR"
) AS ?red_hex2 )
BIND (CONCAT(STR(?red_hex1), STR(?red_hex2)) AS ?red_hex )
# green
BIND ( FLOOR (?green / 16) AS ?green_1 )
BIND ( COALESCE(
IF(?green_1 < 10, STR(?green_1), 1/0),
IF(?green_1 = 10, "a", 1/0),
IF(?green_1 = 11, "b", 1/0),
IF(?green_1 = 12, "c", 1/0),
IF(?green_1 = 13, "d", 1/0),
IF(?green_1 = 14, "e", 1/0),
IF(?green_1 = 15, "f", 1/0),
"ERROR"
) AS ?green_hex1 )
BIND (FLOOR(?green - (16 * xsd:integer( ?green / 16 ))) AS ?green_2)
BIND ( COALESCE(
IF(?green_2 < 10, STR(?green_2), 1/0),
IF(?green_2 = 10, "a", 1/0),
IF(?green_2 = 11, "b", 1/0),
IF(?green_2 = 12, "c", 1/0),
IF(?green_2 = 13, "d", 1/0),
IF(?green_2 = 14, "e", 1/0),
IF(?green_2 = 15, "f", 1/0),
"ERROR"
) AS ?green_hex2 )
BIND (CONCAT(STR(?green_hex1), STR(?green_hex2)) AS ?green_hex )
# blue
BIND ( FLOOR (?blue / 16) AS ?blue_1 )
BIND ( COALESCE(
IF(?blue_1 < 10, STR(?blue_1), 1/0),
IF(?blue_1 = 10, "a", 1/0),
IF(?blue_1 = 11, "b", 1/0),
IF(?blue_1 = 12, "c", 1/0),
IF(?blue_1 = 13, "d", 1/0),
IF(?blue_1 = 14, "e", 1/0),
IF(?blue_1 = 15, "f", 1/0),
"ERROR"
) AS ?blue_hex1 )
BIND (FLOOR(?blue - (16 * xsd:integer( ?blue / 16 ))) AS ?blue_2)
BIND ( COALESCE(
IF(?blue_2 < 10, STR(?blue_2), 1/0),
IF(?blue_2 = 10, "a", 1/0),
IF(?blue_2 = 11, "b", 1/0),
IF(?blue_2 = 12, "c", 1/0),
IF(?blue_2 = 13, "d", 1/0),
IF(?blue_2 = 14, "e", 1/0),
IF(?blue_2 = 15, "f", 1/0),
"ERROR"
) AS ?blue_hex2 )
BIND (CONCAT(STR(?blue_hex1), STR(?blue_hex2)) AS ?blue_hex )
BIND (CONCAT(STR(?red_hex), STR(?green_hex), STR(?blue_hex)) AS ?rgb)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Query found at
graph TD
classDef projected fill:lightgreen;
classDef literal fill:orange;
classDef iri fill:yellow;
v23("?b")
v26("?blue")
v37("?blue_1")
v39("?blue_2")
v41("?blue_hex")
v38("?blue_hex1")
v40("?blue_hex2")
v3("?border")
v8("?border_text")
v6("?data"):::projected
v9("?description"):::projected
v17("?ff")
v22("?g")
v4("?geoshape"):::projected
v25("?green")
v32("?green_1")
v34("?green_2")
v36("?green_hex")
v33("?green_hex1")
v35("?green_hex2")
v15("?hh")
v13("?hue")
v16("?i")
v6("?max_data")
v7("?min_data")
v18("?p")
v11("?percent")
v19("?q")
v21("?r")
v24("?red")
v27("?red_1")
v29("?red_2")
v31("?red_hex")
v28("?red_hex1")
v30("?red_hex2")
v42("?rgb"):::projected
v14("?saturation")
v10("?spread")
v20("?t")
v12("?value")
v2("?x"):::projected
v1("?xLabel"):::projected
c8(["bd:serviceParam"]):::iri
c10(["#91;AUTO_LANGUAGE#93;,en"]):::literal
c3(["wd:Q35657"]):::iri
f0[["?xLabel = 'en'"]]
f0 --> v1
v2 --"wdt:P31"--> c3
v2 --"wdt:P47"--> v3
v3 --"wdt:P31"--> c3
v2 --"wdt:P3896"--> v4
bind2[/"count(?border)"/]
v3 --o bind2
bind2 --as--o v6
v2 --"wdt:P31"--> c3
v2 --"wdt:P47"--> v3
v3 --"wdt:P31"--> c3
v2 --"wdt:P3896"--> v4
bind4[/"count(?border)"/]
v3 --o bind4
bind4 --as--o v6
bind7[/"max(?data)"/]
v6 --o bind7
bind7 --as--o v6
bind8[/"min(?data)"/]
v6 --o bind8
bind8 --as--o v7
v2 --"rdfs:label"--> v1
bind9[/"if(?data = '1^^xsd:integer',' state',' states')"/]
v6 --o bind9
bind9 --as--o v8
bind10[/"concat('The US state of ',?xLabel,' is bordered by ',str(numeric-round(?data)),?border_text)"/]
v1 --o bind10
v6 --o bind10
v8 --o bind10
bind10 --as--o v9
bind11[/"?max_data - ?min_data"/]
v6 --o bind11
v7 --o bind11
bind11 --as--o v10
bind12[/"http://www.w3.org/2001/XMLSchema#integer('100^^xsd:integer' * ?data - ?min_data / ?spread)"/]
v6 --o bind12
v7 --o bind12
v10 --o bind12
bind12 --as--o v11
bind13[/"'1^^xsd:integer' - ?percent / '100^^xsd:integer' + '0^^xsd:integer'"/]
v11 --o bind13
bind13 --as--o v12
bind14[/"'0^^xsd:integer'"/]
bind14 --as--o v13
bind15[/"'1^^xsd:integer'"/]
bind15 --as--o v14
bind16[/"?hue / '60^^xsd:integer'"/]
v13 --o bind16
bind16 --as--o v15
bind17[/"numeric-floor(?hh)"/]
v15 --o bind17
bind17 --as--o v16
bind18[/"?hh - ?i"/]
v15 --o bind18
v16 --o bind18
bind18 --as--o v17
bind19[/"?value * '1^^xsd:integer' - ?saturation"/]
v12 --o bind19
v14 --o bind19
bind19 --as--o v18
bind20[/"?value * '1^^xsd:integer' - ?saturation * ?ff"/]
v12 --o bind20
v14 --o bind20
v17 --o bind20
bind20 --as--o v19
bind21[/"?value * '1^^xsd:integer' - ?saturation * '1^^xsd:integer' - ?ff"/]
v12 --o bind21
v14 --o bind21
v17 --o bind21
bind21 --as--o v20
bind22[/"if(?i = '0^^xsd:integer',?value,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '1^^xsd:integer',?q,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '2^^xsd:integer',?p,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '3^^xsd:integer',?p,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '4^^xsd:integer',?t,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '5^^xsd:integer',?value,'1^^xsd:integer' / '0^^xsd:integer')?value"/]
v16 --o bind22
v12 --o bind22
v19 --o bind22
v18 --o bind22
v20 --o bind22
bind22 --as--o v21
bind23[/"if(?i = '0^^xsd:integer',?t,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '1^^xsd:integer',?value,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '2^^xsd:integer',?value,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '3^^xsd:integer',?q,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '4^^xsd:integer',?p,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '5^^xsd:integer',?p,'1^^xsd:integer' / '0^^xsd:integer')?p"/]
v16 --o bind23
v20 --o bind23
v12 --o bind23
v19 --o bind23
v18 --o bind23
bind23 --as--o v22
bind24[/"if(?i = '0^^xsd:integer',?p,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '1^^xsd:integer',?p,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '2^^xsd:integer',?t,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '3^^xsd:integer',?value,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '4^^xsd:integer',?value,'1^^xsd:integer' / '0^^xsd:integer')if(?i = '5^^xsd:integer',?q,'1^^xsd:integer' / '0^^xsd:integer')?q"/]
v16 --o bind24
v18 --o bind24
v20 --o bind24
v12 --o bind24
v19 --o bind24
bind24 --as--o v23
bind25[/"numeric-floor('255^^xsd:integer' * ?r)"/]
v21 --o bind25
bind25 --as--o v24
bind26[/"numeric-floor('255^^xsd:integer' * ?g)"/]
v22 --o bind26
bind26 --as--o v25
bind27[/"numeric-floor('255^^xsd:integer' * ?b)"/]
v23 --o bind27
bind27 --as--o v26
bind28[/"numeric-floor(?red / '16^^xsd:integer')"/]
v24 --o bind28
bind28 --as--o v27
bind29[/"if(?red_1 < '10^^xsd:integer',str(?red_1),'1^^xsd:integer' / '0^^xsd:integer')if(?red_1 = '10^^xsd:integer','a','1^^xsd:integer' / '0^^xsd:integer')if(?red_1 = '11^^xsd:integer','b','1^^xsd:integer' / '0^^xsd:integer')if(?red_1 = '12^^xsd:integer','c','1^^xsd:integer' / '0^^xsd:integer')if(?red_1 = '13^^xsd:integer','d','1^^xsd:integer' / '0^^xsd:integer')if(?red_1 = '14^^xsd:integer','e','1^^xsd:integer' / '0^^xsd:integer')if(?red_1 = '15^^xsd:integer','f','1^^xsd:integer' / '0^^xsd:integer')'ERROR'"/]
v27 --o bind29
bind29 --as--o v28
bind30[/"numeric-floor(?red - '16^^xsd:integer' * http://www.w3.org/2001/XMLSchema#integer(?red / '16^^xsd:integer'))"/]
v24 --o bind30
bind30 --as--o v29
bind31[/"if(?red_2 < '10^^xsd:integer',str(?red_2),'1^^xsd:integer' / '0^^xsd:integer')if(?red_2 = '10^^xsd:integer','a','1^^xsd:integer' / '0^^xsd:integer')if(?red_2 = '11^^xsd:integer','b','1^^xsd:integer' / '0^^xsd:integer')if(?red_2 = '12^^xsd:integer','c','1^^xsd:integer' / '0^^xsd:integer')if(?red_2 = '13^^xsd:integer','d','1^^xsd:integer' / '0^^xsd:integer')if(?red_2 = '14^^xsd:integer','e','1^^xsd:integer' / '0^^xsd:integer')if(?red_2 = '15^^xsd:integer','f','1^^xsd:integer' / '0^^xsd:integer')'ERROR'"/]
v29 --o bind31
bind31 --as--o v30
bind32[/"concat(str(?red_hex1),str(?red_hex2))"/]
v28 --o bind32
v30 --o bind32
bind32 --as--o v31
bind33[/"numeric-floor(?green / '16^^xsd:integer')"/]
v25 --o bind33
bind33 --as--o v32
bind34[/"if(?green_1 < '10^^xsd:integer',str(?green_1),'1^^xsd:integer' / '0^^xsd:integer')if(?green_1 = '10^^xsd:integer','a','1^^xsd:integer' / '0^^xsd:integer')if(?green_1 = '11^^xsd:integer','b','1^^xsd:integer' / '0^^xsd:integer')if(?green_1 = '12^^xsd:integer','c','1^^xsd:integer' / '0^^xsd:integer')if(?green_1 = '13^^xsd:integer','d','1^^xsd:integer' / '0^^xsd:integer')if(?green_1 = '14^^xsd:integer','e','1^^xsd:integer' / '0^^xsd:integer')if(?green_1 = '15^^xsd:integer','f','1^^xsd:integer' / '0^^xsd:integer')'ERROR'"/]
v32 --o bind34
bind34 --as--o v33
bind35[/"numeric-floor(?green - '16^^xsd:integer' * http://www.w3.org/2001/XMLSchema#integer(?green / '16^^xsd:integer'))"/]
v25 --o bind35
bind35 --as--o v34
bind36[/"if(?green_2 < '10^^xsd:integer',str(?green_2),'1^^xsd:integer' / '0^^xsd:integer')if(?green_2 = '10^^xsd:integer','a','1^^xsd:integer' / '0^^xsd:integer')if(?green_2 = '11^^xsd:integer','b','1^^xsd:integer' / '0^^xsd:integer')if(?green_2 = '12^^xsd:integer','c','1^^xsd:integer' / '0^^xsd:integer')if(?green_2 = '13^^xsd:integer','d','1^^xsd:integer' / '0^^xsd:integer')if(?green_2 = '14^^xsd:integer','e','1^^xsd:integer' / '0^^xsd:integer')if(?green_2 = '15^^xsd:integer','f','1^^xsd:integer' / '0^^xsd:integer')'ERROR'"/]
v34 --o bind36
bind36 --as--o v35
bind37[/"concat(str(?green_hex1),str(?green_hex2))"/]
v33 --o bind37
v35 --o bind37
bind37 --as--o v36
bind38[/"numeric-floor(?blue / '16^^xsd:integer')"/]
v26 --o bind38
bind38 --as--o v37
bind39[/"if(?blue_1 < '10^^xsd:integer',str(?blue_1),'1^^xsd:integer' / '0^^xsd:integer')if(?blue_1 = '10^^xsd:integer','a','1^^xsd:integer' / '0^^xsd:integer')if(?blue_1 = '11^^xsd:integer','b','1^^xsd:integer' / '0^^xsd:integer')if(?blue_1 = '12^^xsd:integer','c','1^^xsd:integer' / '0^^xsd:integer')if(?blue_1 = '13^^xsd:integer','d','1^^xsd:integer' / '0^^xsd:integer')if(?blue_1 = '14^^xsd:integer','e','1^^xsd:integer' / '0^^xsd:integer')if(?blue_1 = '15^^xsd:integer','f','1^^xsd:integer' / '0^^xsd:integer')'ERROR'"/]
v37 --o bind39
bind39 --as--o v38
bind40[/"numeric-floor(?blue - '16^^xsd:integer' * http://www.w3.org/2001/XMLSchema#integer(?blue / '16^^xsd:integer'))"/]
v26 --o bind40
bind40 --as--o v39
bind41[/"if(?blue_2 < '10^^xsd:integer',str(?blue_2),'1^^xsd:integer' / '0^^xsd:integer')if(?blue_2 = '10^^xsd:integer','a','1^^xsd:integer' / '0^^xsd:integer')if(?blue_2 = '11^^xsd:integer','b','1^^xsd:integer' / '0^^xsd:integer')if(?blue_2 = '12^^xsd:integer','c','1^^xsd:integer' / '0^^xsd:integer')if(?blue_2 = '13^^xsd:integer','d','1^^xsd:integer' / '0^^xsd:integer')if(?blue_2 = '14^^xsd:integer','e','1^^xsd:integer' / '0^^xsd:integer')if(?blue_2 = '15^^xsd:integer','f','1^^xsd:integer' / '0^^xsd:integer')'ERROR'"/]
v39 --o bind41
bind41 --as--o v40
bind42[/"concat(str(?blue_hex1),str(?blue_hex2))"/]
v38 --o bind42
v40 --o bind42
bind42 --as--o v41
bind43[/"concat(str(?red_hex),str(?green_hex),str(?blue_hex))"/]
v31 --o bind43
v36 --o bind43
v41 --o bind43
bind43 --as--o v42
subgraph s1["http://wikiba.se/ontology#label"]
style s1 stroke-width:4px;
c8 --"wikibase:language"--> c10
end