What is the PostGIS / PostgreSQL equivalent of the GeoPandas Overlay Union operation?
res_union = geopandas.overlay(df1, df2, how='union')
Say I have two tables, 1 green, 1 red with each with two rows with geometry (polygons):
Then, the objective is to get:
which is a table with 7 rows/ features. See the GeoPandas docs for more info.
res_intersection = geopandas.overlay(df1, df2, how='intersection')
Which is the equivalent of:
using the following SQL command:
SELECT table1.letter, table2.number, ST_Intersection(table1.geom,table2.geom) FROM table1, table2 WHERE ST_Intersects(table1.geom,table2.geom)
One solution is to create a table for the intersections and one for the symmetrical differences and than outer join. However I don't know how to create the Symmetrical Difference:
SELECT test.hybas_nld.pfaf_id, test.gadm_nld.gid_1, ST_SymDifference(test.hybas_nld.geom,test.gadm_nld.geom) FROM test.hybas_nld, test.gadm_nld WHERE ST_????(test.hybas_nld.geom,test.gadm_nld.geom)
Possible duplicate with the exception that I would like to keep the polygons where only geometry of table 1 or 2 exists (symmetrical difference).
What would be the simplest and fastest way to get to the final result (Union)?