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

[VishApte]

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.

[MikeMillerGIS · Accepted solution]

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/Featur...

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/Featur...

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}

[VishApte]

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?

[MikeMillerGIS]

Run QueryDataElements

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

Review this section: