TLDR, live report here
Edit : 9 April 2020, Updated the report to use any suburb as a reference, I am using this formula to dynamically calculate the distance between two points
Due to the COVID19 pandemic Google has made some public dataset free to query, one of them is openstreetmap, I thought it is an excellent opportunity to play with BigQuery GIS functions.
Using the existing documentation, I come up with this Query which return all the geometries in a radius of 100 Km from an arbitrary point ( for some reason I choose Microsoft office building in Brisbane as a reference) and with a tag =amenity
params AS (
-27.467539) AS center,
100000 AS maxdist_m )
ST_CENTROID(geometry) AS center_location,
params.center)/1000 AS distance
UNNEST(all_tags) AS ar
('amenity') IN (
the query return
the query processed 245 GB in 16 seconds !!!, and it did cost 0 $ at least till 14 Sept 2020, after that it will incur cost ( 1 TB/5 $)
you can explore the result using the built in Geoviz, but you can’t share the data.
PowerBI does not support custom queries when connecting to Bigquery , I had to save the query results in a view, then the connection to PowerBI is straightforward.
the query results is returned as a Key, Value
using PowerQuery pivot, it is trivial to denormalize the table ( I could not find how to do that in SQL), anyway the results looks much easier to analyze.
by the way just be careful , PowerBI support a maximum of 32766 characters , but there is an easy workaround, split the column by 32766 and then concatenate in a calculated column, yes it will increase the memory size, but it works.
the custom visual is still in beta, polygons and multipolygons render perfectly, point works but with a visual discrepancy, and I don’t think linestring is supported at all.
Icon map is a very versatile visual, I hope the author will release an official update and fix the rendering bugs and add an option for color per category.
Bigquery GIS is very powerful and easy to use, the documentation is excellent, I wished only they release a smaller public GIS dataset to play with.