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 |
+--------+--------+--------+

--

--

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

151 Followers

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