Question

Write Data into archived relationship class (many-to-many)

  • 16 August 2016
  • 5 replies
  • 12 views

Hi!

I'll try to describe my situation first:

 

I heed to transfer data from an old database (DB) into an ArcSDE-DB (based on Oracle). In this old DB, I have feature classes (FC) that have many-to-many relations with each other. These relationships, however, are stored in a single FC in the following way: 'Origin_FID | Origin_FC | Destination_FID | Destination_FC'. I transferred the FCs into the ArcSDE-DB, enabled archiving and created N:M-relationship classes (RC). By using FME, I want to write the relationships from the FC in the old DB (filtered according to the respective FCs) into the RC of the new DB.

The data and relationships have to be written not only als an initial, "complete" load, but also regularly in the form of incremental updates.

My actual problems:

 

On this FME-page, I've found a way to write data into relationship classes. Unfortunately, according to this tutorial, the ObjectID (in the form of geodb_rel_origin/destination_oid) must be present. In my case, however, I have a FC containing only the featureIDs (FID) of the correspnding objects. To get the ObjectID, I need to import the corresponding FCs as readers and use the FeatureMerger to join the ObjectID. Since my relations are not based on the ObjectIDs but the featureIDs, this information shouldn't be necessary.

Question 1: Isn't there a solution, that allows me to write the FID-values directly into the RC (without having to use the ObjectID)?

Question 2: And do I have to activate the parameter 'message_direction' or anything, so that the entry in the (archived) N:M-relationship class is updated, when one of the objects in an associated FC is deleted via FME (writer mode: DELETE)? Or more generally: How do I get the archiving working for the N:M-relationship class for incremental updates?

 

Greetings,

 

André


5 replies

Regarding question 1:

Since I have to transfer about a hundred relationship classes containing several millions records, a method that doesn't involve loading the associated FCs and joining per FeatureMerger would be much faster and could be automated more easily.

Regarding question 2:

I didn't consider that for N:M-relationships, a message would only deal with DELETEs, but not UPDATEs or INSERTs (as the Information which objects are associated is lacking). Since I have the relationships in an own FC, which is updated as well when the FCs containing the associated objects are updated, writing incremential updates into the relationship class will probably do the Job. Thus, question 2 is actually the same as question 1 :)

Userlevel 3
Badge +13

Hello @andr_. You should be able to simply rename the FID to be the geodb_rel_origin/destination_oid/geodb_oid. The article needs to be modified because ObjectID should really say Primary key - or whichever attribute you used as the Primary key when setting up the relationship. If you still have troubles please do send support your workspace and sample source data, along with the relationship feature class you have set up in ArcGIS.

Hello @CandaceAtSafe

Thanks for your answer. I tried it quickly. It seems that FME still needs to read the associated Feature classes so that it can check whether the Features mentioned in the association really exist. So even if the Primary key-attribute is another one, the Basic mechanic doesn't Change. My Goal is to write the data from the original table containing the associations into the relationship class without having to read the associated feature classes as well (that means to avoid the existance-check of FME). Is there any way to do that?

I also stumbled upon another issue when I tried to write incremential update to an archived relationship class (by using the cumbersome way discribed above) :-)

According to this FME tutorial, it should be possible to send UPDATE-commands when an attributed relationship class is used (and the writer is in "edit session-" and Update-mode). INSERT- and DELETE-commands work, but as soon a INSERT occurs (fme_db_operation-attribute set to the corresponding values), the following error appears:

2147211178': Cannot reset foreign key values for an existing relationship row.

This implies that UPDATES fail nevertheless, right?

Greetings,

André

Userlevel 3
Badge +13

Hello @andr_ - I have sent you an email. It seems we need to investigate this a bit more in support.

Userlevel 3
Badge +13

FME may only be used to UPDATE the attributes in an attributed relationship, not the RID, foreign and primary keys. If this is desirable, a DELETE and INSERT operation must be performed.

With the SDE Geodatabase writer, when writing data into an attributed relationship or M:N (attributed or not), if the features which participate in the relationship class already exist in the database and only the relationship table needs to be populated, you still must READ the features which participate in the relationship to get their OBJECTID's but you don't have to WRITE these features back. The OBJECTID's read from the origin and destination relationship features will need to be renamed to the geodb_rel_origin_oid and geodb_rel_destination_oid respectively in order for the relationship table to be populated. At this time there isn't a way to insert existing relationship values into a relationship table with SDE Geodatabase (ie: take from one legacy relationship table and insert those values into a new relationship table) without reading the features that participate in the relationship to get the OBJECTID.

Reply