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
