PostGIS: Merge Multipolygons and keeping boundaries

Question

We are trying to merge two Multipolygons which are stored in our PostGIS 2.1 database without losing the boundaries that are contained in each Multipolygon.

Our spatial data matches the following criteria.

-- Check whether the polygons share points (boundaries?)
-- ST_Intersects:
-- Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space)
-- and FALSE if they don't (they are Disjoint).
ST_Intersects(higher_geom,lower_geom) = TRUE    

-- ST_Crosses:
-- Returns TRUE if the supplied geometries have some, but not all, interior points in common.
ST_Crosses(higher_geom,lower_geom) = FALSE

-- Since ST_Crosses would return FALSE if the polygons have all interior points in common
-- we have to ensure this is not the case
ST_Within(higher_geom,lower_geom) = FALSE

If we then try to aggregate the columns lower_geom and higher_geom (both of type MultiPolygon) with the following query, the result of ST_Union is lacking the borders of the original polygons.

SELECT
    ST_Union(lower_geom, higher_geom)
FROM
    myTable

To make it more clear, we have added a screenshot. In our desired result, both, the green and the red multipolygons should be contained in ONE new multipolygons still containing ALL boundaries.

enter image description here

Does anyone have an idea!?

Thanks in advance, Cord & Martin

Does the result need to be a multipolygon or can it be a geometry collection? I have managed to do something that involves something like select st_collect(st_intersection(a.geom, b.geom),st_symdifference(st_intersection(a.geom, b.geom),st_union(a.geom, b.geom))) from lower a, higher b; that is merge the intersection with the symdifference of the intersection and the union, but I can only get it to return a geometrycollection keeping the inner boundaries of my test data set. — John Powell, Jun 12 '14 at 14:35
Thanks for your reply! If somehow possible, the resultset should be a Multipolygon too, since it will be stored in another table which already contains Multipolygons. Normally, it should work with: http://postgis.net/docs/ST_Union.html But I still haven't understood the difference between the first and the second variant ;-) Isn't there a way to create a new geometry WITH intersecting regions!? — Cord Kaldemeyer, Jun 12 '14 at 15:24
St_collect is a bit fiddly, and if you are not careful, you end up with multiple overlapping polygons. Nice question, btw. — John Powell, Jun 12 '14 at 15:57
ST_Union will not work in any variant as it will always dissolve common inner boundaries. — John Powell, Jun 12 '14 at 16:38

John Powell · Answer 1 · 2014-06-12T22:58:48.387

3

This works for me for a few test polygons I threw together. It uses an ST_Dump trick to unmerge the geometry collection that otherwise results from the inner query, aliased as table c, and then recollects the geometries using ST_Multi(ST_Collect(geom...). The inner query combines the intersection of the two sets of geometries with the difference of the intersection and the union.

select ST_multi(ST_Collect(d.geom)) 
  from (select (ST_Dump(c.geom)).geom 
    from (select ST_Collect(ST_Intersection(a.geom, b.geom),
            ST_SymDifference(ST_Intersection(a.geom, b.geom),
            ST_Union(a.geom, b.geom))) as geom 
        from lower_geom a, higher_geom b)
   as c)
 as d;

There will be a more elegant and efficient way to write this, but I would like to know if this works with your data, before trying.

edited Jun 12 '14 at 22:58

answered Jun 12 '14 at 15:43

John Powell

12,253
6
59
67

Thanks! I have checked your query on my data. As I do not have the tables a and b an the inner data is a resultset, I have called it "inner_resultset" and put a screenshot with the structure on Dropbox. My query looks like this: [code] SELECT ST_Multi(ST_Collect(d.geom)) FROM ( SELECT (ST_Dump(c.geom)).geom FROM ( SELECT ST_Collect(ST_Intersection(lower_geom, higher_geom), ST_Symdifference(ST_Intersection(lower_geom, higher_geom), ST_Union(lower_geom, higher_geom))) AS geom FROM ( SEE DROPBOX SCREENSHOT ) AS inner_resultset ) AS c ) AS d [/code] – Cord Kaldemeyer Jun 12 '14 at 17:44
https://www.dropbox.com/s/69nndk6qqcnbhov/inner_resultset.png It just returns the following rows: 132 rows inner_resultset 132 rows c 10686 row d 1 row outer query on d Shouldn't it deliver 132 rows like the inner resultset? Thanks for your help! – Cord Kaldemeyer Jun 12 '14 at 17:45
Sorry, updated my query to use lower_geom, upper_geom, a and b are jus t aliases. I'm not sure, I might need to see your data somehow. – John Powell Jun 12 '14 at 18:47
No, ST_Dump splits multipolygons or geometrycollections into parts, and as you are trying to preserve inner boundaries, you would want the outer query to have more geometries than the inner one. – John Powell Jun 12 '14 at 23:00
I have written my resultset into a new table "inner_resultset" and put a dump on Dropbox: https://www.dropbox.com/s/xa6qeew8955tykj/inner_resultset.backup If you are working with PGAdmin3 you should be able to import it. Thanks for your help! – Cord Kaldemeyer Jun 13 '14 at 12:08
OK, I'll look later. PGAdmin, nah, psql and Linux command line :-) You are welcome, it is an interesting challenge. – John Powell Jun 13 '14 at 12:18
@CordK. Could you pg_dump your original two tables, higher_geom and lower_geom and put those on dropbox? I need to work with the raw data, and I can visualize the output in QGIS. – John Powell Jun 13 '14 at 14:45
@ John: the data in the table "inner_resultset" stems from a quite (too) complicated query that aggregates the MultiPolygons from duplicate entries in another table. I am not sure if you really want to have a look at that ;) My goal is to merge the columns "higher_geom" and "lower_geom" into another column that holds the merged data as a Multipolygon WITHOUT losing inner boundaries. But if you want, I can of course create a dump and post the whole query! – Cord Kaldemeyer Jun 13 '14 at 15:59
Ah, OK, so inner_resultset should have all that is needed to generate the above image. I can't read it though. Did it come from pg_dump? – John Powell Jun 13 '14 at 16:01
Yes, it should include everything for the image. I have also used QGIS to browse the data. Hmmm, maybe something went wrong using pg_dump. I have dumped it once again: https://www.dropbox.com/s/xa6qeew8955tykj/inner_resultset.backup – Cord Kaldemeyer Jun 13 '14 at 17:00
OK, I have managed to load your dump file now. I'll see what I can do. – John Powell Jun 15 '14 at 19:28
I am still looking at this, and making some progress. – John Powell Jun 17 '14 at 12:53

PostGIS: Merge Multipolygons and keeping boundaries

1 Answers1