GIS – Page 3 – geoMusings

Routing with BigQuery and ArcGIS Platform APIs

2021-03-11 by Bill Dollins

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

Analyzing Location Change Over Time in PostGIS

2021-05-052021-02-27 by Bill Dollins

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.

Lately…

2021-01-052020-10-25 by Bill Dollins

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

FME, Salesforce, and BigQuery

2023-07-262020-09-04 by Bill Dollins

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.

Attribute Transfer in PostGIS Using Spatial SQL

2020-04-062020-04-06 by Bill Dollins

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.

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

2020-04-022020-03-31 by Bill Dollins

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.

Fulcrum Query API and Koop

2020-03-202020-03-20 by Bill Dollins

I don’t write code as much as I used to, but I have to return to it every so often to keep my sanity. With the current world situation, there are a lot of dashboards going up, many of which are based on the Esri Operations Dashboard or ArcGIS Hub.

I got thinking about a previous crisis in which Fulcrum and Esri’s Koop were used to great effect and started wondering if I could make the interaction between the two easier. Koop, at its core, takes GeoJSON and transforms it to be consumed by clients that can handle ArcGIS feature services.

Fulcrum has two primary ways to expose data as GeoJSON feeds – data shares and the Fulcrum Query API. Koop has a provider that handles arbitrary GeoJSON feeds quite well. It can be found here. In order to use that provider with Fulcrum, the only option is to use Fulcrum data shares. That works really well, but data shares have a couple of drawbacks. First, they are always public. Second, there is a limit to the number of shares that can be enabled from an account. Third, the data is shared all or nothing, so columns cannot be filtered.

Milestones, Goals, and the Power of No

2019-11-252019-11-21 by Bill Dollins

It’s been a few weeks since I hit my latest milestone, but life was fairly full in the immediate aftermath. On the plus side, the extra time was good for reflection.

The milestone to which I am referring is that, on October 27, 2019, I ran the Marine Corps Marathon in Washington, DC. The race itself and the training leading up to it were the hardest physical things I have ever done. The weather during the actual race was crazy, with torrential rains for my first 14 miles or so. My finish time was a lot slower than I’d hoped, but I don’t care because I finished. I learned a lot and am already looking to do another.

Looking back on the process of training for the race, it was very much a clarifying experience. As the daily and weekly mileage ramped up, my time management skills were put to the test. It was no longer possible for me to squeeze in the mid-week training runs during a lunch hour, so it required more communication with my co-workers. To a one, they were supportive.

On the other end of the day, I needed to make sure things didn’t extend in a way that impinged on time with my family. Our kids are older and fully self-sufficient, so a lot of the logistics that were needed when they were younger were no longer a factor. At this stage, I just wanted to make sure I was getting meaningful time with them and my wife.

Ron Lake – The Man In the Arena

2019-09-112019-09-06 by Bill Dollins

It is not the critic who counts; not the man who points out how the strong man stumbles, or where the doer of deeds could have done them better. The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood; who strives valiantly; who errs, who comes short again and again, because there is no effort without error and shortcoming; but who does actually strive to do the deeds; who knows great enthusiasms, the great devotions; who spends himself in a worthy cause; who at the best knows in the end the triumph of high achievement, and who at the worst, if he fails, at least fails while daring greatly, so that his place shall never be with those cold and timid souls who neither know victory nor defeat.
Theodore Roosevelt

I never met Ron Lake, but the majority of my career has taken place in the geospatial technology landscape he helped create. He is best known as the author and chief advocate of Geography Markup Language (GML), the XML encoding of geographic objects that underpins most of the Open Geospatial Consortium’s web standards and is a standard itself. This fact made him a pioneer, a visionary, and a source of controversy.

It is easy, for those of us who have worked with geospatial tools for a long time, to sit at a keyboard in 2019 and forget how much harder it used to be. By that, I mean working with geospatial data. It’s still probably more difficult than it should be, but it used to be so much harder.