Tracking changes in a spatial database using the change log

 Print Previous page Top page

Formation of a cartographic representation of spatial information from a database is performed on the basis of a data sample obtained by a corresponding SQL query. After obtaining in the GIS Panorama of the data sample, in the database various data changes made by other clients of the DBMS can occur. You can get a more up-to-date selection by performing a second query to the database and getting the entire data set as a whole. However, with large amounts of data, this requires significant resources and time. Therefore, it is more expedient to check for changes in the database and additionally receive small samples only as part of the data that has been changed.

For this purpose, a so-called incremental processing is possible. Incremental data processing implies the use of not all records of the data sets in the process of updating the map in the database, but only those that have undergone any changes since the previous operation of updating the cartographic representation in GIS Panorama using information from the database.

For implementing the possibility of incremental processing, into a database  a service table - a change log must be added in which there are recorded all facts of changes in the database tables to be  mapped. Registration of the facts of changes is performed by special triggers of these tables.

The fact of creation, change or removal of each record is  entered into the change log with an indicating a code of operation and time of its execution

Each record is reflected in the changelog only once. That is, if a record was changed and then deleted, the fact of the change in the log will be replaced by the later fact of deletion.

Thus, the maximum number of records in the change log will be equal to the total number of all records (including deleted ones) of all tables that register their changes in this log.

Each copy of GIS Panorama performs registration of the unique session identifier in the database. Due to this, the database records that were added, changed or deleted from a separate instance of GIS Panorama are marked in a certain way in the change log. Due to this, each instance of GIS Panorama can recognize «its own» changes. At the subsequent transfer of changes from the database onto the map, such records are skipped. It allows to avoid reediting of objects on a map.

For creation of the log table and database objects necessary for its conducting, the installation includes SQL-script for each of supported DBMS. Scripts are located in the setupdb subdirectory in the root directory of the program.

By default, scripts create log tables with predefined names and add triggers to track changes to all tables that have fields for storing spatial data.

If necessary, the script can be changed in any way, but with the obligatory preservation of the logging logic and its structure (field names and their types). That is, it is allowed to change the script to use logging not for all, but only for certain tables with spatial data, changing the key field instead of the primary key field - onto any field whose value can uniquely identify a record in the table.

Predefined log table names:

­-   for PostgreSQL DBMS – pgis2map_dbchanges_log (created in the public schema);

­-   for Oracle DBMS – ora2map_dbchanges_log (created in the user's table space, for which a set of tables with spatial data is created, changes in which must be tracked).

 

The structure of the database change log table:

 

 

Field name

Data type

Description

schemaname

name

schema name (username - for Oracle)

tablename

name

table name

idrecord

integer*

unique identifier of the record in the table

changestype

integer

type of record editing (1-INSERT, 2-UPDATE, 3-DELETE)

userid

text

operator (current_user())

stamp

timestamp

time stamp of the fact of record's changes

sessionident

text

identifier of session (or application)

 

Performing updates using the change log imposes the following restrictions on the tables to be reflected on the map:

  1. Data sets (database tables) should not have fields with the following names:

­-   schemaname;

-   tablename;

-   changestype;

-   userid;

-   stamp;

-   sessionident.

  1. The primary keys of all tables registering their changes in the log must have the same data type that matches the type of the idrecord field of the change log, that is, have the integer type (4-byte integer), which is due to the specifics of identifiers in the format of maps of the GIS «Panorama» family.

 

When a DBM map is opened for the first time, the program generates the entire cartographic representation of this database, that is, all records of the data set specified in the settings in the DBM file are applied to the map. Further, if there is a table of the log of changes in the database, data changes are tracked and the cartographic representation is updated only regarding the changed records.