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:

[JohnGoat]

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?

[MikeMillerGIS]

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.

[JohnGoat]

@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?

[JohnGoat]

@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';

[RobertKrisher]

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.

[JohnGoat]

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.