Is it ok to group by geometry in PostGIS?

Question

Is it OK to group by geometry?

We do a lot of counting points by polygon geometries, which involves intersecting the data first in order to count instances of students. by school boundary, block group, etc:

SELECT
  n.nbhd_id
  , count(*) AS count_burglaries
FROM denver.crime AS c
  JOIN denver.neighborhoods AS n
    ON ST_Intersects(c.geom, n.geom)

WHERE c.offense_ty LIKE 'BURG%'
GROUP BY n.nbhd_id

Second step is to wrap in a sub-query in order to join the geometry from the polygon table back to the count query:

SELECT

count.*
, n.nbhd_name
, n.geom
FROM (
SELECT
n.nbhd_id
, count(*) AS count_burglaries
FROM denver.crime AS c
JOIN denver.neighborhoods AS n
ON ST_Intersects(c.geom, n.geom)

WHERE c.offense_ty LIKE 'BURG%'
GROUP BY n.nbhd_id

) AS count

JOIN denver.neighborhoods AS n
ON count.nbhd_id = n.nbhd_id

But it seems you can also use geometry in GROUP BY:

SELECT
n.nbhd_id
, n.nbhd_name
, n.geom

, count(*) AS count_burglaries
FROM denver.crime AS c
JOIN denver.neighborhoods AS n
ON ST_Intersects(c.geom, n.geom)

WHERE c.offense_ty LIKE 'BURG%'
GROUP BY n.nbhd_id, nbhd_name, n.geom

Is including geometry in the GROUP BY a correct approach?

Answer 1

There's almost certainly nothing wrong with grouping by geometry in this case, since you're already grouping by a unique ID (nbhd_id). And, as you point out, it saves you a join and makes the query cleaner.

It's important to know that a GROUP BY geom clause in PostGIS 2.3 and earlier actually groups rows based on bounding box equality, not geometric equality. With real, non-contrived data, this often has the same effect as grouping on geometric equality (and is much faster), but may not produce expected results when multiple distinct polygons have the same bounding box. Starting in PostGIS 2.4, GROUP BY operates on a rigid definition of geometric equality, taking into account the start point and orientation of geometries.

Because you're already grouping on nbhd_id, you'll still see separate rows even if two distinct neighborhood polygons share a bounding box.