topic Setting up layer for offline sync whose data gets truncated daily from another database in ArcGIS Field Maps Questions

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

KL_MSB — Thu, 14 Mar 2024 21:07:06 GMT

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.

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

jcarlson — Thu, 14 Mar 2024 18:42:27 GMT

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:

SQL queries the source table
Python API queries the polygon layer
Compare the two tables
1. Result of "compare" function can identify adds, updates, and deletes
2. All other rows are unchanged
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.

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

KL_MSB — Thu, 14 Mar 2024 20:00:57 GMT

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

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

jcarlson — Thu, 14 Mar 2024 20:03:46 GMT

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.

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

KL_MSB — Fri, 15 Mar 2024 00:44:22 GMT

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

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

jcarlson — Fri, 15 Mar 2024 14:54:13 GMT

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.