geoMusings

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:

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.

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.

TUGIS 2017

2017-03-22 by Bill Dollins

Yesterday, I attended TUGIS 2017, which was the 30th installment of Maryland’s annual statewide GIS conference. It was a great experience as there is a lot of innovative work going on in Maryland. The one-day format is specifically designed to be a high-value experience. Attendees trade the minimal impact on schedule for the fact that they will certainly miss some content they want to see. I think it’s a fair trade-off.

I also happened to give the keynote address at the conference. It was quite an honor to be asked to address so many geospatial practitioners who are working to tackle pressing issues in Maryland. Thanks to Ardys Russakis and the conference organizing committee for inviting me.

Triggered Notifications Using PostGIS, Redux

2017-03-07 by Bill Dollins

A while back, I wrote a post about using Python to generate triggered notifications in PostGIS. The approach described in that post is somewhat fraught with peril and I’ve learned a little bit more since then, so I have a different approach, using PostgreSQL NOTIFY and LISTEN, which I’ll describe here.

Engagement

2017-02-08 by Bill Dollins

A few years ago, we sold our house and moved into a new one that we had built. The old house happened to be the one in which I had grown up. The process of disconnecting from that house got me back in touch with a lot of tasks that had become muscle memory. For example, mowing the lawn.

I had mowed that lawn roughly every week since middle school. By the time I was mowing it for what I realized was the last time, I could have done so blindfolded. I knew where every obstacle was and knew every contour in the ground. I had long since stopped paying attention to the task. There were many other things that I realized had become the same for me.

Making a Change

2017-01-24 by Bill Dollins

HIFLD Open January 2017 Updates

2017-01-19 by Bill Dollins

I just got an announcement in my inbox of a major update to HIFLD Open. A number of new data sets have been added, along with updates to many others. The announcement also addressed HIFLD Secure, but I won’t touch upon that here. From the flyer attached to the email, here are the updates. If you are so inclined, it’s time to get scraping.

Thank You for 10 Years

2016-12-07 by Bill Dollins

Post GIS

2016-11-17 by Bill Dollins

Early in my career, I was interviewing for a job with a large, three-letter, consulting firm. I was going to be the “GIS guy” on the team. The interview was wide ranging and went well. We eventually got around to the topic of dynamic maps on web sites. To place this in the proper technology context, this was 1998.

tiles2

As it happened, I had some experience with web mapping as I had just implemented ArcView IMS for my soon-to-be-former employer. Avenue and HTML, FTW!

The hiring manager, being a mainline IT guy and a tech geek at heart, began to postulate how they must work. In this mind, there was a database full of small JPEG or GIF images that you queried and sent to the browser, based on the user’s current map extent. This, of course, was preposterous to a “GIS guy” like me.

A Funny Thing Happened On the Way to a Calendar

2016-10-26 by Bill Dollins

The call for maps for the 2017 GeoHipster calendar has closed and review is underway. I haven’t begun collating the responses yet, so I have no idea how it will turn out, but I can say that, for me, the process so far has been personally rewarding.

I was not involved in the making of the 2015 calendar. When it came time to considering doing one for 2016, I volunteered to coordinate the process; with no idea what to expect. We had quite a response and I was impressed with the quality of the work received. Because it was my first time through, I was pretty consumed by the process and probably didn’t get to give as much consideration to the art that was before me.