Window Functions and PostGIS

FOSS4G North America was an opportunity for me to reconnect with both community and technology. I enjoyed being able to both learn new things and refresh my skills with technologies such as PostGIS. I was reflecting on how, a couple weeks prior to the conference, I introduced the concept of PostgreSQL window functions to a … Read more

Balancing Organizational Controls and Technical Controls in Data

Technical Controls – The security controls (i.e., safeguards or countermeasures) for an information system that are primarily implemented and executed by the information system through mechanisms contained in the hardware, software, or firmware components of the system.

Organizational Controls – The security controls (i.e., safeguards or countermeasures) for an information system that primarily are implemented and executed by people (as opposed to systems).


The definitions above come from the glossary of the NIST-800 series of cybersecurity publications. While they are focused on cybersecurity, the broader concepts – automated controls versus manual controls – are applicable elsewhere. Over the last couple of weeks, and especially since I attended the TUgis conference, I have been thinking about these concepts in terms of data in general and schema in particular.

I find schema to be an interesting concept. The term “schema” is fairly wide-ranging in its definition but it can be defined as “an underlying organizational pattern or structure; conceptual framework.”

Read more

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.

Read more

SpatiaLite 4.1.0 Beta Preview Available

On the SpatiaLite Google Group this morning, Sandro Furieri announced the availability of a beta preview of SpatiaLite 4.1.0. The primary focus of this preview is to get early comment on new capabilities supporting the storage, validation, and query of XML documents. More information about this update can be found here. Says Sandro: The main … Read more

SpatiaLite for Android Available

On what seems to be turning into SpatiaLite Monday, Sandro Furieri also announced on the SpatiaLite Google Group the availability of a stable version of SpatiaLite for Android. I am happy to see that this version was developed and contributed back by the US Army Geospatial Center. The fact that they contributed back to the … Read more