PostgreSQL

Window Functions and PostGIS

by

FOSS4G North America was an opportunity for me to reconnect with both community and technology. I enjoyed being able to both learn new things and refresh my skills with technologies such as PostGIS. I was reflecting on how, a couple weeks prior to the conference, I introduced the concept of PostgreSQL window functions to a … Read more

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

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

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

Building a Simple Geodata Service With Node, PostGIS, and Amazon RDS

by

tl;dr

This post describes the construction of a simple, lightweight geospatial data service using Node.JS, PostGIS and Amazon RDS. It is somewhat lengthy and includes a number of code snippets. The post is primarily targeted at users who may be interested in alternative strategies for publishing geospatial data but may not be familiar with the tools discussed here. This effort is ongoing and follow-up posts can be expected.

Read more