Getting attributes of intersected polygons using PostGIS

Question

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

The process and table schematics:

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_Overlaps, 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.

Answer 1

The query used to find the intersection adds the clause WHERE a.id < b.id to ensure that an intersection between two polygons is reported once only (A intersects B, and ignores B intersects A).

It is invalid to consider an intersections as being related to a single polygon... they must include two polygons.

It is important to note that since st_intersects returns a boolean, using it in the JOIN clause allows retrieving the complete rows from the two tables, just like a join on id1 = id2.

What you can do is to record the involved polygons IDs while computing the intersection:

SELECT ST_intersection(a.geom, b.geom), a.id as id1, b.id as id2
FROM t1 a 
INNER JOIN t1 b ON  ST_Intersects (a.geom,b.geom)
WHERE a.id < b.id 

It is then easy to join on the original data to get more information about the original polygon.

To produce the table having one source polygon per row, you can union two joins on the source table.

In pseudo code:

SELECT inter_section.name, source.id, source.pop
FROM inter_section
 JOIN source ON inter_section.id1 = source.id
UNION 
SELECT inter_section.name,source.id, source.pop
FROM inter_section
 JOIN source ON inter_section.id2 = source.id