2

I'd like to count the number of points that interact with a given polygon using PostGIS.

Below is my query

SELECT DISTINCT COUNT(pt.geom) OVER w sum_of_pt
FROM pt, poly
WHERE ST_Intersects(pt.geom, poly.geom)
WINDOW w AS (PARTITION BY poly.geom)

The query will return a NULL result if there is not any point interact with a polygon, but I need a solution to replace the NULL value with 0.

I have tried COALESCE(), but still got a null.

SELECT DISTINCT COALESCE(COUNT(pt.geom) OVER w, 0) sum_of_pt
FROM pt, poly
WHERE ST_Intersects(pt.geom, poly.geom)
WINDOW w AS (PARTITION BY poly.geom)

Is there any way to replace the NULL?

4
  • ST_Intersects is boolean and returns 0/1 but you filter out the zeroes by using WHERE ST_Intersects(pt.geom, poly.geom) because it is a shortcut for the TRUE case. Maybe you could rewrite the SQL and filter what ST_Intersects returns.
    – user30184
    Sep 22, 2021 at 14:17
  • is this a 1 time query? or part of a larger process?
    – ziggy
    Sep 22, 2021 at 14:36
  • Well it is part of a process actually @ziggy Sep 22, 2021 at 14:40
  • 1
    This may return no rows (which is not a NULL value!) - is that what you mean? If so, and if you are not interested in anything but the count, you can SELECT SUM(sum_of_pt) AS sum_of_pt FROM (<your_exact_query> UNION ALL SELECT 0) q;. However, your query is...uncommon, even if it works, and could be optimized for both cases when poly is a table of polygons, or a single static one.
    – geozelot
    Sep 22, 2021 at 15:33

1 Answer 1

3

The problem is how you place coalesce function, this way it works:

select coalesce( (SELECT DISTINCT COUNT(pt.geom) OVER w sum_of_pt
FROM pt, poly
WHERE ST_Intersects(pt.geom, poly.geom)
WINDOW w AS (PARTITION BY poly.geom)),0) sum_of_points;

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.