2022 / Day 07: Raster
Have you’ve ever stared really up close into a kinescope of a TV set? The image there is made up of little red and green and blue cells which vary in intensity therefore tricking your brain into thinking that it’s seeing some other colors than red/green/blue. This submission is in a way similar - if you look at it really close, you’ll see only reds and greens and blues. But move further and there’s only Ukrainian flag blue and yellow.
As with 2022 / Day 05 the following SQL snippet includes
Ukrainian first level administrative division unit data - this time as
PostGIS box2d types - calculated from
NaturalEarth
large scale cultural vector data.
The idea of the SQL is to convert input box data into geometry, which can be done simply with
<box_as_a_sring_value>::box2d::geometry(polygon, 4326)
Then calculate the overall bounds so we can generate a grid with st_hexagongrid into them.
The distinction for color is based on approximately half the height of the overall bounds. If the box centroid is north of that then go with blue (r=0, g=87, b=183), south of that use yellow instead (r=255, g=215, b=0) for all hexes in that box.
I’ll go throw the box centroids in aswell, effectively coloring them white (meaning 255 for all - red, green, and blue channels), and draw a “1 pixel” wide black halo around the surrounding “pixels” (meaning 0 for all - red, green, and blue channels).
As a final setup step I’ll use the st_intersects to determine if this “pixel” should be colored blue or yellow. But with other data it could be more complicated aswell, e.g calculating percentages for different layers of the total area that intersect this “pixel”.
With all the setup done, I’ll generate centroids with
st_centroid for every grid hexagon
(“pixel”). One copy of these centroids will be shifted left and one copy to
the right with st_translate. One
will stay just as where it was. But all of them will be
buffered with st_buffer, and
finally scaled into an ellipse with
st_scale. As all these “cells” are
stored in per “pixel” arrays of geometries, we can unnest these with a
generate_series and pick up the corresponding R-or-G-or-B value from the
colors array. For even simpler styling in QGIS the colors as ints could be
turned into hexadecimal values, e.g. for the red cell:
to_hex(<red_value_in_int>)||'0000'
with
boxes as (
select
x.name::varchar,
b as geom,
c
from (
values
(
'BOX(
30.508143345239148 50.34307729787895,
33.486979607922535 52.36894928000008)',
'Чернігівська область'
),(
'BOX(
23.59331913200012 50.29481151014306,
26.095802849721736 51.94975006099999)',
'Волинська область'
),(
'BOX(
25.06346276239657 50.01312327691767,
27.705161174000125 51.9285110470001)',
'Рівненська область'
),(
'BOX(
27.192273389805052 49.582012640770756,
29.736304151715217 51.65196462100006)',
'Житомирська область'
),(
'BOX(
29.270492791309266 49.17064240232497,
32.13915490067808 51.49313710600002)',
'Київ'
),(
'BOX(
27.370402052612633 48.00050207700009,
30.07612837062777 49.881400050771674)',
'Вінницька область'
),(
'BOX(
22.640922485000146 48.72369293957746,
25.43387942889251 50.64448008948449)',
'Львівська область'
),(
'BOX(
32.93951948423677 50.10722606143355,
35.69221013400005 52.36099110900001)',
'Сумська область'
),(
'BOX(
34.84147057490708 48.52703827589568,
38.080859409134405 50.431858216000066)',
'Харківська область'
),(
'BOX(
37.84733361175677 47.811527201000004,
40.1595430910001 50.06278513600007)',
'Луганська область'
),(
'BOX(
22.13283980300011 47.89702585900004,
24.641111281060773 49.08553212500006)',
'Закарпатська область'
),(
'BOX(
36.53113488087001 46.87555573100008,
39.05552859876582 49.23174978312596)',
'Донецька область'
),(
'BOX(
24.92755374520914 47.71393770200011,
27.503829794000126 48.678114326115235)',
'Чернівецька область'
),(
'BOX(
23.532858514209067 47.71006052700004,
25.64626956638739 49.52467763896186)',
'Івано-Франківська область'
),(
'BOX(
28.19949792500006 45.21356842700004,
31.306802605555788 48.22222484050394)',
'Одеська область'
),(
'BOX(
32.48161868600005 44.38104889500005,
36.63835696700005 46.21810560059299)',
'Автономна Республіка Крим'
),(
'BOX(
31.50440514400009 45.71732197013279,
35.279540987026905 47.5828581810602)',
'Херсонська область'
),(
'BOX(
34.203731724339605 46.25514515122774,
37.220911899964165 48.130189114436064)',
'Запорізька область'
),(
'BOX(
30.208988478335584 46.42987702000005,
33.136613396590974 48.220984605255296)',
'Миколаївська область'
),(
'BOX(
32.06949506974166 48.74627554028393,
35.475075311276555 50.54660492597651)',
'Полтавська область'
),(
'BOX(
26.148409458129947 48.45159068526186,
27.88897505105774 50.58714508677633)',
'Хмельницька область'
),(
'BOX(
24.707050408949215 48.5101142445767,
26.38053999242669 50.26525259133399)',
'Тернопільська область'
),(
'BOX(
32.983754509662845 47.47389842457261,
36.92718305755204 49.183044744919016)',
'Дніпропетровська область'
),(
'BOX(
29.59279869970021 48.45332184512705,
32.855441929101744 50.25145498296109)',
'Черкаська область'
),(
'BOX(
29.738164503688893 47.74752513317833,
33.919459669628054 49.26179962835016)',
'Кіровоградська область'
),(
'BOX(
30.185993737221168 50.014154717466226,
30.846045721033192 50.66815305072271)',
'Київ'
)
) x(box, name)
join lateral
st_transform(
box::box2d::geometry(polygon, 4326),
6385
) b on true
join lateral
st_centroid(
b
) c on true
),
bounds as (
/* bounds for all geometries, and AOI width and height
*/
select
b.geom, coords.minx, coords.maxx, coords.miny, coords.maxy,
(coords.maxx - coords.minx) as width,
(coords.maxy - coords.miny) as height
from (
select
st_envelope(st_collect(geom)) as geom
from boxes
) b
join lateral (
select
min(st_x(pts.geom)) as minx, max(st_x(pts.geom)) as maxx,
min(st_y(pts.geom)) as miny, max(st_y(pts.geom)) as maxy
from
st_dumppoints(b.geom) pts
) coords on true
),
colors as (
/* decide on colors - northern parts will get blue, and
seouthern yellow
*/
select
boxes.geom, st_y(boxes.c) as y,
case
/* centroid is north of approximately 1/2 way bbox height -> blue */
when st_y(boxes.c) > bounds.miny + (bounds.height * 0.58)
then array[0, 87, 183]
/* otherwise -> yellow */
else array[255, 215, 0]
end as rgb
from
boxes,
bounds
),
grid as (
/* create a hexagongrid assuming (bounds width in m) / 256 "px" size.
shift will be the amount to translate r and b cells left / right
from the green that will stay in the middle.
*/
select
row_number() over(order by h.i, h.j)::int as oid,
bounds.width / px.v as upp, h.geom,
(bounds.width/px.v) / 2.0 as shift
from
(select 256.0 as v) px,
bounds
join lateral
st_hexagongrid(bounds.width / px.v, bounds.geom) h on true
),
centroids as (
/* calculate the location of box centroids. we'll color those "white"
meaning r=255,g=255,b=255
*/
select
grid.oid as grid_oid, g.name, grid.geom as geom
from
grid
join lateral (
select
boxes.name, boxes.c
from
boxes
where
st_within(boxes.c, grid.geom)
order by
boxes.c <-> grid.geom
limit 1
) g on true
),
centroids_halo as (
/* .. and lets put a 1 "pixel" black halo around the centroid
so they'll be better visible in the south (yellow background)
*/
select
grid.oid as grid_oid
from
grid, centroids c
where
c.geom && grid.geom
),
ints as (
/* add color values to grid */
select
grid.geom, grid.shift,
case
when centroids.grid_oid is not null then array[255,255,255]
when centroids_halo.grid_oid is not null then array[0,0,0]
else f.rgb
end as rgb,
centroids.name
from
grid
join lateral (
select
colors.rgb
from
colors
where
st_intersects(grid.geom, colors.geom)
order by
y
limit 1
) f on true
left join
centroids on
centroids.grid_oid = grid.oid
left join
centroids_halo on
centroids_halo.grid_oid = grid.oid
)
select
/* bring it all together, every "channel" gets its own color assignment (int),
all others will be set to 0 for this channel cell.
*/
row_number() over()::int as oid, s as ch,
geoms[s] as geom,
rgb[s] as color, name
from (
select
rgb, name,
array[
/* prepare geometry for red "channel" */
st_scale(
st_buffer(
st_translate(
st_centroid(d.geom),
-1.0*d.shift,
0
),
shift/2.0
),
st_point(0.75, 3.0),
st_translate(
st_centroid(d.geom),
-1.0*d.shift,
0
)
),
/* prepare geometry for green "channel" */
st_scale(
st_buffer(
st_centroid(d.geom),
shift/2.0
),
st_point(0.75, 3.0),
st_centroid(d.geom)
),
/* prepare geometry for blue "channel" */
st_scale(
st_buffer(
st_translate(
st_centroid(d.geom),
d.shift,
0
),
shift/2.0
),
st_point(0.75, 3.0),
st_translate(
st_centroid(d.geom),
d.shift,
0
)
)
] as geoms
from
ints d
) g, generate_series(1,3,1) as s
;