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.

  • From which GIS is it that you are transitioning?
    – Vince
    May 2, 2019 at 15:10
  • 5
    You are looking for the ST_Intersection function postgis.net/docs/ST_Intersection.html
    – ziggy
    May 2, 2019 at 15:13
  • Thanks @ziggy! I'd looked at that part of the docs previously, but didn't understand the st_intersection(), st_intersects() relationship as @pdavis clarified below. While it lasts, this link may help others like me who failed at RingTFM: bostongis.com/postgis_intersection_intersects.snippet
    – Jeff
    May 3, 2019 at 8:17

2 Answers 2


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


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.

  • 2
    this is poor practice for this example because an island that just hits the purple polygon will get selected. st_intersection should be used to cut the geometries. or at least st_within() or st_contains
    – ziggy
    May 2, 2019 at 16:02
  • You can use ST_Intersection with ST_Intresects ... May 2, 2019 at 16:04
  • @BERA, Now you can try running the ST_CarvesPolygons function :-)...along with ziggy, there was just a timing hitch... Sep 21, 2021 at 20:02

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.