{"id":155,"date":"2014-02-28T21:59:58","date_gmt":"2014-03-01T02:59:58","guid":{"rendered":"http:\/\/brian.digitalmaddox.com\/blog\/?p=155"},"modified":"2014-07-08T16:45:19","modified_gmt":"2014-07-08T20:45:19","slug":"using-free-geospatial-tools-and-data-part-8-2-us-census-tiger-data-national-files","status":"publish","type":"post","link":"https:\/\/brian.digitalmaddox.com\/blog\/?p=155","title":{"rendered":"Using Free Geospatial Tools and Data Part 8.2: US Census TIGER Data National Files"},"content":{"rendered":"

In Part 8.1, we went over how the Census ftp site was set up and what kind of data each directory has in it. We also loaded our first Census data, which were outlines of the US states and territories. In this installment, we will load all the national files on the Census FTP site. These are all individual files so will be easier to load. Next time we will look at using scripts to convert the rest of the county-based data into state-based datasets.<\/p>\n

We are not going to download everything on the Census website. Some of the data you will likely never need. However, once these articles are done you should be able to download and create tables from the other data directories should you decide you need them. After each load, I will show a screen shot of how the data table looks inside QGIS so you can make sure everything loaded properly.<\/p>\n

This is a rather lengthy post as it shows the commands you should type as well as the output so you can check that what you are doing works correctly. Be glad I do not sell advertising or this would be spread across many pages \ud83d\ude09<\/p>\n

For this set aside some time as the Census ftp site limits connection speeds to keep people from using up all of their bandwidth. Open a shell prompt, change to a directory where you will download the data, and run the command:<\/p>\n

lftp ftp:\/\/ftp2.census.gov\/geo\/tiger\/TIGER2013<\/strong><\/pre>\n
Type the command cls<\/em> and you will see this listing:<\/p>\n
lftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> cls<\/strong>\r\n2013-FolderNames-Defined.pdf AREAWATER\/ COUSUB\/ FACESMIL\/ PLACE\/ SLDL\/ UAC\/\r\nADDR\/ BG\/ CSA\/ FEATNAMES\/ POINTLM\/ SLDU\/ UNSD\/\r\nADDRFEAT\/ CBSA\/ EDGES\/ LINEARWATER\/ PRIMARYROADS\/ STATE\/ ZCTA5\/\r\nADDRFN\/ CD\/ ELSD\/ METDIV\/ PRISECROADS\/ SUBMCD\/\r\nAIANNH\/ CNECTA\/ ESTATE\/ MIL\/ PUMA\/ TABBLOCK\/\r\nAITS\/ COAST\/ FACES\/ NECTA\/ RAILS\/ TBG\/\r\nANRC\/ CONCITY\/ FACESAH\/ NECTADIV\/ ROADS\/ TRACT\/\r\nAREALM\/ COUNTY\/ FACESAL\/ OTHERID\/ SCSD\/ TTRACT\/\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013><\/pre>\n
Here we will download the datasets that come in single national-level files from the Census. Run the following commands inside lftp,<\/em> waiting for each to finish:<\/p>\n
lftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror COUNTY\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror AIANNH\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror AITS\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror ANRC\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror CBSA\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror CD\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror CNECTA\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror COAST\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror ESTATE\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror METDIV\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror MIL\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror NECTA\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror NECTADIV\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror PRIMARYROADS\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror RAILS\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror TBG\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror UAC\/<\/strong>\r\nlftp ftp2.census.gov:\/geo\/tiger\/TIGER2013> mirror ZCTA5\/<\/strong><\/pre>\n
This gives you a good number of files to add to your database for this part of the series. Type quit or hit Control-D to exit lftp.<\/em><\/p>\n
We will start with the files under COUNTY as this will give you the county outlines to match the state outlines you loaded during the last installment. Change directory (cd)<\/em> into the COUNTY directory and unzip the tl_2013_us_county.zip file:<\/p>\n
[bmaddox@girls COUNTY]$ unzip tl_2013_us_county.zip<\/strong> \r\nArchive: tl_2013_us_county.zip\r\ninflating: tl_2013_us_county.dbf \r\ninflating: tl_2013_us_county.prj \r\ninflating: tl_2013_us_county.shp \r\ninflating: tl_2013_us_county.shp.xml \r\ninflating: tl_2013_us_county.shx \r\n[bmaddox@girls COUNTY]$ ls -l<\/strong>\r\ntotal 188336\r\n-rwxrwxr-x 1 bmaddox bmaddox 948140 Aug 6 2013 tl_2013_us_county.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_county.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 118339660 Aug 6 2013 \r\ntl_2013_us_county.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 24960 Aug 6 2013 \r\ntl_2013_us_county.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 25972 Aug 6 2013 tl_2013_us_county.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 73500446 Aug 16 2013 \r\ntl_2013_us_county.zip\r\n[bmaddox@girls COUNTY]$<\/pre>\n
Now run the command:<\/p>\n
shp2pgsql -s 4269 -c -D -I -W LATIN1 tl_2013_us_county.shp county_outlines |psql -d Census_2013<\/strong><\/pre>\n
Your command will look similar to the following output. Here I am running the import commands on the same server that is hosting the database.<\/p>\n
[bmaddox@girls COUNTY]$ shp2pgsql -s 4269 -c -D -I -W LATIN1 tl_2013_us_county.shp county_outlines |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 \r\n\"county_outlines_gid_seq\" for serial column \"county_outlines.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \r\n\"county_outlines_pkey\" for table \"county_outlines\"\r\nALTER TABLE\r\naddgeometrycolumn \r\n-----------------------------------------------------------------\r\npublic.county_outlines.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 \r\n(1 row) \r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls COUNTY]$<\/pre>\n
Run QGIS and load the state table that you created in the last installment. Once that finishes, add the county_outlines layer. Once loaded, it should look like this screen shot:<\/p>\n
\"US<\/a>
US County Outlines in QGIS<\/p><\/div>\n
Now change to the AIANNH directory. This is the American Indian \/ Alaska Native \/ Native Hawaiian Areas dataset. This data contains information such as land and water area in each regional corporation. Run the following commands to unzip and load the data into PostGIS, substituting values specific to your setup:<\/p>\n
[bmaddox@girls AIANNH]$ unzip tl_2013_us_aiannh.zip<\/strong> \r\nArchive: tl_2013_us_aiannh.zip\r\ninflating: tl_2013_us_aiannh.dbf \r\ninflating: tl_2013_us_aiannh.prj \r\ninflating: tl_2013_us_aiannh.shp \r\ninflating: tl_2013_us_aiannh.shp.xml \r\ninflating: tl_2013_us_aiannh.shx \r\n[bmaddox@girls AIANNH]$ ls -l<\/strong>\r\ntotal 21576\r\n-rwxrwxr-x 1 bmaddox bmaddox 232620 Aug 6 2013 tl_2013_us_aiannh.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_aiannh.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 13362524 Aug 6 2013 tl_2013_us_aiannh.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 31978 Aug 6 2013 \r\ntl_2013_us_aiannh.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 6708 Aug 6 2013 tl_2013_us_aiannh.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 8447076 Aug 16 2013 tl_2013_us_aiannh.zip\r\n[bmaddox@girls AIANNH]$ shp2pgsql -s 4269 -c -D -I <\/strong>\r\ntl_2013_us_aiannh.shp US_Indian_Alaska_Hawaii_Native_Areas |psql -d <\/strong>\r\nCensus_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 \r\n\"us_indian_alaska_hawaii_native_areas_gid_seq\" for serial column \r\n\"us_indian_alaska_hawaii_native_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \r\n\"us_indian_alaska_hawaii_native_areas_pkey\" for table \r\n\"us_indian_alaska_hawaii_native_areas\"\r\nALTER TABLE\r\naddgeometrycolumn \r\n----------------------------------------------------------------------\r\n----------------\r\npublic.us_indian_alaska_hawaii_native_areas.geom SRID:4269 \r\nTYPE:MULTIPOLYGON DIMS:2 \r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls AIANNH]$<\/pre>\n
The loaded data should look similar to the screen shot below:<\/p>\n
\"AIANNH<\/a>
AIANNH Dataset<\/p><\/div>\n
Next change to the AITS directory. This is the American Indian Tribal Subdivision dataset. It contains information similar to the AIANNH dataset. Since the technique should be familiar now, here are the commands to run and their output. Again, substitute the specific values applicable to your setup.<\/p>\n
[bmaddox@girls census]$ cd AITS\/<\/strong>\r\n[bmaddox@girls AITS]$ ls<\/strong>\r\ntl_2013_us_aitsn.zip\r\n[bmaddox@girls AITS]$ unzip tl_2013_us_aitsn.zip<\/strong> \r\nArchive: tl_2013_us_aitsn.zip\r\ninflating: tl_2013_us_aitsn.dbf \r\ninflating: tl_2013_us_aitsn.prj \r\ninflating: tl_2013_us_aitsn.shp \r\ninflating: tl_2013_us_aitsn.shp.xml \r\ninflating: tl_2013_us_aitsn.shx \r\n[bmaddox@girls AITS]$ ls -l<\/strong>\r\ntotal 14900\r\n-rwxrwxr-x 1 bmaddox bmaddox 134814 Aug 6 2013 tl_2013_us_aitsn.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_aitsn.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 9291396 Aug 6 2013 tl_2013_us_aitsn.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 21736 Aug 6 2013 tl_2013_us_aitsn.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 3884 Aug 6 2013 tl_2013_us_aitsn.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 5794063 Aug 16 2013 tl_2013_us_aitsn.zip\r\n[bmaddox@girls AITS]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_aitsn.shp US_Indian_Tribal_Subdivisions |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_indian_tribal_subdivisions_gid_seq\" for serial column \"us_indian_tribal_subdivisions.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_indian_tribal_subdivisions_pkey\" for table \"us_indian_tribal_subdivisions\"\r\nALTER TABLE\r\naddgeometrycolumn \r\n-------------------------------------------------------------------------------\r\npublic.us_indian_tribal_subdivisions.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 \r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls AITS]$<\/pre>\n
The dataset should look like the following screen shot in QGIS:<\/p>\n
\"AITS<\/a>
AITS Dataset<\/p><\/div>\n
Next up is the Alaska Native Regional Corporations dataset. As with the other datasets, here are the commands you will use to load the data into PostGIS:<\/p>\n
[bmaddox@girls census]$ cd ANRC<\/strong>\r\n[bmaddox@girls ANRC]$ ls -l<\/strong>\r\ntotal 752\r\n-rwxrw-r-x 1 bmaddox bmaddox 768407 Aug 16 2013 tl_2013_02_anrc.zip\r\n[bmaddox@girls ANRC]$ unzip tl_2013_02_anrc.zip<\/strong> \r\nArchive: tl_2013_02_anrc.zip\r\ninflating: tl_2013_02_anrc.dbf \r\ninflating: tl_2013_02_anrc.prj \r\ninflating: tl_2013_02_anrc.shp \r\ninflating: tl_2013_02_anrc.shp.xml \r\ninflating: tl_2013_02_anrc.shx \r\n[bmaddox@girls ANRC]$ ls -l<\/strong>\r\ntotal 1896\r\n-rwxrwxr-x 1 bmaddox bmaddox 3890 Aug 6 2013 tl_2013_02_anrc.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_02_anrc.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 1131900 Aug 6 2013 tl_2013_02_anrc.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 21487 Aug 6 2013 tl_2013_02_anrc.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 196 Aug 6 2013 tl_2013_02_anrc.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 768407 Aug 16 2013 tl_2013_02_anrc.zip\r\n[bmaddox@girls ANRC]$ shp2pgsql -s 4269 -c -D -I tl_2013_02_anrc.shp US_Alaska_Native_Regional_Corporations |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_alaska_native_regional_corporations_gid_seq\" for serial column \"us_alaska_native_regional_corporations.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_alaska_native_regional_corporations_pkey\" for table \"us_alaska_native_regional_corporations\"\r\nALTER TABLE\r\naddgeometrycolumn ----------------------------------------------------------------------------------------\r\npublic.us_alaska_native_regional_corporations.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 \r\n(1 row)\r\n\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls ANRC]$<\/pre>\n
Here is the screenshot of how the data should look in QGIS:<\/p>\n
\"ANRC<\/a>
ANRC Dataset<\/p><\/div>\n
Next change to the CBSA directory. This contains the combined Metropolitan and Micropolitan statistical areas. As before, here are the commands and you will replace things with information specific to your setup:<\/p>\n
[bmaddox@girls census]$ cd CBSA\/<\/strong>\r\n[bmaddox@girls CBSA]$ ls -l<\/strong>\r\ntotal 28960\r\n-rwxrw-r-x 1 bmaddox bmaddox 29654427 Aug 16 2013 tl_2013_us_cbsa.zip\r\n[bmaddox@girls CBSA]$ unzip tl_2013_us_cbsa.zip<\/strong> \r\nArchive: tl_2013_us_cbsa.zip\r\ninflating: tl_2013_us_cbsa.dbf \r\ninflating: tl_2013_us_cbsa.prj \r\ninflating: tl_2013_us_cbsa.shp \r\ninflating: tl_2013_us_cbsa.shp.xml \r\ninflating: tl_2013_us_cbsa.shx \r\n[bmaddox@girls CBSA]$ ls -l<\/strong>\r\ntotal 75708\r\n-rwxrwxr-x 1 bmaddox bmaddox 254035 Aug 6 2013 tl_2013_us_cbsa.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_cbsa.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 47575788 Aug 6 2013 tl_2013_us_cbsa.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 17767 Aug 6 2013 tl_2013_us_cbsa.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 7532 Aug 6 2013 tl_2013_us_cbsa.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 29654427 Aug 16 2013 tl_2013_us_cbsa.zip\r\n[bmaddox@girls CBSA]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_cbsa.shp US_Metro_Micropolitan_Statistical_Areas |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_metro_micropolitan_statistical_areas_gid_seq\" for serial column \"us_metro_micropolitan_statistical_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_metro_micropolitan_statistical_areas_pkey\" for table \"us_metro_micropolitan_statistical_areas\"\r\nALTER TABLE\r\naddgeometrycolumn \r\n-----------------------------------------------------------------------------------------\r\npublic.us_metro_micropolitan_statistical_areas.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 \r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls CBSA]$<\/pre>\n
Here is how it looks in QGIS once loaded:<\/p>\n
\"CBSA<\/a>
CBSA in QGIS<\/p><\/div>\n
Now change to the CD directory. This dataset contains the 113th<\/sup> Congress Congressional districts.<\/p>\n
[bmaddox@girls census]$ ls CD<\/strong>\r\ntl_2013_us_cd113.zip\r\n[bmaddox@girls census]$ cd CD\/<\/strong>\r\n[bmaddox@girls CD]$ ls -l<\/strong>\r\ntotal 38528\r\n-rwxrw-r-x 1 bmaddox bmaddox 39448037 Aug 16 2013 tl_2013_us_cd113.zip\r\n[bmaddox@girls CD]$ unzip tl_2013_us_cd113.zip<\/strong> \r\nArchive: tl_2013_us_cd113.zip\r\ninflating: tl_2013_us_cd113.dbf \r\ninflating: tl_2013_us_cd113.prj \r\ninflating: tl_2013_us_cd113.shp \r\ninflating: tl_2013_us_cd113.shp.xml \r\ninflating: tl_2013_us_cd113.shx \r\n[bmaddox@girls CD]$ ls -l<\/strong>\r\ntotal 100132\r\n-rwxrwxr-x 1 bmaddox bmaddox 50146 Aug 6 2013 tl_2013_us_cd113.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_cd113.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 62997500 Aug 6 2013 tl_2013_us_cd113.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 19400 Aug 6 2013 tl_2013_us_cd113.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 3652 Aug 6 2013 tl_2013_us_cd113.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 39448037 Aug 16 2013 tl_2013_us_cd113.zip\r\n[bmaddox@girls CD]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_cd113.shp US_113_Congress_Districts |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_113_congress_districts_gid_seq\" for serial column \"us_113_congress_districts.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_113_congress_districts_pkey\" for table \"us_113_congress_districts\"\r\nALTER TABLE\r\naddgeometrycolumn \r\n---------------------------------------------------------------------------\r\npublic.us_113_congress_districts.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 \r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls CD]$<\/pre>\n
\"113th<\/a>
113th Congressional Districts<\/p><\/div>\n
Now we move to the CNECTA directory. \u00a0This dataset is the Combined New England City and Town Areas dataset.<\/p>\n
[bmaddox@girls census]$ ls CNECTA\/<\/strong>\r\ntl_2013_us_cnecta.zip\r\n[bmaddox@girls census]$ cd CNECTA\/<\/strong>\r\n[bmaddox@girls CNECTA]$ ls -l<\/strong>\r\ntotal 436\r\n-rwxrw-r-x 1 bmaddox bmaddox 442433 Aug 16 2013 tl_2013_us_cnecta.zip\r\n[bmaddox@girls CNECTA]$ unzip tl_2013_us_cnecta.zip<\/strong> \r\nArchive: tl_2013_us_cnecta.zip\r\ninflating: tl_2013_us_cnecta.dbf \r\ninflating: tl_2013_us_cnecta.prj \r\ninflating: tl_2013_us_cnecta.shp \r\ninflating: tl_2013_us_cnecta.shp.xml \r\ninflating: tl_2013_us_cnecta.shx \r\n[bmaddox@girls CNECTA]$ ls -l<\/strong>\r\ntotal 1128\r\n-rwxrwxr-x 1 bmaddox bmaddox 1944 Aug 6 2013 tl_2013_us_cnecta.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_cnecta.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 679436 Aug 6 2013 tl_2013_us_cnecta.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 16353 Aug 6 2013 tl_2013_us_cnecta.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 148 Aug 6 2013 tl_2013_us_cnecta.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 442433 Aug 16 2013 tl_2013_us_cnecta.zip\r\n[bmaddox@girls CNECTA]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_cnecta.shp US_Combined_New_England_City_Town_Areas |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_combined_new_england_city_town_areas_gid_seq\" for serial column \"us_combined_new_england_city_town_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_combined_new_england_city_town_areas_pkey\" for table \"us_combined_new_england_city_town_areas\"\r\nALTER TABLE\r\naddgeometrycolumn \r\n-----------------------------------------------------------------------------------------\r\npublic.us_combined_new_england_city_town_areas.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2 \r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls CNECTA]$<\/pre>\n
The loaded data should look similar to below:<\/p>\n
\"CNECTA<\/a>
CNECTA Dataset<\/p><\/div>\n
Next we move on to the COAST directory. This is the US Coastline data derived from Census edges.<\/p>\n
[bmaddox@girls census]$ ls COAST\/<\/strong>\r\ntl_2013_us_coastline.zip\r\n[bmaddox@girls census]$ cd COAST\/<\/strong>\r\n[bmaddox@girls COAST]$ ls -l<\/strong>\r\ntotal 15792\r\n-rwxrw-r-x 1 bmaddox bmaddox 16168901 Aug 16 2013 tl_2013_us_coastline.zip\r\n[bmaddox@girls COAST]$ unzip tl_2013_us_coastline.zip<\/strong>\r\nArchive: tl_2013_us_coastline.zip\r\ninflating: tl_2013_us_coastline.dbf\r\ninflating: tl_2013_us_coastline.prj\r\ninflating: tl_2013_us_coastline.shp\r\ninflating: tl_2013_us_coastline.shp.xml\r\ninflating: tl_2013_us_coastline.shx\r\n[bmaddox@girls COAST]$ ls -l<\/strong>\r\ntotal 40272\r\n-rwxrwxr-x 1 bmaddox bmaddox 449644 Aug 6 2013 tl_2013_us_coastline.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_coastline.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 24558540 Aug 6 2013 tl_2013_us_coastline.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 15352 Aug 7 2013 tl_2013_us_coastline.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 34028 Aug 6 2013 tl_2013_us_coastline.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 16168901 Aug 16 2013 tl_2013_us_coastline.zip\r\n[bmaddox@girls COAST]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_coastline.shp US_Coastlines |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_coastlines_gid_seq\" for serial column \"us_coastlines.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_coastlines_pkey\" for table \"us_coastlines\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n ------------------------------------------------------------------\r\npublic.us_coastlines.geom SRID:4269 TYPE:MULTILINESTRING DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls COAST]$<\/pre>\n
The coastlines will look like this:<\/p>\n
\"COAST<\/a>
COAST in QGIS<\/p><\/div>\n
We now move to the CSA directory, which holds the combined statistical area national-level shapefile. These are generally areas where several large cities are combined into a single statistical unit, such as the Branson-Springfield area in Missouri.<\/p>\n
[bmaddox@girls census]$ ls CSA<\/strong>\r\ntl_2013_us_csa.zip\r\n[bmaddox@girls census]$ cd CSA\/<\/strong>\r\n[bmaddox@girls CSA]$ ls -l<\/strong>\r\ntotal 10020\r\n-rwxrw-r-x 1 bmaddox bmaddox 10259685 Aug 16 2013 tl_2013_us_csa.zip [bmaddox@girls CSA]$ unzip tl_2013_us_csa.zip<\/strong>\r\nArchive: tl_2013_us_csa.zip\r\ninflating: tl_2013_us_csa.dbf\r\ninflating: tl_2013_us_csa.prj\r\ninflating: tl_2013_us_csa.shp\r\ninflating: tl_2013_us_csa.shp.xml\r\n<\/span>inflating: tl_2013_us_csa.shx\r\n<\/span>[bmaddox@girls CSA]$ ls -l<\/strong>\r\ntotal 26184\r\n-rwxrwxr-x 1 bmaddox bmaddox 45139 Aug 6 2013 tl_2013_us_csa.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_csa.prj\r\n<\/span>-rwxrwxr-x 1 bmaddox bmaddox 16477264 Aug 6 2013 tl_2013_us_csa.shp -rwxrwxr-x 1 bmaddox bmaddox 16012 Aug 6 2013 tl_2013_us_csa.shp.xml -rwxrwxr-x 1 bmaddox bmaddox 1452 Aug 6 2013 tl_2013_us_csa.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 10259685 Aug 16 2013 tl_2013_us_csa.zip [bmaddox@girls CSA]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_csa.shp US_Combined_Statistical_Areas |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_combined_statistical_areas_gid_seq\" for serial column \"us_combined_statistical_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_combined_statistical_areas_pkey\" for table \"us_combined_statistical_areas\"\r\nALTER TABLE\r\n<\/span>addgeometrycolumn\r\n-------------------------------------------------------------------------------\r\npublic.us_combined_statistical_areas.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n<\/span>(1 row)\r\n<\/span>CREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls CSA]$<\/pre>\n
Once loaded, it should look like this in QGIS:<\/p>\n
\"CSA<\/a>
CSA in QGIS<\/p><\/div>\n
The MIL directory contains areas that are classified as belonging to the US Military… that we know about at least.<\/p>\n
[bmaddox@girls census]$ cd MIL\/<\/strong>\r\n[bmaddox@girls MIL]$ dir<\/strong>\r\ntotal 2904\r\n-rwxrw-r-x 1 bmaddox bmaddox 2973061 Aug 16 2013 tl_2013_us_mil.zip\r\n[bmaddox@girls MIL]$ unzip tl_2013_us_mil.zip<\/strong>\r\nArchive: tl_2013_us_mil.zip\r\ninflating: tl_2013_us_mil.dbf\r\ninflating: tl_2013_us_mil.prj\r\ninflating: tl_2013_us_mil.shp\r\ninflating: tl_2013_us_mil.shp.xml\r\ninflating: tl_2013_us_mil.shx\r\n[bmaddox@girls MIL]$ ls -l<\/strong>\r\ntotal 7628\r\n-rwxrwxr-x 1 bmaddox bmaddox 150451 Aug 6 2013 tl_2013_us_mil.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_mil.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 4653712 Aug 6 2013 tl_2013_us_mil.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 15575 Aug 6 2013 tl_2013_us_mil.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 6524 Aug 6 2013 tl_2013_us_mil.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 2973061 Aug 16 2013 tl_2013_us_mil.zip\r\n[bmaddox@girls MIL]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_mil.shp US_Military_Areas |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_military_areas_gid_seq\" for serial column \"us_military_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_military_areas_pkey\" for table \"us_military_areas\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n-------------------------------------------------------------------\r\npublic.us_military_areas.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls MIL]$<\/pre>\n
And it looks like this in QGIS:<\/p>\n
\"MIL<\/a>
MIL in QGIS<\/p><\/div>\n
Now to NECTA, which is the New England City and Town Areas.<\/p>\n
[bmaddox@girls census]$ cd NECTA<\/strong>\r\n[bmaddox@girls NECTA]$ ls<\/strong>\r\ntl_2013_us_necta.zip\r\n[bmaddox@girls NECTA]$ unzip tl_2013_us_necta.zip<\/strong>\r\nArchive: tl_2013_us_necta.zip\r\ninflating: tl_2013_us_necta.dbf\r\ninflating: tl_2013_us_necta.prj\r\ninflating: tl_2013_us_necta.shp\r\ninflating: tl_2013_us_necta.shp.xml\r\ninflating: tl_2013_us_necta.shx\r\n[bmaddox@girls NECTA]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_necta.shp US_New_England_City_Town_Areas |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_new_england_city_town_areas_gid_seq\" for serial column \"us_new_england_city_town_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_new_england_city_town_areas_pkey\" for table \"us_new_england_city_town_areas\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n--------------------------------------------------------------------------------\r\npublic.us_new_england_city_town_areas.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls NECTA]$<\/pre>\n
Which looks like this in QGIS:<\/p>\n
\"NECTA<\/a>
NECTA in QGIS<\/p><\/div>\n
And the NECTADIV, which are the divisions in the NECTA dataset.<\/p>\n
[bmaddox@girls census]$ cd NECTADIV\/<\/strong>\r\n[bmaddox@girls NECTADIV]$ dir<\/strong>\r\ntotal 184\r\n-rwxrw-r-x 1 bmaddox bmaddox 186486 Aug 16 2013 tl_2013_us_nectadiv.zip\r\n[bmaddox@girls NECTADIV]$ unzip tl_2013_us_nectadiv.zip<\/strong>\r\nArchive: tl_2013_us_nectadiv.zip\r\ninflating: tl_2013_us_nectadiv.dbf\r\ninflating: tl_2013_us_nectadiv.prj\r\ninflating: tl_2013_us_nectadiv.shp\r\ninflating: tl_2013_us_nectadiv.shp.xml\r\ninflating: tl_2013_us_nectadiv.shx\r\n[bmaddox@girls NECTADIV]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_nectadiv.shp US_New_England_City_Town_Divisions |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_new_england_city_town_divisions_gid_seq\" for serial column \"us_new_england_city_town_divisions.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_new_england_city_town_divisions_pkey\" for table \"us_new_england_city_town_divisions\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n------------------------------------------------------------------------------------\r\npublic.us_new_england_city_town_divisions.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls NECTADIV]$<\/pre>\n
QGIS Screenshot:<\/p>\n
\"NECTADIV<\/a>
NECTADIV in QGIS<\/p><\/div>\n
RAILS contains, you guessed it, the national-level file of US railroads.<\/p>\n
[bmaddox@girls RAILS]$ dir<\/strong>\r\ntotal 40660\r\n-rwxrw-r-x 1 bmaddox bmaddox 41634270 Aug 16 2013 tl_2013_us_rails.zip\r\n[bmaddox@girls RAILS]$ unzip tl_2013_us_rails.zip<\/strong>\r\nArchive: tl_2013_us_rails.zip\r\ninflating: tl_2013_us_rails.dbf\r\ninflating: tl_2013_us_rails.prj\r\ninflating: tl_2013_us_rails.shp\r\ninflating: tl_2013_us_rails.shp.xml\r\ninflating: tl_2013_us_rails.shx\r\n[bmaddox@girls RAILS]$ ls -l<\/strong>\r\ntotal 130320\r\n-rwxrwxr-x 1 bmaddox bmaddox 23419778 Aug 6 2013 tl_2013_us_rails.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_rails.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 66902500 Aug 6 2013 tl_2013_us_rails.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 15759 Aug 6 2013 tl_2013_us_rails.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 1463828 Aug 6 2013 tl_2013_us_rails.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 41634270 Aug 16 2013 tl_2013_us_rails.zip\r\n[bmaddox@girls RAILS]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_rails.shp US_Rails |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_rails_gid_seq\" for serial column \"us_rails.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_rails_pkey\" for table \"us_rails\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n-------------------------------------------------------------\r\npublic.us_rails.geom SRID:4269 TYPE:MULTILINESTRING DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls RAILS]$<\/pre>\n
In QGIS:<\/p>\n
\"RAILS<\/a>
RAILS in QGIS<\/p><\/div>\n
Next is TBG, which are national-level Tribal Block Groups.<\/p>\n
[bmaddox@girls census]$ cd TBG\/<\/strong>\r\n[bmaddox@girls TBG]$ ls -l<\/strong>\r\ntotal 16208\r\n-rwxrwxr-x 1 bmaddox bmaddox 90939 Aug 6 2013 tl_2013_us_tbg.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_tbg.prj\r\nrwxrwxr-x 1 bmaddox bmaddox 16468896 Aug 6 2013 tl_2013_us_tbg.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 17719 Aug 6 2013 tl_2013_us_tbg.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 7420 Aug 6 2013 tl_2013_us_tbg.shx\r\n[bmaddox@girls TBG]$ shp2pgsql -s 4269 -c -D -I tl_2013_us_tbg.shp US_Tribal_Block_Groups |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_tribal_block_groups_gid_seq\" for serial column \"us_tribal_block_groups.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_tribal_block_groups_pkey\" for table \"us_tribal_block_groups\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n------------------------------------------------------------------------\r\npublic.us_tribal_block_groups.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls TBG]$<\/pre>\n
In QGIS:<\/p>\n
\"TBG<\/a>
TBG in QGIS<\/p><\/div>\n
UAC contains the Urban Areas from the 2010 Census.<\/p>\n
[bmaddox@girls census]$ cd UAC<\/strong>\r\n[bmaddox@girls UAC]$ unzip tl_2013_us_uac10.zip<\/strong>\r\nArchive: tl_2013_us_uac10.zip\r\ninflating: tl_2013_us_uac10.dbf\r\ninflating: tl_2013_us_uac10.prj\r\ninflating: tl_2013_us_uac10.shp\r\ninflating: tl_2013_us_uac10.shp.xml\r\ninflating: tl_2013_us_uac10.shx\r\n[bmaddox@girls UAC]$ ls -l<\/strong>\r\ntotal 211884\r\n-rwxrwxr-x 1 bmaddox bmaddox 976289 Jul 26 2013 tl_2013_us_uac10.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Jul 26 2013 tl_2013_us_uac10.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 132331308 Jul 26 2013 tl_2013_us_uac10.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 17734 Aug 2 2013 tl_2013_us_uac10.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 28908 Jul 26 2013 tl_2013_us_uac10.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 83595662 Aug 16 2013 tl_2013_us_uac10.zip\r\n[bmaddox@girls UAC]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_uac10.shp US_Urban_Areas_2010 |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_urban_areas_2010_gid_seq\" for serial column \"us_urban_areas_2010.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_urban_areas_2010_pkey\" for table \"us_urban_areas_2010\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n---------------------------------------------------------------------\r\npublic.us_urban_areas_2010.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls UAC]$<\/pre>\n
QGIS:<\/p>\n
\"UAC<\/a>
UAC in QGIS<\/p><\/div>\n
Next we will do the PRIMARYROADS dataset, which can be thought of as basically major US Interstates.<\/p>\n
[bmaddox@girls census]$ ls -l PRIMARYROADS\/<\/strong>\r\ntotal 25364\r\n-rwxrw-r-x 1 bmaddox bmaddox 25969141 Aug 16 2013 tl_2013_us_primaryroads.zip\r\n[bmaddox@girls census]$ cd PRIMARYROADS\/<\/strong>\r\n[bmaddox@girls PRIMARYROADS]$ ls -l<\/strong>\r\ntotal 25364\r\n-rwxrw-r-x 1 bmaddox bmaddox 25969141 Aug 16 2013 tl_2013_us_primaryroads.zip\r\n[bmaddox@girls PRIMARYROADS]$ unzip tl_2013_us_primaryroads.zip<\/strong>\r\nArchive: tl_2013_us_primaryroads.zip\r\ninflating: tl_2013_us_primaryroads.dbf\r\ninflating: tl_2013_us_primaryroads.prj\r\ninflating: tl_2013_us_primaryroads.shp\r\ninflating: tl_2013_us_primaryroads.shp.xml\r\ninflating: tl_2013_us_primaryroads.shx\r\n[bmaddox@girls PRIMARYROADS]$ ls -l<\/strong>\r\ntotal 66828\r\n-rwxrwxr-x 1 bmaddox bmaddox 1633818 Aug 6 2013 tl_2013_us_primaryroads.dbf\r\n-rwxrwxr-x 1 bmaddox bmaddox 165 Aug 6 2013 tl_2013_us_primaryroads.prj\r\n-rwxrwxr-x 1 bmaddox bmaddox 40696696 Aug 6 2013 tl_2013_us_primaryroads.shp\r\n-rwxrwxr-x 1 bmaddox bmaddox 16481 Aug 6 2013 tl_2013_us_primaryroads.shp.xml\r\n-rwxrwxr-x 1 bmaddox bmaddox 101412 Aug 6 2013 tl_2013_us_primaryroads.shx\r\n-rwxrw-r-x 1 bmaddox bmaddox 25969141 Aug 16 2013 tl_2013_us_primaryroads.zip\r\n[bmaddox@girls PRIMARYROADS]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_primaryroads.shp US_Primary_Roads |psql -d Census_2013<\/strong> Shapefile type: Arc\r\nPostgis type: MULTILINESTRING[2]\r\nSET\r\nSET\r\nBEGIN\r\nNOTICE: CREATE TABLE will create implicit sequence \"us_primary_roads_gid_seq\" for serial column \"us_primary_roads.gid\" CREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_primary_roads_pkey\" for table \"us_primary_roads\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n---------------------------------------------------------------------\r\npublic.us_primary_roads.geom SRID:4269 TYPE:MULTILINESTRING DIMS:2 (1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls PRIMARYROADS]$<\/pre>\n
And in QGIS:<\/p>\n
\"PRIMARYROADS<\/a>
PRIMARYROADS in QGIS<\/p><\/div>\n
And finally (yes FINALLY!), the ZCTA5 dataset, which is the list of US zip code tabulation areas.<\/p>\n
[bmaddox@girls census]$ cd ZCTA5\/<\/strong>\r\n[bmaddox@girls ZCTA5]$ dir<\/strong>\r\ntotal 513180\r\n-rwxrw-r-x 1 bmaddox bmaddox 525492077 Aug 16 2013 tl_2013_us_zcta510.zip\r\n[bmaddox@girls ZCTA5]$ unzip tl_2013_us_zcta510.zip<\/strong>\r\nArchive: tl_2013_us_zcta510.zip\r\ninflating: tl_2013_us_zcta510.dbf\r\ninflating: tl_2013_us_zcta510.prj\r\ninflating: tl_2013_us_zcta510.shp\r\ninflating: tl_2013_us_zcta510.shp.xml\r\ninflating: tl_2013_us_zcta510.shx\r\n[bmaddox@girls ZCTA5]$ shp2pgsql -s 4269 -c -D -I -WLATIN1 tl_2013_us_zcta510.shp US_Zip_Code_Areas |psql -d Census_2013\r\n<\/strong>Shapefile type: Polygon\r\nPostgis type: MULTIPOLYGON[2]\r\nSET\r\nSET\r\n<\/span>BEGIN\r\nNOTICE: CREATE TABLE will create implicit sequence \"us_zip_code_areas_gid_seq\" for serial column \"us_zip_code_areas.gid\"\r\nCREATE TABLE\r\nNOTICE: ALTER TABLE \/ ADD PRIMARY KEY will create implicit index \"us_zip_code_areas_pkey\" for table \"us_zip_code_areas\"\r\nALTER TABLE\r\naddgeometrycolumn\r\n------------------------------------------------------------------- public.us_zip_code_areas.geom SRID:4269 TYPE:MULTIPOLYGON DIMS:2\r\n(1 row)\r\nCREATE INDEX\r\nCOMMIT\r\n[bmaddox@girls ZCTA5]$<\/pre>\n
And the final screen shot:<\/p>\n
\"ZCTA5<\/a>
ZCTA5 in QGIS<\/p><\/div>\n
So that is it. You now have the US state borders, county outlines, and a large amount of national-level data such as railroads and interstates. In 8.3 we will tackle some of the other datasets that are comprised of multiple county files for each state. We will convert them to single state files and then import them into PostGIS. Until then, examine and experiment with what you have just done and see what data is actually inside the tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
In Part 8.1, we went over how the Census ftp site was set up and what kind of data each directory has in it. We also loaded our first Census data, which were outlines of the US states and territories. … 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-155","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\/155","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=155"}],"version-history":[{"count":7,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/155\/revisions"}],"predecessor-version":[{"id":179,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/155\/revisions\/179"}],"wp:attachment":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}