Let’s look at how to aggregate data by a geography column. BigQuery refuses to GROUP BY using geography, so let’s consider alternatives, and learn about under-appreciated ANY_VALUE function.

Lacking the ability to GROUP BY a geography column, what can one use right now? A very common solution is to do GROUP BY by some other unique id that you might have in the table. E.g. you might often have some geo_id. Another common solution is to convert geography to text, typically using ST_AsText, and create new unique column suitable as GROUP BY key.

What if you need the geography value…

Michael Entin

Hi, I'm TL of BigQuery GIS project. Posting small recipes and various notes for BQ GIS users.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store