Using the new Convexhull function in BigQuery to reduce Geometry complexity

BigQuery recently introduced two new GIS functions, ST_CONVEXHULL and ST_DUMP

Read the announcement here , when I saw the announcement I already thought about this use case.

The Problem

Although showing map in BI software has improved dramatically in the last couple of years, still unless you use Tableau, there is always a hard limit how much data you can show in a map, even if you can show more, it is better to reduce the volume of data just for performance sake, users are so spoiled those day that they complain when their report does not show up in less than 2 second.

Although the example used here is very specified, I am sure it can be extended to other uses cases.

Let’s say you want to show a lot of points  with one colour coded attribute, in a lot of cases, the end user wants only to see the distribution of the attribute not the individual points, see here  

That’s a lot of points ( my real case is 58 Thousand)

Convexhull to the rescue

Convexhull is very handy the input will be group of points and the output will be a closed polygons, I use it a lot in QGIS, but the killer feature here, because it is SQL and the attribute are dynamic, (in my use case they changed daily), you can write a Query that dynamically generate new geometry, either polygons or linestring or even  keep the original points if they can’t be grouped.

Now the trick is we group by status and existing grouping, for example in this dataset.

  1. Check if in one area all the status is the same using count distinct, if in one area it is the same attribute, it will generate a polygons.
  2. 2- if one area has multiple status and hence multiple colours then fien, we jump to the row level and generate line strings.
  3.  If one line string has multiple colors then we jump to points.

I built this SQL View with the help of  Mikhail Berlyant, the source data is here, replace “xxxxx.SolarFarm ” with your table.

WITH
  source AS (
  SELECT
    *,
    ST_GEOGFROMTEXT(CONCAT( "POINT (",x," ", y,")")) AS POINT,
    COUNT(DISTINCT status) OVER (PARTITION BY ROW) AS multiple_status,
    COUNT(DISTINCT status) OVER (PARTITION BY area) AS multiple_status_area
  FROM
    `xxxxxxx.SolarFarm`),
  tt AS (
  SELECT
    id, pole_nr,color,area, ROW,status, POINT,
    CASE
      WHEN multiple_status_area=1 THEN area
      WHEN multiple_status=1 THEN row
    ELSE
    CAST (id AS string)
  END
    AS newgroup
  FROM
    source),
  ff AS (  SELECT newgroup, ST_ASTEXT(ST_CONVEXHULL(ST_UNION_AGG(POINT))) AS WKT
  FROM
    tt
  GROUP BY
    1),
  xx AS (
  SELECT
    tt.newgroup,
    wkt,
    tt.status
  FROM
    tt
  LEFT JOIN
    ff
  ON
    tt.newgroup = ff.newgroup)
SELECT
  newgroup,
  wkt,
  status
FROM
  xx
GROUP BY
  1,
  2,
  3

and here is the result side by side with the original data from 3528 rows to 283 rows, that’s a big improvement,

as of July 2020, Google Data Studio does not support Geometry, and the total number of points is limited to 10K, you can use other custom Visual but currently tiles are blocked.

if you are using PowerBI to view the data, you need to use the excellent Icon Map as it support WKT geometry

Load polygons geometry into PowerBI data model

Starting a new project that involve plotting a relatively big map ( 3000 polygons), it should be straightforward, as I have done it before, but a limitation in my prefered PowerBI custom visuals had an interesting result.

1-  synoptic panel: in my view it is the best visual to show custom map, ( floor plan, general layout etc), unfortunately not this time, my new map has 3000 polygons, when I tried to plot some attributes, I get the equivalent of Windows screen of death for PowerBI, too many values !!!

1

That’s bad, I filled a bug report, the author was kind enough to reply that it will be fixed in a next release, (something to do with incremental loading) fine let’s try other options

2- Shape map : (it did not even render the polygons correctly, and to be honest the visual did not receive any update since the first time I use it, 2 years ago)

3- Mapbox: showstopper, to load your own shape file and keep it private, you need a business plan.

I am stuck, I can’t plot a 3000 polygons map in PowerBI, let’s try Rstats.

4-Leaflet: is very versatile mapping engine, right now I use it in other projects (outside of PowerBI) and it plot 60K points and nearly 500 polygons in sub 2 seconds, but there is a problem, Leaflet output is HTML which is not supported under PowerBI, I never manage to make decent screenshot, something to do with the zoom.

5-ggplot2 + SF : quick google and got this code

library(sf)

library(tidyverse)

map_shp <- read_sf(‘C:/Users/mimoune.djouallah/test.geojson’) map =dataset %>%

left_join(map_shp, ., by = c(‘id’ = ‘id’)) %>%

ggplot() +

geom_sf(aes(fill = type),lwd = 0)

map

2

Great, I have my map now ( this is only a portion, I can show the whole layout as it is proprietary), there is only a little problem, code take 25 seconds to render, it is very annoying, especially as in PowerBI you touch anything, and all the visuals render again.

my first thought maybe the join between PowerBI dataset ( dataframe) and the geojson is slow !!! the beauty of R integration in PowerBI is: you click on R icon, a new window open with an empty dataset, then you add the columns you want to analyse, yes that simple no ETL, no messing with data, you have to the mighty data model.

Loading Geometry data into PowerBI data model ?

 

After reading the SF documentation, I learned all I need is a dataframe  with a column that store geometry data, too easy just use QGIS , save as csv and make sure to select geometry, WKT

Capture

Now I loaded the csv file to the data model and join it with the attribute

3

Now here is the new code, notice, we don’t load any external file, all data is from the data model

library(sf)

library(tidyverse)

map_file= st_as_sf(dataset, wkt = “geometry”)

map = ggplot(map_file) +

geom_sf(aes(fill = type),lwd = 0)

map

basically, you need just to tell SF which column represent the geometry , unfortunately it is still slow maybe now it is 24 seconds

Another round of googling and I find the issue ggplot2 is very slow to render, no problem, this is R after all, there are a least a couple dozen of packages that do the same thing 🙂

6-Pure SF

 Turn out SF is not only to manage Geometric dataframe but it can plot too,( who would thought that)

library(sf)

map = plot(st_as_sf(dataset, wkt =geometry”)[“type”])

map

 render in less than 2 seconds, happy dance  

 

Bonus point, Cross-filing geometry

 My purpose to load geometry was just to speed ggplot2, but hold on, the geometry is in the data model, not only the attribute change ( install quantities, category etc), I can even filter polygons on the fly !!!, this is quite amazing, I can load all the layers and filter out any shape as I want.

4