{"id":212,"date":"2014-03-13T17:03:47","date_gmt":"2014-03-13T21:03:47","guid":{"rendered":"http:\/\/brian.digitalmaddox.com\/blog\/?p=212"},"modified":"2014-07-08T16:45:19","modified_gmt":"2014-07-08T20:45:19","slug":"using-free-geospatial-tools-and-data-part-8-4-us-census-tiger-data-state-files-part-2","status":"publish","type":"post","link":"https:\/\/brian.digitalmaddox.com\/blog\/?p=212","title":{"rendered":"Using Free Geospatial Tools and Data Part 8.4: US Census TIGER Data State Files Part 2"},"content":{"rendered":"

Now we move on to the rest of the Census data that I’m discussing for this series: ROADS,\u00a0LINEARWATER, AREAWATER, and AREALM. \u00a0If you have not already, go ahead and use lftp<\/em> to mirror these directories. \u00a0Keep in mind that ROADS and LINEARWATER are several gigabytes in size so make sure you have enough room to stage them.<\/p>\n

We will start with the ROADS data. \u00a0By now you know how to download the data and stage it so we will skip that part. \u00a0When you are done you will find all of the following files:<\/p>\n

[bmaddox@girls ROADS]$ ls\r\ntl_2013_01001_roads.zip tl_2013_19039_roads.zip tl_2013_30037_roads.zip tl_2013_46127_roads.zip\r\ntl_2013_01003_roads.zip tl_2013_19041_roads.zip tl_2013_30039_roads.zip tl_2013_46129_roads.zip\r\ntl_2013_01005_roads.zip tl_2013_19043_roads.zip tl_2013_30041_roads.zip tl_2013_46135_roads.zip\r\ntl_2013_01007_roads.zip tl_2013_19045_roads.zip tl_2013_30043_roads.zip tl_2013_46137_roads.zip\r\ntl_2013_01009_roads.zip tl_2013_19047_roads.zip tl_2013_30045_roads.zip tl_2013_47001_roads.zip\r\n...\r\ntl_2013_19031_roads.zip tl_2013_30029_roads.zip tl_2013_46119_roads.zip tl_2013_72153_roads.zip\r\ntl_2013_19033_roads.zip tl_2013_30031_roads.zip tl_2013_46121_roads.zip tl_2013_78010_roads.zip\r\ntl_2013_19035_roads.zip tl_2013_30033_roads.zip tl_2013_46123_roads.zip tl_2013_78020_roads.zip\r\ntl_2013_19037_roads.zip tl_2013_30035_roads.zip tl_2013_46125_roads.zip tl_2013_78030_roads.zip\r\n[bmaddox@girls ROADS]$ \r\n3232 files<\/pre>\n
First thing to do now is make sure all of the files are not corrupt. \u00a0Sometimes downloads can be silently corrupted (although I have found lftp<\/em> seems to be more reliable than things I have used in the past). \u00a0The following command will help identify any issues with the files:<\/p>\n
[bmaddox@girls ROADS]$ for foo in *.zip; do unzip -tq $foo; done |grep -v \"No errors\"\r\n[bmaddox@girls ROADS]$<\/pre>\n
If you get output like the above, your zip files are at least correct. \u00a0The next thing now is to run the following script, called makestates.sh<\/em>, to create directories for all of the US states and territories and move the files into them. \u00a0It is simple, but it works \ud83d\ude42<\/p>\n
makestates.sh<\/strong><\/p>\n
#!\/bin\/sh\r\necho \"Making state directories.\"\r\nmkdir 01_Alabama\r\nmkdir 02_Alaska\r\nmkdir 04_Arizona\r\nmkdir 05_Arkansas\r\nmkdir 06_California\r\nmkdir 08_Colorado\r\nmkdir 09_Connecticut\r\nmkdir 10_Delaware\r\nmkdir 11_District_of_Columbia\r\nmkdir 12_Florida\r\nmkdir 13_Georgia\r\nmkdir 15_Hawaii\r\nmkdir 16_Idaho\r\nmkdir 17_Illinois\r\nmkdir 18_Indiana\r\nmkdir 19_Iowa\r\nmkdir 20_Kansas\r\nmkdir 21_Kentucky\r\nmkdir 22_Louisiana\r\nmkdir 23_Maine\r\nmkdir 24_Maryland\r\nmkdir 25_Massachusetts\r\nmkdir 26_Michigan\r\nmkdir 27_Minnesota\r\nmkdir 28_Mississippi\r\nmkdir 29_Missouri\r\nmkdir 30_Montana\r\nmkdir 31_Nebraska\r\nmkdir 32_Nevada\r\nmkdir 33_New_Hampshire\r\nmkdir 34_New_Jersey\r\nmkdir 35_New_Mexico\r\nmkdir 36_New_York\r\nmkdir 37_North_Carolina\r\nmkdir 38_North_Dakota\r\nmkdir 39_Ohio\r\nmkdir 40_Oklahoma\r\nmkdir 41_Oregon\r\nmkdir 42_Pennsylvania\r\nmkdir 44_Rhode_Island\r\nmkdir 45_South_Carolina\r\nmkdir 46_South_Dakota\r\nmkdir 47_Tennessee\r\nmkdir 48_Texas\r\nmkdir 49_Utah\r\nmkdir 50_Vermont\r\nmkdir 51_Virginia\r\nmkdir 53_Washington\r\nmkdir 54_West_Virginia\r\nmkdir 55_Wisconsin\r\nmkdir 56_Wyoming\r\nmkdir 60_American_Samoa\r\nmkdir 64_Federated_States_of_Micronesia\r\nmkdir 66_Guam\r\nmkdir 68_Marshall_Islands\r\nmkdir 69_Commonwealth_of_the_Northern_Mariana_Islands\r\nmkdir 70_Palau\r\nmkdir 72_Puerto_Rico\r\nmkdir 74_US_Minor_Outlying_Islands\r\nmkdir 78_US_Virgin_Islands\r\necho \"Moving files to state directories\"\r\nmv tl_2013_01* 01_Alabama\r\nmv tl_2013_02* 02_Alaska\r\nmv tl_2013_04* 04_Arizona\r\nmv tl_2013_05* 05_Arkansas\r\nmv tl_2013_06* 06_California\r\nmv tl_2013_08* 08_Colorado\r\nmv tl_2013_09* 09_Connecticut\r\nmv tl_2013_10* 10_Delaware\r\nmv tl_2013_11* 11_District_of_Columbia\r\nmv tl_2013_12* 12_Florida\r\nmv tl_2013_13* 13_Georgia\r\nmv tl_2013_15* 15_Hawaii\r\nmv tl_2013_16* 16_Idaho\r\nmv tl_2013_17* 17_Illinois\r\nmv tl_2013_18* 18_Indiana\r\nmv tl_2013_19* 19_Iowa\r\nmv tl_2013_20* 20_Kansas\r\nmv tl_2013_21* 21_Kentucky\r\nmv tl_2013_22* 22_Louisiana\r\nmv tl_2013_23* 23_Maine\r\nmv tl_2013_24* 24_Maryland\r\nmv tl_2013_25* 25_Massachusetts\r\nmv tl_2013_26* 26_Michigan\r\nmv tl_2013_27* 27_Minnesota\r\nmv tl_2013_28* 28_Mississippi\r\nmv tl_2013_29* 29_Missouri\r\nmv tl_2013_30* 30_Montana\r\nmv tl_2013_31* 31_Nebraska\r\nmv tl_2013_32* 32_Nevada\r\nmv tl_2013_33* 33_New_Hampshire\r\nmv tl_2013_34* 34_New_Jersey\r\nmv tl_2013_35* 35_New_Mexico\r\nmv tl_2013_36* 36_New_York\r\nmv tl_2013_37* 37_North_Carolina\r\nmv tl_2013_38* 38_North_Dakota\r\nmv tl_2013_39* 39_Ohio\r\nmv tl_2013_40* 40_Oklahoma\r\nmv tl_2013_41* 41_Oregon\r\nmv tl_2013_42* 42_Pennsylvania\r\nmv tl_2013_44* 44_Rhode_Island\r\nmv tl_2013_45* 45_South_Carolina\r\nmv tl_2013_46* 46_South_Dakota\r\nmv tl_2013_47* 47_Tennessee\r\nmv tl_2013_48* 48_Texas\r\nmv tl_2013_49* 49_Utah\r\nmv tl_2013_50* 50_Vermont\r\nmv tl_2013_51* 51_Virginia\r\nmv tl_2013_53* 53_Washington\r\nmv tl_2013_54* 54_West_Virginia\r\nmv tl_2013_55* 55_Wisconsin\r\nmv tl_2013_56* 56_Wyoming\r\nmv tl_2013_60* 60_American_Samoa\r\nmv tl_2013_64* 64_Federated_States_of_Micronesia\r\nmv tl_2013_66* 66_Guam\r\nmv tl_2013_68* 68_Marshall_Islands\r\nmv tl_2013_69* 69_Commonwealth_of_the_Northern_Mariana_Islands\r\nmv tl_2013_70* 70_Palau\r\nmv tl_2013_72* 72_Puerto_Rico\r\nmv tl_2013_74* 74_US_Minor_Outlying_Islands\r\nmv tl_2013_78* 78_US_Virgin_Islands<\/pre>\n
Running it will give the following output:<\/p>\n
[bmaddox@girls ROADS]$ ~\/bin\/makestates.sh \r\nMaking state directories.\r\nMoving files to state directories\r\nmv: cannot stat \u2018tl_2013_64*\u2019: No such file or directory\r\nmv: cannot stat \u2018tl_2013_68*\u2019: No such file or directory\r\nmv: cannot stat \u2018tl_2013_70*\u2019: No such file or directory\r\nmv: cannot stat \u2018tl_2013_74*\u2019: No such file or directory\r\n[bmaddox@girls ROADS]$<\/pre>\n
For example purposes, change to the 01_Alabama directory now. \u00a0We will go through how to convert one state to a single Shapefile and then leave the rest for you to do on your own \ud83d\ude42 \u00a0First we will use the doogr.sh<\/em> script that I mentioned last week. \u00a0Here it is again:<\/p>\n
doogr.sh<\/strong><\/p>\n
#!\/bin\/bash\r\n\r\n# Script to automatically create state-based shapefiles using ogr2ogr\r\n# Grab the name of the output shapefile\r\nshapefilename=$1\r\n\r\n# Now grab the name of the initial input file\r\nfirstfile=$2\r\n\r\n# make the initial state file\r\necho \"Creating initial state shapefile $shapefilename\"\r\nogr2ogr $shapefilename $firstfile\r\n\r\n# Grab the basename of the firstfiles\r\nfirstfilebase=`basename $firstfile .shp`\r\n\r\n# Grab the basename of the shapefile for ogr2ogr update\r\nshapefilenamebase=`basename $shapefilename .shp`\r\n\r\n# Delete the first files\r\necho \"Now deleting the initial shapefiles to avoid duplication\"\r\nrm $firstfilebase.*\r\n#ls $firstfilebase.*\r\n\r\n# Now make the rest of the state shape files\r\necho \"Merging the rest of the files into the main shapefile\"\r\nfor foo in tl*.shp; do\r\n ogr2ogr -update -append $shapefilename $foo -nln $shapefilenamebase\r\ndone<\/pre>\n
The following commands show how to convert all of the county-level Shapefiles in the 01_Alabama into a single state-level Shapefile.<\/p>\n
[bmaddox@girls 01_Alabama]$ doogr.sh 01_Alabama_Roads.shp tl_2013_01001_roads.shp<\/strong>\r\nCreating initial state shapefile 01_Alabama_Roads.shp\r\nNow deleting the initial shapefiles to avoid duplication\r\nMerging the rest of the files into the main shapefile\r\n[bmaddox@girls 01_Alabama]$ \\rm tl*<\/strong>\r\n[bmaddox@girls 01_Alabama]$ ls -l<\/strong>\r\ntotal 186472\r\n-rw-rw-r-- 1 bmaddox bmaddox 53279870 Mar 3 10:34 01_Alabama_Roads.dbf\r\n-rw-rw-r-- 1 bmaddox bmaddox 165 Mar 3 10:34 01_Alabama_Roads.prj\r\n-rw-rw-r-- 1 bmaddox bmaddox 134344780 Mar 3 10:34 01_Alabama_Roads.shp\r\n-rw-rw-r-- 1 bmaddox bmaddox 3304268 Mar 3 10:34 01_Alabama_Roads.shx\r\n[bmaddox@girls 01_Alabama]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 01_Alabama_Roads.shp US_Roads |psql -d Census_2013<\/strong>\r\nShapefile type: Arc\r\nPostgis type: MULTILINESTRING[2]\r\nSET\r\nSET\r\nBEGIN\r\nNOTICE: CREATE TABLE will create implicit sequence \"us_roads_gid_seq\" for serial column \"us_roads.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_roads_pkey\" for table \"us_roads\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n-------------------------------------------------------------\r\npublic.us_roads.geom SRID:4269 TYPE:MULTILINESTRING DIMS:2\r\n(1 row)\r\n\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls 01_Alabama]$<\/pre>\n
Now it is your turn to do the rest of the directories. \u00a0Note, however, that for the others, you will have to slightly change your shp2pgsql command to append instead of insert, as the example below shows:<\/p>\n
shp2pgsql -s 4269 -a -D -WLATIN1 02_Alaska_Roads.shp US_Roads |psql -d Census_2013<\/pre>\n
You will need to use the -a command to APPEND instead of INSERT and CREATE into the database. \u00a0Now move on and do the LINEARWATER and AREAWATER data the same way. \u00a0The first state you load use the -c -D -I<\/em> options, the rest the -a -D<\/em> ones. \u00a0As a reminder, as I write these posts, I am uploading the files to my website at\u00a0https:\/\/brian.digitalmaddox.com\/blog\/?page_id=202<\/a>.<\/p>\n
Now move on to the AREALM directory to process the area landmark files. \u00a0Mirrored, the directory will have the following files:<\/p>\n
bmaddox@girls AREALM]$ ls\r\ntl_2013_01_arealm.zip tl_2013_18_arealm.zip tl_2013_32_arealm.zip tl_2013_47_arealm.zip\r\ntl_2013_02_arealm.zip tl_2013_19_arealm.zip tl_2013_33_arealm.zip tl_2013_48_arealm.zip\r\ntl_2013_04_arealm.zip tl_2013_20_arealm.zip tl_2013_34_arealm.zip tl_2013_49_arealm.zip\r\ntl_2013_05_arealm.zip tl_2013_21_arealm.zip tl_2013_35_arealm.zip tl_2013_50_arealm.zip\r\ntl_2013_06_arealm.zip tl_2013_22_arealm.zip tl_2013_36_arealm.zip tl_2013_51_arealm.zip\r\ntl_2013_08_arealm.zip tl_2013_23_arealm.zip tl_2013_37_arealm.zip tl_2013_53_arealm.zip\r\ntl_2013_09_arealm.zip tl_2013_24_arealm.zip tl_2013_38_arealm.zip tl_2013_54_arealm.zip\r\ntl_2013_10_arealm.zip tl_2013_25_arealm.zip tl_2013_39_arealm.zip tl_2013_55_arealm.zip\r\ntl_2013_11_arealm.zip tl_2013_26_arealm.zip tl_2013_40_arealm.zip tl_2013_56_arealm.zip\r\ntl_2013_12_arealm.zip tl_2013_27_arealm.zip tl_2013_41_arealm.zip tl_2013_60_arealm.zip\r\ntl_2013_13_arealm.zip tl_2013_28_arealm.zip tl_2013_42_arealm.zip tl_2013_66_arealm.zip\r\ntl_2013_15_arealm.zip tl_2013_29_arealm.zip tl_2013_44_arealm.zip tl_2013_69_arealm.zip\r\ntl_2013_16_arealm.zip tl_2013_30_arealm.zip tl_2013_45_arealm.zip tl_2013_72_arealm.zip\r\ntl_2013_17_arealm.zip tl_2013_31_arealm.zip tl_2013_46_arealm.zip tl_2013_78_arealm.zip<\/pre>\n
Check the files to make sure they are not corrupt:<\/p>\n
for foo in *.zip; do unzip -tq $foo; done |grep -v \"No errors\"<\/pre>\n
Unzip all of the files:<\/p>\n
for foo in *.zip; do unzip $foo; done<\/pre>\n
Use doogr.sh to merge them (it can be used to make national-level files from state-level ones as well:<\/p>\n
[bmaddox@girls AREALM]$ doogr.sh AreaLM.shp tl_2013_01_arealm.shp\r\nCreating initial state shapefile AreaLM.shp\r\nNow deleting the initial shapefiles to avoid duplication\r\nMerging the rest of the files into the main shapefile<\/pre>\n
Finally, add the national-level file to PostGIS:<\/p>\n
[bmaddox@girls AREALM]$ shp2pgsql -s 4269 -c -D -I AreaLM.shp US_Area_Landmarks |psql -d Census_2013<\/strong>\r\nShapefile type: Polygon\r\nPostgis type: MULTIPOLYGON[2]\r\nSET\r\nSET\r\nBEGIN\r\nNOTICE: CREATE TABLE will create implicit sequence \"us_area_landmarks_gid_seq\" for serial column \"us_area_landmarks.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_area_landmarks_pkey\" for table \"us_area_landmarks\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n-------------------------------------------------------------------\r\npublic.us_area_landmarks.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\n\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls AREALM]$<\/pre>\n
With this, you should be able to finish importing all of the Shapefile data you want. \u00a0I will be uploading the files I have processed for this round up on the website soon. \u00a0In the meantime, happy GISing!<\/p>\n","protected":false},"excerpt":{"rendered":"
Now we move on to the rest of the Census data that I’m discussing for this series: ROADS,\u00a0LINEARWATER, AREAWATER, and AREALM. \u00a0If you have not already, go ahead and use lftp to mirror these directories. \u00a0Keep in mind that ROADS … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"class_list":["post-212","post","type-post","status-publish","format-standard","hentry","category-gis"],"_links":{"self":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/212","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=212"}],"version-history":[{"count":6,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/212\/revisions"}],"predecessor-version":[{"id":218,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/212\/revisions\/218"}],"wp:attachment":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=212"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=212"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=212"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}