Spatial query with clip (PostGIS)

Question

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.

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:

And I can still find those islands overlapping the boundary of my polygon using ST_Overlap() to find these pink 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.

Answer 1

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

Answer 2

Run the script and select your islands, add semantic fields if necessary ...

CREATE TABLE land_clip as SELECT a.geom FROM blue a JOIN purple b ON ST_Intersects(a.geom, b.geom);

In my proposal, all the islands will be selected by a clip without crossing them, if they need to be cut, use the script from the pdavis answer ...

or try this script, but this is a bad tone of writing the script:

CREATE TABLE land_clip as SELECT ST_Intersection(a.geom, b.geom) geom FROM blue a, purple b WHERE ST_Intersects(a.geom, b.geom);

So move on from jokes to business.

The surgeon is on the spot and does anesthesia: "Paul Ramsey and his creative team you are great !!!". I start a small operation: "Enough people fooling about the behavior of the function to select and cut!!!."

My first cut: "You need to create a function, for example ST_Selects (), which must completely SELECT some objects from other objects";

My second cut: "You need to put the ST_Intersects () function in order, so that as a result of its operation, some objects CUT other objects."

The whole operation is completed, I hope none of the patients were injured and did not spill a single drop of blood ... With you there was a kind surgeon Mr. Carver ... :-(...O.S.