EDITED QUESTION: PROPER SYNTAX FOR ST_DIFFERENCE BETWEEN TABLES?
I have two huge polygon datasets from shapefiles, call them 'baselevel' coverage and 'deluxe' coverage. They each contain over 2.4 million polygons, with many polygons describing complex coastal features.
The baselevel dataset has complete coverage over the area of interest but less detailed attributes, while the deluxe dataset has gaps and less extensive spatial coverage but more detailed attributes.
I want to create a third dataset which uses all the deluxe polygons, and uses the baselevel coverage to fill the gaps and extend the coverage. I've been approaching this as two steps: produce a 'fill' dataset which is just the base-level coverage minus all the deluxe polygons, then union the 'baselevel-fill' dataset with the 'deluxe' dataset.
I can do this fairly efficiently in grass via v.overlay (create the fill layer with operator=NOT, then union with deluxe using operator=AND), after dicing the shapefiles into much smaller tiles.
I'm pasting my final syntax below, though I'd still welcome any optimization suggestions ;-) based on Paul Ramseys answer, as well as some other stackexchange answers and postgis tutorials
GRASS is pretty excellent, and could handle my polygons where a lot of other python, ogr, and qgis approaches. However the fastest I could complete a similar operation on my (very small) test dataset was 154 seconds. Code below completed in under 28 seconds :-)
CREATE TABLE deluxe_plus_baselvl AS WITH deluxe_cutter AS ( SELECT ST_Union(d.geom) AS geom, b.gid FROM baselevel b JOIN deluxe d ON ST_Intersects(b.geom, d.geom) GROUP BY b.gid ), baselevel_cut AS ( SELECT ST_Difference(b.geom, d.geom) AS geom, b.gid FROM baselevel b JOIN deluxe_cutter d ON b.gid = d.gid ) SELECT 'baselevel' AS type, b.geom, b.gid FROM baselevel_cut b UNION ALL SELECT 'deluxe' AS type, d.geom, d.gid FROM deluxe d UNION ALL SELECT 'baselevel' AS type, b.geom, b.gid FROM baselevel b LEFT JOIN deluxe d ON ST_Intersects(b.geom,d.geom) where d.gid is null;
Thank you for your help!