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 <[email protected]> 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 <[email protected]> > > 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: [email protected] > > > [mailto:[email protected]] On > Behalf Of L > > > Bogert-O'Brien > > > Sent: Friday, September 10, 2010 3:26 PM > > > To: [email protected] > > > 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 > > > [email protected] > > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > > > postgis-users mailing list > > > [email protected] > > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
