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

--

--

--

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

Time for an alternate to Markdown?

An overview of requirement engineering

The requirements are the first step.

Visual Studio 2019 — The Essential Productivity Tricks You Should Know

SimpleChain Weekly Report:June14-June27

Building an RSS Microservice with Deno and Graph QL

Node Reward Governance Vote Completed

Tools that make Python,the most popular programming language.

How to use AWS Rekognition using Ruby on Rails!

How to use AWS Rekognition using Ruby on Rails!

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

Physical Modeling of Tables in BigQuery

Extra geography simplification in BigQuery

Nested Record Processing with BigQuery

Building a data analytics dashboard on GCP — Part II