4 replies to this topic

[chris henrick]

Mapublisher users,

 

Does anyone happen to use the substitute_rx() function in mapublisher's MAP Attribute expression builder? I'm attempting to abbreviate road name endings in an attribute table so for example "Main Street" will become "Main St" Here is what I've tried thus far:

 

SUBSTITUTE_RX(name, "Street$", "St") SUBSTITUTE_RX(name, "Lane$", "Ln") SUBSTITUTE_RX(name, "South$", "S") SUBSTITUTE_RX(name, "North", "N")

SUBSTITUTE_RX(name, "East$", "E") SUBSTITUTE_RX(name, "West$", "W") SUBSTITUTE_RX(name, "Avenue$", "Ave") SUBSTITUTE_RX(name, "Drive$", "Dr")

SUBSTITUTE_RX(name, "Boulevard$", "Blvd") SUBSTITUTE_RX(name, "Pike$", "PK") SUBSTITUTE_RX(name, "Parkway$", "Pkwy") SUBSTITUTE_RX(name, "Terrace$", "Trce")

 

I also have access to QGIS if anyone knows a way to do this with QGIS or with OGR2OGR SQL.

 

much thanks,

 

-Chris

[kay]

I think you should use SUBSTITUTE(name, "Street", "St") OR SUBSTITUTE(name, "South", "S") OR SUBSTITUTE(name, "North", "N").....

 

Also when you apply the expression, you will have to use "Apply Expression" from MAP Attribute panel, not the selection filter.

 

I hope this helps,

Kay

[chris henrick]

Hi Kay, thanks for responding. I did originally try using the OR operator but when I did I got FALSE values for the field. 

 

-Chris

[Hans van der Maarel]

Hmm... I can't get it to work with AND or OR either... but if you leave them out alltogether and just put spaces in, it works. In a way... It seems that if there's more than one of the substitutions being applicable to one text string it only applies the last one. So Queen Street East would end up Queen Street E instead of Queen St E

 

Also, you may want to put your search pattern between \b tags to make sure it only does whole words, rather than any occurance of that string.

[chris henrick]

Hi Hans, I'm not sure how you wrote the expression to get it to work. I originally tried: SUBSTITUTE_RX(name, "Street$", "St") SUBSTITUTE_RX(name, "Lane$", "Ln") SUBSTITUTE_RX(name, "South$", "S") which resulted in only the last SUBSTITUTE_RX() expression working. 

 

However, I did get a response from the Avenza support team and learned that the syntax should be like: SUBSTITUTE_RX(SUBSTITUTE_RX(SUBSTITUTE_RX(name, "South$", "S"), "Lane$", "Ln"), "Street$", "St")

You can have up to 15 substitute_rx() functions chained together for one expression / field.

 

Thanks for the tip on the \b tag, I will try that. I realize only the last word is changed, a result of using the $ character which tells the engine to search the last word of the string.