PostGIS Spatial Query – Spatial Query with Clip in PostGIS

clippostgisspatial-query

I'm making the transition from GIS to PostGIS and am stuck on this spatial query.

I have background geometry (purple, outlined red) which has an overlay of islands (blue, below); I need the area of only the islands within the red line. In GIS this would be a clip, using the purple shape as my mask.

background purple shape

blue islands

So I query using ST_Within() and obtain the green islands, which is great, but – logically – I'm missing those which are not fully within the mask:
st_within result

And I can still find those islands overlapping the boundary of my polygon using ST_Overlap() to find these pink polygons:

st_overlap results in pink overlap polygons

But is there an efficient way to get the area of the islands only within the red outline? This will cut a percentage of the island polygons, I understand, those will be associated with the adjacent background polygon.

Best Answer

This is what you're looking for:

SELECT ST_Intersection(t1.geom, t2.geom)
FROM t1 JOIN t2 ON ST_Intersects(t1.geom, t2.geom)

For more info: https://postgis.net/docs/ST_Intersection.html

Related Question