Refreshing a PostGIS Materialized View in FME

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.

Continue reading “Refreshing a PostGIS Materialized View in FME”

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.

Continue reading “Working with Materialized Views in PostGIS”

Personal Geospatial Workflows, July 2016 Edition

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.

Continue reading “Personal Geospatial Workflows, July 2016 Edition”

Revisiting Two Old Friends: ArcGIS and PostGIS

Back in the dark old days of ArcSDE, when it first started to support PostgreSQL/PostGIS as a back-end data store, I did a series of posts about how to work with it. Of course, working with PostGIS in ArcGIS was a theme of the early days of this blog, through my association with zigGIS. Although it’s been the case for a while, I’m feeling a bit happy today that it’s now as simple as this to work with (vanilla, non-geodatabased) PostGIS in ArcMap. (Post continues below the GIF.)


You might ask “Why not just work in QGIS?” and you would have a valid question. QGIS is a perfectly fine desktop PostGIS client. As a matter of fact, I went almost two years without a functioning copy of ArcMap and using QGIS as my primary desktop tool (which is why I’m exploring the capabilities of ArcGIS 10.4 now). Sometimes, projects dictate what tools you need to use. The data-level interoperability implied by the support shown above has me thinking about hybrid workflows to allow shops (especially small ones) that have need for final products to end up in an Esri stack to still exercise a measure of choice with regard to tools. It may be time to re-tool that old series of posts for the state of GIS tools circa the middle of this decade.

Extending Hibernate Spatial

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.

Continue reading “Extending Hibernate Spatial”

Desktop GIS

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.

Continue reading “Desktop GIS”

A Brief Retrospective

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.


Continue reading “A Brief Retrospective”