topic Re: Auto-Updating features in Data Management Questions

Auto-Updating features

BrianBulla — Thu, 24 Mar 2022 19:43:27 GMT

Hi,

What I need to do is figure out how to update a field in any feature class each time an edit is made to it. This is to support our asset management system, Maximo.

The field is called MXCREATIONSTATE and will exist on many different feature classes in our corporate SDE. Some users use ArcMap, some use ArcGIS Pro, some use FME to make updates, some use web editing tools. Going forward other front-end apps could also make edits.

What is the best way to handle this sort of situation? The out-of-the-box Attribute Rules/Attribute Assistant don't really seem to cover all the bases here.

Basically, anytime an edit is made (or new feature is created) MXCREATIONSTATE must be updated to 1.

Any ideas are appreciated.

Thanks,

Re: Auto-Updating features

ABishop — Thu, 24 Mar 2022 20:06:06 GMT

Hello Brian,

What version of ArcGIS Desktop are you using? ArcMap? ArcGIS Pro?

Re: Auto-Updating features

jcarlson — Thu, 24 Mar 2022 20:07:39 GMT

This is one of the "greatest hits" here on the Community. Unfortunately, there's nothing "off the shelf" in the Esri-verse that accomplishes what you're looking to do, if Attribute Rules doesn't quite fit the bill. Why exactly doesn't an Attribute Rule work here, though?

Alternatively, you may be able to use a SQL trigger in your SDE, but that's a bit out of my wheelhouse.

And also, you could just create a bare-bones Python script to run a field calculation at regular intervals to get as close to "real-time" updates as possible.

Re: Auto-Updating features

ABishop — Thu, 24 Mar 2022 20:44:46 GMT

Hello Brian,

I was thinking maybe a relationship class?

See info here: https://pro.arcgis.com/en/pro-app/2.8/tool-reference/data-management/create-relationship-class.htm

Re: Auto-Updating features

JohannesLindner — Fri, 25 Mar 2022 07:12:29 GMT

Why exactly doesn't an Attribute Rule work here, though?

Brian first asked this question in the ArcGIS Pro SDK community, where I told him that. My reason for that was that some of the users work in ArcMap, which doesn't like Attribute Rules. I don't know about FME, webservices and "other front-end apps" probably depends.

If there was a way to get Attribute Rules to work reliably in all those systems, they would of cource be the goto choice for something this simple.

// Calculation Attribute Rule // field: MXCREATIONSTATE // triggers: insert, update if($editcontext.editType == "INSERT") { return 1 } if($feature.MXCREATIONSTATE != $originalfeature.MXCREATIONSTATE) { return $feature.MXCREATIONSTATE } return 1

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 12:13:55 GMT

Hi Johannes,

Because as you seem to know already, not all systems work with Attribute Rules. Even with between ArcMap and ArcGIS Pro, I would need implement two different systems to accomplish the same thing. Then add in all the ArcGIS Online products. If I use QuickCollect to capture features then I also have to accommodate for this, as well any other web-editing product. It would be a perpetual cycle of constantly having to make adjustments to any bit of code/app anyone creates going forward in order to accomodate for this MXCREATIONSTATE field.

IMO the best solution is something that works on the back-end and is invisible to the front-end users/developers.

SQL Triggers might just be it, but that is up to the DBAs to decide.

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 12:25:10 GMT

Hi Amanda,

I don't quite see how a relationship class would help. Can you explain??

Re: Auto-Updating features

ABishop — Fri, 25 Mar 2022 12:35:58 GMT

Good Morning Brian,

The idea is to take advantage of the parent/child relationship that is possible with a relationship class. Depending on the cardinality of the relationship, you may have to assess a different approach, but the relationship class description for the composite relationship seems appropriate.

A composite relationship is one in which the lifetime of one object controls the lifetime of its related objects. For example, power poles support transformers, and transformers are mounted on poles. Once a pole is deleted, a delete message is propagated to its related transformers, which are deleted from the transformers' feature class. Composite relationships are always one-to-many.

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 12:47:52 GMT

Thanks Amanda. I'll keep that in mind.

So far I've been messing with the Attribute Rules/Assistant as well as creating custom Add-Ins in Visual Studio to listen for editing events. They both work OK, but are not 100% fool-proof. There's always certain situations where they don't work.

I'm hoping our DBA's can sort out something on the back-end.

Thanks for your input.

Re: Auto-Updating features

jcarlson — Fri, 25 Mar 2022 13:37:32 GMT

Any thoughts as to the Python-based approach?

If your service has editor tracking enabled, and thus a last_edited_date field, this kind of thing would be incredibly simple. You'd need to keep track of when the script was last successfully run, perhaps with a config file. I have a script that does this sort of thing, though it only runs daily. Still, the whole thing only takes a couple seconds to run, so this could be firing many times a minute with room to spare.

from arcgis import GIS from configparser import ConfigParser from datetime import datetime # portal connection gis = GIS('your portal url', 'user', 'pass') # get layer fl = gis.content.get('itemid of service').layers[0] # or whatever layer index applies # read config file config = ConfigParser() config.read('config.ini') # get last run timestamp from config last_run = config.get('last_run', 'time') # calculate field, using timestamp to selectively identify new adds/edits fl.calculate( where = f"last_edited_date >= timestamp '{last_run}'", calc_expression = {'field': 'MXCREATIONSTATE', 'value': 1} ) # set last run timestamp to current date config.set('last_run', 'time', datetime.now().strftime('%Y-%m-%d %H:%M:%S')) with open('config.ini', 'w') as c: config.write(c)

Note that the method you use to query a layer based on a timestamp may differ depending on your DB. We're on postgreSQL, so timestamp 'YYYY-MM-DD HH:MM:SS' is what we use.

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 13:47:35 GMT

Hi Josh,

I'm going to plead ignorance on this one. I have no clue how web-editing works, so not really sure what to think. I am just a front-end user and do all my work in ArcMap/ArcGIS Pro with some development work customizing the desktop with Add-Ins. But I will take what you have suggested to our IT department who manage all of our servers/GIS and see what they think.

We don't really do a lot of web-editing right now, but have been experimenting with Geocortex GO. Would what you are suggesting work with any web-editing app??

Thanks for your input!

Re: Auto-Updating features

jcarlson — Fri, 25 Mar 2022 13:54:59 GMT

Assuming your layer is a published service in your GIS portal, it just runs against the service directly, so apps and web editing don't really come into play here. Since the ArcGIS Python API only requires authentication with your portal, it can be run nearly anywhere, even on a headless Linux machine.

Re: Auto-Updating features

Bud — Mon, 28 Mar 2022 13:43:17 GMT

Ugh, Maximo Spatial. You poor soul.

We took a deep dive with our Maximo/GIS integrations a couple of years ago. We abandoned most of the IBM mechanisms, including the MXCREATIONSTATE field thing.

I honestly don’t know where to start. There are so many things I want to warn you about.

@jcarlson is on the right track with the last_edited_date thing, as an alternative to MXCREATIONSTATE.
We did it by using database views on our feature classes to generate a sync_needed field, in conjunction with a database link to the Maximo database:

select
    ...,
    case  
        when fc.last_edited_date > maximo_asset.lastsyncdate or maximo_asset.lastsyncdate is null then 1
        else 0
    end as sync_needed
from
    gis_fc fc
left join
    custom_maximo_owner.asset@gistomax maximo_asset --this is a dblink in the Maximo database that lets GIS select from the Maximo asset table
    on fc.asset_id = maximo_asset.assetnum

--note: lastsyncdate is a custom field we created in the Maximo asset table. It's easy to populate that field with the Maximo system time using the &SYSDATE& default value: https://i.stack.imgur.com/2cNyk.png

One additional benefit to using a db view in GIS is that you can translate/format your data before it gets to Maximo. That's potentially easier than trying to do the translation in Maximo's JSON Mapping application.

Re: Auto-Updating features

Bud — Mon, 28 Mar 2022 13:45:22 GMT

Various other things to keep in mind:

Regarding Maximo Spatial's MXCREATIONSTATE / sync mechanism: Bear in mind that when Maximo syncs a record, it will then update the MXCREATIONSTATE field in GIS to "successfully synced" or whatever the actual numeric code is (I can't remember). The problem with that is: I believe the edit will trigger editor tracking, since the edit is done through the ArcGIS REST API. So, if you were using that editor tracking field for other stuff in GIS, then it means that Maximo has compromised your editor tracking info. Your editor tracking will indicate the last time Maximo edited the field, not necessarily the last time a user edited the field.
Maximo Spatial's cron task for syncing GIS records to Maximo sometimes fails. There are scenarios where it runs, but nothing happens -- no records are synced. So you don't want to use logic like the following: "If the last Maximo cron run date is less than the GIS last_edited_date, then sync the record". The last cron run date in Maximo will be misleading in that case...records likely failed to sync, and wouldn't be picked up in the next sync.
If I remember correctly, GIS's editor tracking fields need to be set to UTC time -- for ArcGIS-specific reasons that I can't remember off hand. However, the time zone in Maximo will likely be different. So comparing date fields between the two systems/time zones can get messy.
With that said, we put some thought into the logic at play, and didn't think the time zone thing would end up being a problem for us -- for our weekly asset syncs.

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 15:14:59 GMT

Hi Bud,

Thanks for sharing. Yes, I represent an end user group (public Works) where many of our assets we maintain through GIS need to be used within Maximo. I do not think we are implementing the mapping functionality of Maximo, but we still need to maintain the Maximo field within our GIS. The following fields are added to our data: MXCREATIONSTATE, MXSITEID and MSASSETNUM.

From what I remember, we update MXCREATIONSTATE to "1" for each edit/new feature, and then once Maximo syncs itself up, Maximo sets them all back to "0".

With your system, how are you dealing with system deletes out of the GIS. How is Maximo recognizing deleted items??

Thanks for reminding me about user tracking. We do have Editor Tracking enabled right now, so something will need to be figured out for that. I think that is why I originally started creating Visual Studio Add-Ins to listen for edit/new feature events, so I could auto populate the editor fields as well as the Maximo fields, but it's not fool-proof.

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 15:14:08 GMT

The problem with that is: that edit will trigger editor tracking, since the edit is done through the ArcGIS REST API.

Did your group look into SQL Triggers to help with this? I'm not a DBA, but I would think if you could set up rules where the Triggers will not run when Maximo is doing it's reconciliation of records, and then enable them again once complete.

Re: Auto-Updating features

MatthewDriscoll — Fri, 25 Mar 2022 18:02:32 GMT

Have you tried managing the templates before publishing? Just set the template property of the MXCREATIONSTATE attribute to 1 before publishing. This is how I manage something very similar. I am not sure how it handles edits if they already have a different value, but this could probably be handled in the in the domains of the database and setting the properties of split/merge policy to default. You will also need to change the default value of feature class' field value too. Do all this before publishing as well.

https://pro.arcgis.com/en/pro-app/latest/help/editing/create-a-feature-template.htm

Edit: Here is an example. To not confuse you, the Template Properties I did in pro, and the others I used old school ArcCatalog.

Re: Auto-Updating features

Bud — Mon, 28 Mar 2022 13:56:38 GMT

Regarding triggers: No, we didn't investigate them. We have 20-40 GIS layers that we integrate to Maximo. Triggers on 40 layers seemed like it'd be a lot to manage. The db view option seemed simpler for our purposes. And as analysts, we could create and manage the views ourselves, whereas we'd need to get other staff involved with triggers, which would have taken extra time.

The db views (and dblink) option I mentioned worked well for us. And like I said, we needed to translate the GIS data for Maximo anyway, so db views worked good for that too.

Edit: I forgot to mention that we take the db views and export them to feature classes on a schedule. Reason: static feature classes are faster than views.

Regarding deletes: If I remember correctly, once an asset is created (or integrated) in Maximo, Maximo won't allow us to delete it. We can only "decommission" it. So our hands were tied -- we had to come up with a "don't delete GIS assets, only flag them as decommissioned" business rule for our GIS technicians. We'll likely enforce that rule using ArcGIS Pro attribute rules. We've already done it for a couple of FCs. But it sounds like attribute rules aren't an option for you.
Is your GIS data versioned? If so, then it isn't possible to allow edits, and at the same time, specifically prevent deletes. At least not through the ArcMap/ArcGIS Pro privilege settings for a given feature class.

With that said, I did come up with a proof-of-concept db trigger that seemed to work with versioned feature classes. But my testing was limited (I didn't end up needing the trigger; we'll probably use attribute rules instead). Prevent deletes in a versioned table at the database level

Re: Auto-Updating features

Bud — Mon, 28 Mar 2022 14:25:07 GMT

If I've understood correctly (it's possible that I haven't), I think you're suggesting that all rows in all FCs be flagged as needing to be synced to Maximo. We wondered about that too...just sync everything...once a week or once a month. That would be nice and simple.

The problem with that is that the Maximo Integration Framework (MIF) is relatively slow. A municipality like @BrianBulla's (or ours) has hundreds of thousands, possibly millions, of asset records. Unfortunately, it would take Maximo several days or even weeks to do a sync like that. So it wouldn't be feasible.

The challenge is: How to flag new or updated GIS features -- so that only those changes, aka "the delta" are synced to Maximo. IBM's mechanisms for this sort of thing are naive/incorrect. A lot of organizations try to find alternatives. Companies like ActiveG and GeoNexus have made entire products for it.

Re: Auto-Updating features

BrianBulla — Fri, 25 Mar 2022 19:38:54 GMT

Thanks Bud,

This is giving me lots to think about, although being a front-end user I'm not sure how much say in any of this I really have. I just know that we don't want to make huge changes to our current work processes in order to accommodate Maximo.

Regarding "Deletes", how does your GIS function if you don't remove features? For example, if you do a road rehab contract and completely replace the storm sewer, what happens with the original features?

For us, we need to remove features immediately. For instance, if a hydrant gets replaced, we delete it and add a new one. Our GIS feeds into our inspection programs, asset management, mapping, web viewers, etc. I can't have our depot guys going out looking to do inspections on hydrants that aren't really there.

It just seems that Maximo does not have a way to deal with the deleted GIS features, and I guess they just stay in Maximo forever??