I have multiple spatial records in a Postgres table, imported from a shapefile using PostGIS. I am also using QGIS to view & edit the spatial layers.
What is the easiest way to combine the geometry for multiple records into a single geometry value?
4
-
so this table consists of multiple records with 1 geometry type? (ei: polygons) and you want to combine/collapse these records to one record? is so check out st_union or st_collect for geometry aggregation– ziggyCommented May 30, 2017 at 19:00
-
Correct, the geometry types are all the same and I want to combine them into one geometry value.– DonCommented May 30, 2017 at 19:34
-
2okay yeah so you should be using the functions st_union(postgis.net/docs/ST_Union.html) or st_collect(postgis.net/docs/ST_Collect.html)– ziggyCommented May 30, 2017 at 19:42
-
@ziggy I wound up uploading the original shapefile in QGIS, selecting all of the polygons in the layer and then using 'Merge Selected Features' downloaded the new shapefile which I could then import into postgresql as one single record with all of the geometry combined. It looks like ST_Union/ST_Collect would also work. How would I use those calls to combine all the geometry values of all the records in a table? That's the answer I'm looking for.– DonCommented May 30, 2017 at 23:12
Add a comment
|
2 Answers
if I am reading it correctly all you are trying to do is dissolve the geometry to one record? is so then you will just need a basic usage of st_union or a clever trick to use st_collect with st_buffer and type cast over to a multipolygon geometry (credit goes to @Alexandre Neto for the st_collect answer)
these two functions are aggregate functions, similar to using a standard SQL count,sum or avg function but st_union in particular, dissolves aka aggregates geometries together.
st_union:
create table noury as
select col1,col2, st_union(geom) as geom from table group by col1,col2
st_collect:
create table noury2 as
select col1,col2,
st_buffer(st_collect(geom),0)::geometry(multipolygon,102689) as geom from table group by col1,col2
ps** i included col1,col2 if you have other columns you need to dissolve down, they must go in the group by when using any aggregate function
-
1As the OP wants "QGIS to view", he could create a view with that queries.
CREATE VIEW as ....– pLumoCommented May 31, 2017 at 5:54
You have a couple option you can try:
Create the (empty) table first, then use INSERT INTO...SELECT... FROM to get all the data from each of the original tables into the merged one.
Create the new table from one big UNION statement.
-
I'm trying to combine the records in a table into a single record combining all of the geometry so I have a single record in the table.– DonCommented May 30, 2017 at 23:02
-
This will combine geoms from multiple layers (tables) to one layer. That was not the question.– pLumoCommented May 31, 2017 at 5:50