BigQuery geospatial visualization news

More data

First, BigQuery GeoViz can handle much more data now. It is still a client-side app and is limited by the client memory, but can handle up to about a quarter gigabyte of data. This is not huge, but it allows it to show e.g. choropleth map of all US counties. Let’s use NOAA lightning strikes dataset to show it, coloring counties by the number of strikes per square km.

SELECT 
geo_id,
sum(number_of_strikes) as number_of_strikes,
any_value(county_name) as county_name,
any_value(county_geom) as county_geom,
sum(number_of_strikes) * 1e6 /
any_value(area_land_meters+area_water_meters) as strikes_per_km2
FROM `bigquery-public-data.noaa_lightning.lightning_2018` l
CROSS JOIN `bigquery-public-data.geo_us_boundaries.counties` c
WHERE ST_INTERSECTS(l.center_point, c.county_geom)
GROUP BY geo_id
Lightning strikes map of 2018

ST_Simplify

This is not a GeoViz update, but a new BigQuery function that is very useful here. By reducing geography shape details, it allows GeoViz to show even more data and improves its responsiveness. E.g. while GeoViz can render all US counties in the query above, it might get sluggish on a not so powerful machine. We don’t need full spatial resolution to render this image though — the simplified shapes should be enough. Let’s measure how much “savings” can we get from this simplification:

SELECT 
sum(length(st_asgeojson(county_geom))) orig,
sum(length(st_asgeojson(st_simplify(county_geom, 10)))) s10,
sum(length(st_asgeojson(st_simplify(county_geom, 100)))) s100,
sum(length(st_asgeojson(st_simplify(county_geom, 1000)))) s1k,
sum(length(st_asgeojson(st_simplify(county_geom, 10000)))) s10k
FROM `bigquery-public-data.geo_us_boundaries.counties`
orig = 198,832,836 -- almost 200 MB of original data
s10 = 36,180,791 -- polygons simplified to 10 meters tolerance
s100 = 9,215,490 -- 100 meters tolerance
s1k = 2,316,429 -- 1km tolerance
s10k = 1,218,814 -- 10km tolerance
CREATE OR REPLACE TABLE views.counties_simplified AS
SELECT *,
ST_Simplify(county_geom, 100) AS county_geom_100m,
ST_Simplify(county_geom, 1000) AS county_geom_1km
FROM `bigquery-public-data.geo_us_boundaries.counties`

Data Studio

DataStudio has become much better at visualizing geospatial data — it can draw BigQuery results, here is a write up about showing points

Carto tiler

Carto BigQuery tiler is a great third-party solution for visualizing massive spatial datasets when the BigQuery GeoViz limits are not enough.

dekart.xyz

This is another great open-source 3rd party tool that supports visualizing BigQuery GEOGRAPHY data.

--

--

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.

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.