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

884
3
Jump to solution
02-16-2021 05:11 PM
VishApte
Esri Contributor

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.

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
MikeMillerGIS
Esri Regular Contributor

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}

 

View solution in original post

0 Kudos
3 Replies
MikeMillerGIS
Esri Regular Contributor

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}

 

0 Kudos
VishApte
Esri Contributor

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?

0 Kudos
MikeMillerGIS
Esri Regular Contributor