Now we move on to the rest of the Census data that I’m discussing for this series: ROADS, LINEARWATER, AREAWATER, and AREALM. If you have not already, go ahead and use lftp to mirror these directories. Keep in mind that ROADS and LINEARWATER are several gigabytes in size so make sure you have enough room to stage them.
We will start with the ROADS data. By now you know how to download the data and stage it so we will skip that part. When you are done you will find all of the following files:
[bmaddox@girls ROADS]$ ls tl_2013_01001_roads.zip tl_2013_19039_roads.zip tl_2013_30037_roads.zip tl_2013_46127_roads.zip tl_2013_01003_roads.zip tl_2013_19041_roads.zip tl_2013_30039_roads.zip tl_2013_46129_roads.zip tl_2013_01005_roads.zip tl_2013_19043_roads.zip tl_2013_30041_roads.zip tl_2013_46135_roads.zip tl_2013_01007_roads.zip tl_2013_19045_roads.zip tl_2013_30043_roads.zip tl_2013_46137_roads.zip tl_2013_01009_roads.zip tl_2013_19047_roads.zip tl_2013_30045_roads.zip tl_2013_47001_roads.zip ... tl_2013_19031_roads.zip tl_2013_30029_roads.zip tl_2013_46119_roads.zip tl_2013_72153_roads.zip tl_2013_19033_roads.zip tl_2013_30031_roads.zip tl_2013_46121_roads.zip tl_2013_78010_roads.zip tl_2013_19035_roads.zip tl_2013_30033_roads.zip tl_2013_46123_roads.zip tl_2013_78020_roads.zip tl_2013_19037_roads.zip tl_2013_30035_roads.zip tl_2013_46125_roads.zip tl_2013_78030_roads.zip [bmaddox@girls ROADS]$ 3232 files
First thing to do now is make sure all of the files are not corrupt. Sometimes downloads can be silently corrupted (although I have found lftp seems to be more reliable than things I have used in the past). The following command will help identify any issues with the files:
[bmaddox@girls ROADS]$ for foo in *.zip; do unzip -tq $foo; done |grep -v "No errors" [bmaddox@girls ROADS]$
If you get output like the above, your zip files are at least correct. The next thing now is to run the following script, called makestates.sh, to create directories for all of the US states and territories and move the files into them. It is simple, but it works 🙂
makestates.sh
#!/bin/sh echo "Making state directories." mkdir 01_Alabama mkdir 02_Alaska mkdir 04_Arizona mkdir 05_Arkansas mkdir 06_California mkdir 08_Colorado mkdir 09_Connecticut mkdir 10_Delaware mkdir 11_District_of_Columbia mkdir 12_Florida mkdir 13_Georgia mkdir 15_Hawaii mkdir 16_Idaho mkdir 17_Illinois mkdir 18_Indiana mkdir 19_Iowa mkdir 20_Kansas mkdir 21_Kentucky mkdir 22_Louisiana mkdir 23_Maine mkdir 24_Maryland mkdir 25_Massachusetts mkdir 26_Michigan mkdir 27_Minnesota mkdir 28_Mississippi mkdir 29_Missouri mkdir 30_Montana mkdir 31_Nebraska mkdir 32_Nevada mkdir 33_New_Hampshire mkdir 34_New_Jersey mkdir 35_New_Mexico mkdir 36_New_York mkdir 37_North_Carolina mkdir 38_North_Dakota mkdir 39_Ohio mkdir 40_Oklahoma mkdir 41_Oregon mkdir 42_Pennsylvania mkdir 44_Rhode_Island mkdir 45_South_Carolina mkdir 46_South_Dakota mkdir 47_Tennessee mkdir 48_Texas mkdir 49_Utah mkdir 50_Vermont mkdir 51_Virginia mkdir 53_Washington mkdir 54_West_Virginia mkdir 55_Wisconsin mkdir 56_Wyoming mkdir 60_American_Samoa mkdir 64_Federated_States_of_Micronesia mkdir 66_Guam mkdir 68_Marshall_Islands mkdir 69_Commonwealth_of_the_Northern_Mariana_Islands mkdir 70_Palau mkdir 72_Puerto_Rico mkdir 74_US_Minor_Outlying_Islands mkdir 78_US_Virgin_Islands echo "Moving files to state directories" mv tl_2013_01* 01_Alabama mv tl_2013_02* 02_Alaska mv tl_2013_04* 04_Arizona mv tl_2013_05* 05_Arkansas mv tl_2013_06* 06_California mv tl_2013_08* 08_Colorado mv tl_2013_09* 09_Connecticut mv tl_2013_10* 10_Delaware mv tl_2013_11* 11_District_of_Columbia mv tl_2013_12* 12_Florida mv tl_2013_13* 13_Georgia mv tl_2013_15* 15_Hawaii mv tl_2013_16* 16_Idaho mv tl_2013_17* 17_Illinois mv tl_2013_18* 18_Indiana mv tl_2013_19* 19_Iowa mv tl_2013_20* 20_Kansas mv tl_2013_21* 21_Kentucky mv tl_2013_22* 22_Louisiana mv tl_2013_23* 23_Maine mv tl_2013_24* 24_Maryland mv tl_2013_25* 25_Massachusetts mv tl_2013_26* 26_Michigan mv tl_2013_27* 27_Minnesota mv tl_2013_28* 28_Mississippi mv tl_2013_29* 29_Missouri mv tl_2013_30* 30_Montana mv tl_2013_31* 31_Nebraska mv tl_2013_32* 32_Nevada mv tl_2013_33* 33_New_Hampshire mv tl_2013_34* 34_New_Jersey mv tl_2013_35* 35_New_Mexico mv tl_2013_36* 36_New_York mv tl_2013_37* 37_North_Carolina mv tl_2013_38* 38_North_Dakota mv tl_2013_39* 39_Ohio mv tl_2013_40* 40_Oklahoma mv tl_2013_41* 41_Oregon mv tl_2013_42* 42_Pennsylvania mv tl_2013_44* 44_Rhode_Island mv tl_2013_45* 45_South_Carolina mv tl_2013_46* 46_South_Dakota mv tl_2013_47* 47_Tennessee mv tl_2013_48* 48_Texas mv tl_2013_49* 49_Utah mv tl_2013_50* 50_Vermont mv tl_2013_51* 51_Virginia mv tl_2013_53* 53_Washington mv tl_2013_54* 54_West_Virginia mv tl_2013_55* 55_Wisconsin mv tl_2013_56* 56_Wyoming mv tl_2013_60* 60_American_Samoa mv tl_2013_64* 64_Federated_States_of_Micronesia mv tl_2013_66* 66_Guam mv tl_2013_68* 68_Marshall_Islands mv tl_2013_69* 69_Commonwealth_of_the_Northern_Mariana_Islands mv tl_2013_70* 70_Palau mv tl_2013_72* 72_Puerto_Rico mv tl_2013_74* 74_US_Minor_Outlying_Islands mv tl_2013_78* 78_US_Virgin_Islands
Running it will give the following output:
[bmaddox@girls ROADS]$ ~/bin/makestates.sh Making state directories. Moving files to state directories mv: cannot stat ‘tl_2013_64*’: No such file or directory mv: cannot stat ‘tl_2013_68*’: No such file or directory mv: cannot stat ‘tl_2013_70*’: No such file or directory mv: cannot stat ‘tl_2013_74*’: No such file or directory [bmaddox@girls ROADS]$
For example purposes, change to the 01_Alabama directory now. We will go through how to convert one state to a single Shapefile and then leave the rest for you to do on your own 🙂 First we will use the doogr.sh script that I mentioned last week. Here it is again:
doogr.sh
#!/bin/bash # Script to automatically create state-based shapefiles using ogr2ogr # Grab the name of the output shapefile shapefilename=$1 # Now grab the name of the initial input file firstfile=$2 # make the initial state file echo "Creating initial state shapefile $shapefilename" ogr2ogr $shapefilename $firstfile # Grab the basename of the firstfiles firstfilebase=`basename $firstfile .shp` # Grab the basename of the shapefile for ogr2ogr update shapefilenamebase=`basename $shapefilename .shp` # Delete the first files echo "Now deleting the initial shapefiles to avoid duplication" rm $firstfilebase.* #ls $firstfilebase.* # Now make the rest of the state shape files echo "Merging the rest of the files into the main shapefile" for foo in tl*.shp; do ogr2ogr -update -append $shapefilename $foo -nln $shapefilenamebase done
The following commands show how to convert all of the county-level Shapefiles in the 01_Alabama into a single state-level Shapefile.
[bmaddox@girls 01_Alabama]$ doogr.sh 01_Alabama_Roads.shp tl_2013_01001_roads.shp Creating initial state shapefile 01_Alabama_Roads.shp Now deleting the initial shapefiles to avoid duplication Merging the rest of the files into the main shapefile [bmaddox@girls 01_Alabama]$ \rm tl* [bmaddox@girls 01_Alabama]$ ls -l total 186472 -rw-rw-r-- 1 bmaddox bmaddox 53279870 Mar 3 10:34 01_Alabama_Roads.dbf -rw-rw-r-- 1 bmaddox bmaddox 165 Mar 3 10:34 01_Alabama_Roads.prj -rw-rw-r-- 1 bmaddox bmaddox 134344780 Mar 3 10:34 01_Alabama_Roads.shp -rw-rw-r-- 1 bmaddox bmaddox 3304268 Mar 3 10:34 01_Alabama_Roads.shx [bmaddox@girls 01_Alabama]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 01_Alabama_Roads.shp US_Roads |psql -d Census_2013 Shapefile type: Arc Postgis type: MULTILINESTRING[2] SET SET BEGIN NOTICE: CREATE TABLE will create implicit sequence "us_roads_gid_seq" for serial column "us_roads.gid" CREATE TABLE NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "us_roads_pkey" for table "us_roads" ALTER TABLE addgeometrycolumn ------------------------------------------------------------- public.us_roads.geom SRID:4269 TYPE:MULTILINESTRING DIMS:2 (1 row) CREATE INDEX COMMIT [bmaddox@girls 01_Alabama]$
Now it is your turn to do the rest of the directories. Note, however, that for the others, you will have to slightly change your shp2pgsql command to append instead of insert, as the example below shows:
shp2pgsql -s 4269 -a -D -WLATIN1 02_Alaska_Roads.shp US_Roads |psql -d Census_2013
You will need to use the -a command to APPEND instead of INSERT and CREATE into the database. Now move on and do the LINEARWATER and AREAWATER data the same way. The first state you load use the -c -D -I options, the rest the -a -D ones. As a reminder, as I write these posts, I am uploading the files to my website at https://brian.digitalmaddox.com/blog/?page_id=202.
Now move on to the AREALM directory to process the area landmark files. Mirrored, the directory will have the following files:
bmaddox@girls AREALM]$ ls tl_2013_01_arealm.zip tl_2013_18_arealm.zip tl_2013_32_arealm.zip tl_2013_47_arealm.zip tl_2013_02_arealm.zip tl_2013_19_arealm.zip tl_2013_33_arealm.zip tl_2013_48_arealm.zip tl_2013_04_arealm.zip tl_2013_20_arealm.zip tl_2013_34_arealm.zip tl_2013_49_arealm.zip tl_2013_05_arealm.zip tl_2013_21_arealm.zip tl_2013_35_arealm.zip tl_2013_50_arealm.zip tl_2013_06_arealm.zip tl_2013_22_arealm.zip tl_2013_36_arealm.zip tl_2013_51_arealm.zip tl_2013_08_arealm.zip tl_2013_23_arealm.zip tl_2013_37_arealm.zip tl_2013_53_arealm.zip tl_2013_09_arealm.zip tl_2013_24_arealm.zip tl_2013_38_arealm.zip tl_2013_54_arealm.zip tl_2013_10_arealm.zip tl_2013_25_arealm.zip tl_2013_39_arealm.zip tl_2013_55_arealm.zip tl_2013_11_arealm.zip tl_2013_26_arealm.zip tl_2013_40_arealm.zip tl_2013_56_arealm.zip tl_2013_12_arealm.zip tl_2013_27_arealm.zip tl_2013_41_arealm.zip tl_2013_60_arealm.zip tl_2013_13_arealm.zip tl_2013_28_arealm.zip tl_2013_42_arealm.zip tl_2013_66_arealm.zip tl_2013_15_arealm.zip tl_2013_29_arealm.zip tl_2013_44_arealm.zip tl_2013_69_arealm.zip tl_2013_16_arealm.zip tl_2013_30_arealm.zip tl_2013_45_arealm.zip tl_2013_72_arealm.zip tl_2013_17_arealm.zip tl_2013_31_arealm.zip tl_2013_46_arealm.zip tl_2013_78_arealm.zip
Check the files to make sure they are not corrupt:
for foo in *.zip; do unzip -tq $foo; done |grep -v "No errors"
Unzip all of the files:
for foo in *.zip; do unzip $foo; done
Use doogr.sh to merge them (it can be used to make national-level files from state-level ones as well:
[bmaddox@girls AREALM]$ doogr.sh AreaLM.shp tl_2013_01_arealm.shp Creating initial state shapefile AreaLM.shp Now deleting the initial shapefiles to avoid duplication Merging the rest of the files into the main shapefile
Finally, add the national-level file to PostGIS:
[bmaddox@girls AREALM]$ shp2pgsql -s 4269 -c -D -I AreaLM.shp US_Area_Landmarks |psql -d Census_2013 Shapefile type: Polygon Postgis type: MULTIPOLYGON[2] SET SET BEGIN NOTICE: CREATE TABLE will create implicit sequence "us_area_landmarks_gid_seq" for serial column "us_area_landmarks.gid" CREATE TABLE NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "us_area_landmarks_pkey" for table "us_area_landmarks" ALTER TABLE addgeometrycolumn ------------------------------------------------------------------- public.us_area_landmarks.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 (1 row) CREATE INDEX COMMIT [bmaddox@girls AREALM]$
With this, you should be able to finish importing all of the Shapefile data you want. I will be uploading the files I have processed for this round up on the website soon. In the meantime, happy GISing!