1. Why historisation?
  2. Goals of a historisation implementation
  3. The approaches
  4. Downloads

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.

The function updatelayer() - see download below - first runs the following checks before doing the updates:

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 insert_timegis() and update_timegis() listed in the download section below. They are best imported into the "public" schema. Next you have to do the following 6 steps, which can be automated by the script add_history(), also available below:

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 insert_timegis() and update_timegis() 4. Create indizes on the master table, specifically for create_date and archive_date 5. Create a view representing the current data state 6. Create rules (insert, update and delete) to enable data manipulation on the current view

4. Downloads

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

updatelayer()

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

add_history() function

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