How To Process NHD High Resolution Water Data

Author: Dan Blomberg | Last updated April 7th, 2008 at 04:26pm

Introduction

This tutorial will teach you a quick way to process USGS National Hydrography high resolution data.  This final result of this tutorial will be a three shapefiles with water data.  There will be on for each type of water data.  This tutorial assumes you have already downloaded all the water data for the area your map will be covering.

Requirements

Certain software is required to process the NHD data.

In addition, the shape file at the end will be ready for Global Mapper because Global Mapper recognizes the MP_TYPE field.

Putting The Data Into The Database

This section details how to get all the shape files into the PostgreSQL database so PostGIS can start manipulating the data.

  1. Open the command prompt (Start>Run>cmd)
  2. Change to the postgresql bin directory normally: C:Program FilesPostgreSQL8.3in use this command: cd "C:Program FilesPostgreSQL8.3in"
  3. We will use the following command to convert the shapefiles to sql files:
    shp2pgsql SHAPEFILE TABLE > FILENAME.sql
    Replace SHAPEFILE with the name of your shapefile (for example C:gps_dataNHDNDHLine.shp). Replace TABLE with a meaningful table name (for example line for lines, waterbody for waterbodies, etc).  Replace FILENAME with the name of the sql file (For example NHDLine)
  4. Now that we have a .sql file we need to load it into the database with this command:
    psql -d DATABASE -h localhost -U postgres -f FILENAME.sql
    Replace DATABASE with postgis (or any other database name), replace FILENAME with the filename you used above.
  5. You can now repeat these commands for each NHD data file.  Be aware: You cannot load line data, point data, and area data together.  So use a different table for each type of data.

Processing The Data

Once all the water data is loaded into databases we load pgAdmin III and run a few queries to delete data we don’t want, and give the rest of the data garmin types.

  1. Open pgAdmin III
  2. Go to the table containing your data (it is located under your server>databases>postgis>schemas>public>tables)
  3. Press the SQL button:
  4. In the query box that pops up type a query similar to this one:
    ALTER TABLE tablename ADD COLUMN mp_type character varying(50);
    Change tablename to be the table that your data is in
  5. Press the execute query button (the button at the top that looks like a play button)

Now that the table is setup we need to execute a set of queries.  The queries are dependent on what type of data you are working with.  As with the ALTER query above you will copy all of the queries into the SQL query editor and then press play.

NHD Area Queries (table named area)

DELETE FROM area WHERE fcode = '53700' or fcode = '30700' or fcode = '31800' or fcode = '34300' or fcode = '34305' or fcode = '34306' or fcode = '36400' or fcode = '37300' or fcode = '56800' or fcode = '43100' or fcode = '45400' or fcode = '45401' or fcode = '45402' or fcode = '45403' or fcode = '45404' or fcode = '46100' or fcode = '48500' or fcode = '40300' or fcode = '40307' or fcode = '40308' or fcode = '40309';
UPDATE area SET mp_type = '0x28' WHERE fcode = '31200' or fcode='44500';
UPDATE area SET mp_type = '0x49' WHERE fcode = '33600' or fcode = '33601' or fcode = '33602';
UPDATE area SET mp_type = '0x3b' WHERE fcode = '36200' or fcode='39800' or fcode='45500';
UPDATE area SET mp_type = '0x49' WHERE fcode = '46000' or fcode = '46006';
UPDATE area SET mp_type = '0x46' WHERE (fcode = 46000 or fcode = 46006) and areasqkm >= 15;
UPDATE area SET mp_type = '0x47' WHERE (fcode = 46000 or fcode = 46006) and areasqkm >= 10 and areasqkm < 15;
UPDATE area SET mp_type = '0x48' WHERE (fcode = 46000 or fcode = 46006) and areasqkm >= 2 and areasqkm < 10;
UPDATE area SET mp_type = '0x4c' WHERE fcode = '46003' or fcode='48400';
DELETE FROM area WHERE mp_type IS NULL;

NHD Flowline Queries (table named flowline)

DELETE FROM flowline WHERE fcode = '33600' or fcode = '33601' or fcode = '33602' or fcode = '42000' or fcode = '33400';
DELETE FROM flowline WHERE fcode >= '42800' and fcode <= '42850';
UPDATE flowline SET mp_type = '0x18' WHERE fcode = '55800' or fcode = '46000' or fcode = '46006';
UPDATE flowline SET mp_type = '0x15' WHERE fcode = '56600';
UPDATE flowline SET mp_type = '0x26' WHERE fcode = '46003';
DELETE FROM flowline WHERE mp_type IS NULL;

NHD Waterbody Queries (table named waterbody)

DELETE FROM waterbody WHERE fcode >='43600' AND fcode <='43626';
UPDATE waterbody SET mp_type = '0x4d' WHERE fcode = '37800';
UPDATE waterbody SET mp_type = '0x40' WHERE fcode = '49300';
UPDATE waterbody SET mp_type = '0x41' WHERE fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012' OR fcode='39000';
UPDATE waterbody SET mp_type = '0x40' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 0.25 and areasqkm < 11;
UPDATE waterbody SET mp_type = '0x3f' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 11 and areasqkm < 25;
UPDATE waterbody SET mp_type = '0x3e' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 25 and areasqkm < 77;
UPDATE waterbody SET mp_type = '0x3d' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 77 and areasqkm < 250;
UPDATE waterbody SET mp_type = '0x3c' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 250 and areasqkm < 600;
UPDATE waterbody SET mp_type = '0x44' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 600 and areasqkm < 1100;
UPDATE waterbody SET mp_type = '0x43' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 1100 and areasqkm < 3300;
UPDATE waterbody SET mp_type = '0x42' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 3300;
UPDATE waterbody SET mp_type = '0x4c' WHERE fcode = '39001' OR fcode = '39005' OR fcode = '39006';
UPDATE waterbody SET mp_type = '0x51' WHERE fcode = '46600';
UPDATE waterbody SET mp_type = '0x53' WHERE fcode = '36100';
DELETE FROM waterbody WHERE mp_type IS NULL;

Once the queries are executed the data is ready to be exported.

Export The Data Back To Shapefiles

Now we will take the data and turn it back into a shapefile which we can then import into Global Mapper (or any other program).

  1. Open the command prompt (start>run>cmd)
  2. Change to the postgresql bin directory normally: C:Program FilesPostgreSQL8.3in use this command: cd "C:Program FilesPostgreSQL8.3in"
  3. Run the following command to export the data into a shapefile:
    pgsql2shp  -f SHAPEFILE -u postgres DATABASE TABLE
    Replace SHAPEFILE with what you want the shapefile to name named (ex: waterbody.shp), replace DATABASE with the database name (most likely postgis), replace TABLE with the table name you want to export.
  4. The shapefile will now be in the PostgreSQL bin directory and can be moved and used however you see fit.

If you have any questions or see a better way to write this tutorial please contact me.