[postgis-users] ST_Union vs ST_Collect

[L Bogert-O'Brien]

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