2

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

Any suggestions?

UPDATE

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.

1

You can add and manipulate fields in your query

SELECT 
  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);
  • Thank you so much for your helpful answer ! – Gpetr Feb 13 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.