Reconsidering the Spreadsheet

by

I’m a CIO. That means a typical day can involve a range of activities: responding to data calls for security audits, reviewing SOC2 reports of prospective platform vendors, managing subscriptions of existing vendors, handling GDPR requests, ensuring data from corporate systems is meaningful and relevant for our internal stakeholders, attending meetings of various sorts. Somewhere … Read more

Return on Non-Investment

by

Yesterday evening, I had the pleasure of participating in a panel discussion on Clubhouse, hosted by Todd Barr and Jordan Cullen, and including Will Cadell of SparkGeo. Clubhouse seems to be a really convenient venue for setting up such a forum with low barriers to entry, so that was enjoyable. The topic of the discussion was “Geospatial ROI” and we talked about various ways to articulate the value of geospatial (the data and the concept) and GIS (the toolset to exploit geospatial).

One topic that we didn’t have time to get to, but has been at the front of my mind for a while is the “return on non-investment” with regard to open-source tools, geospatial or otherwise. Open-source has been mainstream for quite some time and platforms like Github make it easier to publish, manage, and maintain open-source tools. As a result, it’s easier than it’s ever been to find and use open-source tools to solve your problem.

Read more

BigQuery and Koop

by

As I continued my experimentation with BigQuery, I found myself wanting to more easily use it with my regular GIS tool set. BigQuery has a lot of powerful analytic capability, but the SQL console is intimidating for the casual user and the GeoViewer tool is fairly limited. As I began digging deeper in my previous … Read more

Routing with BigQuery and ArcGIS Platform APIs

by

This post is a continuation of last month’s post about analyzing location change with BigQuery. At the end of that post, I was already thinking of ways to extend the analysis and visualization. I decided to take the opportunity to explore Esri’s recently-announced ArcGIS Platform APIs. These APIs are the same that have been available via an AGOL subscription or an ELA, but they are now presented in a consumption-based model, similar to Google or Mapbox APIs, that allow you to make use of them without having to make a larger up-front commitment to the rest of the ArcGIS stack. Esri’s basemaps and their location services have always been high-quality, so it’s nice to see them available under a more accessible model.

Decided to use the Esri routing API to visualize possible routes between the various locations of the “Sample Asset” from my last post. I chose to build a very simple Node API to access the BigQuery data and use that API from a simple page that calls the Esri API and displays the output on a Leaflet map. The first thing I needed to do was add a little extra SQL in BigQuery to return coordinates in a format consumable by the Esri API. The raw API expects coordinates delimited as such:

-76.519989228,38.303696474000006;-76.695443826137989,38.376038894414251;-76.635015354340467,38.29745667728772;-76.519989228,38.303696474000006;-76.695443826137989,38.376038894414251;-76.635015354340467,38.29745667728772;-76.519989228,38.303696474000006;-76.495959193,38.236694621

Read more

Analyzing Location Change Over Time in PostGIS

by

Following up on my previous post, I decided to attempt the same analysis in PostgreSQL. The analysis doesn’t make use of any spatial logic itself (yet), but I consider this a PostGIS post because it is using PostGIS geometries.

A simple FME workspace to move my data to PostGIS.

In the past, I have noticed that BigQuery SQL is very reminiscent of that of PostgreSQL, which has helped me ramp my productivity with BigQuery. In the case of the LAG function as used here, that reminiscence is an exact copy. So, again, PostGIS comes through.

Read more

Analyzing Location Change Over Time in BigQuery

by

I’ve recently spent a lot of time doing various forms of business analytics in BigQuery. As discussed in a previous post, I’ve been using BigQuery as the data integration environment for several business systems. I’ve found integration at the data level via an ETL/ELT/IPaaS pipeline to be a lot more stable than system-level integrations that involve chaining together dependencies on fairly volatile SaaS APIs.

The original premise was fairly straightforward: Given a table of user-level statistics over time, identify only those points in time where one or more of the statistics changed value. In our case, we had several million rows of user-level data captured on a daily cadence. Manually inspecting this data for changes in individual values by customer was simply not a viable plan. The BigQuery LAG function came to the rescue.

Read more

Mornings and Evenings

by

I’ve been paying more attention to my morning and evening routines the past few months. This started as a outgrowth of my focus on fitness, but has since expanded. Originally, I set out to block out time during my day to ensure that I had time for fitness activities. This usually shows up as an “out of office” block on my Google Calendar (which automatically rejects meeting requests).

That typically happens in the mornings, especially for runs. I am a morning runner and the likelihood of me actually going out for a run decreases as the day goes along. I am more successful with yoga/HIIT/weight training at other times of the day, but running has to happen in the morning if it’s going to happen.

Read more

Lately…

by

These are some of the things I’ve been up to lately, while the blog has been quiet:

At work, I’ve continued delving into BigQuery. Our FME jobs are running like clockwork and I’ve been spending a lot of time writing queries and doing analysis for various stakeholders across the company. The next phase of the project is to expose a BI infrastructure to make the data a little more self-service. That will most likely start out in Google Data Studio and then we’ll assess from there. We also have a few more systems to integrate, but the ease of data fusion across the ones we have so far is already paying dividends.

FME workflow to post Salesforce opportunities to Jira

Read more

FME, Salesforce, and BigQuery

by

More often that not in my current role, opportunities to get my hands dirty come from the data side of our operation rather than the engineering side. These days, the data side involves corporate data rather than a lot of geospatial data. If I were to be guided by my own personal inertia, I’d drift toward traditional geospatial data 99% of the time, but working with other data stores and building pipelines involving them is good exposure.

Most recently, I’ve been working a lot with Salesforce and other data sources to support customer success operations. Customer success, as a discipline, is relatively new, having grown out of the SaaS market from the best practices involved in nurturing and growing customers post-sale as part of the SaaS land-and-expand model.

SaaS typically begets SaaS – meaning that you won’t often find SaaS companies using on-prem versions of business tools. This presents interesting challenges for data integration and analytics. Fifteen years ago, there’d most likely be a server room with installs of various vertical systems that had been configured to use whatever the organization’s blessed database platform was. In the event that an individual system didn’t support that database, there might be some nominal ETL performing a one-way sync so that the necessary charts and graphs could be made as needed.

Read more