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
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App