GIS Analytics

Overview

Challenge: Rapidly develop custom analytics for a client of a major GIS provider
Solution: Built an Excel-based solution in VBA to query the provider API, cache the data and generate the analytics on-demand
Technologies: Microsoft Excel, Visual Basic for Applications (VBA)

Rapid Analytics

Even the best analytics portals might miss just that specific view the client really needs.

And as the old saying goes, "all data ends up in Excel".

The requirements originally envisioned a Google Sheet + GAS solution, but initial testing during systems design showed that Google Sheet wasn't a viable option due to capacity constraints.

Excel is only limited by the computer it runs on and, for better or worse, VBA is a very stable language that didn't change much for the past two decades. In addition, Excel is available on pretty much all corporate computers, so it became the ideal platform to deliver the required analytics.

The delivered tool would locally cache the relevant data to minimise the number of queries to the GIS API. This also cut the update time from 20-30 minutes to a few seconds of incremental updates, as the total amount of data used was quite substantial.

The analytics could then be run quickly using the cached data.