1

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

enter image description here

  • 1
    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
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

enter image description here

Result:

enter image description here

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

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 :

enter image description here

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

enter image description here

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.