[postgis-users] aggregate different geometries

[Regina Obe]

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
differently.

 

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
aggregate

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
points

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,

Christopher

 

 

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