Want to populate Street name based on restricted domain values (Arcade SQL)

2279
9
10-20-2020 05:23 PM
ShahriarRahman
New Contributor III

I want to populate the Street name, but it should be restricted based on the selected domain values (“RESERVE” & “PARK”) of another attribute field (LOCATION_GROUP). The following script is giving me the Street name, but it’s not restricting my SQL query only for “RESERVE” and “PARK”,

 

Var StreetID = $feature.Street_ID;

Var Location = FeatureSetByName($datastore, “Street_Locations”);

Var ids = “STREET_ID = ‘” + “LOCATION_GROUP = RESEREVE || LOCATION_GROUP = PARK‘”  + StreetID + “’”; (maybe I am doing something wrong here)

Var related_ids = Filter(Location, ids);

Var cnt_street = Count(related_ids);

Var StreetName = “”;

if (cnt_street ==1){

                For row (var row in related_ids){

                                StreetName = row.STREET_NAME;

}

else {

                StreetName = “”;

}

Return StreetName;

 

Great to have your expert suggestion.

 

Kind regards,


Shahriar

0 Kudos
9 Replies
XanderBakker
Esri Esteemed Contributor

Hi srahman ,

Maybe you can try this:

var StreetID = $feature["Street_ID"];
var Location = FeatureSetByName($datastore, "Street_Locations");
var sql = "STREET_ID = @StreetID AND (LOCATION_GROUP = 'RESERVE' OR LOCATION_GROUP = 'PARK')";
var related_ids = Filter(Location, sql);
var cnt_street = Count(related_ids);
var StreetName = "";

if (cnt_street == 1) {
    // only 1 result, take street name from first
    StreetName = First(related_ids)["STREET_NAME"];
} else {
    // no result or multiple resuls, return empty string
    StreetName = "";
}

Return StreetName;
ShahriarRahman
New Contributor III

Hi Xander Bakker,

Thank you for your kind support. But, this script is failing with this message,

"Invalid expression.

 Error on line 5.

 Field not found Street_ID"

Tried with the "ObjectID" field, it validates the Arcade expresssion but does not return Street name.

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Shahriar Rahman ,

The error is indicating that you don't have a field called "Steet_ID" (a bit strange that it refers to line 5, since the error should have occurred before that line). Can you share the field names that you have in both the Street_Locations layer and the current layer where the Arcade expression is defined?

0 Kudos
ShahriarRahman
New Contributor III

Hi Xander Bakker‌,

Maybe I am not doing the right SQL query.

"Street_Locations" has "Location Group" field and has no common field with the feature class having the field "Street_ID".
"The error is indicating that you don't have a field called "Steet_ID" (a bit strange that it refers to line 5, since the error should have occurred before that line). ".....is it necessary to have a common identifier in between two layers (reference layer: "Street_Locations" and feature class)? I want to select all the records of "Street_ID" from the feature class and also want to select based on "Location Group" from the "Street_Locations" reference layer.


If in case there will be no common field between the two tables (reference layer and editing feature layer) is it possible to do the SQL query?

Feature: "Street_IDs" having fields "Street_ID" & "ObjectID"

Reference Layer (here "Street_Locations") have fields "OBJECTID", "LOCATION_GROUP", "STREET_NAME"

Great to have your suggestion.

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Shahriar Rahman ,

If you want to filter the Street_Locations based on some attribute of the current feature, it will need some information to have in common otherwise it is impossible to filter the Street Locations. 

If there is no common identifier shared among the two layers, maybe you need to use a spatial relationship. Does that exist between the two layers?

Maybe you can expand a little about what each layer represents and how the relation between the two should be established and what you are trying to achieve. Based on your initial code in the question the common identifier "Street_ID" was implied, but I understand now that that is not the case.

ShahriarRahman
New Contributor III

Hi Xander Bakker‌,

Thank you for your kind support.

"If there is no common identifier shared among the two layers, maybe you need to use a spatial relationship. Does that exist between the two layers?": Yes, there is a spatial relationship exists between the feature class and reference layer ("Street_Locations"). Do you think "intersects" will be a solution? Like, if I create a record and if will intersect with the base layer (selected based on the two attributes of "Location_Group" field) then it will only show the Streetname. If it is possible then it will solve my issue. Great to have your suggestion in this regard.

"Maybe you can expand a little about what each layer represents and how the relation between the two should be established and what you are trying to achieve. Based on your initial code in the question the common identifier "Street_ID" was implied, but I understand now that that is not the case.": I have two layers (one is feature layer: "Street_IDs" & base layer: "Street_Locations"), there is no common field between these two. What I want to do, I want to perform the query based on the selected attribute values (here, "Reserve" & "Park" of "LOCATION_GROUP") of the base layer, and want to select all records of "Street_ID" from the layer ("Street_IDs") and selected value of base layer (above mentioned two domain values from the attribute field present in base layer). Afterwards, if any created record will intersect "Reserve" or "Park" (of the layer "Street_Locations") then it will populate "STREET_NAME" (which is in the layer "Street_Locations), otherwise, there will be no "STREET_NAME" will be populated.

Great to have your kind suggestion.

Best regards,

Shahriar

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Shahriar Rahman ,

So, location can be the way of retrieving the information that you are after. Intersects could be an option, but not is you are comparing for instance points and lines. In that case there are other things we can do like first buffering the features with a tolerance distance and then use Intersects or use the distance to establish the relation.

In this case a screenshot of some specific cases you want to solve would help here. What if a features crosses multiples street locations or none, what should happen in these situations. Do you need to find out if the feature (and is this a point a line or a polygon) is nearest or more similar to the street locations? I do think it is possible with what you have mentioned, but some more information like a screenshot would help understand what is exactly needed.

ShahriarRahman
New Contributor III

Thank you, Xander Bakker‌ .

I need to send you a video rather than a screenshot.

"Intersects could be an option, but not is you are comparing for instance points and lines. In that case there are other things we can do like first buffering the features with a tolerance distance and then use Intersects or use the distance to establish the relation."
Can you provide some hints or sample script?

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Shahriar Rahman , 

Once you share some more insight (screenshot or video) on what you have are what you need, I can help you better and perhaps share some examples.

0 Kudos