2

I have a PostGIS enabled PostgreSQL database with 2 tables that contain polygon geometries called "developments" and "farm_portions".

A development is a collection of 1 or more farm portions.

I want to know which PostGIS function I must use that is similar to ST_Contains that will return TRUE if a row from "farm_portions" has a geometry that is inside the geometry of a row of "developments", including the boundaries of the polygons.

enter image description here

The picture above shows the development (black outline) that is formed by 6 farm portions. All the farm portions share a boundary with the development boundary, but the ST_Contains function doesn't return any TRUE values.

If I use the ST_Intersects function, it will return true for farm portions that are outside the development but share a boundary, so it's not the right function.

Which function should I use?

6
  • 1
    St-covers should do that I think
    – Ian Turton
    May 28, 2023 at 21:01
  • I try ST_Covers but it doesn't work May 29, 2023 at 6:47
  • 1
    What expression are you using? Maybe your farms arent inside. Try difference farms with development to see if there are parts left that are outside
    – BERA
    May 29, 2023 at 11:03
  • 5
    This is an issue of inaccuracy - for non-equal borders to align perfectly, vertex distribution needs to align perfectly; this is highly unlikely in a mathematically infinite vector space represented with floating point (im)precision.
    – geozelot
    May 29, 2023 at 12:14
  • 2
    Buffer the development will a very small distance and st_covers will work
    – BERA
    May 30, 2023 at 12:11

3 Answers 3

0

According to the PostGIS manual st_covers should be the solution to your problem:

Returns true if no point in Geometry/Geography B is outside Geometry/Geography A. Equivalently, tests if every point of geometry B is inside (i.e. intersects the interior or boundary of) geometry A.

1
  • 1
    ST_Contains should work for this as well. The problem is likely that the geometries are not fully noded together, so the portion polygons aren't actually fully contained/covered.
    – dr_jts
    Jul 15, 2023 at 4:42
0

As mentioned already, the reason that ST_Contains and ST_Covers are not giving the expected answer is that the farm_portion polygons probably have small areas lying outside the developments polygon, due to different noding.

Another technique is to test if a point inside a farm_portion polygon lies in a development polygon:

ST_Contains(developments.geom, ST_PointOnSurface(farm_portion.geom))
0

If the issue is imprecision in the geometries, calculate the area of intersection and then add a cutoff for the minimum area of overlap.

ST_Intersects(developments.geom, farm_portion.geom)
AND ST_Area(ST_Intersection(developments.geom, farm_portion.geom)) > your_cutoff

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.