[postgis-users] ST_Union vs ST_Collect

Sat Sep 11 21:06:39 PDT 2010

Thanks for your detailed explanation Nicklas.  I am worried now that
ST_Collect is not what I want then, because I really do want a
"dissolve" of all areas.  If you use st_area on the query you sent as
follows:
SELECT st_area(st_AsText(ST_Collect(a, b))) as collected,
st_area(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;

you get 13 for the collected one and 12 for the unioned one, as there is
one square overlapping between the two polygons.  It is the 12 value
that I would be wanting from my data.  

So how do I correct these errors I get, so that I can get ST_Union to
return successfully?

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

I am using PostGIS 1.4 on PostgreSQL 8.4.

That is why I tried cleangeometry as found at:
http://www.sogis1.so.ch/sogis/dl/postgis/cleanGeometry.sql and referenced at
http://postgis.refractions.net/pipermail/postgis-users/2008-August/021042.html

I also thought that ST_Buffer with 0 was supposed to simplify things and
get rid of problems in geometries.

Thanks again for your assistance.

Regards,

Loretta

----- Original Message -----
From: Nicklas Avén <nicklas.aven at jordogskog.no>
Date: Saturday, September 11, 2010 12:57 pm
Subject: Re: [postgis-users] ST_Union vs ST_Collect

> 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((33, 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 
> butrolled 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 
> and4 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 
> whenit 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
> > 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 