Add NHD stream order attribute

[mmcfarland]

NHD Flowline features are currently loaded into the database. The NHD dataset contains a related table set which includes an attribute called stream order. Produce an nhdflowlines table to replace the current one that include this attribute.

[mmcfarland]

The nhdflowline dataset was updated to have stream_order field.

To test, run ./setupdb.sh -s

• Verify the script reloads the nhdflowline table
• The table has a new attribute stream_order
• The number of rows in the table is unchaged: 2993513

[mmcfarland]

Additional test, check that layer still renders with the tiler and that a MapShed run, outside of the DRB, still works.

[lewfish]

+1, tested

[mmcfarland]

Leaving some instructions here about how this was accomplished. NHD datasets can be downloaded by region from http://www.horizon-systems.com/NHDPlus/NHDPlusV2_data.php. There are several dozen regions. When the data is extracted, it forms a nested directory structure and at one of the leafs of the tree will be a PlusFlowlineVAA.dbf file that has attributes for nhdflowlines. A key field called comid relates a VAA row to a flowline feature. The PG driver for ogr2ogr can't append to a table, so I do an intermediate step of collecting the several dozen dbf files into a single dbf file. Simultaneously, I only extract the attribute I care about (streamorde) to keep the table size small:

find . -name 'PlusFlowlineVAA.dbf' -exec ogr2ogr -update -append ./totals.dbf {} -sql "SELECT comid, streamorde from PlusFlowLineVaa" \;

I then copy the new dbf file to postgres:

ogr2ogr -update -append -f "PostgreSQL" PG:"host=localhost user=mmw dbname=mmw password=***" -nln plusflowlinevaa totals.dbf

Then a simple join against the ~3M flowline rows to add the attribute to a new table which forms the basis of the data update:

SELECT f.*, a.streamorde INTO nhdflowline_plus 
FROM nhdflowline f LEFT JOIN plusflowlinevaa a ON f.comid = a.comid;