CODEWITHSUNDEEP
sqlpostgis
Martin C
Martin C

Reputation: 85

PostGIS Intersection of multiple tables

In my postGIS DB, I need to create a new layer from the intersection of multiple polygons layers, while maintaining all fields (or Columns) from all layers (or tables) ==> in the output table, I need all columns for the 3 input tables

I believe it has to include ST_Intersects, but I am unable to find the correct syntax. Can you help me designing the SQL command line, knowing the following generic table names:

- TableA (with the columns: GeomA (geometry), ColumnA1, ColumnA2)
- TableB (with the columns: GeomB (geometry), ColumnB1)
- TableC (with the columns: GeomC (geometry), ColumnC1)

All geometry fields from TableA,TableB and TableC are in the same projection.

Thanks

Upvotes: 2

Views: 5406

Answers (1)

nathanvda
nathanvda

Reputation: 50057

For clarity, since "interaction with multiple polygons layers" is a bit vague, it could mean:

  • you want to find all polygons from A that intersect with a polygon from B and a polygon from C
  • or A with B, B with C
  • or A with B, A with C and B with C

For simplicity let us assume the first scenario, and I presume the others will be pretty easy to deduce:

 select A.*, B.*, C.*
 from A, B, C
 where st_intersects(A.geomA, B.geomB) = true
   and st_intersects(A.geomA, C.geomC) = true

[EDIT] Not just finding the rows, but if the intersection itself is important we can do the following (in the simple case of two geometries intersecting)

 select A.*, B.*, st_intersection(A.geomA, B.geomB) as geomAB
 from A, B
 where st_intersects(A.geomA, B.geomB) = true

I simplified the case because if A intersects with B, and A intersects with C, it is not even sure those two intersections intersect again and have a result.

If we suppost A intersects with B, B with C and C with A then we should have a intersection imho. So that would look like: 

 select A.*, B.*, C.*, st_intersection(A.geomA, st_intersection(B.geomB, C.geomC)) as geomABC
 from A, B, C
 where st_intersects(A.geomA, B.geomB) = true
   and st_intersects(A.geomA, C.geomC) = true 
   and st_intersects(B.geomB, C.geomC) = true

Upvotes: 4

Related Questions