I have a PostGIS table containing about 50,000 polygons and some of them are overlapping.
I'm trying to use PostGIS to return a table with all the Intersecting parts of the polygons along with some of the attributes of the original polygons.
To get the Intersecting parts table (table (b) in the figure below), I use this script (Similar to the solution here):
SELECT (ST_intersection(a.geom, b.geom)) INTO Intersecting_parts _table FROM t1 a INNER JOIN t1 b ON ST_Intersects (a.geom,b.geom) WHERE a.id < b.id
THE PROBLEM I'M STRUGGLING WITH:
I want the table with the intersecting parts (table b) to hold attributes from the original polygons, so anyone exploring new table can retrace the intersection to the originals. Furthermore, database-wise, I know it is better to put these attributes in a new relationship-table, and not in table b).
So, I've put in the figure below a schematic of the new table I want to create (table (c)). I tried doing this:
SELECT ST_intersection(a.geom, b.geom), a.* INTO Intersecting_parts_table FROM t1 a INNER JOIN t1 b ON ST_Intersects (a.geom,b.geom) WHERE a.id < b.id
But of course – In the simple case although there were only 2 intersecting polygons, it returns only the attributes of one of them, randomly, which clearly does not meet my needs.
But, I think I'm stuck in the basic understanding:
It seems to me that as the
ST_Intersection geographic operator (and
ST_Intersects and so on) works on whole tables, they are not the answer. They can't return attributes from two polygons at once (or I don't understand how to do it).
I tried extracting the attributes using a point derived from
ST_PointOnSurface (and not
ST_centroid), but didn't get very far with it).
I'm using PostGIS 2.3 and PostgreSQL 9.6.1.