PREFIX xsd: PREFIX schema: PREFIX rdf: PREFIX rdfs: PREFIX iisgv: PREFIX iisgv2: PREFIX gg: PREFIX geo: PREFIX bio: PREFIX bif: SELECT DISTINCT ?amco ?mean ?1918 ?excess ?wktColor ?wktTooltip ?max ?wkt WHERE { FILTER(?mean >1) # to avoid 'division by 0' error FILTER(?1918 >1) { select ?amco ?gg_gemeente ?max ?wkt (sum(if(?year = "1918"^^, 1, 0)) as ?1918) (sum(if(?year = "1910"^^, 1, 0)) as ?1910) (sum(if(?year = "1911"^^, 1, 0)) as ?1911) (sum(if(?year = "1912"^^, 1, 0)) as ?1912) (sum(if(?year = "1913"^^, 1, 0)) as ?1913) (sum(if(?year = "1914"^^, 1, 0)) as ?1914) (sum(if(?year = "1915"^^, 1, 0)) as ?1915) (sum(if(?year = "1916"^^, 1, 0)) as ?1916) (sum(if(?year = "1917"^^, 1, 0)) as ?1917) { { select (max(?wktyear) as ?max) ?gg_gemeente { ?gg_gemeente geo:hasGeometry ?geo . ?geo gg:year ?wktyear . FILTER(?wktyear <= "1918"^^xsd:gYear) } GROUP BY ?gg_gemeente } ?gg_gemeente geo:hasGeometry [gg:year ?max ; geo:asWKT ?wkt ] . ?death a bio:Death . ?death iisgv:deceased ?deceased . ?death bio:date ?date . ?death iisgv:event_location ?gemeente . ?gemeente gg:amsterdamCode ?amco . ?amco ^gg:amsterdamCode ?gg_gemeente . BIND(year(?date) as ?year) FILTER NOT EXISTS { ?gg_gemeente gg:startDate ?startdate FILTER(?startdate >= "1918-01-01"^^) } FILTER NOT EXISTS { ?gg_gemeente gg:endDate ?enddate FILTER(?enddate <= "1918-01-01"^^) } } } BIND(xsd:float(?1910+?1911+?1912+?1913+?1914+?1915+?1916+?1917)/8 as ?mean) BIND(xsd:decimal(?1918/?mean) as ?excess) BIND ( IF ( ?excess > 1.60, 1, IF ( ?excess < 1.38, 0.35, IF (?excess >= 1.38 && ?excess <= 1.60, 0.65, ?v ) )) AS ?category) BIND(concat('reds,', (?category)) as ?wktColor) BIND(concat(substr(STR(?excess), 0, 4),'_', STRAFTER(STR(?gg_gemeente), 'gemeentenaam/')) as ?wktTooltip) } ORDER BY ASC (?amco)

Documentation

Triply