[postgis-users] aggregate different geometries

Regina Obe lr at pcorp.us
Mon May 20 07:18:37 PDT 2019

Use ST_Collect (or ST_Union) instead of ST_MakeLine.


The difference between ST_Collect and ST_Union is in the case of polygons
you make end up with invalid multipolygon if your polygons overlap


In the case of linestring and point, your point will disappear if it
intersects the linestring if you use ST_Union.


ST_Collect will  maintain your original geometries where as ST_Union will
collapse them if they have any intersection.  In case of no intersection,
the results would be more or less the same except possibly ordered


SELECT e. foreign_key,

                   ST_Multi(ST_Collect(e.geom)) as geom

                FROM gis_schema.single_objects e

GROUP BY e. foreign_key



Note to control order, you can always include an ORDER by clause in the

e.g ST_Collect(e.geom ORDER BY e.some_field)




From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Braune, Christopher
Sent: Monday, May 20, 2019 9:52 AM
To: 'postgis-users at lists.osgeo.org' <postgis-users at lists.osgeo.org>
Subject: [postgis-users] aggregate different geometries


Hello postgis-community,


I'm searching for a solution to create aggregated geometries from a table
with different single geometries. The input data looks like that:


id            foreign_key       geom

1             100                         point

2             200                         point

3             200                         point

4             300                         point

5             300                         point

6             300                         point

7             400                         point

8             400                         line

9             500                         point

10           500                         polygon


Now, I want to group this data by the "foreign_key". The result table have
to be this structure:

foreign_key       geom    

100                         point                     * result of one point

200                         line                        * result of two

300                         polygon               * result of three points

400                         polygon               * result of one line and
one point

500                         polygon               * result of one polygon
and one point

600                        (.)                         * other combinations


The target is the smallest geometry that contains all single geometries. For
example, three points creates a triangle-polygon.


My last try looks like this:


SELECT e. foreign_key,

                   ST_Multi(ST_makeline(e.geom)) as geom

                FROM gis_schema.single_objects e

GROUP BY e. foreign_key


(* Of course, polygons are not considered yet ;-)


Have anybody an idea? Thank's a lot!


Best regards,




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190520/9fde4f02/attachment.html>

More information about the postgis-users mailing list