5

I have nine spreadsheets containing information about a total of 33,401 unique events in the greater Chicago area. I have been asked to geocode all of these, if possible, and I am certainly no stranger to geocoding. However, the location information for each of them is the worst I have ever seen, written in a single field, with no particular convention.

I have no ZIP codes or city names, but I do have county names in almost every case. When street names are included, they are often missing their suffix ("Ave", "St", "Rd"). State and US highways are both frequently coded as the indiscriminate "Rte/Rt". The majority of locations have been written as intersections (often with extra irrelevant information), such as:

SB Pulaski & 162nd St.
I-55 @ Rt.30
Devon and Cicero (Il 50) NW corner TS
NB Rt.41 @ Half Day Rd. Exit Ramp.

In the case of Interstates, these "intersections" often do not actually exist -- it's just referencing a street the Interstate passes over. A fair number have (relatively) proper addresses:

1800 s Wolf rd. south of Oakton, north of Touhy.
1010 S. Rt. 14 - in front of Thunderbird Country C
Grayslake Maintenance Yard, 217 N. Baron, Grayslak

Some are more vague, but still generally well-specified:

South bound Busse rd south of Oakton and Higgins
EB Elgin-O'Hare W of Rohlwing Rd
NB IL-59,  1 mile north of IL-132

And some are almost certainly impossible to locate without additional context:

EB Elgin  O'Hare expresway
Prairie View Rest Area
Comm Center/Stevenson Yard

My question is, given the wide range of formats and anything-goes approach for specifying addresses in these datasets, are there any suggested methods for parsing at least some of this stuff into a reasonably clean set of geocodable addresses? I've so far been stumped and have been going through the painful process of making sense of individual records in Google Maps. I want to trim off as much manual work as possible, as I'd prefer not to spend the next three years working on this.

nmpeterson
  • 8,276
  • 33
  • 59
  • 2
    Can't you just say no? This is why it is important to have geocoding/addressing standards and something to point to to say you won't support it if the data isn't up to the standard. Any way you slice it it will just be a lot of manual labor, and you don't want to be the one stuck doing it by yourself. – blah238 Nov 03 '11 at 21:43
  • are these events all a particular point on roads? – Matthew Snape Nov 03 '11 at 22:30
  • @MatthewSnape generally speaking, yes, they are specific points. There are a handful of cases where it may apply to a short stretch of road, but being coded as a point is fine. – nmpeterson Nov 03 '11 at 22:50
  • 1
    @blah238 thankfully my supervisor views this is an "it would be nice, but if it's too hard then we can do without it" situation. As far as the data quality goes, we can't enforce any kind of standards because it wasn't created/collected by us -- it was given to us by a government agency. – nmpeterson Nov 03 '11 at 22:53
  • Should this question be made a Community Wiki? I'm not sure there will ever be a "right" answer to it. – nmpeterson Jan 12 '12 at 18:40

2 Answers2

2

Here is how I would approach it, YMV. Hope this helps.

I would get the data loaded in a database so the records would be easier to work with. Then I would use a programming language to manipulate the data. The first thing I would probably do would sort the intersection data out from the rest of the data. I would do that by looking for "&, and, @, /", then I would sort out the half decent addresses using regular expressions.

I would then try and see how geocoding worked.

The locater choice is going to be really critical. I would use a single field street locater. I think you are going to have better luck with that style.

Jamie
  • 1,684
  • 8
  • 13
  • I like this suggestion. One of the biggest problems is going to be that, even when I can pick out a relatively decent intersection, the fact that most streets are missing their suffixes and that there are so many streets in the Chicago region means that a lot of these intersections could theoretically correspond to multiple street-pairs. The only option I have for filtering out inaccurate matches of this sort is by using the county information (which I've never seen built into an address locator, but I'm guessing it would not be that difficult to do?). – nmpeterson Nov 04 '11 at 18:10
  • If you have addressing data that has county, it is easy to add to the template (I am using ArGIS as my base). Yahoo's or Google's locater could possible make it easier. When I get home I will see if I can find the site that has the locater service. – Jamie Nov 04 '11 at 19:05
0

Use http://www.hamstermap.com is faster - just copy / paste your addresses in any format, as long as you keep one address per line.

After you are finished with geocoding, you can grab the coordinates and display them using Quick Map.

Petra Hauerlandova
  • 129
  • 2