I have inserted a MultiPolygon table in PostGIS with the following columns: id, e_id , p_id, com, geom
Assume that I have 4 polygons with overlapped areas with the following values:
id com geom 1 1_1 geom1 2 1_2 geom2 3 2_1 geom3 4 2_2 geom4
Is it possible to extract only the intersected areas and concatenate their related values ? The result that I want to succeed is a table or shapefile like the following:
id com geom 1 1_1_2_1 geom1 2 2_1_2_2 geom2
If the polygon with id: 1 intersects with polygon with id: 3 and polygon with id: 3 intersects with polygon with id: 4
Until now I can extract only the geometries of intersected areas in a column with the following SQL code:
SELECT ST_INTERSECTION(a.geom, b.geom) FROM mytable a, mytable b WHERE a.ID < b.ID AND ST_INTERSECTS(a.geom, b.geom);
with @JGH helpful answer, I managed to develop more my code and now I can concatenate the com values from two intersected areas. The problem that remains the same is the id column that contains duplicate values. Is it possible to create a unique id column in the query ?
I tried SEQUENCE command in order to create a unique id column:
CREATE SEQUENCE serial START 1; SELECT nextval('serial'), a.com || '_' || b.com as com, ST_INTERSECTION(a.geom, b.geom) as geom FROM mytable a, mytable b WHERE a.ID < b.ID AND ST_INTERSECTS(a.geom, b.geom);
But it's not the solution that I wanted because, every time that I execute the code the id numbers increment.