# A Spatio-Temporal Building Exposure Database and Information Life-Cycle Management Solution
## 3 Database Model
### Multi-Temporal Support  
A bi-temporal representation of time has been implemented on the primitive spatial object level to allow for a detailed description of the temporal variability within objects. Valid time is considered an object qualifier and is therefore specified for each record by qualifier timestamps. Valid time refersto real world timestamps, and, therefore, unlike the transaction time, it needs to be set for each record by the user or by the application that inserts or updates the records as a consequence of a real world change. Therefore, even continuous real world changes are detected in discrete steps in the database. This is, however, in line with the considered data acquisition methods for which the database is intended. In-situ surveys or remote sensing analysis for example depend on (stepwise) acquisition times of the surveys or the satellite images acquired over an area of interest. Satellites have specific revisit periods or temporal resolutions, which depend upon their orbit, sensor specifications, and number of satellites (in the case of a satellite constellation), which in practice define the temporal granularity. Similarly, in-situ surveys are usually carried out iteratively at discrete time steps.  
In order to keep track of the history of the real world objects, a live-history approach is followed. This means that deleted or changed records are archived in the history schema (_history.logged_actions_) along with additional information about the corresponding database transaction. A generic trigger function _history.if_modified_func()_ archives the records and logs the transaction times and additional information about changes to selected tables and attributes. The function is based on the PostgreSQL Audit Trigger and was modified according to the requirements of this work. The logging of transactions can be done at a statement level or at a row level. Control is logged separately for each table, and the data columns to be logged can be specified individually. Row values are recorded as _listore_ fields instead of _text_, which allows for more sophisticated queries on the history and reduces query complexity and storage space.  
The combination of a bi-temporal data model with a live-history approach allows for a straightforward recovery of former states of the database at defined transaction or valid times and for sophisticated temporal queries. Two functions have been implemented that allow views of the transaction and valid time history, which can be easily queried for spatial, attribute, valid, and transaction time components, to be recovered.  
* _titime_gethistory()_: This function selects from _history.logged_actions_ all records that have been modified in the logged tables. It provides the transaction time history.
* _titime_gethistory()_: This function selects from _history.logged_actions_ all records that have been subject to a real world change. For each record, the latest version at each valid time is selected. It provides the valid time history. Examples that use transaction and valid time history functions are given in Section 6.2.