Thanks Nate for the update. I don’t know if this has already been mentioned/addressed, but I thought it might be important to reiterate given the nature of this listserve and conversation thread . . .
In addition to the issue of the LOGRECNO index being unique within each state (e.g., LOGRECNO 000123 in Oregon is not the same as LOGRECNO 000123 for California, or any other State):
a.) The LOGRECNO field in the SF1 database is NOT the same as the LOGRECNO in the ACS database (e.g., LOGRECNO 000456 in the SF1 for Oregon is NOT the same geographic unit for LOGRECNO 000456 in the ACS for Oregon). This is important to know because both SF1 and ACS call their index fields LOGRECNO, leading to a potentially serious error if one tries to join the two datasets (obviously, you’re not supposed to do that anyway).
b.) The LOGRECNO indices for ACS datasets prior to the 2010 census are NOT comparable to the indices for datasets that span or cross over the 2010 census (i.e., the ACS 2005-2009 5-year and ACS 2007-2009 3 year do not share the same indexing as ACS 2006-2010 5-year and 2008-2010 3 year, respectively).
I hope I’m not reiterating an obvious point. Thanks again to Nate for the update and the notice to the list.
Mike Greenwald
Michael J. Greenwald
Senior Transportation Planner
Lane Council of Governments
859 Willamette Street, Suite 500
Eugene, OR 97402
Tel: 541-682-6595
Fax: 541-682-4099
mgreenwald@lcog.org
From: ctpp-news-bounces@chrispy.net [mailto:ctpp-news-bounces@chrispy.net] On Behalf Of Roth, Nate
Sent: Monday, October 15, 2012 5:13 PM
To: ctpp-news@ryoko.chrispy.net
Subject: Re: [CTPP] Regional and Statewide Census Geodatabases
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
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
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