[postgis-users] Data Versioning?

[chodgson@refractions.net]

> I would do this versioning in the database though with a before trigger on
> the spatial table that on insert/update/or delete copies the prior version
> of the record into the meta table.

At first, this sounds like a nice solution - but there are some use cases which 
cause problems with this sort of "automatic" historical tracking.

It might be the case that you want the "retired" or "deleted" date in the 
database to correspond with the actual date the corresponding real-life 
geographical feature changed, not the date and time that it was updated in the 
database. Along the same lines, you might also want to go back and update your 
history to show something that you now know to be true, which was previously in 
error - this change wouldn't affect the "current" data at all.

To go even further, perhaps you want to track the changes to the historical 
data - there was a presentation this at the Mapserver user meeting. That is, so 
that you are able to ask questions like:

What did we think this road looked like in 1991, when we were in 1995?
What do we think it looked like in 1991, now?
What do we think the road looks like today?

The key is that you are separately tracking differences due to actual re-paving 
of the road, and due to corrections in digitizing errors. Perhaps the first 
thing you should think about when starting to develop a geographic data 
versioning system should be defining what changes you are tracking - real-life 
changes to the actual geographic features, or just updates to the digital 
model - or both.

Either way, the solution can be as simple as just adding a created date and 
deleted date to the table, and the code to support the updates to those fields.

Chris