I am trying to update multiple columns in my PostgreSQL table, based on different conditions and a geometric intersection.
My input table is made up of million of squares 100mtx100mt and its structure is:
id_grid|geom|road1|road2|road3|road4|road5|road6|
Columns Road 1,2,3...
are empty.
My second table is "roads" and it's made up of 6 types of road described in the field "Legenda":
id_road|geom|legenda
I need to update columns Road 1,2,3...
with a X according with the type of road that intersects the grid. For instance, if the square with id 1 is intersected by roads "road1" and "road3" its row should be:
id_grid|geom|road1|road2|road3|road4|road5|road6|
1 |abc |x |null |x |null |null |null |
In order to do that, I have used this query:
update
anagr_grid100_marche_clc
set
mob_autostrada = case s.legenda WHEN 'AUTOSTRADA' THEN 'x' END,
mob_strada_principale = case s.legenda WHEN 'STRADA PRINCIPALE' THEN 'x' END,
mob_strada_secondaria = case s.legenda WHEN 'STRADA SECONDARIA' THEN 'x' END,
mob_strada_locale = case s.legenda WHEN 'STRADA LOCALE' THEN 'x' END,
mob_altra_strada = case s.legenda WHEN 'ALTRA STRADA' THEN 'x' END,
mob_ferrovie = case s.legenda WHEN 'FERROVIA' THEN 'x' END
from
stradebis as s
where
st_intersects(anagr_grid100_marche_clc.geom, s.geom)
But its output is not correct. Basically, multiple columns should be updated as 'x' at the same time, but this query doesn't allow it: if the first updated column has value 'x' the others won't be in any case.
May someone help me?