How To: Query a Geodatabase Archive Class for a given Moment in Time Using SQL

Summary

An archive class can be accessed, using direct SQL, to retrieve attributes or spatial geometries, by setting the correct predicate filter using the gdb_from_date and gdb_to_date attributes.

Procedure

A geodatabase archive class maintains every object for an archive enabled objectclass or feature class, and each occurrence of every modified object.

The archive class uses two additional attributes for maintaining a time stamp when an object was inserted, updated, or deleted. The default DATE attribute gdb_from_date records the time stamp when the object is initially created or was last updated. The default DATE attribute gdb_to_date records the time stamp when the object is retired. If the current value for the gdb_to_date is infinity, then the object remains active (the object's current representation).

If the objective is to query the archive class for a given moment, the following example, in Oracle using SQL*Plus, demonstrates how to obtain all the objects that are current as of February 25, 2008 with a spatial filter.

Code:
SQL> SELECT objectid, segment_id
  2  FROM sewers_h
  3  WHERE sde.st_envintersects (shape,10,20,20,40) = 1
  4  AND gdb_from_date <= TO_DATE('FEB-25-2008','MON-DD-YYYY')
  5  AND gdb_to_date > TO_DATE('FEB-25-2008','MON-DD-YYYY');

The result set from the query contains only those rows that were current as of February 25, 2008.

If the objective is to obtain a result set that is equivalent to the DEFAULT version, first obtain the time stamp for the DEFAULT historical marker from the SDE schema's gdb_historicalmarkers table.

Code:
 SQL> SELECT hm_timestamp
   2  FROM sde.gdb_historicalmarkers
   3  WHERE hm_name = ‘DEFAULT’;

HM_TIMESTAMP
------------
25-FEB-08

Article ID:000009911

Software:

ArcMap 9 x

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Query a geodatabase archive class for a given moment in time using SQL

Summary

Procedure

Discover more on this topic