I have two PostGIS tables with geometries. One with number attributes and one with letter attributes. Each table has two features (rows).
I would like to apply a operation on both stables so that I create the following result:
The resulting table has 7 rows and contains all attributes from the input tables. Some features have both the letter attribute and number attributes.
I struggle to obtain this result using PostGIS / SQL. So far I've only been able to create the features that have both letter and number attributes.
SELECT table1.letter, table2.number, ST_Intersection(table1.geom,table2.geom) FROM table1, table2 WHERE ST_Intersects(table1.geom,table2.geom)
Is there a way to include the remaining features in the same query? I could also create multiple tables and perform a join but this is a non-parsimonious solution. I've also tried ST_Union to no avail.
In ArcMap and Geopandas this is often referred to as "Union" without dissolving however as far as I can tell ST_Union in PostGIS does something different. http://geopandas.org/set_operations.html https://desktop.arcgis.com/en/arcmap/10.3/tools/analysis-toolbox/how-union-analysis-works.htm