Setting up layer for offline sync whose data gets truncated daily from another database

[KL_MSB]

I currently have a non-editable polygon layer whose data runs a nightly SQL script, pulling data from our CAMA system onto our enterprise database connection. The current workflow is to run a nightly job that TRUNCATE the table and then INSERTs the data from CAMA into our database. 

However, I am hoping to add the capability to take the data offline on field maps. When taking the layer offline, on the database , new fields are added to the table:

• GDB_ARCHIVE_OID
• GlobalID
• GDB_FROM_DATE
• GDB_TO_DATE
• GDB_ROW_CREATE_REPLICA_GUID
• GDB_ROW_EXPIRE_REPLICA_GUID

With this, the current SQL Script syncing with our CAMA system becomes detrimental to maintaining offline capability on field maps. Because I am unsure how the offline data collection process works on the database side of things. I am wondering what would be best practice on how to approach creating a new SQL Script. 

[jcarlson]

Another route would be to edit your polygons in place. We do something like that for our parcels, CAMA, etc., and it looks like this:

1. SQL queries the source table
2. Python API queries the polygon layer
3. Compare the two tables
   1. Result of "compare" function can identify adds, updates, and deletes
   2. All other rows are unchanged
4. Use Python API edit_features to send adds, updates, and deletes to the polygon layer

It would take some tweaking to fit your particular situation, but we've found it to be a good approach. Our CAMA layer has something like 48,000 points in it, but only a few dozen might be edited on a given day. By identifying and selectively editing those few records, the bulk of the data can stay put, and we don't need to truncate anything.

[KL_MSB]

I could understand how that would work when using SQL to update between two tables. However, I am unsure how it would work while trying to maintain Offline Editing Capabilities. While running with a test dataset, I discovered that deleting the data does not work as GDB_ARCHIVE_OID could not be NULL. This made me more aware of trying to maintain the integrity of data created by enabling Archiving and Replica Tracking.

I was curious with this offline workflow on field maps what considerations must be made to the SQL script.

Thanks,

- Kevin Li

Matanuska Susitna Borough GIS

[jcarlson]

Given that it would edit your features in place, it would work quite well. Some of our layers we update this way are used in an offline capacity, and there is no issue. When the source data has adds or deletes, those are made, everything else is simply edited in place or left alone, so any internal fields used to link features to the offline copy would still be in place.

[KL_MSB]

Could I get some further detail as to what the "Compare Function" is? A geoprocessing tool, SQL statement or a python function?

[jcarlson]

It's a method on the GeoAccessor, Esri's version of a spatial dataframe.

https://developers.arcgis.com/python/api-reference/arcgis.features.toc.html#geoaccessor

You can also use pandas.DataFrame.compare() to compare two dataframes, but it operates differently.