I'm trying to select polygons which centroids fall in another polygon:

enter image description here

SELECT smaller_polygons.id
FROM big_polygon
LEFT JOIN big_polygon smaller_polygons
ON ST_Within(ST_Centroid(smaller_polygons.wkb_geometry),big_polygon.wkb_geometry)

I expect 33,44 to be selected but I get id of the big polygon.

What am I doing wrong?

  • 1
    Hi BERA, it seems LEFT JOIN limits the records to the ones provided by the big_polygon table. How about CROSS JOIN? – Kazuhito Oct 20 '18 at 7:35
  • INNER JOIN seems to work with correct FROM table etc. – BERA Oct 20 '18 at 8:07

This seems to work:

FROM smaller_polygons
INNER JOIN big_polygon
ON ST_Within(ST_Centroid(smaller_polygons.wkb_geometry),big_polygon.wkb_geometry)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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