BigQuery geospatial visualization news

More data

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

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

Carto tiler

dekart.xyz

--

--

--

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

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

More from Medium

Leveraging Geospatial Framework in Big Data Analytics

Extra geography simplification in BigQuery

Overcoming large-scale geospatial analysis using Google BigQuery

#30DayMapChallenge 2021 Recap — The Last Part