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

Beginners Guide to R Markdown

Insights from Alibaba Cloud Experts: 3 Ways to Deal with High Concurrency

Tableau Metadata API: building a custom data source audit with Python

Testnet competition results

CS371p Blog: Week 7

Kafka Producer/Consumer with Message key and offset

Polkadot v0.7.0 and Kusama CC-3

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

Cloud Data Fusion: Customizing Compute Profiles at Runtime

Overcoming large-scale geospatial analysis using Google BigQuery

Why I built the python-bigquery-validator package

Determine the Winding of a Polygon in BigQuery to calculate Interior Angles