Update multiple columns based on multiple criteria and intersection in PostgreSQL

87 Views Asked by At

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?

0

There are 0 best solutions below