Using Spatial Contains in PostGIS

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

• Contains is a "completely within" relationship. Why are you expecting features which share boundary to be within? Commented Jan 29, 2022 at 13:14
• I guess because of `if no points of B lie in the exterior of A`. Is the boundary or A exterior or interior? Commented Jan 29, 2022 at 13:42
• We would need to have a look at your geometries. Real data very often have small unintentional overlaps at nanometer scale because of inaccurate floating point computing Commented Jan 29, 2022 at 13:45
• All right, postgis.net/docs/ST_Contains.html continues: `A subtlety of the definition is that a geometry does not contain its boundary. This implies that polygons and lines do not contain lines and points lying in their boundary.` Need some more thinking to understand what it really means. Commented Jan 29, 2022 at 13:49
• Polygon having vertices at the boundary the other one is OK for PostGIS `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 ))'))` (two triangles having two corners in common). I bet there are square-nanometer overlap areas in the data. Commented Jan 29, 2022 at 13:55

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.

• I have checked and found that overlap fails but even after zooming in to 100 % cant find difference between the big and small polygons Commented Feb 2, 2022 at 6:12
• Try the topology checker plugin (core plugin, just activate it) with must not overlap rule docs.qgis.org/3.16/en/docs/user_manual/plugins/core_plugins/…. It will create a list of overlaps and highlight them on the map. I guess that the overlaps are minimal and 100% zoom is not close enough. Commented Feb 2, 2022 at 8:45

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 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))`