I am trying to port the ArcMap query by location ("Have their centroid in the source layer feature") to PostGIS. The aim is to select all feature that are completely within and touches the source layer. Currently, my ST_Intersect & ST_Within are not giving me the results I want.

SELECT     a.*, b.* 
FROM       town a
INNER JOIN local b ON ST_Intersects(a.geom, b.geom)
  • 1
    That query would return two geometries, which will not work in ArcGIS, even if the relationship was correct.
    – Vince
    Feb 28, 2023 at 13:36

1 Answer 1


If you want to select all features in table a, having their centroids in table b:

select a.*
from yourschema.A a
join yourschema.B b
on st_intersects(st_centroid(a.geom), b.geom)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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