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
