2

I have two large sets of polygons that overlap. I am looking to combine them with removing the overlapping areas. One dataset carries higher importance and needs to be on top of the other.

Normally I would use Update tool from Analysis Tools in ArcMap that does exactly what I need, however, I am building a bigger model in Postgres and so far I was unable to find an equivalent of the Update tool.

Is there a function or a workflow that can update one polygon with another?

7
  • are those simple or multi geometries you have? and what exactly do you mean by 'needs to be on top', do you refer to the attributes and which of two respective features to keep (since, if you remove overlaps, naturally there can be no top or bottom polygons anymore)?
    – geozelot
    Commented Aug 24, 2017 at 10:19
  • ah, the update tool preserves the geometry of one data set in case they overlap and cut the other respectively...that is what you mean?
    – geozelot
    Commented Aug 24, 2017 at 10:27
  • @ThingumaBob both datasets are polygons (simple geometries) not multipolygons. And yes, the geometry of one dataset is what I want to preserve and cut the overlap from the other one
    – GeoMeteoMe
    Commented Aug 24, 2017 at 10:33
  • I don't fully understand the question, but you are looking for one of the ST_Difference or ST_SymDifference functions, possibly with ST_Union. If they are combined, how can one be on top of the other? Commented Aug 24, 2017 at 11:27
  • @JohnPowellakaBarça ST_Union is the equivalent of Dissolve tool and it will not preserve the geometry. By one on top of the other means that dataset A remains unchanged and the overlaps are removed from the dataset B. In another words dataset A is burned into daset B
    – GeoMeteoMe
    Commented Aug 24, 2017 at 22:38

2 Answers 2

3

Okay, I wasn´t able to test this, but out of my head, something along the lines of this should give you what you want:

    SELECT res.<unique_id>,
           res.<other_column_1>,
           res.<other_column_2>,
           res.<...>,
           ST_Union(res.geom) AS geom
    FROM (
      SELECT ipt.<unique_id>,
             ipt.<other_column_1>,
             ipt.<other_column_2>,
             ipt.<...>,
             ST_Difference(ipt.<geometry_column>, upd.<geometry_column>) AS geom
      FROM <input_table> AS ipt
      JOIN <update_table> AS upd
        ON ST_Overlaps(ipt.<geometry_column>, upd.<geometry_column>)
    ) AS res
    GROUP BY res.<unique_id>

All statements between <> are of course to be replaced with your data's specific table or column names, where <input_table> and <update_table> are according to ArcGIS Update Tool.

ST_Difference returns those parts of your <input_table> geometries that do not overlap with those from your <update_table> based on a join on those geometries that actually do overlap via ST_Overlaps. I used that in a subquery and in conjunction with ST_Union to combine resulting geometries that were cut in two separate parts (you´ll get multigeometries in those cases). You might need to alter the GROUP BY statement with other columns, I am not sure without testing.


EDIT1: I think that ST_Difference might already return multi geometries. You can test if you get desired results by just taking the inner query (...FROM ( <only_this_part> ) AS res...)


EDIT2: It does, you don´t need the outer query (which would do it´s job, though, if you add all columns to the GROUP BY clause):

SELECT ROW_NUMBER() OVER() AS uid,
       ipt.<column_1>,
       ipt.<column_2>,
       ipt.<...>,
       ST_Difference(ipt.<geometry_column>, upd.<geometry_column>) AS geom
FROM <input_table> AS ipt
JOIN <update_table> AS upd
  ON ST_Overlaps(ipt.<geometry_column>, upd.<geometry_column>)

will return the expected results, with multi-geometries in cases where geometries were split in multiple, separated parts.


 SELECT ROW_NUMBER() OVER() AS uid,
        sub.*
 FROM (
   SELECT ipt.<column_1>,
          ipt.<column_2>,
          ipt.<...>,
          (ST_Dump(ST_Difference(ipt.<geometry_column>, upd.<geometry_column>))).geom AS geom
   FROM <input_table> AS ipt
   JOIN <update_table> AS upd
     ON ST_Overlaps(ipt.<geometry_column>, upd.<geometry_column>)
) AS sub

will return simple geometries, where those geometries that were split in multiple, separated parts are in one row each and share the same attributes.

Only backdraw with ST_Overlaps in both versions would be that containing geometries are not considered, if you have trouble with that, change into ST_Intersects.


EDIT3: And to actually merge both tables into one as the Update Tool does:

SELECT ROW_NUMBER() OVER() AS uid,
       merged.*
FROM (
  SELECT ROW_NUMBER() OVER() AS input_table_id,
         sub.*
  FROM (
   SELECT ipt.<column_1>,
          ipt.<column_2>,
          ipt.<...>,
          (ST_Dump(ST_Difference(ipt.<geometry_column>, upd.<geometry_column>))).geom AS geom
   FROM <input_table> AS ipt
   JOIN <update_table> AS upd
     ON ST_Overlaps(ipt.<geometry_column>, upd.<geometry_column>)
  ) AS sub
  UNION
  SELECT ROW_NUMBER() OVER() AS update_table_id,
         <column_1>
         <column_2>
         <...>,
         <geometry_column> AS geom
  FROM <update_table>
) AS merged

Make sure that there is the same amount of columns in the selections above and underneath the UNION to make it work. If both tables have the same attributes, easy, if not, include default (or null) values as columns in one or both selections accordingly.


Try it, if there are any errors or unexpected results, say a word. Also, this returns a query result set; consider creating a new table with these (CREATE TABLE <new_table> AS ( <above_query> )) instead of trying to update your old one.

2
  • thanks for helping me. Your query runs very fast and does not produce any errors, however, the results are quite unexpected. The update feature that should be on top are partially hidden beneath input features
    – GeoMeteoMe
    Commented Aug 30, 2017 at 11:07
  • so they still (partially) overlap, is that what you mean? okay, let's see: which query did you use? did you try 'st_intersects' instead of 'st_overlaps'? what are the CRS for your input features? I was able to exactly reproduce the example on the ESRI Update Tool docs with the last query (Edit 3)...
    – geozelot
    Commented Aug 30, 2017 at 13:07
2

I use ST_DifferenceAgg() from the PostGIS Addons. You have to merge the two tables together, have a unique identifier and an index on the geometry column. Here is a short example:

WITH overlappingtable AS (
  SELECT 1 id, ST_GeomFromText('POLYGON((0 1, 3 2, 3 0, 0 1), (1.5 1.333, 2 1.333, 2 0.666, 1.5 0.666, 1.5 1.333))') geom
  UNION ALL
  SELECT 2 id, ST_GeomFromText('POLYGON((1 1, 3.8 2, 4 0, 1 1))')
  UNION ALL
  SELECT 3 id, ST_GeomFromText('POLYGON((2 1, 4.6 2, 5 0, 2 1))')
  UNION ALL
  SELECT 4 id, ST_GeomFromText('POLYGON((3 1, 5.4 2, 6 0, 3 1))')
  UNION ALL
  SELECT 5 id, ST_GeomFromText('POLYGON((3 1, 5.4 2, 6 0, 3 1))')
)
SELECT a.id, ST_DifferenceAgg(a.geom, b.geom) geom
FROM overlappingtable a,
     overlappingtable b
WHERE a.id = b.id OR -- Make sure to pass at least once the polygon with itself
      ((ST_Contains(a.geom, b.geom) OR -- Select all the containing, contained and overlapping polygons
        ST_Contains(b.geom, a.geom) OR
        ST_Overlaps(a.geom, b.geom)) AND
       (ST_Area(a.geom) < ST_Area(b.geom) OR -- Make sure bigger polygons are removed from smaller ones
        (ST_Area(a.geom) = ST_Area(b.geom) AND -- If areas are equal, arbitrarily remove one from the other but in a determined order so it's not done twice.
         a.id < b.id)))
GROUP BY a.id
HAVING ST_Area(ST_DifferenceAgg(a.geom, b.geom)) > 0 AND NOT ST_IsEmpty(ST_DifferenceAgg(a.geom, b.geom));

This will merge the overlapping parts with the biggest overlapping polygon. If you want to keep the overlapping part separated look at the ST_SplitAgg() example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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