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”

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.)

arcmap_pg

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.

Building a Simple Geodata Service With Node, PostGIS, and Amazon RDS

tl;dr

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.

Continue reading “Building a Simple Geodata Service With Node, PostGIS, and Amazon RDS”