Skip to main content

Is isit ok to group by geometry in PostGIS?

Tweeted twitter.com/StackGIS/status/907142179851788288
Source Link
Inactivated Account
  • 18.9k
  • 5
  • 70
  • 114

Is is 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?