PostGIS – geoMusings

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:

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.

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.

IMG_20160728_090705094_HDR

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.

sunrise_crop_small

Consider the ‘Alternative’

2013-11-27 by Bill Dollins

When I was in college, I had a psychology professor who posited that you could train a cat (a dodgy proposition at best) to take a circuitous route to its food bowl by only rewarding that behavior. He was clearly a behaviorist and was convinced that you could completely condition the instinct to go straight to the food bowl out of the cat. To my knowledge, this professor did not own a cat and never attempted to test his assertion.

I was reminded of this after reading my friend Atanas Entchev’s post in reaction to the PostGISDay hangout panel discussion. In his post, Atanas describes difficulty in convincing customers to consider open-source geospatial tools. These customers and prospects are comfortable with their proprietary tools and associated workflows and are reluctant to consider switching. I have encountered this attitude many times myself so I take no issue with the observation. Barriers to exit are real considerations, regardless of the new technology being considered. Organizations align themselves around their tools to achieve maximum efficiency with them. I discussed these issues at a talk I gave last year to the New Jersey Geospatial Forum about how organizations can extend their existing geospatial technology investments with open-source technologies. These issues are very real for any organization with a mature, extended investment in a particular technology stack.