Postgis dissolve and keep values
Published On May 04, 2016
How to merge adjacent polygons of the same type keeping attributes?
This is a graphical idea of the problem.
The trick is to save all the grouped values into an array and then extract values from that array.
This is the query I have tested in a very simple case:
select geom, my_type ,
case when the_path is not null then values1[the_path] else values1[1] end as value1,
case when the_path is not null then values2[the_path] else values2[1] end as value2
from (
select
st_asewkt( (st_dump(st_union(geom))).geom ) as geom,
(st_dump(st_union(geom))).path[1] as the_path ,
my_type,
array_agg(value1) as values1,
array_agg(value2) as values2
from t1
group by my_type
) tx
This is teh result you have:
This is the set query to run for the setup of this simple test case:
drop table t1;
create table t1(
value1 text,
value2 text,
my_type text,
geom geometry(Polygon)
);
insert into t1 (value1,value2,my_type,geom) values ('1-one','2-one','red',ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'));
insert into t1 (value1,value2,my_type,geom) values ('1-two','2-two','red',ST_GeomFromText('POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'));
insert into t1 (value1,value2,my_type,geom) values ('1-three','2-three','blue',ST_GeomFromText('POLYGON((4 0, 4 1, 5 1, 5 0, 4 0))'));
insert into t1 (value1,value2,my_type,geom) values ('1-four','2-four','blue',ST_GeomFromText('POLYGON((7 0, 7 1, 8 1, 8 0, 7 0))'));
Look at this discussion on stackoverflow.
Tags: postgis postgres sql