A follow up from long past…

 

For those that are interested, I did a (very) little bit of cleanup on the scripts that I’ve used to load ACS data into a postgres database and committed them to github. These scripts should work fine on either a postgres or a postgis database as they make no direct reference to the spatial operations. In fact, you’ll need to build views linking the data tables to the geography table and then to the spatial data separately.  I can provide examples of that process if needed.

 

A couple known issues:

Because the logrecno field is only unique within a single state, this structure will need some minor modification if you want it to span multiple states.

 

It takes a bit of time to run (I’d just start it off when you’re going to head home for the evening, and it should be done when you get in the next morning).  I started to switch it to use the psycopg2.copy_from method, but since the data tables basically have two null value characters “” and “.” I’d have had to process the tables row by row anyways. (see the LoadSequences2 function for the detritus of that effort.)

 

I’m sure there are more issues out there, but it’s worked for me.

 

I’m listing it as an Apache2.0 license. Please use it, spread it around, but I’d love it if you’d let me know you’re using it, and a little about what you plan to do with it. Also, if you fix/improve it, I’ll be happy to either give you commit access, or to do the commit for you.

 

https://github.com/ucdavis-ice/census-tools

 

Nate

 

 

 

Nathaniel (Nate) Roth

GIS Programmer

Information Center for the Environment

Department of Environmental Science & Policy

University of California, Davis

1 Shields Ave

Davis, CA 95616

neroth@ucdavis.edu

530-754-6212

 

From: Roth, Nate
Sent: Wednesday, August 01, 2012 10:40 AM
To: 'ctpp-news@ryoko.chrispy.net'
Subject: RE: [CTPP] Regional and Statewide Census Geodatabases

 

I’ve written python scripts to process and load ACS data into a PostGIS database, and know someone who has done similar for the SF1 data. I’ve been planning on cleaning up my python for the ACS and make it available under an open source license (probably Apache 2). I’ll see if my colleagues who did the SF1 load (and then shared it with me) are willing to do the same.

 

Nate

 

Nathaniel (Nate) Roth

GIS Programmer

Information Center for the Environment

Department of Environmental Science & Policy

University of California, Davis

1 Shields Ave

Davis, CA 95616

neroth@ucdavis.edu

530-754-6212

 

From: John-Paul Hopman [mailto:jhopman@macog.com]
Sent: Tuesday, July 31, 2012 12:18 PM
To: CTPP News
Subject: Re: [CTPP] Regional and Statewide Census Geodatabases

 

We have been referred to the demographic profile geodatabases before and made need to take a second look. However we were hoping to keep everything in a single central geodatabase, if one already existed, preferably with more then just the basic demographic tables.

 

Thank-you for the feedback and links though.

 

John-Paul Hopman

 

 

 

 >>  Geography Division, Census Bureau released TIGER/Line shapefiles and
 >>  geodatabase files with linework and 2010 Census demographic profile data.
 >>  See our TIGER Products page at http://www.census.gov/geo/www/tiger/ and
 >>  specifically http://www.census.gov/geo/www/tiger/tgrshp2010/2010DP1.html
 >>
 >>  ______________________________________
 >>  Michael R. Ratcliffe
 >>  Assistant Division Chief, Geocartographic Products and Criteria
 >>  Geography Division
 >>  U.S. Census Bureau
 >>  4600 Silver Hill Road/ MS-7400
 >>  Washington, DC 20233-7400
 >>  301-763-8977
 >>  michael.r.ratcliffe@census.gov
 >>
 >>  -----ctpp-news-bounces@chrispy.net wrote: -----
 >>  To: CTPP News <ctpp-news@chrispy.net>
 >>  From: John-Paul Hopman
 >>  Sent by: ctpp-news-bounces@chrispy.net
 >>  Date: 07/31/2012 12:18PM
 >>  Subject: [CTPP] Regional and Statewide Census Geodatabases
 >>
 >>  We are looking for a pre-configured 2010 Census Geodatabase with both
 >>  linework and data attached for at least the state of Indiana, if not also
 >>  including the surrounding states. Although all of the data can be
 >>  downloaded from the Census Bureau on an as needed basis, we would like a
 >>  more permanent copy in-house for our GIS needs and publication to our
 >>  regional GIS website.
 >>
 >>  We are hoping to find something pre-built, rather then downloading and
 >>  compiling everything ourselves. Any assistance in the endeavor is
 >>  appreciated.
 >>
 >>  John-Paul Hopman
 >>  Michiana Area Council of Gov'ts
 >>
 >>  _______________________________________________
 >>  ctpp-news mailing list
 >>  ctpp-news@ryoko.chrispy.net
 >>  http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
 >>
 >>  _______________________________________________
 >>  ctpp-news mailing list
 >>  ctpp-news@ryoko.chrispy.net
 >>  http://ryoko.chrispy.net/mailman/listinfo/ctpp-news