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