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”

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 goals of these recently introduced enhancements are:
– storing XML Documents directly within the DBMS
– supporting XML validation
– supporting plain SQL queries on behalf of XML Documents
via canonical XPath expressions

Implementing directly into the Spatial DBMS a common core of XML-oriented features surely is an interesting and useful option, just considering that ISO- and INSPIRE-Metadata or SLD/SE Styles are fully based on XML.

Although I find myself working more with GeoJSON and CartoCSS these days, I think support for XML is a good step for SpatiaLite. There are some very mature use cases based on XML, as Sandro points out. While SLD is not my favorite, this may keep it on my radar. Also, it would be interesting to see how this new capability would possibly affect the evolution of the OGC GeoPackage draft specification.


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 project under a standard open-source license is a nice step from a DoD organization.

The message is quoted below:

Hi List,

I’m really proud to announce you all that finally a rock solid stable and really easy-to-be-deployed binary porting of SpatiaLite for the Android platform is now available for download [1].


Many thanks to the U.S. Army Geospatial Center [2] who very generously contributed this highly appreciated and really useful resource under a genuine free software license (LGPLv3).


A detailed tutorial [3] explaining how-to deploy and use SpatiaLite on Android platforms has been kindly contributed by Andrea Antonello, who spent many long hours during the last week while performing a thorough testing of SpatiaLite-Android, then deciding to publicly share his experiences with the SpatiaLite community. Feel absolutely free to pay a beer to Andrea; he’ll surely appreciate 😉


enjoy, Sandro

SpatiaLite 4.0 Released

It looks like it was a busy weekend for Sandro Furieri and the rest of the SpatiaLite team as version 4.0 was announced on Sunday. There are a number of changes, so it’s best to catch up on them before switching over.

Not this SpatiaLite

I have a couple of Federal customers that are integrating SpatiaLite into their workflows so I’ll need to take a day or so to assess impacts any impacts there. Looks like the perfect way to slide back into work after a long weekend.