How to join two line geometries together into a single record?

user_18429677 0
Viewed 87

I have a series of lines that I need to join together into a single record. Bellow is a mockup representation of my data. Some section numbers are duplicated and I need to update the geometries of the same section number into a single record.

Mockup Data:

id section geom
1 32 1234
2 32 1213
3 32 1231
4 33 3121

What I need:

id section geom
1 32 1234,1213,1231
4 33 3121

From what I have read ST_union is a good way to to this but can figure out how to update the rows. I think I need to subquery an update query but am unsure how to format this.

Sample Code:

UPDATE TABLE "tlbsections"
SELECT section,
       ST_Union(geom) as singlegeom
FROM "Ecorys_alkmaar_sections"
WHERE section = '32'
GROUP BY section
;
postgis postgresql geometry
1 Answers

There are two options to do it

  1. Insert union records into new table using below query -
insert into Ecorys_alkmaar_sections_1 (id,section,geom)
select min(id),section,ST_UNION(geom) as shape 
from Ecorys_alkmaar_sections
group by section

 Output of the above query will be -

enter image description here

  1. Update the existing table and delete duplicate records

You can use the below code to do the update based on the join condition

update Ecorys_alkmaar_sections t1
set geom = t2.geom 
from (select section,ST_UNION(geom) as shape 
from Ecorys_alkmaar_sections
group by section) t2 
where t1.section=t2.section

the output of the above command will be -

enter image description here

so you need to delete the duplicate records once the update is done.

user_73665570
Related