Identifying changes using Geodatabase Archive Tables

JohnFannon — Fri, 05 Jun 2015 17:14:16 GMT

I have a requirement to identify changes to a versioned feature class since a given date. The feature class has archiving enabled and I have been looking at the documentation regarding this and have come up with the following queries which should enable detection of the deleted, modified and new features:

--DELETED

select OBJECTID, GDB_FROM_DATE, GDB_TO_DATE

from MYFC_H

where

GDB_TO_DATE <> '9999-12-31' AND

GDB_TO_DATE > '2015-06-05 00:00' AND

OBJECTID not in (select OBJECTID from MYFC_H where GDB_TO_DATE = '9999-12-31')

--MODIFIED

select OBJECTID, GDB_FROM_DATE, GDB_TO_DATE

from MYFC_H

where

GDB_TO_DATE <> '9999-12-31' AND

GDB_TO_DATE > '2015-06-05 00:00' AND

OBJECTID in (select OBJECTID from MYFC_H where GDB_TO_DATE = '9999-12-31')

--NEW

select OBJECTID, GDB_FROM_DATE, GDB_TO_DATE

from MYFC_H

where

GDB_TO_DATE = '9999-12-31' AND

GDB_FROM_DATE > '2015-06-05 00:00' AND

OBJECTID not in (select OBJECTID from MYFC_H where GDB_TO_DATE <> '9999-12-31')

This appears to be working OK, based on some limited testing, but I was just wondering if anyone can verify the above or suggest a better way to do this using SQL (SQL Server) as I'm not sure the above is the most efficient SQL to achieve this.

Any input most welcome.

Regards

John

Re: Identifying changes using Geodatabase Archive Tables

MichaelRobb — Wed, 17 Jun 2015 20:13:20 GMT

Enabling Archiving is indeed the correct way to access data on a time stamp level.

Right click on the Database Connection DEFAULT is Current, or you can give it a date.. then the connection takes place... all your featureclasses etc are actually being viewed by a View on the back end. e.g. table name OWNER would have a view OWNER_EVW.

Editing the view for example OWNER_EVW, runs the TRIGGERS to modify the 'raw' tables. 9999 dates are current and actual dates are of those dates.

I use this technique for identifying changes of parcels within a time period. You can then also implement the time scaling in maps...etc etc.

Downfall to this is of course, no data is ever deleted. DB size grows.

topic Identifying changes using Geodatabase Archive Tables in ArcGIS Enterprise Questions

Identifying changes using Geodatabase Archive Tables

Re: Identifying changes using Geodatabase Archive Tables