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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — Materialized View: public.mvw_cellular_test | |
| CREATE MATERIALIZED VIEW public.mvw_cellular_test AS | |
| SELECT cellular_test.id, | |
| cellular_test.geom, | |
| cellular_test.licensee, | |
| cellular_test.loccity, | |
| us_states_lookup.name AS state_name | |
| FROM cellular_test | |
| INNER JOIN us_states_lookup ON cellular_test.locstate::text = us_states_lookup.abbr::text | |
| WITH DATA; |
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.
Disclaimer: This example wastes the power of FME. If this were all I needed to do, I’d probably just use OGR. I kept it simple for this post.
Here is a screenshot of my import process from FME workbench:
As can be seen, 656 records from the GeoJSON file will be appended to the PostGIS table.
To make FME run the REFRESH statement after the import, I just put the SQL into the advanced parameters for the PostGIS writer.
For demonstration purposes, I’ll run the import without the REFRESH statement. This will result in the source table having a different record count than the matview, as shown here.
Now, I’ll clear out the Virginia records from the base table, add the REFRESH statement back into FME, and rerun the process. This time, the table and the matview are in sync, as seen here.
This basic example illustrates one way to automate the refresh of matviews as part of a data update process. Whether using FME, GeoKettle, or your own application code, it’s very easy to keep matviews in sync.
It’s important to remember however, that every refresh is writing data to a new table. Care should be taken to tune how often matviews are refreshed to ensure that performance doesn’t suffer. They aren’t appropriate for high-velocity data sets, but can work nicely for data that is updated less frequently.
FME FTW! It’s like having to play with LEGO for work. And using the SQL parameters before and after writing the data is a huge time saver and everything is nicely contained within the workbench.
I am still very much an FME neophyte, but I’m sold.