Using Spatial Contains in PostGIS

Question

When performing Spatial Contains query I am not getting expected results.

I have below use case where I want only those small polygons as shown in first image namely (1,2,3,4,5,6,7,8,9) whose geometry which lie within big polygons as shown in second image (let's assume polygon 1) should get highlighted.

When I am performing spatial query in PostgreSQL using PostGIS I am using ST_Contains(geometry geomA, geometry geomB) method which clearly states as per documentation "Returns TRUE if geometry B is completely inside geometry A. A contains B if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A" I am getting only polygon 3 as shown in third image below. However, I am expecting all polygons in first image from 1-9 should get highlighted

Answer 1

You are right that a big polygon contains a small polygon and documentation is correct with it

Returns TRUE if geometry B is completely inside geometry A. A contains B if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A

select ST_contains( ST_GeomFromText('POLYGON (( 300 380, 420 480, 420 260, 300 380 ))'),
ST_GeomFromText('POLYGON (( 360 380, 420 480, 420 260, 360 380 ))'));

true

Off-topic when it comes to this question, but polygon does not contain a point or linestring which lies on the polygon boundary because there are no points in the interior of the polygon.

select ST_contains(
    ST_GeomFromText('POLYGON (( 300 380, 420 480, 420 260, 300 380 ))'),
    ST_GeomFromText('LINESTRING ( 300 380, 420 480 )'));

false

Your case looks like the first case but because most of the small polygons are not selected the most probable explanation is that they really are not contained. Shape base polygon layers have very often a poor topology and boundaries which are supposed to be identical are not that. Typically some vertices are missing from one of the polygons or coordinate values of vertices that should be the same differ with some nanometers. A quick test would be to check if the small polygons overlap with the big polygon.

Answer 2

As @user30184 said, the small polygons should be contained, even if a part of it lie on boundary, and the problem is probably because the geometries are not perfect, or there is projection artifacts.

There is a few way to handle this:

• You can try to correct geometries (for example by using ST_SnapToGrid), but it can sometimes add more problems that it can resolve...
• You can add a margin to your ST_Contains. There is multiple ways:
  • Add a positive ST_Buffer to your big poly
  • Add a negative ST_Buffer to your small poly
  • compare the areas of the polygons intersection with ST_Area (for example, instead of doing ST_Contains(big.geom, small.geom) you can do ST_Area(ST_Intersection(big.geom, small.geom)) > (0.99 * ST_Area(small.geom))

Also, as a reminder, for this kind of work, be sure to work with projected geometries, not 4326.