The workflow is simple:
- Create a view to filter the data in the database
- Add the view to catalog in ArcGIS Pro
- Extract the data to a staging table
- Add the table to the map (note: why cant I work on the data in the catalog without adding to the map?)
- Perform work – lots of GIS functions add and remove columns, perform field calculations. Note: if you haven’t tried the field calculation functionality in QGIS you should, its really good.
- Finally, join the the data back to the source and use a sql update script to update the source data.
So whats the problem?
As I am working though the process of using this table in the ESRI platform, it drops the identity field, and leaves me without a key to join back to my source data.
ESRI identifies that there is a column that is an identity with unique values and guaranteed not null. It sets this column to a type of: ObjectID. Then as we all know, OBJECTID fields are now for esri use only and no longer maintained throughout the workflow.
So what about adding my own ObjectID field like in this post: Add and object ID to your view? Turns out this still doesnt work, as the software still sees the other id field.
This means that Pro is smart enough to look for an identity column but dumb enough to screw it up. The best thing to do in this case is create another column in the view that is a duplicate of the key with a different name. Then you can join that data on that field and still have a key for your records.