How To Process NHD High Resolution Water Data

Author: Dan Blomberg | Last updated January 9th, 2016 at 10:46pm

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 and GPSFPshp2mp 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 pgadmin III (in the start menu)
  2. Double click the PostgreSQL database to connect to it. Enter the password if required.
  3. Expand databases
  4. Double click of postgis to access/initialize that database.
  5. Expand postgis
  6. Expand Schemas
  7. Expand public
  8. Expand Tables
  9. Make sure that you don't currently had nhdarea, nhdflowline, or nhdwaterbody tables. If you do, right click each one and select Delete/Drop
  10. Now go to the Plugins menu and select "PostGIS Shapefile and DBF Loader 2.2"
  11. In the Import tab click "Add File"
  12. Select the NHDArea.shp file and click Open
  13. Click "Add File" again
  14. Select the NHDArea.shp file and click Open
  15. Click "Add File" again
  16. Select the NHDWaterbody.shp file and click Open
  17. Click Import
  18. Close the PostGIS Shapefile Import/Export manager.

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. Still in pgAdmin III right click on tables and select refresh.
  2. You should now see three new tables: nhdarea, nhdflowline, nhdwaterbody
  3. Click the SQL query button.
  4. Put the following query into the SQL editor box:
    ALTER TABLE nhdarea ADD COLUMN MP_TYPE character varying(50);
    ALTER TABLE nhdarea ALTER COLUMN MP_TYPE SET STORAGE EXTENDED;
    ALTER TABLE nhdarea RENAME COLUMN gnis_name TO name;
    DELETE FROM nhdarea 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 nhdarea SET MP_TYPE = '0x28' WHERE fcode = '31200' or fcode='44500';
    UPDATE nhdarea SET MP_TYPE = '0x49' WHERE fcode = '33600' or fcode = '33601' or fcode = '33602'; 
    UPDATE nhdarea SET MP_TYPE = '0x3b' WHERE fcode = '36200' or fcode='39800' or fcode='45500';
    UPDATE nhdarea SET MP_TYPE = '0x49' WHERE fcode = '46000' or fcode = '46006';
    UPDATE nhdarea SET MP_TYPE = '0x46' WHERE (fcode = 46000 or fcode = 46006) and areasqkm >= 15;
    UPDATE nhdarea SET MP_TYPE = '0x47' WHERE (fcode = 46000 or fcode = 46006) and areasqkm >= 10 and areasqkm < 15;
    UPDATE nhdarea SET MP_TYPE = '0x48' WHERE (fcode = 46000 or fcode = 46006) and areasqkm >= 2 and areasqkm < 10;
    UPDATE nhdarea SET MP_TYPE = '0x4c' WHERE fcode = '46003' or fcode='48400';
    DELETE FROM nhdarea WHERE MP_TYPE IS NULL;
  5. Click execute query
  6. Close the SQL window.
  7. Click the SQL query button.
  8. Put the following query into the SQL editor box:
    ALTER TABLE nhdflowline ADD COLUMN MP_TYPE character varying(50);
    ALTER TABLE nhdflowline ALTER COLUMN MP_TYPE SET STORAGE EXTENDED;
    ALTER TABLE nhdflowline RENAME COLUMN gnis_name TO name;
    DELETE FROM nhdflowline WHERE fcode = '33600' or fcode = '33601' or fcode = '33602' or fcode = '42000' or fcode = '33400';
    DELETE FROM nhdflowline WHERE fcode >= '42800' and fcode <= '42850';
    UPDATE nhdflowline SET MP_TYPE = '0x18' WHERE fcode = '55800' or fcode = '46000' or fcode = '46006';
    UPDATE nhdflowline SET MP_TYPE = '0x15' WHERE fcode = '56600';
    UPDATE nhdflowline SET MP_TYPE = '0x26' WHERE fcode = '46003';
    DELETE FROM nhdflowline WHERE MP_TYPE IS NULL;
  9. Click execute query.
  10. Close the SQL window.
  11. Click the SQL query button.
  12. Put the following query into the SQL editor box:
    ALTER TABLE nhdwaterbody ADD COLUMN MP_TYPE character varying(50);
    ALTER TABLE nhdwaterbody ALTER COLUMN MP_TYPE SET STORAGE EXTENDED;
    ALTER TABLE nhdwaterbody RENAME COLUMN gnis_name TO name;
    DELETE FROM nhdwaterbody WHERE fcode >='43600' AND fcode <='43626';
    UPDATE nhdwaterbody SET MP_TYPE = '0x4d' WHERE fcode = '37800';
    UPDATE nhdwaterbody SET MP_TYPE = '0x40' WHERE fcode = '49300';
    UPDATE nhdwaterbody SET MP_TYPE = '0x41' WHERE fcode = '39000' OR fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012';
    UPDATE nhdwaterbody 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 nhdwaterbody 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 nhdwaterbody 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 nhdwaterbody 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 nhdwaterbody 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 nhdwaterbody 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 nhdwaterbody 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 nhdwaterbody SET MP_TYPE = '0x42' WHERE (fcode = '39004' OR fcode = '39009' OR fcode = '39010' OR fcode = '39011' OR fcode = '39012') and areasqkm >= 3300;
    UPDATE nhdwaterbody SET MP_TYPE = '0x4c' WHERE fcode = '39001' OR fcode = '39005' OR fcode = '39006';
    UPDATE nhdwaterbody SET MP_TYPE = '0x51' WHERE fcode = '46600';
    UPDATE nhdwaterbody SET MP_TYPE = '0x53' WHERE fcode = '36100';
    DELETE FROM nhdwaterbody WHERE MP_TYPE IS NULL;
  13. Click execute query.
  14. Close the SQL window.

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. With pgAdmin III still open go to the Plugins menu and select "PostGIS Shapefile and DBF Loader 2.2"
  2. Click the export tab
  3. Click "Add Table"
  4. Select nhdarea, nhdflowline, and nhdwaterbody (hold ctrl to select them all at once) and click OK
  5. Click "Export"
  6. Go to the folder where your NHD data is stored and click "Open". This will overwrite the old NHD data and replace it with the modified data.
  7. Once the export is complete close the Loader
  8. To save space and not make a mistake later, in pgAdmin III delete each table. Right click on nhdarea and click Delete/Drop. Right click on nhdflowline and click Delete/Drop. Right click on nhdwaterbody and click Delete/Drop.