How to merge polygons but keeping all the inner polygons?

Question

I am merging polygons using ST_Union, but what I get is the "exteriors" only of the polygons. How can I merge them, but keeping all the eventual inner polygons?

For example given two polygons A and B with relative inner rings, how can "stack" them when they overlap (ST_Intersects):

Answer 1

You can use ST_Collect instead of ST_Union.

This function returns the all the geometries that you collect as one geometry

SELECT ST_collect (a.geom, b.geom) from table a
CROSS JOIN table b
WHERE a.name = 'A'
  AND b.name='B'

**change it as follows:**

table = your table name
name = your field name
A, B = your polygons to be collect

Example:

select ST_collect (a.geom, b.geom) from polygons a, polygons b
where a.id = 6 and b.id=5

Result:

Answer 2

You can also use ST_Difference and merge the resulting polygon with Union. I once had the same problem when doing dynamic agregation using configurable polygonal ring zones. As stated in the docs, ST_Difference returns a geometry that represents that part of geometry A that does not intersect with geometry B. So, the basic idea here is to clip the outer polygon using the inner to get the exterior and then UNION both result: the exterior and the inner.

Try this in postgis sql console :

WITH polyring AS (
    SELECT *, buffer.geom_0 as ring_0, 
        ST_Difference(buffer.geom_1, buffer.geom_0) as ring_1, 
        ST_Difference(buffer.geom_2, buffer.geom_1) as ring_2 
    FROM (
        WITH params AS ( 
            SELECT 4326 proj_d, 3857 utm, (110.446998275757)::NUMERIC lng, (-7.54121917776336)::NUMERIC lat, 
            5123.32434 d0, '#ff0000' c0, 10247.53308 d1, '#ffff66' c1, 15370.85742 d2, '#00ff00' c2 )
    SELECT *, 
        ST_Transform( ST_Buffer(ST_Transform(ST_SetSRID( ST_Point(lng,lat),proj_d),utm),d0),proj_d) as geom_0, 
        ST_Transform( ST_Buffer(ST_Transform(ST_SetSRID( ST_Point(lng,lat),proj_d),utm),d1),proj_d) as geom_1, 
        ST_Transform( ST_Buffer(ST_Transform(ST_SetSRID( ST_Point(lng,lat),proj_d),utm),d2),proj_d) as geom_2 
    FROM params
    ) as buffer

) 
SELECT c0::text fillcolor, 255 r, 0 g, 0 b, d0||' m' distance, st_asgeojson(ring_0) from polyring 
    UNION  
SELECT c1::text fillcolor, 255 r, 255 g, 102 b, d1||' m' distance, st_asgeojson(ring_1) from polyring 
    UNION  
SELECT c2::text fillcolor, 0 r, 255 g, 0 b, d2||' m' distance, st_asgeojson(ring_2) from polyring

You'll get :

for an archiving purpose : the first SELECT inside WITH param serves as variables. These initial latlng (big decimal number) is the center point of the buffering. The next sequence: d0-c0, d1-c1, d2-c2 stand for distance and color.

SELECT 4326 proj_d, 3857 utm, (110.446998275757)::NUMERIC lng, (-7.54121917776336)::NUMERIC lat, 
            5123.32434 d0, '#ff0000' c0, 10247.53308 d1, '#ffff66' c1, 15370.85742 d2, '#00ff00' c2

The color field is added as a proof that each polygon exists (has its own attributes) so it can be assigned to do other tasks. In my case, i do this recursively with concentric circle shapes to generate sql like the above example and to answer simple questions such as how many properties/people are there or how much land is there being affected in each zoning category.