Attribute Assistant - InStr Operator for Parsing Street Names

JasonBalmut — Thu, 28 Jul 2022 19:56:57 GMT

I am trying to parse values for my NENA NextGen 9-1-1 street name fields by using the VBScript InStr operator. For example, if the original street name field contains a USPS Publication 28 Street Suffix the expression should be able to find that prefix/suffix in the name and put it in the Pre-Type or Post-Type field, find any pre- or post-separators, and then remove the suffix and separators from the Street Name field . Here's what I have so far, although I do not get results with the Pre-Type ValueInfo expressions I have listed. I'm unsure if this is a problem with the InStr operator itself because a similar expression using Left does work.

Original Road Name Fields and Values

ROADNAME: Old US Highway 41A

NENA Road Name Fields and (Expected) Values

St_PreMod : Old

St_PreDir: <Null>

St_PreTyp: United States Highway

St_Name: 41

St_PosType: <Null>

St_PosDir: <Null>

St_PosMod: Alternate

I will list the following lines in the Attribute Assistant in order of their Rule Weights so they go in order:

Street Name (This works)

TableName: MasterStreetName
FieldName: St_Name
ValueMethod: FIELD
ValueInfo: ROADNAME
RuleWeight: 985
Comments: Copies the ROADNAME to NENA St_Name

Street Post-Modifier (This works)

TableName: MasterStreetName
FieldName: St_PosMod
ValueMethod: EXPRESSION
ValueInfo: IIF(InStr([St_Name], "41A"), "Alternate", [St_PosMod])
RuleWeight: 984
Comments: If the St_Name contains 41A, set the St_PosMod to Alternate

Street Pre-Modifier (This works)

TableName: MasterStreetName
FieldName: St_PreMod
ValueMethod: EXPRESSION
ValueInfo: IIF(Left([St_Name], 4) = "Old ", "Old", "<Null>")
RuleWeight: 983
Comments: If the St_Name starts with Old + a space, set the St_PreMod to Old

Street Pre-Type (Neither method works)

TableName: MasterStreetName
FieldName: St_PreTyp
ValueMethod: EXPRESSION
ValueInfo:
- IIF(InStr([St_Name], "US Highway "), "United States Highway", "")
- IIF(InStr([St_Name], "US Highway "), "United States Highway", [St_PreTyp])
RuleWeight: 983
Comments: If the St_Name starts with the NENA type, set the St_PreTyp to that value

Street Name (This works)

TableName: MasterStreetName
FieldName: St_Name
ValueMethod: EXPRESSION
ValueInfo: TRIM(Replace([St_Name], "Old ", ""))
RuleWeight: 982
Comments: Replace the equivalent NENA pre- or post-type in the St_Name with nothing

Street Name (This works)

TableName: MasterStreetName
FieldName: St_Name
ValueMethod: EXPRESSION
ValueInfo: TRIM(Replace([St_Name], "US Highway ", ""))
RuleWeight: 982
Comments: Replace the equivalent NENA pre- or post-type in the St_Name with nothing

Street Name (This works)

TableName: MasterStreetName
FieldName: St_Name
ValueMethod: EXPRESSION
ValueInfo: TRIM(Replace([St_Name], "41A", "41"))
RuleWeight: 981
Comments: Replace 41A with 41.

NENA Road Name Fields and (Resultant) Values

St_PreMod : Old

St_PreDir: <Null>

St_PreTyp: <Null>

St_Name: 41

St_PosType: <Null>

St_PosDir: <Null>

St_PosMod: Alternate

It looks like the InStr operator fails. This is not the only situation I have this problem. We have Spanish streets names that need their pre-types and separators parsed as well. What is odd is that I have two identical expressions that have different results:

Street Pre-Type (Different Results)

TableName: MasterStreetName
FieldName: St_PreTyp
ValueMethod: EXPRESSION
ValueInfo:
- IIF([St_Name] = "Via Del Mar", "del", "<NULL>") Does not work
- IIF([St_Name] = "Vista Del Rio", "del", "<NULL>") This works
RuleWeight: 983
Comments: If the St_Name starts with the NENA type, set the St_PreTyp to that value

The InStr operator is inconsistent buts tends to not work the way I have them coded.

Any help is welcome.

Thank you.

Re: Attribute Assistant - InStr Operator for Parsing Street Names

JeffSilberberg — Fri, 29 Jul 2022 02:18:10 GMT

I can't specifically help you with the IIF issue, although I thought it was single quotes not double. But I might refer you to the NENA document for parsing the CFS address

NENA Next Generation 9-1-1 (NG9-1-1) United States
Civic Location Data Exchange Format (CLDXF) Standard
NENA-STA-004.1.1-2014, March 23, 2014

for a lot of good rules on address parsing and the related APCO cfsinfo.pdf document as well. I had a link to the latter it but it seems broken ..

Also, you should look at this community post - https://community.esri.com/t5/arcgis-online-questions/multiple-iif-statements-in-arcade-expression/td-p/485731

Hope this helps --

Re: Attribute Assistant - InStr Operator for Parsing Street Names

JasonBalmut — Fri, 29 Jul 2022 11:47:20 GMT

@JeffSilberberg, our organization is still using ArcMap with the Attribute Assistant add-in until we can convert to ArcGIS Pro. Until then, we are stuck with VBScript and unable to use Arcade. I'm familiar with the NENA best practices and just need help with the InStr operator for this function. Thanks.

Re: Attribute Assistant - InStr Operator for Parsing Street Names

JeffSilberberg — Fri, 29 Jul 2022 22:23:53 GMT

Okay, In your Street Pre-Type (Neither method works) What doesn't work .. I tried it in a Fiddle and it appeared to work for me.. Returning the True value in both IIF statements based on the ROADNAME: Old US Highway 41A value. In your second issue Street Pre-Type (Different Results) there is no inStr() not sure what does not work.

Most these should use the third value of the inStr(String, Find, Compare) Where the default f the third value is 0 (Case Sensitive) verses 1 (Case Insensitive). Since I never worked in ArcMAP that's about all I have to offer. Hope this helps more.

topic Attribute Assistant - InStr Operator for Parsing Street Names in State & Local Government Questions

Attribute Assistant - InStr Operator for Parsing Street Names

Original Road Name Fields and Values

NENA Road Name Fields and (Expected) Values

NENA Road Name Fields and (Resultant) Values

Re: Attribute Assistant - InStr Operator for Parsing Street Names

Re: Attribute Assistant - InStr Operator for Parsing Street Names

Re: Attribute Assistant - InStr Operator for Parsing Street Names