1

I have two data sets with a large number of points that I’m organizing into a hexbin grid.

I'm associating these points with the hexbin polygons by using PostGIS to determine if a point falls within a hexbin polygon. The problem with my initial query is that if st_contains or st_intersects returned false, then I didn’t get the polygon geometry at all. It was simply a hexagon shaped hole in my map. What I wanted to return, instead, was the geometry and then a count of 0. So, when there is an intersection, return the polygon + the count (this is a common operation and many previous questions cover how to do this). But I also want to return the rows where st_disjoints is true and have the count (of points in the polygon) be 0. I can do this with these CTEs and a union:

with disjoint_table as (
    select
        a.the_geom_webmercator,
        a.cartodb_id,
        0 as count
    from
        hexbin_polygons_table a
        LEFT join points_table b on st_intersects(a.the_geom_webmercator, b.the_geom_webmercator)
    where
        b.the_geom_webmercator IS NULL
),
intersect_table as (
    select
        a.the_geom_webmercator,
        a.cartodb_id,
        count(b.the_geom_webmercator)
    from
        hexbin_polygons_table a
        join points_table b on st_contains(a.the_geom_webmercator, b.the_geom_webmercator)
    group by
        a.the_geom_webmercator,
        a.cartodb_id
)
select
    *
from
    disjoint_table
union
select
    *
from
    intersect_table

The disjoint_table part (left join where is NULL...etc.) comes from Paul Ramsey's answer here

This CTE + union works but is very slow. Is there a simpler or better way to get this sort of thing?

3
  • 3
    Research the use of an OUTER JOIN to match even when no rows match, and the Coalesce() function to map NULL to zero.
    – Vince
    Aug 11 '20 at 0:53
  • @Vince That approach makes sense. But getting FULL JOIN is only supported with merge-joinable or hash-joinable join conditions Aug 11 '20 at 15:43
  • left outer join seems to be the winner Aug 13 '20 at 23:43
2

As @Vince mentions in comments, the key here is to include all non-matching rows from the joined relation in the result; this is trivially solved using a LEFT|RIGHT|FULL [OUTER] JOIN, where the thus denoted relation(s) will pass all (unfiltered) rows to the result set, with or without fulfilled match condition.
In the result set, 1:n matches will be represented as expected, having duplicated row selections from the joined relation for multiple matches of a row condition in the join relation, while non-matches of the joined table will have NULL values in join relation columns.

However, there's no need for a COALESCE here if you COUNT rows from the join relation explicitly, as an all-NULL set is counted as 0.

Run

SELECT ply.id,
       COUNT(pnt.*) AS cnt
FROM   <polygon> AS ply
LEFT JOIN
       <point> AS pnt
  ON   ST_Intersects(ply.geom, pnt.geom)
GROUP BY
       ply.id
;
4
  • If it were count(geom), then coalesce(count(geom),0) would be necessary.
    – Vince
    Aug 11 '20 at 12:42
  • This was it. Now I know that count(table.*) works differently than count(table.column_a) Aug 11 '20 at 15:52
  • 1
    @Vince COUNT cannot return NULL?
    – geozelot
    Aug 11 '20 at 16:58
  • 1
    Doh, yes. But that was the issue, indirectly.
    – Vince
    Aug 11 '20 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.