Dr. Horst Duester (Kanton Solothurn, horst dot duester at kappasys dot ch)
Andreas Neumann (Stadt Uster, Andreas dot Neumann at Stadt-Uster dot ch)
1. Why historisation?
2. Goals of a historisation implementation
The following requirements should be taken into account when implementing historisation:
3. The approaches
The following is common to both approaches:
3.1. Repository approach
The repository approach developed by the Kanton of Solothurn (SO!GIS) allows data to be checked out, revised externally (potentially in completely different software) and to be checked in again. Alternatively, one can just import data from external production systems and store the data in a central, historised respository. The advantage is that data consistency is always guaranteed.
After the checks ran successfully the actual data synchronsation can take place. In a first step, the script finds all records that exist in the old repository table, but not in the new table to be checked in. These records are archived as deleted in the repository table. As a second step the script finds all records that are present in the new table, but not in the repository. Those records are retrieved into the repository and marked as new inserts.
3.2. Live historisation approach
For the live historisation approach developed by the City of Uster (based on existing work in PostgreSQL, e.g. TimeTravel by Varlena), we first need to import two trigger functions written in pl/Perl. You need to load the two trigger functions
1. Create additional columns required for the historisation (see above)
2. Create a delete rule on the master repository table
3. Create insert and update row-level before triggers for insert and update that call the two functions
FOSSGIS 2009 article "[Historisierung von PostGIS-Daten als Grundlage zur Langzeitarchivierung von Geodaten http://www.kappasys.ch/pgtools/pghistory/postgis_historisierung_fossgis2009_neumann_duester.pdf" (german) explaining the historisation principles.
4.1. Repository approach
Goal: synchronise external data source with historised repository. Syntax: updatelayer(new_schema.new_table,historised_schema.historised_table)
updatelayer(), pl/PgSQL, by Horst Duester, Kanton Solothurn
4.2. Live historisation approach
insert_timegis() trigger function
insert_timegis(), pl/Perl, by Andreas Neumann, City of Uster
update_timegis() trigger function
update_timegis(), pl/Perl, by Andreas Neumann, City of Uster
Goal: add necessary columns, triggers, rules, indizes and views for historised datasets. Syntax: add_history('name_schema','name_table','name_schema_view','name_view')
add_history(), pl/Perl, by Andreas Neumann, City of Uster
4.3. Sample data (SQL dump)
Goal: historised dataset to experiment with. Please create schema "test", as this SQL script creates a table and view in the schema "test". It also assumes that the above listed trigger functions "insert_timegis()" and "update_timegis()" are present. Assumes utf-8 encoding.
historised_table_example.sql, by Andreas Neumann, City of Uster