PostGIS – geoMusings

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.

The One About PostGIS

2019-03-29 by Bill Dollins

Refreshing a PostGIS Materialized View in FME

2017-06-09 by Bill Dollins

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:

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.

Working with Materialized Views in PostGIS

2017-06-06 by Bill Dollins

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.

Personal Geospatial Workflows, July 2016 Edition

2016-07-28 by Bill Dollins

It’s hard to believe, but I last touched upon this topic over two years ago, when my family and I were living in our between-houses rental. One of the goals I had when building our current house was to create a space where I could more effectively work from home. To that end, I have a dedicated office that I’ve been working toward optimizing for my technical work.

One advantage of a dedicated space, which I did not anticipate ate the time, is compartmentalization. One of the dangers with working at home is the blurring of the boundary between work time and personal/family time. In our old house, I definitely felt that as I was working from the dining room table. Now, I can more effectively shut the door and step away. I’m not perfect at doing that, yet, but I am getting better.

As a consultant doing federal work, I don’t get to work off-site all the time. I’ve been fortunate, however, to have worked a few projects over the past couple of years that have allowed it, so I’ve taken advantage of it as much as possible.

Revisiting Two Old Friends: ArcGIS and PostGIS

2016-05-06 by Bill Dollins

Extending Hibernate Spatial

2016-02-12 by Bill Dollins

Java is my “Groundhog Day” language. It’s one that I use just infrequently enough to feel like I’m starting over every time a new requirement pops up. As a result of planning the system migration I discussed in my last post, I’ve been doing some work with it, which is my first sustained Java work since about 2006.

Desktop GIS

2015-08-19 by Bill Dollins

I’ve found myself using desktop GIS more and more lately. While I don’t tend to think of myself as an analyst and I’ll never be confused with a cartographer, it is simply not possible to perform GIS software development without making occasional use of desktop GIS. My typical use cases involve data preparation or query verification or similar such tasks to prove out some logic before I commit it to my application code. The screenshot below depicts my default desktop GIS configuration:

Yes, I have come full circle back to command-line GIS. After years of fiddling with the latest Arc/Q-GUI-du-jour, I find myself spending most of my time working with a flashing cursor.

A Brief Retrospective

2014-12-23 by Bill Dollins

This is the time of year where retrospectives of the previous twelve months become all the rage as content providers have column inches and/or pixels to fill up while skipping out the door on holiday breaks. As an independent blogger, I have no such requirements and the topic of this post, while retrospective, has nothing to do with 2014.