Spatial SQL: Should you ever GROUP BY geometry/geography?

TLDR: Probably not, unless you are sure of what you are doing!

In spatial analysis, it is common to use aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX() etc) to generate statistics related to a particular geography. All aggregate functions have to be followed by a GROUP BY ... clause to instruct the query how to gather your data.

For example, to answer the question "How many populated places are there in each US State?" you might tempted to use a simple query like the following:

The problem with grouping by geometry/geography is that the GROUP BY clause depends on the equality operator. Whilst it is easy to test equality between numeric data types, testing equality between geometry/geographies is a little more complex... Consider the polygons below:

• Polygon 2 is geometrically equal to polygon 1 however the node order is reversed
• Polygon 3 is geometrically equal to polygon 1 however the start node and node order are different
• Polygon 4 is geometrically equal to polygon 1 however it has double the number of nodes
• Polygon 5 is a different shape to polygon 1 however it has an identical bounding box to all other polygons.

You can see how this introduces complexity - should two geometries only be considered equal if they are geometrically identical, have the same number of nodes in the same order? Or is it only the size/shape that we are worried about? The answer of course depends on your use case.

This nuance is why Google BigQuery currently does not support grouping by geography, as there is still debate around what the expected behaviour should be. If you are using PostGIS you can group by geometry/geography and it will consider geometries to be equal when they have the same number of nodes, in the same order with the same start point i.e. exactly equal.

To further highlight the danger of grouping by geometry, PostGIS used to use a bounding box comparison for GROUP BY for the first 16 years of its existence (up to v2.4) meaning the same query grouping by geometry could give different results depending on the version of PostGIS that you are running!

Best practice therefore is to group by id, or another appropriate field to avoid the problems highlighted above (which is also cheaper computationally). As you have to include all items in a SELECT statement within the GROUP BY clause, you can use the handy ANY_VALUE() function to return geometry/geography as part of your result set to plot the results on a map. ANY_VALUE is an aggregate function and you are presuming that you are grouping by identical geometries so it doesn't matter which is ultimately returned! See below for an example: