[postgis-users] ST_Union vs ST_Collect

[Nicklas Avén]

Hi Loretta

In the documentation of ST_Collect there is a note explaining the
difference.

http://postgis.org/documentation/manual-1.5/ST_Collect.html



As an example you can compare the output of the first and the second
column in: 

SELECT st_AsText(ST_Collect(a, b)) as collected, st_AsText(ST_Union(a,
b)) as unioned 
FROM
(SELECT 'POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))'::geometry as a, 'POLYGON((3
3, 3 5, 5 5, 5 3, 3 3))'::geometry as b) as v;

in the first column you will get:
MULTIPOLYGON(((1 1,1 4,4 4,4 1,1 1)),((3 3,3 5,5 5,5 3,3 3)))

and in the second column you will get:
POLYGON((1 1,1 4,3 4,3 5,5 5,5 3,4 3,4 1,1 1))


So when collected the result is just the same geometries as inputed but
rolled into a Multi or Collection. When unioned the function tries to
really melt the polygons together. The vertex 4 4 and 3 3 is removed and
instead you have the crossing points of the two polygon borders 3 4 and
4 3

That is also the answer to why you can collect just any geometries
because the function doesn't care about what it is collecting. But when
it comes to melting the geometries together it is more tricky and the
function has some demands for the algorithm to work.

HTH
Nicklas




On Sat, 2010-09-11 at 02:25 -0400, L Bogert-OBrien wrote:
> Hi,
> 
> Yes, thanks Jamie, that is indeed what I meant.  Didn't proofread the
> message well enough!
> 
> Regards,
> 
> Loretta
> 
> ----- Original Message -----
> From: James DeMichele <james.demichele at redfin.com>
> Date: Friday, September 10, 2010 6:50 pm
> Subject: Re: [postgis-users] ST_Union vs ST_Collect
> 
> > Hello, Loretta, when you said: " replacing the ST_DUMP with
> > ST_COLLECT...", did you mean that you replaced the ST_UNION with
> > ST_COLLECT?
> > 
> > -Jamie
> > 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of L
> > Bogert-O'Brien
> > Sent: Friday, September 10, 2010 3:26 PM
> > To: postgis-users at postgis.refractions.net
> > Subject: [postgis-users] ST_Union vs ST_Collect
> > 
> > The question:
> > Why would CT_COLLECT work when CT_UNION does not?
> > 
> > The background:
> > There are 49 distinct CMAs in my y2006.ca_ct.cbf_clp table.  Each of
> > them have many multipolygons within them that touch each other by
> > boundaries only.  I wanted to create a table containing a single
> > dissolved multipolygon for each CMA, so I created the following query
> > to insert the data into the new table, y2006.ca_cma_cbf_clp:
> > 
> > INSERT INTO y2006.ca_cma_cbf_clp (pruid, cmauid, the_geom_4269)
> > (SELECT dmp.pruid, dmp.cmauid,
> >         ST_MULTI(ST_BUFFER(ST_UNION(dmp.dmp_geom), 0.0))
> >    FROM (SELECT pruid, cmauid,
> >                 
> > cleangeometry(ST_BUFFER((ST_DUMP(the_geom_4269)).geom,0.0)) AS 
> > dmp_geom            FROM y2006.ca_ct_cbf_clp) AS dmp  --9683 rows 
> > of dumped
> > geometries
> >    GROUP BY pruid, cmauid
> >    ORDER BY pruid, cmauid);  --49 rows of CMAs
> > 
> > This returned 49 rows, but there were two of them that had geometries
> > that were NULL, and the following errors were seen:
> > 
> > NOTICE:  TopologyException: found non-noded intersection between
> > -79.4565 44.2272, -79.4565 44.2272 and -79.4565 44.2272, -79.4565
> > 44.2272 -79.4565 44.2272
> > NOTICE:  TopologyException: Directed Edge visited twice during
> > ring-building -123.035 49.3916
> > (These locations were within the two geometries that were missing.)
> > 
> > So I just made one change, replacing the ST_DUMP with ST_COLLECT, and
> > then I got the results I was expecting.  There were no errors and all
> > 49 entries in the new table had valid geometries.
> > 
> > Thanks for any clarification you can give on the difference between
> > ST_UNION and ST_COLLECT and when one should be used over the other.
> > 
> > Regards,
> > 
> > Loretta
> > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>