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

Working with Materialized Views in PostGIS

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


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