Is there an Postgis equivalent of ArcGis Update tool?

Question

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?

Answer 1

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.

Answer 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.