FGDB: Propagate duplicate features via 1:M join to related table

850
1
Jump to solution
12-03-2022 12:40 AM
Labels (1)
Bud
by
Notable Contributor

In a FGDB, I have:

  • A point FC with a unique ID field.
  • A related table that has the ID field as a foreign key, of sorts.
  • The relationship is: 1:M (one point feature to many related table records).

I want to export a copy of the FC that brings in the information from the related records. So the challenge is: how to handle the 1:M relationship?

Options:

  1. Join from the FC to the table using an in-map join in the attribute table. In-map attribute table joins get the first arbitrary related record that it finds (“one-to-first”), and ignores the rest of the related records. So that's not quite what I want.
  2. Or, pivot the related records into columns, making the relationship 1:1.
  3. Or, create duplicate points for each related record.

For option #3, how can I propagate duplicate features for each related record?

If I were working in pure SQL, I'd simply use a LEFT JOIN from the FC to the table. But I don't think I can write full-blown SQL queries with JOIN clauses on FGDB data (i.e. Query Layers).

Thanks.

0 Kudos
1 Solution

Accepted Solutions
Bud
by
Notable Contributor

It looks like the Add Join (Data Management) geoprocessing tool might do what I want:

400AEEAA-A995-4CCA-8C69-E9AAF9D4F262.jpeg

Related:

 

Test:

Before:

  • No join
  • Three features

Bud_0-1670681317954.png

After:

  • Joined to a table that has duplicates. The join is based on the common ASSET_ID.
  • Now, there are 4 features. ASSET_ID #3 has been duplicated, since the related table has duplicate rows for that asset.

Bud_0-1670684201570.png

Bud_0-1670684393239.png

 


A different option:

Alternatively, I could add XY fields to the point FC. Then join (in-map join in attribute table) from the table to the FC. Then make an XY event layer from the result. I think that would give me the duplicated points.

View solution in original post

0 Kudos
1 Reply
Bud
by
Notable Contributor

It looks like the Add Join (Data Management) geoprocessing tool might do what I want:

400AEEAA-A995-4CCA-8C69-E9AAF9D4F262.jpeg

Related:

 

Test:

Before:

  • No join
  • Three features

Bud_0-1670681317954.png

After:

  • Joined to a table that has duplicates. The join is based on the common ASSET_ID.
  • Now, there are 4 features. ASSET_ID #3 has been duplicated, since the related table has duplicate rows for that asset.

Bud_0-1670684201570.png

Bud_0-1670684393239.png

 


A different option:

Alternatively, I could add XY fields to the point FC. Then join (in-map join in attribute table) from the table to the FC. Then make an XY event layer from the result. I think that would give me the duplicated points.

0 Kudos