2

I have a PostgreSQL 12 database with PostGIS (using EPSG 4326). There are some tables with millions of points, and others with polygons.

In general , I had no problems with the queries. However, I recently noticed that in some cases, when I count points in polygons, some points are excluded or counted twice. I use different tables with a grid of square polygons of distinct sizes, one degree, half degree, etc. If I use:

SELECT polygon, count(point) 
FROM   point, polygon 
WHERE  ST_Within(point, polygon)
GROUP  BY 1

Some points located over the edge of the polygon are excluded from the count.

If I use:

SELECT polygon, count(point) 
FROM   point, polygon 
WHERE  ST_Intersects(point, polygon)
GROUP  BY 1

Those points get counted twice, once for each polygon.

How can I handle this situation to avoid errors in the analysis?

1 Answer 1

5

The spatial predicate ST_Contains and the reverse ST_Within have the idiosyncrasy that "polygons do not contain their boundary" (see this writeup). This is why your use of ST_Within does not report points lying on the boundary of polygons.

If a point lies on the boundary of two or more polygons then ST_Intersects will correctly detect that. You will have to use some strategy to assign the point to a unique polygon (perhaps by choosing the polygon with lowest id value, if that is appropriate to your data model).

2
  • Yes, I've learned about that behavior when I detected the problem. However, it is neither clear nor easy to apply an assignation strategy. The basic data I have are point positions for vessels, and I usually use different groups of polygons for grouping. Some are grids of rectangles and others are very complex areas (eg. Exclusive Economic Zones, Marine Protected Areas, etc.)
    – Gustavo
    Apr 20, 2021 at 20:29
  • I think this is a general SQL problem, not unique to spatial. If there is no clear assignment strategy then perhaps it's fine to simply pick one containing polygon at random? A query using DISTINCT ON point is one way to do this.
    – dr_jts
    Apr 20, 2021 at 22:40

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.