{"id":248,"date":"2014-03-23T11:09:53","date_gmt":"2014-03-23T15:09:53","guid":{"rendered":"http:\/\/brian.digitalmaddox.com\/blog\/?p=248"},"modified":"2018-06-29T19:54:53","modified_gmt":"2018-06-29T23:54:53","slug":"using-free-geospatial-tools-and-data-part-10-usgs-gnis-data","status":"publish","type":"post","link":"https:\/\/brian.digitalmaddox.com\/blog\/?p=248","title":{"rendered":"Using Free Geospatial Tools and Data Part 10: USGS GNIS Data"},"content":{"rendered":"

The USGS<\/a> Board on Geographic Names<\/a> maintains the Geographic Names Information System (GNIS) database. \u00a0It is a database of over two million points in the United States. \u00a0This database:<\/p>\n

contains information about physical and cultural geographic features in the United States and associated areas, both current and historical (not including roads and highways). The database holds the Federally recognized name of each feature and defines the location of the feature by state, county, USGS topographic map, and geographic coordinates.<\/p><\/blockquote>\n

You can download the 79 megabyte GNIS zip file from here<\/a>. \u00a0You will want to select the NationalFile as it is not broken up into individual states. \u00a0Importing GNIS into PostGIS is slightly more complicated as it does not come as a Shapefile, but instead as a 293 megabyte text file once it is unzipped from the \u00a0above. \u00a0Download the file, unzip it, and open a command window where the unzipped file is. \u00a0Note that the last time I did this on Windows, using the command line client was an exercise in pain due to how Windows handles code pages and character types. \u00a0On Windows it might be easier to do this inside something like pgadmin<\/a>.<\/p>\n

To import, first create a database inside PostgreSQL using something like the following:<\/p>\n

createdb -T gistemplate USGS<\/pre>\n
Once done, you will want to run<\/p>\n
psql -d USGS<\/pre>\n
to start the PostgreSQL database client. \u00a0Now you will want to create the table to hold the data. \u00a0To do this, copy and paste this statement into the psql<\/em> client window:<\/p>\n
CREATE TABLE gnis\r\n(\r\n feature_id integer NOT NULL,\r\n feature_name character varying,\r\n feature_class character varying,\r\n state_alpha character(2),\r\n state_numeric character(2),\r\n county_name character varying,\r\n county_numeric character(3),\r\n primary_lat_dms character varying,\r\n prim_long_dms character varying,\r\n prim_lat_dec real,\r\n prim_long_dec real,\r\n source_lat_dms character varying,\r\n source_long_dms character varying,\r\n source_lat_dec real,\r\n source_long_dec real,\r\n elev_in_m integer,\r\n elev_in_ft integer,\r\n map_name character varying,\r\n date_created date,\r\n date_edited date\r\n);<\/pre>\n
Note that I COULD have spent the time figuring out the maximum size of each column, instead of just making them varchars, to save space. \u00a0But, again, I’m lazy \ud83d\ude42<\/p>\n
Now to import, you will run the following command. \u00a0The .txt files is over two million rows, so it could take a while to import depending on the speed of your system.<\/p>\n
\u00a0USGS=# \\copy gnis from NationalFile_20140204.txt DELIMITER '|' CSV HEADER<\/strong><\/pre>\n
If you get a file not found error,run \\copy<\/em> with the full path to the NationalFile. \u00a0Depending on when you do this, the file name may be different based on when it was last updated.<\/p>\n
We are not done yet. \u00a0There is no actual geospatial geometry column in the database. \u00a0We will need to create one from the existing columns. \u00a0To do this, first we must create a geometry column to hold the geospatial points.<\/p>\n
USGS=# SELECT AddGeometryColumn('public', 'gnis', 'geom', 4326, 'POINT', 2);<\/strong><\/pre>\n
This command tells PostgreSQL to add a geometry column named geom<\/em> to the gnis<\/em> table in the public<\/em> schema using NAD83. \u00a0Now we need to actually populate this column. \u00a0We need to take the latitude and longitude columns in the table and convert them into a binary representation that PostGIS uses internally.<\/p>\n
USGS=# update public.gnis <\/strong>\r\nSET geom = ST_PointFromText('POINT(' || prim_long_dec || ' ' || prim_lat_dec || ')', 4326);<\/strong><\/pre>\n
Here we have PostgreSQL convert the prim_long_dec<\/em> and prim_lat_dec<\/em> columns into a POINT<\/a> and then to the actual geometry using the ST_PointFromText<\/a> function inside PostGIS.<\/p>\n
Now we need to add a geospatial index on the geom column. \u00a0You need an index to use the data in apps such as QGIS as it makes area look-ups much faster.<\/p>\n
USGS=# create index gnis_geom_gist_idx on gnis using gist(geom);<\/strong><\/pre>\n
Now that we have an index, we need to create our database statistics and cluster it on the geom<\/em> column. \u00a0As I mentioned in a previous post, you will run these commands in order (waiting for each one to complete before running the next):<\/p>\n
 <\/p>\n
USGS=# vacuum analyze gnis;<\/strong>\r\nUSGS=# cluster gnis using gnis_geom_gist_idx;\r\n<\/strong>USGS=# analyze gnis;<\/strong><\/pre>\n
And now we are done. \u00a0You have your own local copy of GNIS that you can use in visual GIS tools or from the command line. \u00a0There are some fun things you can do with the data, such as the below figure shows where I used QGIS to load all points in GNIS that have my last name in them (my modesty astounds even me \ud83d\ude09<\/p>\n
\"Maddox<\/a>
Maddox Places in QGIS<\/p><\/div>\n
Happy GISing!<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
The USGS Board on Geographic Names maintains the Geographic Names Information System (GNIS) database. \u00a0It is a database of over two million points in the United States. \u00a0This database: contains information about physical and cultural geographic features in the United … 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-248","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\/248","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=248"}],"version-history":[{"count":7,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/248\/revisions"}],"predecessor-version":[{"id":509,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/248\/revisions\/509"}],"wp:attachment":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}