Skip to content

California POIs 2013


I took a short course on the US Census at the University of California, Berkeley D-Lab recently. Of course, the first topic was the shutdown of People using the convenient, interactive Census API have been left without access to census data.

Two hours of condensed lecture was just enough time to cover the basics of the breadth of the Census, and the Instructor Dr. Jon Stiles did an expert job.

Following up on some leads from the class, I looked into “points of interest in the 2013 Census data versus OpenStreetMap.” The Census includes a table called pointlm. In the case of California, the file I looked at is called tl_2013_06_pointlm.shp. It is a simple layout, with the state code, an ANSI code, point ID, fullname and something called mtfcc. Rather than dig through 1000 pages of census docs, on a site that is out of service, I found this table , which has an easy to read format and a pointer to the definitions. A quick summary in SQL shows:

US Census

Census 2013 pointlm California
   Definition                           | count
"Hospital/Hospice/Urgent Care Facility" | 292
"School or Academy"                     | 3547


OpenStreetMap California latest-snapshot
   Definition                           | count
hospital                                | 829
school                                  | 9201

I used a tool called imposm (ignoring the latest-latest new version 3.0 and using the 2.x I was familiar with..) and imported a dump of OpenStreetMap California ‘latest’ to postgresql.

After seeing some of the other counts in the 2013 Census pointlm classes, I quickly became more interested in the OSM dataset for further refinements.

Important Links
Import “ACS2012 1 Year” to PostgreSQL script