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.
Solved! Go to Solution.
Update: With help from multiple contributors, here is a way to achieve what I set out to.
Layers included: (must be in same datastore)
Starting with RoadCenterline
Help in this section came from @KenBuja in the thread Arcade - Intersecting Polygons - return multiple answers if multiple.
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
// 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!
have you tested this yet? I am not sure if it can be done with a dataset that is based on a relate.
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.
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.
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.
I don't think so.
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.
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"]),",")
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.
Update: With help from multiple contributors, here is a way to achieve what I set out to.
Layers included: (must be in same datastore)
Starting with RoadCenterline
Help in this section came from @KenBuja in the thread Arcade - Intersecting Polygons - return multiple answers if multiple.
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
// 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!