Community
Select to view content in your preferred language

Attribute Assistant - InStr Operator for Parsing Street Names

1101
3
07-28-2022 12:56 PM
JasonBalmut
by
Frequent Contributor
‎07-28-2022 12:56 PM

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.

0 Kudos
3 Replies
JeffSilberberg
by
Frequent Contributor
‎07-28-2022 07:18 PM

 

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/t...  

Hope this helps -- 

 

0 Kudos
JasonBalmut
by
Frequent Contributor
‎07-29-2022 04:47 AM

@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.

0 Kudos
JeffSilberberg
by
Frequent Contributor
‎07-29-2022 03:23 PM

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. 

0 Kudos