Lately…

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

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

Your Culture Will Adjust

All hail the mighty water cooler – the oft-praised bastion of corporate culture, where the strategies of organizations great and small are made or destroyed in the hushed tones of whispered conversations among those who gather for daily hydration.

By now, everyone who can has been working remotely for several weeks. Companies are starting to ponder what to do as the world opens back up. A few have already committed to fully remote work going forward while many others of all sizes are trying to divine how they will move forward.

Read more

Recent Diversions

Work in the new all-remote world has actually been quite busy, and I realize I am very fortunate to be able to say that. But we know what they say about all work and no play. For me, play often involves cracking open an IDE, especially since work for me isn’t centered on that anymore. This post is a loose roundup of extracurricular activities that have gotten some attention lately.

Read more

Rural Broadband – An Anecdotal Look

I spent the better part of a decade and a half building geospatial applications in support of infrastructure analysis. Not infrastructure in the modern tech sense of containers and cloud providers and orchestration, but infrastructure in the classic sense of roads and rail and telecommunications. If we consider infrastructure through the lens of the ISO/OSI layered model, I spent a lot of time looking at the physical layer.

Read more

Personal Geography

Human geography or anthropogeography is the branch of geography that deals with the study of people and their communities, cultures, economies, and interactions with the environment by studying their relations with and across space and place.

Wikipedia (https://en.wikipedia.org/wiki/Human_geography)

I was catching up with my friend and former boss, Tony Quartararo, a couple of days ago when our discussion got around to behavioral changes we have made in response to the COVID-19 pandemic and subsequent lock-downs and social distancing guidelines. Not surprisingly, given our backgrounds, geography figured in strongly.

The use case we discussed was grocery shopping. Both of us admitted to limiting our choices of venue to those that we knew from memory, so that we could gather groceries and proceed to the exit as expeditiously as possible. This has the effect of geographically constraining our choices to our immediate locality. Even in the case of large chain grocers, the floorplan of each store varies widely.

Read more

Nonessential

The cursor flashes, waiting for a command.Any command.An editor window is open,Empty.The fan does not hum, with nothing to cool.The keyboard simply restsAs dust,Illuminated by the stripes of sunlightmoving across the desk,Settles between its idle keys. It all taunts meWith the truth: There is no map I can make,No chart,No graph,No query,No post nor comment; … Read more

Attribute Transfer in PostGIS Using Spatial SQL

Data conflation is a meat-and-potatoes task in most GIS workflows. There are numerous reasons one might need to get data from one data set into another. Perhaps you want to attach a new geometry type to existing attributes or a table. Or maybe you need to pull attributes from one or more data sets into a single, “master” version. I have seen this latter use case referred to as “attribute transfer.” In an interactive, desktop setting, this can be tedious, but it’s a task at which spatial SQL excels.

Here is a simple example that uses just one line of spatial SQL (or two lines if you need to add the column) to do the heavy lifting. First, some table setting. This example takes the number of confirmed COVID-19 cases from the Johns Hopkins university county-level data (a point data set) and transfers it to a polygon data set of the US counties. There’s one caveat at the end of this post.

Read more

Watching COVID-19 Data for Your County with PostgreSQL and Node

I have addressed the topic of triggered notifications a couple of times on this blog previously. I’ve taken the opportunity to apply the technique to a current use case – the ability to get notifications whenever the confirmed count of COVID-19 cases changes in my county or surrounding ones.

I am basing this workflow on the Johns Hopkins University county-level data available as an ArcGIS feature service here: https://services1.arcgis.com/0MSEUqKaxRlEPj5g/arcgis/rest/services/ncov_cases_US/FeatureServer

Using the “f=geojson” parameter, it is possible to download the data in a format (GeoJSON) that is readily consumable by OGR. As a result, I was able to initiate a core workflow using the following steps.

Read more