Using a Relationship to intersect layer and return to table

1144
9
Jump to solution
01-06-2022 09:23 AM
Amarz
by
Occasional Contributor II

Hello,

I am attempting to subvert creating an extra Feature Class & manual manipulation of an Excel sheet to create a Street Index table for my maps.

My data consists of a RoadName table, a RoadCenterline fc (line) and a Grid fc (poly). The Roadname and RoadCenterline are related via a unique ID, and I am attempting to use an Attribute Rule in the RoadName table to ask the RoadCenterline fc to intersect with the Grid fc and return the results, concatenated and ordered into the RoadName table field of grid.

The data is housed in the same datastore, and I am using ArcPro 2.8.

Any help or advice would be much appreciated, 

Thank you, 

Alex

 

edit: I am not sure if this is possible through Arcade, but worth asking.

 

Update:

I have changed my methodologies to the following. 

Added a Grid field into the Road Centerline with an intersect + array with the following script to Sort & remove duplicates on each line segment:

 

var arr = [];
var count = 0;
var i = Intersects(FeatureSetByName($datastore,"Grid"), $feature);

for (var f in i){
   arr[count] = (f.PAGE);
   count += 1;
}
var results = Distinct(arr);
var results = Sort(results);

return results

 

With some extra lines, it formats the array into the fields such as below: 

 

P6, Q6

 

My next and last step is to use a sql statment inside the code to match the foreignkey in the RoadCenterline fc to the primarykey in the MasterRoadName table, with a Relationship class.

I am able to use the following code to get data over,

 

// first read out the Foreign Key of the Destination Table (Feature Class)
var fcID = $feature.roadnameid
// access the Orgin table (Table)
var tbl = FeatureSetByName($datastore, 'MasterRoadCenterline');
// create a sql expression to query on Primary Key
var sql = "F_roadnameid = '" + fcID + "'";
var related_data = Filter(tbl, sql);
// count the resulting records
var cnt = Count(related_data);
// initiate a variable to hold the result
var arr = [];
var count = 0;
var i = related_data;

for (var f in i){
   arr[count] = (f.GRID);
   count += 1;
}

var results = Trim(arr);
var results = Distinct(results);
var results = Sort(results);

return results

 

but end up with 

 

P6, P6, Q6, Q6

 

This is picking up all three segments and seeing them as Distinct, because

seg1 Grid = P6

seg2 Grid = P6, Q6

seg3 Grid = Q6

 

I am assuming I need to take the array that is in the RoadCenterline.Grid field, and break that into a string, so there are no values that are Grid= 'P6 , Q6' and then put that back into an array and Distinct & Sort it?

I am not sure if that's the best way or not.

Thank you for anyone looking at this, the help would be appreciated. If I get it to work I will post a detailed workflow.

0 Kudos
1 Solution

Accepted Solutions
Amarz
by
Occasional Contributor II

Update: With help from multiple contributors, here is a way to achieve what I set out to.

Layers included: (must be in same datastore)

  1. Grid (polygon fc)
  2. RoadCenterline (line fc) related to RoadName via uniqueID
  3. RoadName (table)

Starting with RoadCenterline

Help in this section came from @KenBuja in the thread  Arcade - Intersecting Polygons - return multiple answers if multiple.

  1. Add new Field 'Grid' to accommodate Grid (polygon fc) string input
  2. Attribute Rule pointing to above new field 'Grid'

 

var arr = [];
var count = 0;
var i = Intersects(FeatureSetByName($datastore,"Grid"), $feature);

for (var f in i){
   //f.PAGE is the field name in feature Grid
   arr[count] = (f.PAGE);
   count += 1;
}
var results = Distinct(arr);
var results = Sort(results);

return results

 

Next in the RoadName table

Help in this section comes from @jcarlson and the following thread: Arcade: Array output from related table help 

 

  1. Add new Field 'Grid'
  2. Attribute Rule pointing to 'Grid'

 

// Output array
var grid_arr = []

// Sort function for later
function grid_sort(a, b){
    if (a > b){
        return 1
    } else {
        return -1
    }
}

// Get related records
var related_data = FeatureSetByRelationshipName(
    $feature,
    //Add reltionship class name below
    'RelationshipName',
    ['Grid'],
    false
)

// Check if related records exist
if(Count(related_data) > 0){
    
    for (var row in related_data){
        
        // split value by commas
        var items = Split(row.Grid, ',', -1, true)
        
        for (var i in items){
        
            // trim any whitespace    
            var grid_value = Trim(items[i])
            
            // Check if grid value already in array
            if (Find(grid_value, grid_arr) == -1){
            
                // Add to array
                Push(grid_arr, grid_value)
            }
        }
    }
    
    // Sort grid_arr with custom sort function
    var sorted = Sort(grid_arr, grid_sort)

    // Return concatenated string of array values
    return Concatenate(sorted, ', ')
} else {
    return 'No related records.'
}

 

I hope this helps you all in your endeavors as a way to automate the creation of a Street Index Grid using relationship classes & Attribute Rules!

View solution in original post

9 Replies
ABishop
MVP Regular Contributor

have you tested this yet?  I am not sure if it can be done with a dataset that is based on a relate. 

Amanda Bishop, GISP
0 Kudos
Amarz
by
Occasional Contributor II

My knowledge of Arcade it pretty limited, mostly just use scripts others have used and alter to suite my needs. I am also not sure if it can be done, just thought it may be a possibility.

0 Kudos
ABishop
MVP Regular Contributor

Somebody correct me if I'm wrong, but I think the attribute rule only works for the actual attribute table in a feature.  With a relate, you don't really have the data populated into the attribute table, it just a related view that is only available once you identify the record.

Amanda Bishop, GISP
0 Kudos
Amarz
by
Occasional Contributor II

You can use Attribute rules in fc1 to intersect fc2  and imbed that data into a  fc1 field. I just figured, you may be able to jump that step into a related table. Again, maybe it is not possible.

0 Kudos
ABishop
MVP Regular Contributor

I don't think so.

Amanda Bishop, GISP
0 Kudos
Amarz
by
Occasional Contributor II

Here is an excerpt from this link like provided by ESRI

Edit another feature class with a calculation rule.

You can use attribute rules to perform inserts, updates, and deletes to another feature class by using the edit dictionary keyword. The example below is a calculation rule on a text field of a district boundaries feature class. When editing a polygon in the district boundaries feature class, this attribute rule updates any intersecting address points with the district name.

 

SarahHartholt
Occasional Contributor III

I would be interested in hearing about what solution you come up with as I asked a similar question last year. 

I think you will find this post very helpful: Solved: Re: Populate Street Name Index using Arcade - Esri Community

I think something like this should work, assuming that you have 1 road segment per road name (my centerline layer breaks segments up at intersection therefore I don't think this code would work). Lines 13 - 16 might need some work. I'm a bit rusty and off the top of my head I'm thinking that if a road segment intersects with multiple grid polygons you may need to put those names into an array and then sort them alphabetically before concatenating.

 

var fsRoad = FeatureSetByName ($datastore, "RoadCenterline");
var fsGrid = FeatureSetByName ($datastore, "Grid");
var RoadinGrid = Intersects (fsRoad, FsGrid);
var cnt = Count(RoadinGrid);

//check situation
if (cnt == 0) {
    // no roads intersect grids
    return null;
} else if (cnt == 1) {
     // one grid cell intersects the road, return the Grid Cell Name
     return RoadinGrid["GridName"]
} else {
    // multiple grids intersect the road, return all Grid Cell Names
return Concatenate(sort(RoadinGrid["GridName"]),",")
    

 

 

0 Kudos
Amarz
by
Occasional Contributor II

Sarah,

I think something like this would work if you wanted the Grid data inside your Road Segment layer, however I am attempting to add the Grid data into my Master Road Name table, using a relationship between the Road Name and the Road Segment layer. 

Thank you for the article, I have come across that as well. I may end up having to do it that way, just seems like a pain to keep updating with new construction always happening.

0 Kudos
Amarz
by
Occasional Contributor II

Update: With help from multiple contributors, here is a way to achieve what I set out to.

Layers included: (must be in same datastore)

  1. Grid (polygon fc)
  2. RoadCenterline (line fc) related to RoadName via uniqueID
  3. RoadName (table)

Starting with RoadCenterline

Help in this section came from @KenBuja in the thread  Arcade - Intersecting Polygons - return multiple answers if multiple.

  1. Add new Field 'Grid' to accommodate Grid (polygon fc) string input
  2. Attribute Rule pointing to above new field 'Grid'

 

var arr = [];
var count = 0;
var i = Intersects(FeatureSetByName($datastore,"Grid"), $feature);

for (var f in i){
   //f.PAGE is the field name in feature Grid
   arr[count] = (f.PAGE);
   count += 1;
}
var results = Distinct(arr);
var results = Sort(results);

return results

 

Next in the RoadName table

Help in this section comes from @jcarlson and the following thread: Arcade: Array output from related table help 

 

  1. Add new Field 'Grid'
  2. Attribute Rule pointing to 'Grid'

 

// Output array
var grid_arr = []

// Sort function for later
function grid_sort(a, b){
    if (a > b){
        return 1
    } else {
        return -1
    }
}

// Get related records
var related_data = FeatureSetByRelationshipName(
    $feature,
    //Add reltionship class name below
    'RelationshipName',
    ['Grid'],
    false
)

// Check if related records exist
if(Count(related_data) > 0){
    
    for (var row in related_data){
        
        // split value by commas
        var items = Split(row.Grid, ',', -1, true)
        
        for (var i in items){
        
            // trim any whitespace    
            var grid_value = Trim(items[i])
            
            // Check if grid value already in array
            if (Find(grid_value, grid_arr) == -1){
            
                // Add to array
                Push(grid_arr, grid_value)
            }
        }
    }
    
    // Sort grid_arr with custom sort function
    var sorted = Sort(grid_arr, grid_sort)

    // Return concatenated string of array values
    return Concatenate(sorted, ', ')
} else {
    return 'No related records.'
}

 

I hope this helps you all in your endeavors as a way to automate the creation of a Street Index Grid using relationship classes & Attribute Rules!