<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:70.85pt 70.85pt 56.7pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Use ST_Collect (or ST_Union) instead of ST_MakeLine.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>In the case of linestring and point, your point will disappear if it intersects the linestring if you use ST_Union.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:35.4pt'>SELECT e. foreign_key,<o:p></o:p></p><p class=MsoNormal style='margin-left:35.4pt'> ST_Multi(ST_Collect(e.geom)) as geom<o:p></o:p></p><p class=MsoNormal style='margin-left:35.4pt'> FROM gis_schema.single_objects e<o:p></o:p></p><p class=MsoNormal style='margin-left:35.4pt'>GROUP BY e. foreign_key<o:p></o:p></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Note to control order, you can always include an ORDER by clause in the aggregate<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>e.g ST_Collect(e.geom ORDER BY e.some_field)<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b>From:</b> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Braune, Christopher<br><b>Sent:</b> Monday, May 20, 2019 9:52 AM<br><b>To:</b> 'postgis-users@lists.osgeo.org' <postgis-users@lists.osgeo.org><br><b>Subject:</b> [postgis-users] aggregate different geometries<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=DE>Hello postgis-community,<o:p></o:p></span></p><p class=MsoNormal><span lang=DE><o:p> </o:p></span></p><p class=MsoNormal>I’m searching for a solution to create aggregated geometries from a table with different single geometries. The input data looks like that:<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>id foreign_key geom<o:p></o:p></p><p class=MsoNormal>1 100 point<o:p></o:p></p><p class=MsoNormal>2 200 point<o:p></o:p></p><p class=MsoNormal>3 200 point<o:p></o:p></p><p class=MsoNormal>4 300 point<o:p></o:p></p><p class=MsoNormal><span lang=DE>5 300 point<o:p></o:p></span></p><p class=MsoNormal><span lang=DE>6 300 point<o:p></o:p></span></p><p class=MsoNormal><span lang=DE>7 400 point<o:p></o:p></span></p><p class=MsoNormal><span lang=DE>8 400 line<o:p></o:p></span></p><p class=MsoNormal><span lang=DE>9 500 point<o:p></o:p></span></p><p class=MsoNormal><span lang=DE>10 500 polygon<o:p></o:p></span></p><p class=MsoNormal><span lang=DE><o:p> </o:p></span></p><p class=MsoNormal>Now, I want to group this data by the “foreign_key”. The result table have to be this structure:<o:p></o:p></p><p class=MsoNormal>foreign_key geom <o:p></o:p></p><p class=MsoNormal>100 point * result of one point<o:p></o:p></p><p class=MsoNormal>200 line * result of two points<o:p></o:p></p><p class=MsoNormal>300 polygon * result of three points<o:p></o:p></p><p class=MsoNormal>400 polygon * result of one line and one point<o:p></o:p></p><p class=MsoNormal>500 polygon * result of one polygon and one point<o:p></o:p></p><p class=MsoNormal>600 (…) * other combinations<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The target is the smallest geometry that contains all single geometries. For example, three points creates a triangle-polygon.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>My last try looks like this:<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-left:35.4pt'>SELECT e. foreign_key,<o:p></o:p></p><p class=MsoNormal style='margin-left:35.4pt'> ST_Multi(ST_makeline(e.geom)) as geom<o:p></o:p></p><p class=MsoNormal style='margin-left:35.4pt'> FROM gis_schema.single_objects e<o:p></o:p></p><p class=MsoNormal style='margin-left:35.4pt'>GROUP BY e. foreign_key<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>(* Of course, polygons are not considered yet ;-)<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='text-autospace:none'><span style='mso-fareast-language:DE'>Have anybody an idea? Thank’s a lot!<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='mso-fareast-language:DE'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='mso-fareast-language:DE'>Best regards,<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='mso-fareast-language:DE'>Christopher<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:8.0pt;font-family:"Arial",sans-serif;mso-fareast-language:DE'><o:p> </o:p></span></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>