[postgis-users] Data Versioning?

[Graeme Leeming]

Jeff, the answer is yes -- we have been successful in implementing 
spatial data versioning with PostGIS/PostgreSQL.

Refractions maintains the Digital Road Atlas (DRA) spatial database for 
the British Columbia provincial government.  Monthly update processes 
integrate attribute and geometry data associated with roads in the 
province.  Historical DRA data is modeled using temporal date fields 
similar to what Regina describes: SQL insert/update of admit and retire 
dates, so that each piece of information has a lifespan.  No row is ever 
automatically deleted in the update process, because that would result 
in a loss of information.  To solve her issue about weeding out older 
versions, we have also included a boolean flag called "most_recent".  
Because the majority of queries are done only on the most current data, 
the boolean field can be easily used instead of fiddling with date 
ranges.  It is really efficient, and still allows specific queries using 
admit/retire dates should one want to access previous data versions.  
This temporality has been applied to both attribute and spatial tables.

The DRA database consists of about 30 tables and contains 22 versions 
representing BC road data and changes over the past two years.  There 
are about 3 million rows, of which 400,000 have been updates.  We manage 
these updates using custom C++ code, however you could also use triggers 
or stored procedures to do something similar in the database itself.  
More information about the model and its temporal aspects can be found 
at http://bcdra.refractions.net

- Graeme

Jeff McKenna wrote:

> I guess I was hoping to hear that PostGIS/PostgreSQL already handled 
> spatial data versioning similar to ArcSDE.
> I understand the concept of 'meta tables' storing extra timestamp 
> info, but are there examples of this already in use out there?  Has 
> anyone been successful in implementing spatial data versioning with 
> PostGIS/PostgreSQL????  (we deal with large datasets and this would 
> impact the way we do things bigtime)
>
> thanks.
>
>
> Jeff
>
>
>
>
>
> Obe, Regina DND\\MIS wrote:
>
>> I'm not familiar with how SDE does it - but it would seem to me to 
>> implement
>> such a thing in PostGIS/PostGresql - that as you said you would have 
>> a meta
>> table of some sort which maybe mirrors the original table but has extra
>> fields to keep track of whether it is a delete/insert/update and the
>> datetime stamp.  The only reason I wouldn't keep it in the same table is
>> that to the unsuspecting user who just wants the latest version (and 
>> doesn't
>> ahve aneed to revert etc.) -  it's unnecessary processing to weed out 
>> older
>> versions.
>>
>> 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.
>>
>>
>>
>> -----Original Message-----
>> From: Gary Sherman [mailto:sherman at mrcc.com]
>> Sent: Wednesday, July 09, 2003 10:11 PM
>> To: 'PostGIS Users Discussion'
>> Subject: RE: [postgis-users] Data Versioning?
>>
>>
>> ArcSDE implements versioning at the server (read SDE software)  level.
>> The database tier has no inherent "versioning" capability, regardless of
>> whether it is running on Oracle, Informix or Brand X. Versioning is
>> implemented using "delta" tables that track changes (adds and deletes)
>> across versions. There are a slew of other tables (what I call "meta"
>> tables) that make up the ArcSDE schema.
>>
>> My guess is that a middle tier would be required between
>> PostgreSQL/PostGIS and the client to implement a versioning scheme
>> similar to ArcSDE.
>>
>> -gary
>>
>> -----Original Message-----
>> Subject: Re: [postgis-users] Data Versioning?
>>
>>
>> SDE is ESRIs Spatial Database Engine.  It supports versioning for
>> GeoSpatial data the same way that CVS does this for code.  CVS will not
>> work for this purpose, as Spatial Data stored in PostGIS is not stored
>> in flat text files like source code.  Postgres / PostGIS would need to
>> support this directly.
>>
>> I don't have the answer to the question, but am also very interested to
>> know if Postgres/PostGIS support any form of geospatial data versioning.
>> I am also curious to know if it supports long transactions.  I should
>> probably RTFM before asking though.
>>
>> --Craig
>>
>>
>>
>> ----- Original Message ----- From: "Rich Shepard" 
>> <rshepard at appl-ecosys.com>
>> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>> Sent: July 09, 2003 3:36 PM
>> Subject: Re: [postgis-users] Data Versioning?
>>
>>
>>
>>> On Wed, 9 Jul 2003, Jeff McKenna wrote:
>>>
>>>
>>>> Pardon my beginner question, but I am trying to find out how 
>>>> PostGIS/PostgreSQL handles data versioning.  Myself I am familiar 
>>>> with SDE, but would love to find an open source alternative to 
>>>> versioning
>>>
>>>
>> large
>>
>>>> datasets.  Does PostGIS handle data versioning?  If not, are there 
>>>> plans for this in the future?  Are there any documents/past email 
>>>> postings regarding this?  Thanks.
>>>
>>>
>>> Jeff,
>>>
>>>  I've no idea what SDE might be, but there's always CVS. This is used
>>
>>
>>
>>> primarily for source code development projects, but I've used it for 
>>> document development, too. So, I imagine that one could check in and 
>>> out different data sets, too.
>>>
>>> Rich
>>>
>>> Dr. Richard B. Shepard, President
>>>
>>>                       Applied Ecosystem Services, Inc. (TM)
>>
>>
>>
>>
>>
>
>
>