# How to merge polygons but keeping all the inner polygons?

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):

• The left image are two separate polygons. It is unclear why you think you want a union. Please explain more, with images of input and desired output. – bugmenot123 May 29 '18 at 8:09
• Do you mean, in case shown, that you want to have three polygons resulting from the union? That's not exactly what union does... – kogexo May 29 '18 at 8:37
• @bugmenot123 kogexo I have added an image hopefully more clear – Randomize May 29 '18 at 8:58
• @Randomize I am still not sure what you want to get. Do you want a single feature/polygon geometry "stack" together or you want to combain polygons into single table and store each geometry as a separate row ? If so do you care about other attributes or only geometry? Are polygons needs to keep original areas or after "merging" they will get new areas based on intersections? – Losbaltica May 29 '18 at 10:03
• @Losbaltica single feature/polygon geometry "stack" together when they intersect. – Randomize May 29 '18 at 10:11

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:**

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:

• It's not clear from looking at your screen shot what we're looking at. =( – Evan Carroll Jun 3 '18 at 1:35

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.