HowTo: Group By Geography column

SELECT geo_id, ST_Union(geog) as geog  -- don't use this!
FROM table GROUP BY geo_id
SELECT ST_GeogFromText(geog), s  -- don't use this either!
FROM (SELECT ST_AsText(geog), SUM(something) s
FROM table GROUP BY 1)
SELECT geo_id, ANY_VALUE(geog) g  -- no formatting or parsing needed
FROM table GROUP BY geo_id
SELECT ANY_VALUE(geog), SUM(something) s 
FROM table GROUP BY ST_AsText(geog)
with points as (
select st_geogfromtext('point(1 1)') as a,
st_geogfromtext('point(1 1.000000000006)') as b,
st_geogfromtext('point(1 1.000000000012)') as c
)
select st_equals(a, b), st_equals(b, c), st_equals(a, c)
from points
+--------+--------+--------+
| true | true | false |
+--------+--------+--------+

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Scouting (for tests)

Answers to recent questions encountered with Torah tokens

I found this very relevant as a PM in tech, nice list Ajaikumar!

Java Web Services

Knowledge Sharing: Category-based Interpretation of Kubernetes v1.14 Release Notes

MySQL — Ongoing Replication using Data Migration Service from AWS

Musings on Microservices

SCSS: Harnessing @for and @each to build reusable components

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
Michael Entin

Michael Entin

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

More from Medium

How To Generate an Audit Table with Python and Matillion ETL for Snowflake

Spatial Autocorrelation and Moran's I in SQL and BigQuery

How to use the new Table Clones Function in BigQuery

Historical & Incremental Pipelines - Snowflake to BigQuery