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.

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.

geoMusings责编内容来自:geoMusings (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 综合技术 » Refreshing a PostGIS Materialized View in FME

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录