Question

Testing two datasets, one FGDB, one SDE; for changes

  • 9 December 2015
  • 6 replies
  • 3 views
lorinda
Rank
Badge +5

Hi All,

I've got two tables that need to be compared for no change, adds, updates and deletes. One has been created in SDE from a previous version of the table with two unique fields in it. I'm taking the second newer table from a FGDB, running it through a clipper, and then joining one of the unique fields to it based on the other unique field. I.E. - parcel points have been created with parcel id and pid_attr (a created number that is not related to the objectID field); to have the updated version of parcel polygons (points created) compared to it after joining the pid_attr based on parcel id and then adding the updated, added, or deleted rows to the existing point table - while leaving the unchanged ones alone.

I then have a TestFilter that checks for same pid_attr, parcel id, situs address - original, unchanged; same pid_attr, different parcel id and/or situs address - updated; no pid_attr - added; no parcel id in the new data - deleted.

I have tried this via TestFilter, ChangeDetector, UpdateDetector, Matcher, and several testers in a row - none of them seem to be finding the adds or the changes or even the original ones. All are going to the deleted filter.

I have watched this process to see how it is running. I have reordered the inputs and put both tables going into the clipper. I see that it is processing either of the two tables first and but then not seeming to see that there is a column to be tested. I not in the past been comparing two tables to see the changes, but it is a new item that has come up that it will need to be perfected soon.

Thanks for any hints that can be offered. Should both of the tables be read into a temporary table that is only in the FME bitworld?

Lorinda

6 replies

erik_jan
Rank
Userlevel 2
Badge +12

Do both datasets have the same number of decimals for the coordinates. Sometimes using a CordinateRounder will help if this is not the case.

erik_jan
Rank
Userlevel 2
Badge +12

Another thought is about the attributes. If the attributes are CHAR type attributes the number of spaces added to the actual value might differ. In that case the AttributeTrimmer can solve your problem.

erik_jan
Rank
Userlevel 2
Badge +12

For comparison either the ChangeDetector (attributes and Geometry) or the FeatureMerger (attributes only) are good options

fmelizard
Rank
Userlevel 3
Badge +13

Just to add to Erik's comments, could you upload the workspace or a screen shot? Seems like running both tables right into a ChangeDetector should work. You may need CoordinateRounders first, and possibly AttributeRenamers if they don't have the same schemas.

roland.martin
Badge +2

As @erik_jan says, in my experience this is normally to do with number accuracy.

What I tend to do in cases like this is use a CRCCalculator to generate a checksum based on the attributes and/or geometry. If you add an extra column when you write initially, you can just use that when you need to run the comparison. It also means you can get away with a much simpler comparison test. The downside is the need for the extra column, but I've never had any complaints yet!

tim_wood
Rank
Badge +7

I agree with @erik_jan - however I have a Workspace that does a similar thing and discovered when building it that CoordinateRounder does not result in identical values in X and Y attribute columns when using Matcher to compare ArcSDE and FGDB layers. See https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Transformers/Transformers/coordinaterounder.htm

 

In my case I'm using a CoordinateExtractor to get the X and Y values of point data then AttributeRounder to round them to 3 decimal places. For info, this is because the data comes in CSV with Lat and Long attributes and I want the geometry in British National Grid and the X and Y (Easting and Northing) values as attributes.

 

 

In another case, I do use CoordinateRounder, along with AttributeManager before testing with Matcher then using FeatureMerger to send records to the appropriate AttributeCreator to set the fme_db_operation value. I think I developed this before ChangeDetector, but some of the preparation such as CoordinateRounder is likely to still be valid. I've posted screenshots of this Workspace. Note also that I don't compare ObjectID in Matcher because otherwise identical records could have the same ObjectID. Also note @DanAtSafe's comments on https://knowledge.safe.com/questions/1797/change-detector-attribute-matching-strategy.html: 'The ChangeDetector and Matcher are working correctly. Unexposed attributes are included with "Match All Except Selected Attributes". Use an AttributeExposer before or else "Match Selected Attributes" in the Matcher.' Personally I think this is wrong. I think that if you haven't explicitly exposed an attribute, particularly a format one, then Matcher should ignore it. But it's not too much of a hardship to do a positive "Match Selected Attributes" and it's then clear what you're matching.

Reply


Community Stats

28,634
Posts
109,181
Replies
36,514
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.