Re: [CTPP] Regional and Statewide Census Geodatabases

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(a)lcog.org From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces@chrispy.net] On Behalf Of Roth, Nate Sent: Monday, October 15, 2012 5:13 PM To: ctpp-news(a)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 neroth@ucdavis.edu<mailto:neroth@ucdavis.edu> 530-754-6212 From: Roth, Nate Sent: Wednesday, August 01, 2012 10:40 AM To: &#39;ctpp-news(a)ryoko.chrispy.net&#39; 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<mailto: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