PostGIS

A Few Updates to pg_webhooks

by

At the time of my last post, there were a few outstanding issues that I wanted to address in the code of pg_webhooks. I’ve addressed three of them this week. There wasn’t actually a route to unsubscribe from a channel, so I added that shortly after the initial release. Another key shortcoming was that the … Read more

A Simple Webhook Interface for PostgreSQL NOTIFY

by

PostgreSQL’s NOTIFY/LISTEN method for subscribing to events from a database is a subject I return to periodically. I’ve touched on it in one form or another over several years. My latest run at it involves building a Node Express application that will allow external systems to subscribe to webhooks that are fired by NOTIFY statements from PostgreSQL.

I was prompted to do this by a number of factors. First, I have stepped back into the consulting world and find myself doing much more coding and technical work than I had been doing in my previous role. Some of my current work has involved building and optimizing data workflows and have been using database triggers in key parts of it. In my previous role, webhooks figured prominently into data integration tasks among various SaaS platforms. Finally, I simply wanted start a new coding project.

Read more

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

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

Attribute Transfer in PostGIS Using Spatial SQL

by

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

by

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

Refreshing a PostGIS Materialized View in FME

by

I am following up my previous post with an extremely simple example using FME to kick off the refresh of a materialized view (matview) after a data import. I had never used FME prior to coming to Spatial Networks, but now I’m hooked. I’m having a really hard time finding things it can’t do.

As I mentioned in my last post, it’s really easy to refresh a matview in PostgreSQL using the REFRESH MATERIALIZED VIEW statement. This leaves open the possibility of automating the refresh as appropriate in an application or other process.

I decided to illustrate this using a basic FME example. Using the cellular tower data set from my past post, I extracted a table containing only the records for the state of Maryland. The towers data set contains the two letter abbreviation for the state, but not the full state name. So, I built a matview to join the state name to a subset of columns from the towers data set. The SQL for that matview is here:

https://gist.github.com/geobabbler/76eb5db4ec685833a61e69bd58a57522

I will use FME to append the records for the state of Virginia from a GeoJSON file to the PostGIS table containing the records for Maryland.

Read more

Working with Materialized Views in PostGIS

by

It’s been a few months since I’ve posted, owing mainly to getting my feet under me at Spatial Networks. About a month after I started, the company re-merged with Fulcrum, which had previously been spun off as a separate company. As a result, I’ve gotten to know the Fulcrum engineering team and have gotten to peer under the hood of the product.

Of course, Spatial Networks is also a data company. What had originally attracted me was the opportunity to help streamline the delivery of their data products, and this remains a pressing issue. This has kept me elbow-deep in PostGIS, and has led me to delve into using materialized views more than I have before.

What is a materialized view? If you are familiar with relational databases, then you are familiar with views, which are saved queries that are stored in the database. Similar to tables, you can select data from a view; but, rather than directly selecting physically stored data, you are executing the SQL that defines the view, which will stitch together data at the time of execution.

Read more