topic Re: Is it possible to query Associations for a given asset in UN? in ArcGIS Utility Network Questions

Is it possible to query Associations for a given asset in UN?

VishApte — Wed, 17 Feb 2021 01:11:00 GMT

Hi,

I have a query from an electric dist. client about being able to extract associations (connectivity, container, structural attachment) using SQL queries. The client is in the process of deploying UN with SQL Server as a geodatabase. Client wants to query connectivity and containment relationship directly in the database for reporting, DMS integration etc. Their legacy GIS supported it. e.g. for a given transformer, find what High/Primary side protection device is.

My initial feel is this is not possible with UN as this information is stored as Blobs. There are few UN_X_* tables, but I guess it is not something that can be exposed or relied upon.

Is there any other way? I cannot even find REST API to do something like this.

Re: Is it possible to query Associations for a given asset in UN?

MikeMillerGIS — Wed, 17 Feb 2021 10:56:47 GMT

You can access the association information via rest. The tables are part of the service, just hidden from the rest pages.

If you look as the json of the UN layer - https://sampleserver7.arcgisonline.com/arcgis/rest/services/UtilityNetwork/NapervilleElectric/FeatureServer/921?f=pjson

You will see a system layers section:

"systemLayers": {
  "dirtyAreasLayerId": 3,
  "lineErrorsLayerId": 1,
  "pointErrorsLayerId": 0,
  "polygonErrorsLayerId": 2,
  "associationsTableId": 500001,
  "subnetworksTableId": 500002,
  "rulesTableId": 500003,
  "diagramEdgeLayerId": 500005,
  "diagramJunctionLayerId": 500006,
  "diagramContainerLayerId": 500007,
  "temporaryDiagramEdgeLayerId": 500008,
  "temporaryDiagramJunctionLayerId": 500009,
  "temporaryDiagramContainerLayerId": 500010
 },

Use the ID to get access to the association table, such as

https://sampleserver7.arcgisonline.com/arcgis/rest/services/UtilityNetwork/NapervilleElectric/FeatureServer/500001

Which you can run a query against to get back info such as:

objectid: 1
fromnetworksourceid: 8
fromglobalid: {4D0259B3-9323-4A31-979D-F81499B5A278}
fromterminalid: null
tonetworksourceid: 6
toglobalid: {B6874CA0-3584-4ED6-8A17-0FAFBA3C3891}
toterminalid: null
associationtype: 2
iscontentvisible: 0
creationdate: 1565741744000
creator: UNADMIN
lastupdate: 1565741744000
updatedby: UNADMIN
globalid: {1B5E5C18-8493-4D42-B67A-2F3D711EA356}

objectid: 2
fromnetworksourceid: 8
fromglobalid: {4D0259B3-9323-4A31-979D-F81499B5A278}
fromterminalid: null
tonetworksourceid: 6
toglobalid: {08421426-33C5-4DE2-8FBC-134295D81AD6}
toterminalid: null
associationtype: 2
iscontentvisible: 0
creationdate: 1565741745000
creator: UNADMIN
lastupdate: 1565741745000
updatedby: UNADMIN
globalid: {8B7D620A-B978-48DC-9B44-148925AF4E68}

Re: Is it possible to query Associations for a given asset in UN?

VishApte — Wed, 17 Feb 2021 11:41:20 GMT

Thanks @MikeMillerGIS . Bit more involved than SQL queries and joins but I think that will do the trick. Just a quick query, I guess fromnetworksourceid and tonetworksourceid translate to Junction/Device/Line/Assembly layer that can be queried using from/to globalid for additional details. Where will the mapping be for fromnetworksourceid and tonetworksourceid to the Junction/Device/Line/Assembly layer ids?

Re: Is it possible to query Associations for a given asset in UN?

MikeMillerGIS — Wed, 17 Feb 2021 12:07:04 GMT

Run QueryDataElements

https://sampleserver7.arcgisonline.com/arcgis/rest/services/UtilityNetwork/NapervilleElectric/FeatureServer/queryDataElements

Review this section:

Re: Is it possible to query Associations for a given asset in UN?

JohnGoat — Tue, 11 Mar 2025 13:02:27 GMT

Hi @MikeMillerGIS

I want to perform these operations with spatial updates in the Oracle database.

When an attachment relationship is established, what kind of updates are made in which fields and tables in the database? Is it possible to create a manual relationship in these tables?

Re: Is it possible to query Associations for a given asset in UN?

MikeMillerGIS — Tue, 11 Mar 2025 13:50:29 GMT

Are you are asking can you detect this edit in the database? I do not think you can. Creating an attachment can change its association status, but if it already has that status, it will not change it. If you need to detect what features changed status, then the version api has a change component you can use.

Re: Is it possible to query Associations for a given asset in UN?

JohnGoat — Tue, 11 Mar 2025 14:25:40 GMT

@MikeMillerGIS

I tested the question I asked, I detected this relationship spatially via Oracle, and when I updated the relevant fields, I saw that it established the relationship via Pro.

In this way, it will be faster to establish associations using the power of the database. Will it cause any system obstacles somewhere?

Re: Is it possible to query Associations for a given asset in UN?

JohnGoat — Wed, 12 Mar 2025 07:05:25 GMT

@MikeMillerGIS

INSERT INTO UN_4372_ASSOCIATIONS (OBJECTID, GLOBALID, FROMGLOBALID, TOGLOBALID, FROMNETWORKSOURCEID, TONETWORKSOURCEID, ASSOCIATIONTYPE, ISCONTENTVISIBLE, STATUS) SELECT UN_4372_ASSOCIATIONS_SEQ.NEXTVAL, SYS_GUID(), s.globalid AS FROMGLOBALID, e.globalid AS TOGLOBALID, '6' AS FROMNETWORKSOURCEID, '9' AS TONETWORKSOURCEID, '2' AS ASSOCIATIONTYPE, '0' AS ISCONTENTVISIBLE, '0' AS STATUS FROM ElectricDevice e, StructureBoundary s WHERE SDO_WITHIN_DISTANCE( e.shape, s.shape, 'distance=0' ) = 'TRUE';

Re: Is it possible to query Associations for a given asset in UN?

RobertKrisher — Wed, 12 Mar 2025 13:50:42 GMT

Do not create data that affects the network using SQL. This will not properly set the status of features and create dirty areas. If you need a way to bulk load associations in a way that is respectful of the network topology, use SQL to identify that associations that need to be created (don't create duplicates!), turn them into a CSV file, and use the import associations tool to import them. This will properly maintain that status bits and dirty areas necessary for them to work properly.

Re: Is it possible to query Associations for a given asset in UN?

JohnGoat — Thu, 13 Mar 2025 07:45:23 GMT

I don't think there will be any problem in doing this operation when the topology is disabled. Is that correct? I will update after bulk data conversions.

Re: Is it possible to query Associations for a given asset in UN?

MikeMillerGIS — Thu, 13 Mar 2025 11:30:47 GMT

The features association status will not get updated.