Two BigQuery Geospatial query tricks

SELECT
a.geo_id,
COUNT(b.point_geom) AS count
FROM
`polygon_dataset` a
LEFT OUTER JOIN -- ERROR
`point_dataset` b
ON
st_intersects(a.poly_geom, b.point_geom))
GROUP BY
a.geo_id
SELECT 
aa.geo_id,
COALESCE(bb.count, 0) AS count
FROM
`polygon_dataset` aa
LEFT OUTER JOIN
(
SELECT
a.geo_id,
COUNT(b.point_geom) AS count
FROM
`polygon_dataset` a
JOIN
`point_dataset` b
ON
st_intersects(a.poly_geom, b.point_geom))

GROUP BY
a.geo_id
) bb
ON aa.geo_id = bb.geo_id
SELECT
a.geo_id,
COUNTIF(real_data) AS count
FROM
`polygon_dataset` a
JOIN
(SELECT point_geom, TRUE AS real_data FROM `point_dataset`
UNION ALL
-- add whole Earth, and flag it with real_data = FALSE
SELECT ST_GeogFromText('FULLGLOBE'), FALSE) b
ON
st_intersects(a.poly_geom, b.point_geom))
GROUP BY
a.geo_id
--- WARNING: INCORRECT QUERY, do not copy ---WITH polygon_dataset2 AS (
(SELECT ROW_NUMBER() row_id, * FROM `polygon_dataset`)
)
SELECT
aa.row_id, bb.count
FROM
`polygon_dataset2` aa
LEFT OUTER JOIN
(
SELECT
a.row_id,
COUNT(b.point_geom) AS count
FROM
`polygon_dataset2` a
JOIN
`point_dataset` b
ON
st_intersects(a.poly_geom, b.point_geom))
GROUP BY
a.row_id
) bb
-- BAD JOIN, the two row_id are not guaranteed to match
ON aa.row_id = bb.row_id
--- WARNING: INCORRECT QUERY, do not copy ---
SELECT
-- unpacks the row into individual fields
ANY_VALUE(a.properties).*,
COUNTIF(real_data) AS count
FROM
(SELECT poly_geom, geo_id,
-- packs whole row
p AS properties
FROM `polygon_dataset` p) a
JOIN
(SELECT point_geom, TRUE AS real_data FROM `point_dataset`
UNION ALL
SELECT ST_GeogFromText('FULLGLOBE'), FALSE) b
ON
st_intersects(a.poly_geom, b.point_geom))
GROUP BY
a.geo_id

--

--

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.