17

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. – Vince Sep 10 '17 at 23:14
  • 1
    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? – DPSSpatial Sep 10 '17 at 23:38
18

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.

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.