Geographic Information Systems Asked on February 14, 2021
I have an ST_GEOMETRY view with 50,000 rows (in an Oracle 18c/10.7.1 geodatabase). The view references a WORKORDER table in an external system (via a dblink/Oracle 12c).
Problem:
The view is too slow to be used in ArcGIS; there is a 5-10 second refresh-time due to the dblink.
As an alternative to using the view directly (in real-time), I want to synchronize the view’s resultset to a feature class (near-real-time).
I would sync any changes in the view to the feature class on a 1-minute schedule (scheduled task on the server).
changed_date
in the view is newer than the last_edited_date
in the feature class (or where the record doesn’t exist in the feature class).Question:
Is there a geoprocessing tool that I could use, on a schedule, to sync the data from the view to the feature class?
I think you're going to have to write a script using a combination of tools to accomplish what you want. The simplest approach is probably going to be to delete any rows that have been modified and then use the append tool to re-create them and add your new records all in one pass.
Answered by Anna Forrest on February 14, 2021
The ArcPy ends up being lengthy:
import arcpy,sys
arcpy.env.overwriteOutput = True
##************************
##Execute as: C:Progra~1ArcGISProbinPythonScriptspropy ViewUpdateFC.py
##************************
###***********Change these if necessary
LinkField = "WONUM" ##A common key field of unique identifiers
editDateFieldFC = "LAST_SYNC_DATE" ##The featureclass date field
editDateFieldVIEW = "CHANGEDATE" ##The View date field
####Data Paths
FAKE_WORKORDER_VIEW = "C:Users<<USER>>AppDataRoamingESRIDesktop10.6ArcCatalogLOCAL_SDE.sdeGISDATA.dbo.FAKE_WORKORDER_VIEW"
WORKORDER_COPY_FC = "C:Users<<USER>>AppDataRoamingESRIDesktop10.6ArcCatalogLOCAL_SDE.sdeGISDATA.dbo.WORKORDER_COPY_FC"
###***********
def buildDictionary(inputFC, attributes, primaryKey):
# Build a dictionary of the primary key and its fields
d = {}
cur = arcpy.SearchCursor(inputFC)
for row in cur:
compareList = []
for attribute in attributes:
# print "attribute: " + attribute
# print "ROWVAL: " + row.getValue(attribute)
compareList.append(row.getValue(attribute))
d[row.getValue(primaryKey)] = compareList
del cur
return d
def DeleteFeatures(lstRecordsToDelete):
strFeatures = "','".join(lstRecordsToDelete)
removestring = LinkField + " IN ('" + strFeatures + "')"
with arcpy.da.UpdateCursor(WORKORDER_COPY_FC, [LinkField], removestring) as cursor:
for row in cursor:
cursor.deleteRow()
def Update():
###Build lists of attribute columns to compare
attributesToCompare1 = [LinkField]
attributesToCompare1.append(editDateFieldFC)
attributesToCompare2 = [LinkField]
attributesToCompare2.append(editDateFieldVIEW)
#Get the list of unique IDs of features with differences
d1 = buildDictionary(WORKORDER_COPY_FC, attributesToCompare1, LinkField)
d2 = buildDictionary(FAKE_WORKORDER_VIEW, attributesToCompare2, LinkField)
diffList = [str(key) for key in set(d1.keys() | d2.keys()) if d1.get(key) != d2.get(key)]
###IF no difference, end the script
if(len(diffList) == 0):
sys.exit("Layers are in sync")
### Create a where clause of the features that are different to use with the cursors
strFeatures = "','".join(diffList)
DeltaOIDS = LinkField + " IN ('" + strFeatures + "')"
### Make a dictionary of the the differing VIEW features AND their edit dates
dictView = {}
with arcpy.da.SearchCursor(FAKE_WORKORDER_VIEW, [LinkField,editDateFieldVIEW],DeltaOIDS) as ViewCursor:
for row in ViewCursor:
LinkFieldValue = str(row[0])
changed_date = str(row[1])
dictView[LinkFieldValue] = changed_date
### Make a dictionary of the the differing faetureclass features AND their edit dates
dictFC = {}
with arcpy.da.SearchCursor(WORKORDER_COPY_FC, [LinkField,editDateFieldFC],DeltaOIDS) as FCCursor:
for row in FCCursor:
LinkFieldValue = str(row[0])
last_edited_date = str(row[1])
dictFC[LinkFieldValue] = last_edited_date
###Cycle through the Featureclass features, get compare dates with the same feature in the VIEW.
###If the View is later or the feature is not in the featureclass, then add the feature to the list of features to update
lstUpdateRecords = []
for key, value in dictView.items():
LinkFieldValue = key
Viewchanged_date = value
if key in dictFC.keys():
FClast_edited_date = dictFC[key]
# print("Viewchanged_date: " + str(Viewchanged_date) + " -- FClast_edited_date: " + str(FClast_edited_date))
if(Viewchanged_date > FClast_edited_date):
# print ("Date diff: " + str(Viewchanged_date))
lstUpdateRecords.append(key)
else:
# print("Key: " + key + " not found")
lstUpdateRecords.append(key)
# print("lstUpdateRecords: " + str(lstUpdateRecords))
###Delete features from the featureclass that are to be updated
DeleteFeatures(lstUpdateRecords)
strFeatures = "','".join(lstUpdateRecords)
strDeltupdate = LinkField + " IN ('" + strFeatures + "')"
print("strDeltupdate: " + str(strDeltupdate))
###Append the features from the VIEW to the featureclass, replacing the one above that were deprecated, then deleted.
###Note that NO_TEST is used because all columns are the same except for the date fields, and a field make has been used for those.
print("Append")
arcpy.Append_management(FAKE_WORKORDER_VIEW, WORKORDER_COPY_FC, "NO_TEST", "WONUM "WONUM" true true false 25 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,WONUM,-1,-1;CLASSIFICATION "CLASSIFICATION" true true false 635 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,CLASSIFICATION,-1,-1;STATUS "STATUS" true true false 40 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,STATUS,-1,-1;DIVISION "DIVISION" true true false 62 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,DIVISION,-1,-1;ASSETNUM "ASSETNUM" true true false 50 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,ASSETNUM,-1,-1;LOCATION "LOCATION" true true false 30 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,LOCATION,-1,-1;OWNERGROUP "OWNERGROUP" true true false 30 Text 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,OWNERGROUP,-1,-1;LONGITUDEX "LONGITUDEX" true true false 8 Double 8 38 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,LONGITUDEX,-1,-1;LATITUDEY "LATITUDEY" true true false 8 Double 8 38 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,LATITUDEY,-1,-1;LAST_SYNC_DATE "LAST_SYNC_DATE" true true false 8 Date 0 0 ,First,#,lyrFAKE_WORKORDER_VIEW_Layer,CHANGEDATE,-1,-1", "",strDeltupdate)
Update(WORKORDER_COPY_FC,FAKE_WORKORDER_VIEW)
Answered by User1973 on February 14, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP