# Is it ok to group by geometry in PostGIS?

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?

• What does the `GROUP BY` gain you? Isn't it joinable by a unique key of `nbhd_id`? You are making the sort wider, which increases runtime, possibly by more than a subsequent join. Sep 10, 2017 at 23:14
• It allows me to not have to wrap the count query as a sub-query to join on the nbhd_id - if it increases runtime to group by geometry, then that is a possible reason to NOT do this, wouldn't you say? Sep 10, 2017 at 23:38

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.