The many products we offer

Change History Reading the change history for HIS Registry of Geography.

Email | Print | More

The registry contains just one change history table.

ROG_PoliticalChangeHistory

All changes to ROG_Political reported in ROG_PoliticalChangeHistory. This table is cumulative, listing all changes to successive versions of the registry. The table has the following four columns:

Code char(16) The code that is affected by the change reported in this record.
 Type char(1)

A one-letter code indicating the type of change: There are four possible values:

C Created. The geopolitical region code is newly added to the HIS standard.
E   Extended. Included in definition for conformance with HIS registry standards. Our intent is that this Type will never be used for geographic data, since by intention codes will always be retired if the land area represented changes.
R Retired. The geopolitical region code has been retired and should no longer be used in a database of current political entities. Retired codes may potentially be used for coding historical data.
U Updated. There has been no change to the code or its meaning, but other information in the code table entry or cross-reference information has changed.
 Date  char(10)  The date the change was released in a new version of the registry. Dates are expressed as 8 digits with hyphens to separate the parts of the date, e.g. YYYY-MM-DD.
 Description  varchar(255)  Describes the change. In the case of R changes, it also describes what a user should do to fix existing data that uses the now retired code.

 

Note that there is not a change type for the case of narrowing the meaning of a code, such as when the region denoted by one code is split into two regions. In such a case, the original code is retired, and two new codes are added. In this way, the user of the code set is assured that once a code has been used to tag an item of data, it will continue to be the right code to use for as long as the code remains an active member of the code set.

The SQL statement for creating the change history table is as follows:

CREATE TABLE ROG_PoliticalChangeHistory (
Code varchar(16) NOT NULL,
Type char(1) NOT NULL,
Date char(10) NOT NULL,
Description varchar(255) )

The change history table holds the cumulative list of all changes that have every been made to the registry. Thus it may be queried to learn the complete history of a given code, or to learn all the changes that have been made since a given date. For instance, the following SQL query would be used to find out what changes have occurred since the beginning of 2002:

SELECT * FROM ROG_PoliticalChangeHistory WHERE Date >=2002-01-01

 

For a site that has used ROG_Political codes in its own database, an important use of the change history table is to discover codes used in its data that are now obsolete and thus need to be changed. These will be only the codes that have been retired. Thus a full list of all data records needing to be changed can be found by doing a JOIN on the change history table. For instance, if the column named code in MyTable holds an ROG_Political code, then the following SQL statement will select all records that need to be changed due to changes to the code set since the beginning of 2002:

SELECT * FROM MyTable as M
JOIN ROG_PoliticalChangeHistory as C ON M.code=C.Code
WHERE C.Type='R' AND C.Date >=2002-01-01

 

Note that the Description field of the joined result set will describe what needs to be done to bring the offending code up-to-date.