[postgis-users] ST_Union vs ST_Collect

[James DeMichele]

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