Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Parse usable Street Address, City, State, Zip from a string [closed]

Problem: I have an address field from an Access database which has been converted to SQL Server 2005. This field has everything all in one field. I need to parse out the address's individual sections into their appropriate fields in a normalized table. I need to do this for approximately 4,000 records, and it needs to be repeatable.

Assumptions:

  1. Assume an address in the US (for now)

  2. assume that the input string will sometimes contain an addressee (the person being addressed) and/or a second street address (i.e. Suite B)

  3. states may be abbreviated

  4. zip code could be standard 5 digits or zip+4

  5. there are typos in some instances

UPDATE: In response to the questions posed, standards were not universally followed; I need need to store the individual values, not just geocode and errors means typo (corrected above)

Sample Data:

  • A. P. Croll & Son 2299 Lewes-Georgetown Hwy, Georgetown, DE 19947

  • 11522 Shawnee Road, Greenwood DE 19950

  • 144 Kings Highway, S.W. Dover, DE 19901

  • Intergrated Const. Services 2 Penns Way Suite 405 New Castle, DE 19720

  • Humes Realty 33 Bridle Ridge Court, Lewes, DE 19958

  • Nichols Excavation 2742 Pulaski Hwy Newark, DE 19711

  • 2284 Bryn Zion Road, Smyrna, DE 19904

  • VEI Dover Crossroads, LLC 1500 Serpentine Road, Suite 100 Baltimore MD 21

  • 580 North Dupont Highway Dover, DE 19901

  • P.O. Box 778 Dover, DE 19903

like image 268
Rob Allen Avatar asked Aug 19 '08 15:08

Rob Allen


People also ask

How do I parse an address string?

The easiest way to parse an address is by applying a Regex. This method really proves itself when you have regular form addresses. For example, if all the address strings are like STREET_NAME XX, YYYYYY CITY_NAME, you can select a regexp that will split the strings to [STREET_NAME, XX, YYYYYY, CITY_NAME].

What is a parsing address?

Use the Parse Address tool to breaks down any street address into its component parts, like a street number, street name, suffix, and others. A single parse candidate is returned. You can also parse last line data, containing the city, state, and ZIP code.


2 Answers

I've done a lot of work on this kind of parsing. Because there are errors you won't get 100% accuracy, but there are a few things you can do to get most of the way there, and then do a visual BS test. Here's the general way to go about it. It's not code, because it's pretty academic to write it, there's no weirdness, just lots of string handling.

(Now that you've posted some sample data, I've made some minor changes)

  1. Work backward. Start from the zip code, which will be near the end, and in one of two known formats: XXXXX or XXXXX-XXXX. If this doesn't appear, you can assume you're in the city, state portion, below.
  2. The next thing, before the zip, is going to be the state, and it'll be either in a two-letter format, or as words. You know what these will be, too -- there's only 50 of them. Also, you could soundex the words to help compensate for spelling errors.
  3. before that is the city, and it's probably on the same line as the state. You could use a zip-code database to check the city and state based on the zip, or at least use it as a BS detector.
  4. The street address will generally be one or two lines. The second line will generally be the suite number if there is one, but it could also be a PO box.
  5. It's going to be near-impossible to detect a name on the first or second line, though if it's not prefixed with a number (or if it's prefixed with an "attn:" or "attention to:" it could give you a hint as to whether it's a name or an address line.

I hope this helps somewhat.

like image 186
Tim Sullivan Avatar answered Sep 29 '22 04:09

Tim Sullivan


I think outsourcing the problem is the best bet: send it to the Google (or Yahoo) geocoder. The geocoder returns not only the lat/long (which aren't of interest here), but also a rich parsing of the address, with fields filled in that you didn't send (including ZIP+4 and county).

For example, parsing "1600 Amphitheatre Parkway, Mountain View, CA" yields

{   "name": "1600 Amphitheatre Parkway, Mountain View, CA, USA",   "Status": {     "code": 200,     "request": "geocode"   },   "Placemark": [     {       "address": "1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA",       "AddressDetails": {         "Country": {           "CountryNameCode": "US",           "AdministrativeArea": {             "AdministrativeAreaName": "CA",             "SubAdministrativeArea": {               "SubAdministrativeAreaName": "Santa Clara",               "Locality": {                 "LocalityName": "Mountain View",                 "Thoroughfare": {                   "ThoroughfareName": "1600 Amphitheatre Pkwy"                 },                 "PostalCode": {                   "PostalCodeNumber": "94043"                 }               }             }           }         },         "Accuracy": 8       },       "Point": {         "coordinates": [-122.083739, 37.423021, 0]       }     }   ] } 

Now that's parseable!

like image 37
James A. Rosen Avatar answered Sep 29 '22 05:09

James A. Rosen