Matt Schwartz

The National Park Service website offers a dataset containing a list of all of the 85,000 entries of the National Register of Historic Places along with geopoint information for most all of the entries.  As is frequently the case with data that comes from such sources the data is high quality, but a few steps away from useful in a hacking session using the more common open source tools.

The NPS’s data is available here: http://nrhp.focus.nps.gov/natreg/docs/Download.html#spatial

The dataset comes as a several tables in a Microsoft Access file.  That’s straight forward enough to get moved into a MySQL database.  However, the larger challange was that the geodata was encoded in NAD 27 UTM.

• UTM = Universal Transverse Mercator – a flavor of geodata encoding that divides the world up into grids on a projection of the earth and references a point as northing and easting within that grid
• NAD 27 = North American Datum 27 – this is a UTM format that uses a projection made in 1899 and formalized in 1927

To make use of this data I built a few ruby scripts that carry out the conversion between UTM and our standard, loveable lat / lng.

Feel free to use the dataset for any purpose.

All of the data is lumped into one table, but can easily be broken back out if needed.  Of the 85,000 or so entries in the list of historical places, the data from NPS provided geocoding for around 65,000 or so.  Not perfect, but still a good chunk of data.

mysql> describe historic_places;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| refnum      | varchar(15)  | YES  |     | NULL    |                |
| resname     | varchar(256) | YES  |     | NULL    |                |
| address     | varchar(256) | YES  |     | NULL    |                |
| city        | varchar(256) | YES  |     | NULL    |                |
| vicinity    | varchar(256) | YES  |     | NULL    |                |
| county      | varchar(256) | YES  |     | NULL    |                |
| state       | varchar(256) | YES  |     | NULL    |                |
| certdate    | varchar(256) | YES  |     | NULL    |                |
| multname    | varchar(256) | YES  |     | NULL    |                |
| acreage     | varchar(256) | YES  |     | NULL    |                |
| utmzone     | varchar(256) | YES  |     | NULL    |                |
| utmeasting  | varchar(256) | YES  |     | NULL    |                |
| utmnorthing | varchar(256) | YES  |     | NULL    |                |
| latitude    | varchar(256) | YES  |     | NULL    |                |
| longitude   | varchar(256) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
16 rows in set (0.00 sec)

historic_places.sql.tar.gz (gzipped SQL dump, 4.8 MB)
historic_places_csv.tar.gz (gzipped CSV, 4.8MB)

I’m working on a mapping project at the moment and have delved into the world of geopoints and GIS.  Joy oh joy.

From my day job I have seen that there is a wealth of free data available at data.gov, but its not always in a form that is immediately usable for an average hacking session.

Example one: http://www.data.gov/geodata/g602088/

The data presented here seems to be of a very high quality:  a list of every populated place in the US, the population (2000 census), and most importantly: a geocoded location.  This is a set of data that a data warehouse would be happy to sell you for a thousand or so dollars.  However, the data is offered for download as a shapefile.

Now don’t get me wrong: shapefiles are completely standard raw materials for GIS professionals and it is a (semi) open format.  For a guy like me that isn’t going to buy a license for ESRI GIS software or delve into PostGIS it is still a step away from what I actually need though.

Enter open source software.

After some Googling I came across a great little set of C scripts called shp2txt.  A small executable built on top of the shplib Open Source C libraries.  These libraries parse the shapefile and harvest the stored data out of the DBase formated datastore that accompanies it.

So for me (open data + open source software) = usable dataset.

One open turn deserves another so here is the resulting dataset that came out of the shapfiles:

cities2.csv.tar.gz — gzipped CSV, 1.2MB
cities2.sql.tar.gz — gzipped SQL dump file, 1.2MB

The dataset contains the following fields:

mysql> describe cities2;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| xcoord     | varchar(15) | YES  |     | NULL    |       |
| ycoord     | varchar(15) | YES  |     | NULL    |       |
| z          | varchar(10) | YES  |     | NULL    |       |
| m          | varchar(10) | YES  |     | NULL    |       |
| citiesx020 | varchar(10) | YES  |     | NULL    |       |
| feature    | varchar(25) | YES  |     | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| poprange   | varchar(25) | YES  |     | NULL    |       |
| pop2000    | varchar(15) | YES  |     | NULL    |       |
| fips55     | varchar(10) | YES  |     | NULL    |       |
| county     | varchar(40) | YES  |     | NULL    |       |
| fips       | varchar(40) | YES  |     | NULL    |       |
| state      | varchar(5)  | YES  |     | NULL    |       |
| statefips  | varchar(15) | YES  |     | NULL    |       |
| display    | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

Data attributions:

The original data set was downloaded from data.gov and is not subject to any usage restrictions per the data.gov Data Policy.

You are free to use the derivative SQL and CSV files that I produced for any use.