I am trying to select polygons that are within a larger polygon in PostGIS but those who share boundaries with the larger polygon are not being returned.
If I use ArcGIS Select By Location I get the result I need, 17 features. But if I use PostGIS I get only 4 features, the same as if I use ArcGIS "are completely within".
Queries that I have tried, but without success:
select count(a.cd_geocodi)
from t_ibge_setor_censitario a, t_ibge_municipio_2015 b
where b.nm_municip = 'PARAÍSO DAS ÁGUAS'
cand ST_Intersects(a.geom,b.geom);
Result: 34
select count(a.cd_geocodi)
from t_ibge_setor_censitario a, t_ibge_municipio_2015 b
where b.nm_municip = 'PARAÍSO DAS ÁGUAS'
and ST_Within(a.geom,b.geom);
Result: 4
select count(a.cd_geocodi)
from t_ibge_setor_censitario a, t_ibge_municipio_2015 b
where b.nm_municip = 'PARAÍSO DAS ÁGUAS'
and ST_Contains(b.geom,a.geom);
Result: 4
select count(a.cd_geocodi)
from t_ibge_setor_censitario a, t_ibge_municipio_2015 b
where b.nm_municip = 'PARAÍSO DAS ÁGUAS'
and ST_Coveredby(a.geom,b.geom);
Result: 4
select count(a.cd_geocodi)
from t_ibge_setor_censitario a, t_ibge_municipio_2015 b
where b.nm_municip = 'PARAÍSO DAS ÁGUAS'
and ST_Covers(b.geom,a.geom);
Result: 4
Is there a way to get all those 17 features in PostGIS?
Best Answer
This depends a little on whether the small features are dependent on the larger features: do they actually share a common boundary, or are there small discrepancies along their apparently common edges?
ST_Within(a.geom, b.geom)
: gives you the geometries inb
that are fully withina.geom
. And it means fully within.ST_Intersects(a.geom, b.geom)
: this is a little more tolerant, returning anything inb
that merely touchesa
at all, even at a single point on the boundary. But if you combine this withNOT ST_Touches(a.geom, b.geom)
as mentioned in a comment by John Barça (ST_Intersects(a.geom, b.geom) AND NOT ST_Touches(a.geom, b.geom)
), then you will be left with (hopefully) the result you want. Note that small imperfections in the boundaries might still mean that the result isn't adequate, and you might need some kind of tolerance to get the correct result. If you still have issues, you could apply a small negative buffer to the geometries inb
to make them smaller (this is pretty dirty though), or useST_DWithin(a.geom, b.geom, 10)
, or you could perhaps ensure thata.geom
andb.geom
have a verified topological relationship and don't differ slightly at the edges.