Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have to do some massaging of our parcel data to make it usable by a program in sheriff helicopters. The program requires one of the following address formats within the fields:

enter image description here

Our addresses are currently in one field: ex: 1234 W Main St.

Is there a way to automate the splitting of the fields into either of these desired formats?

I can imagine the two field format would be easier by just calling for a split after the numbers, but could also cause a problem for streets such as 1st Ave, etc.

share|improve this question
    
The "less desirable" format could be fairly easily achieved by splitting after the first space. Splitting the rest becomes a bit trickier, since you may or may not have a direction prefix and the street name may or may not have spaces in it, etc. –  Erica Jul 23 at 14:44
    
Are ALL your streetname's formatted the same way? I would guess not which would make parsing out the PreDIR tricky –  GISKid Jul 23 at 14:48
    
No. Some have PREDIR and some don't. Would this be a good place to create some sort of if/then statement into a script? If SE, SW, NE, NE, etc then populate PREDIR else do nothing? –  Craig Jul 23 at 14:51
    
Alternatively, in conjunction with my answer, you could parse out all directions as you go, all the numbers and then see what you're left with. It's not pretty or easy. –  GISKid Jul 23 at 15:43

4 Answers 4

up vote 10 down vote accepted

Instead of using multiple RegExes to parse addresses, just use Esri's out of the box tool that is designed for this task, Standardize Addresses. It's available at all license levels and my experience with it has been positive.

Esri image

share|improve this answer
    
Thanks for suggesting this tool. Worked perfectly. –  Craig Jul 23 at 15:46
    
OOB solutions are best, nice (+1)! –  Aaron Jul 23 at 15:50
    
@Paul Come on, he asked for python! Just kidding, I'll be using this in the future as well, much easier than regex. –  GISKid Jul 23 at 15:52
    
@GISKid, haha! I started out trying to parse with regex, and finally came across this tool, which doesn't seem to be all that commonplace. I have no idea why. –  Paul Jul 23 at 16:02

You can achieve this in field calculator using python. This may not be the most elegant but it's a start, assuming the simpliest case (ie. your addresses all look the same). I would first create the additional fields needed. Assuming your column with the full address is called "Address".

For HOUSENO in the field calculator write:

##Return just numbers

import re
def strip_digits(s):
    return re.sub("\D+", "", s)

This can then be called from the calculate box as:

strip_digits(!ADDRESS!)

For your street name:

# Return just the alpha characters


import re
def strip_letters (s):
  return re.sub ("\d",  "", s[1:])

Codeblock:

strip_letters(!ADDRESS!)

For direction assuming it's the first character each time:

#First character in streetname
import re
def strip_dir(s):
  return re.sub("\d", "", s[0])

Codeblock:

strip_dir(!ADDRESS!)

Here is the python resource for re. This 7.1 Case Study: Street Addresses has helped me numerous times with sorting out my street/address database also using python and re modules. This should help you out, from here if you're not getting results you want comment and I can alter my code

share|improve this answer
    
tried the HOUSENO code, it worked, but it also brought all numerical street name data along with house number data. perhaps for this portion splitting at first space is the way to go. –  Craig Jul 23 at 15:03
    
Whoops, sorry that's an error in my code –  GISKid Jul 23 at 15:05
    
Alright try the new one @Craig –  GISKid Jul 23 at 15:15
    
I've tried both HOUSENO and STREETNAME codes. The issue I'm having is, for HOUSENO, its grabbing all numerical data even if the street name is 1234th Street, so I wind up with some records being 1231234 from 123 1234th Street. Similarly, from the STREETNAME code, it is stripping all numerical data, so 1234th Street is returning "th Street" –  Craig Jul 23 at 15:24
    
Getting regex to work on something as unstandardized as addresses in the U.S. will be a difficult task indeed. –  Paul Jul 23 at 15:29

Like Erica said, your second format is easy. If all your street names were one word, you could check the length of the list after splitting the original field. Length of 3 = no prefix, length of 4 = has a prefix (also assuming SUFTYPE is always populated). This fails when a street is more than one word, such as 'Grand River'. You could check if the second element matches a list of allowed prefixes, and proceed from there. You'd have to test this to see if it worked with your data.

share|improve this answer

Make sure your input is in string format and simply use the string.split() function which will split on white space and return a list. (ex. "1234 W Main St." would return ["1234","W","Main","St."])

From there I would use some conditional statements to check your data depending on how consistent your input is. If the only inconsistency in your data is that sometimes there is a predir and other times there isn't, simply do a len check on the returned list to see if it has a length of either 3 or 4 (or > 4 if your street name has spaces in it).

if len(splitList) == 4:

    HouseNo = splitList[0]
    PreDir = splitList[1]
    StreetName = splitList[2]
    SufType = splitList[3]

elif len(splitList) ==3:

    HouseNo = splitList[0]
    PreDir = ""
    StreetName = splitList[1]
    SufType = splitList[2]
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.