Importing Census 2000

1. Importing Census 2000 Andy Richardson Caltrans District 5 GIS Coordinator Andy.Richardson@dot.ca.gov

2. Caltrans District 5 GIS Overview Acquiring data Importing tables Importing geography Linking geography and tables

3. Caltrans District 5 GIS Download Sites Census www.census.gov ESRI www.esri.com/data/download/census2000_tigerline/index.html California Spatial Information Library (CaSIL) www.gis.ca.gov/data_index.epl Geography only

4. Caltrans District 5 GIS SF1, SF2, SF3 – From Census www.census.gov/main/www/cen2000.html www.census.gov/Press-Release/www/2001/sumfile1.html Link to FTP site, FTP instructions Technical documentation (large PDF files) www.census.gov/support/SF1ASCII.html Instructions for importing ASCII Census files Access, SAS, FoxPro, dBaseIV Full set of tables

5. Caltrans District 5 GIS SF1 – From ESRI www.esri.com/data/download/census2000_tigerline/index.html Only SF1 “Demographics” available Not full set of tables But conveniently broken down by geography Blocks, Block Groups, Tracts, County, State, etc. Can download by county, or entire state Geography also available….

6. Caltrans District 5 GIS Geography – From Census www.census.gov/geo/www/tiger/tiger2002/tgr2002.html Download TIGER format Requires import to ArcInfo coverage format ArcInfo “TIGERARC” command ArcToolbox “TIGER to Coverage Wizard” (requires ArcInfo) ArcView 3.x 3rd party extension ($$) Results are all TIGER Geography Block, roads, hydro, etc.

7. Caltrans District 5 GIS Geography – From ESRI www.esri.com/data/download/census2000_tigerline/index.html Already in shapefile format Already broken down into separate geographies (separate shapefiles) Available as statewide seamless

8. Caltrans District 5 GIS Geography – CaSIL www.gis.ca.gov/data_index.epl Census blocks only Additional processing of TIGER by Forestry Merged counties to statewide Dissolved to blocks (removed roads, hydro, etc.) Resolved some coding issues

9. Caltrans District 5 GIS Importing Geography Chose CaSIL/CDF dataset Trust the additional processing expertise Likely to be used by many state agencies Import from .e00 to Arc/Info Coverage Project from Albers NAD27 to Albers NAD83 Removed all 100% water polys Dissolved to Block Groups, Tracts

10. Caltrans District 5 GIS Importing Tables - Overview Download & unzip Use Access template Import geography table & subset to D5 counties Link all other tables Relate to D5 Geography & export to new Access database

11. Caltrans District 5 GIS Importing Tables - Downloading Chose raw files from Census Full set of tables SF1, SF2, SF3 available Downloaded from Census site Advantages of a fast connection Unzipped multiple files WinZip’s command line option – batch processing!

12. Caltrans District 5 GIS Importing Tables - Access Used Access template provided by Census Tables are separated into multiple “adjacent” files 40 for SF1, 100’s for SF2, 77 for SF3 Logrecno is common field used to link them all Template has import specs for each file Census provides Access 2000, Caltrans uses 97 Import specs don’t go from 2000 to 97 Solution: take it home

13. Caltrans District 5 GIS Importing Tables – Import Geo Geo table defines the geography Block, block group, tract, county, state, etc. Imported using import spec Fixed field format Query to create new table holding just 5 D5 counties, plus Santa Clara Will be used to subset all other tables

14. Caltrans District 5 GIS Importing tables – Link all others Tables for California too large to import, plus takes hours before fail Solution: link to external data files Very fast, external tables are smaller Wrote VBA script to automate Can still take advantage of import specs

15. Caltrans District 5 GIS

16. Caltrans District 5 GIS Importing Tables – Subset & Export Wrote VBA script to: Relate linked table to D5 geo table Relate on Logrecno Subset to D5 Export to new Access database Problem: 6 counties too large for Access 97 database (1GB limitation) Solution: Separate into P (pop by block), PCT (pop by tract), H (housing) databases

17. Caltrans District 5 GIS

18. Caltrans District 5 GIS Link Geography & Tables Geography Concatenate State (06), County (079), Tract, Block to ID block Concatenate State (06), County (079), Tract, BlockGroup to ID block group Concatenate State (06), County (079), Tract to ID tract

19. Caltrans District 5 GIS Link Geography & Tables Access Extract Logrecno, Sumlev, State, County, Tract, Block Group, Block Concatenate block, block group, tract to create corresponding unique ID Delete records where PartFlag <> “” (partial blocks?) Join to shapefile on concatenated fields Copy over Logrecno Logrecno is common field in all census tables With Logrecno, shapefile can be linked to any census table

20. Caltrans District 5 GIS Using the Data From GIS In Arcview can create DSNs to Access tables, then join any table to geography shapefile Use Logrecno as join field ArcMap can link directly to table From Access Any user can open empty Access database, then link to tables in census databases Library storage Ability to join from GIS, link from Access allows write-protected Access databases to be stored in data library

21. Caltrans District 5 GIS Limitations Census site very large, and difficult to find relevant information Documentation (PDFs) very large (SF1 over 600 pages, SF2 over 300, SF3 over 1000), but have omitted useful information (like what’s new in Advance & Final Nationals) Different SUMLEVs that refer to the same geography (080, 140, 158, 511 are all tracts?)

22. Caltrans District 5 GIS Unresolved Questions What’s different in Advance National, Final National? Updated/new data for existing geography? Different geography? (seems to be this) Not documented? What’s the best way to find specific geography? Tracts, block groups, blocks Sumlev is part of the answer?