Question

Help with loading a 1:m arcgis (esri) relationship

fallingdog
Rank
Badge

The problem: I am loading conductors from oracle spacial into a file geodatabase (ultimately the output will need to be SDE). The target datasets are all populated with existing data that I am trying to add to. The source conductors are currently split into a few target feature classes by fme; Primary overhead and primary underground for now. I will need to add the secondary overhead, secondary underground, and abandoned as well in the future. This part is working well for the primaries. But I am having a hard time dealing with the relationships. Each esri conductor feature class has a number of relationships.

Relationships for primary overhead.

In the end I will need to deal will most if not all of the relationships. But for now, I am I just working on the PiOH_ConductorInfo and its partner PiUG_ConductorInfo relationships. This is a one to many relationship and it does not have a relationship table.

The primary underground looks much the same but uses a deferent field in the conductor info table for the foreign key.

Each primary conductor can have 1, 2, or 3 phases. Each phase gets a conductor info row (1:M).

The sources data has all of this information bundled together in one table. So, for each row (feature) in the source table, I need to detect how many phases it has and then create 1, 2, or 3 related conductor info rows.

 

The Solution so far (not working):

So, I added the geodb_feature_has_relationship attribute and then a counter for the geodb_oid (they all have the name counter--I think this will ensure each integer is unique) to each branch of what will become the output features and conductor info row. I have not made an effort to deal with detecting the number of phases or creating the extra rows...

 

Any input would be great! Thanks!

 

On a side note: does it make sense to try and manage all of the relationships and other transformations in one workspace? I was thinking this would run the fastest in the end but harder to get right. Or, should I try and make a number of smaller workspaces for each operation? One to update the features and one for each relationship that is updated? Thanks!

7 replies

dellerbeck
Badge +1

Am I understanding this correctly, you want to build the relationships and create the "number of conductors" in the output database? Esri simple relationships are simply PK \\ FK values using your origin and destination fields.

There are a lot of ways to accomplish this, but if you are not working with 100s of thousands of rows, the feature merger is amazing. You can ask it to create a list and use the Origin and Destination columns as the requester and supplier. Once completed the parent will have all selected attributes of the child in list format.

This is just one of dozens of ways to accomplish this but I hope that it helps! As far as the multiple workspaces, that depends on how much work you want to put into it. I have had great luck with creating "steps" and then combining them all with a master workspace that uses a workspace caller in order of operation. This was of course before feature writers and the new caching options that Safe has given us. You can cache each step so you can test from that location.

Hope this gives you an idea!

RelatesToList.fmw

RelateTest.gdb.zip

fallingdog
Rank
Badge

@dellerbeck Thanks for the feedback and the example workspace and data! It is much appreciated.

 

"Am I understanding this correctly, you want to build the relationships and create the "number of conductors" in the output database? Esri simple relationships are simply PK \\ FK values using your origin and destination fields."

 

I don't think this is exactly right. I should have done a better job explaining the problem. I will provide some mocked up data/example (I am going to skip the feature mapping to primary overhead and underground part and just focus on the relationship part):

 

Say the input row looks something like this after it comes out of the attribute filter as primary overhead:

 

idvoltagephasematerial136kvABCaluminum

 

So, I want the writer to make one new row in the output primary overhead feature class. This will be the polyline of the conductor.

 

objectidvoltagephase8000036kvABC

 

Then in the conductor info table, there will be three new rows. One for each phase.

 

primary overhead objectidphasematerial80000Aaluminum80000Baluminum80000Caluminum

 

With "objectid" as the PK and "primary overhead objectid" as the FK for the relationship between the objects. Now, of course, if the input row had only two phases, e.g. AB, then only two rows would be made in the conductor info table.

I don't know what the PK object id will be ahead of time as this is managed by esri. But it looks like fme will support this "single pass" workflow. This is the part I am currently stuck on. I have thought about loading all of the data and then going back and updating the relationships in a separate operation. But that would add time to my process. We are trying to keep the load time at a minimum.

 

Thanks for the tip on the workspace caller. I did not know that existed!

M
Badge +2

@fallingdog We have a couple of tutorials on working with Esri Geodb relationships and the attributes you need to set on the origin & destination feature classes . Introduction to Working with Geodatabase Relationship Classes and Writing Geodatabase Relationship Classes

I've attached a workspace (based on @dellerbeck 's data) that illustrates what you need to do (using a GeoPackage instead of Oracle as the source). Workspace (2019): create1tomrelationship.fmwt

Just to re-emphasize: FME uses the geodb_oid to make the join between the origin & destination feature classes. So geodb_oid must have the same value for the corresponding records in A_ParentClass & B_ChildClass (or CondctorInfo and PriOHElectricLineSegment)

dellerbeck
Badge +1

Much clearer!!!! Sorry, I came from an electrical GIS background using FME. I LOVE ELECTRIC!! So let's have some fun here. As Mark (Insert true FME master here) has shown, working with 1:n relationships in Esri using FME is quite easy.

I attached a simple workspace that I think will really help you out. It looks at the phase of the Primary then uses a cloner \\ attribute manager to generate a "conductor unit". I know this is simple but it's just one example of how to do this. I know not all of the voltages or data will be exact, but hey it's still fun.

CloneUnits.fmw

fallingdog
Rank
Badge

Much clearer!!!! Sorry, I came from an electrical GIS background using FME. I LOVE ELECTRIC!! So let's have some fun here. As Mark (Insert true FME master here) has shown, working with 1:n relationships in Esri using FME is quite easy.

I attached a simple workspace that I think will really help you out. It looks at the phase of the Primary then uses a cloner \\ attribute manager to generate a "conductor unit". I know this is simple but it's just one example of how to do this. I know not all of the voltages or data will be exact, but hey it's still fun.

CloneUnits.fmw

@dellerbeck - Awesome! Nice to see other people with electrical GIS backgrounds here! And thanks for the workspace! I was easily able to apply it to mine to create the info rows. But the relationship is still killing me :(

fallingdog
Rank
Badge

@fallingdog We have a couple of tutorials on working with Esri Geodb relationships and the attributes you need to set on the origin & destination feature classes . Introduction to Working with Geodatabase Relationship Classes and Writing Geodatabase Relationship Classes

I've attached a workspace (based on @dellerbeck 's data) that illustrates what you need to do (using a GeoPackage instead of Oracle as the source). Workspace (2019): create1tomrelationship.fmwt

Just to re-emphasize: FME uses the geodb_oid to make the join between the origin & destination feature classes. So geodb_oid must have the same value for the corresponding records in A_ParentClass & B_ChildClass (or CondctorInfo and PriOHElectricLineSegment)

@markatsafe - thanks for the workspace! Sadly mine is still not working. I have added to mine based on @dellerbeck workspace and yours. The only difference I see between yours and mine, in the relationship area, is the uses of ParentID. Because I am splitting up one row into a feature class and a table, I only have one source ID. So, I have used counters for the geodb_oid's (I have seen this done in some of the tutorials). But I am getting:

An error occurred while attempting to store a row into the table 'ConductorInfo'. The error number from ArcObjects is: '-2147221230'. The error message from ArcObjects is: {}

 

 

I was getting this same error before in the environment when I would just try and write out the primary overheads. I ended up deleting an annotation relationship and it cleared up. But now I can't get past it... even if I delete all relationships but the target one I still get it...

 

salvaleonrp
Rank
Badge +10

Looking at the screenshot of your workspace, not exactly sure what your problem is. Just to ask the not too obvious, was the relationship classes already created in your destination fgdb?

I support that approach of starting small and incrementally build from it. The Electric dataset is fairly complex and its always nice to isolate things that work as you build.

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.