22 replies to this topic

[Dennis McClendon]

I have a list of 300 US and Canadian towns from 1912 that I need to locate on a small-scale map. Can ArcMAP place them without ZIP codes? My ArcMAP book says something about it usually ignoring the state because the ZIP code is sufficient.

[benbakelaar]

I have a list of 300 US and Canadian towns from 1912 that I need to locate on a small-scale map.  Can ArcMAP place them without ZIP codes?  My ArcMAP book says something about it usually ignoring the state because the ZIP code is sufficient.

<{POST_SNAPBACK}>

How do you handle the fact that there is no database from 1912 with town locations? Anyway, if you have trouble with using ArcMap, you can try http://www.batchgeocode.com or send me a CSV list of your locations and I can run them through my batch geocoder which utilizes Google's geocoding API (I don't know of any freely available interface that does this yet). But still, being that the town names are from 1912... and anyway even if you had zip codes, those boundaries change over the years too.

[Dennis McClendon]

Well, the towns are still there, with the same name.

I downloaded the list of populated places from GNIS, which shows lat-long. I just have to think about how to combine it with my listing. I was never very good with Excel lookup tables. There's probably some way to do this in ArcMAP.

[benbakelaar]

Well, the towns are still there, with the same name.

I downloaded the list of populated places from GNIS, which shows lat-long.  I just have to think about how to combine it with my listing.  I was never very good with Excel lookup tables.  There's probably some way to do this in ArcMAP.

<{POST_SNAPBACK}>

Well if you need any help with combining, let me know, that's one of my specialties... database cleanup/manipulation.

I guess I don't understand what your intended result is; since you already have the lat/long's for the town names, you can place them by importing the table and using Display XY data.

[Rob]

dennis,

sounds like you'll need to scrub your 300 town list to create some type of key field to match the GNIS record with the LatLong data. Then join the records and use that to display the XY off the geographic coords in Arc.

rob

[benbakelaar]

Well, the towns are still there, with the same name.

I downloaded the list of populated places from GNIS, which shows lat-long.  I just have to think about how to combine it with my listing.  I was never very good with Excel lookup tables.  There's probably some way to do this in ArcMAP.

<{POST_SNAPBACK}>

Oh I see now... do you think the GNIS coords are more accurate than NavTeq (Yahoo's geocoder source) or TeleAtlas (Google's geocoder source)? If not, it would be much easier to geocode it than to use db operations to combine from the GNIS list. Or am I still missing something? Does the GNIS coords table have a "year" field which you are using to look up historical coordinates?

[Hans van der Maarel]

Dennis,

If you want me to do a match between GNIS and your list, let me know. FME is up for that task

Alternatively, you could try simply geocoding them (I personally use Manifold + MapPoint) and see what happens. Are they large towns?

[frax]

you would just have to join the tables in access then (better than excel for this kind of stuff)

[Dennis McClendon]

I guess I could try joining the tables in ArcMAP. Access is some PC-only program, right?

The GNIS coordinates are plenty accurate enough for small-scale mapping. As for the "historic coordinates," I don't understand the inquiries. Atlantic City is in the same place now as it was in 1912. The only difference is that it didn't have a ZIP code back then.

[benbakelaar]

I guess I could try joining the tables in ArcMAP.  Access is some PC-only program, right?

The GNIS coordinates are plenty accurate enough for small-scale mapping.  As for the "historic coordinates," I don't understand the inquiries.  Atlantic City is in the same place now as it was in 1912.  The only difference is that it didn't have a ZIP code back then.

<{POST_SNAPBACK}>

Ah you are a Mac Yes Access is PC only, and frax is right, skip Excel for any table-joining attempts.

As for historic inquiries, I guess our imaginations are running wild at the thought of nearly 100 years ago

[Kartograph]

Having the lat/lon data is much more precise than ever-changing ZIP-Codes, no?

[Martin Gamache]

You should be able to join the tables based on the town names with any database program mac or pc and then map those towns using the lat/long coordinates.

The town names may need some formatting to join correctly or a secondary field such as State name to match correctly.

[Dennis McClendon]

I'm afraid Excel is the only database program I have handy.

I'll do a little manipulating to put the city, state in the same field in both tables, which should allow me to join them somehow. One thing that worries me is that the populated places list is bigger than Excel can open, even after deleting the historical and subdivision names.

[Kartograph]

Excel has a problem with the lines, they cap somewhere around the ten thousands. So no matter how many columns you remove, the number of entries is a limiting factor.

BTW, open office has an Access clone, so you might want to check that out as a database.

[benbakelaar]

I'm afraid Excel is the only database program I have handy.

I'll do a little manipulating to put the city, state in the same field in both tables, which should allow me to join them somehow.  One thing that worries me is that the populated places list is bigger than Excel can open, even after deleting the historical and subdivision names.

<{POST_SNAPBACK}>

Seems like a lot of extra work for what could be a 5 minute geocoding exercise.. unless I still am missing something But my offer still stands, if you need help, you can email me the files at benbakelaar at nospam gmail dot com.