Update west-coast to use latest High Resolution of USGS NHD.

[andest01]

https://www.usgs.gov/core-science-systems/ngp/national-hydrography/nhdplus-high-resolution

[andest01]

High Resolution USGS NHD data acquired. Needs to be folded into backend.

[andest01]

Houston, we have a problem.

It seems that using the GNIS_ID wasn't checked very well in the latest betas for HUC2 17 (Pacific Northwest), and we get some real, real shitty shapes.

I'll have to see what the damage is, but it involves group bying on GNIS_ID and then counting the number of resultant geometries. if we get 1 geometry (a damn stream), then we're good. If I get two or more (like this jackass), then we've got a big problem.

Here's an example:

This ugliness was caused by somebody at the USGS using the same GNIS_ID for "Squaw Creek" (!!) when they should have used TWO separate instances of "Squaw Creek".

Eeesh.

[andest01]

Here are the offending multi-line group-bys

Most are merely 2 parts, but some are 3 all the way up to 8. Not sure how to fix them consistently.

included: Yampa River, CO, Bitterroot River, MT, Snake River, WY/ID Flathead River, MT Big Lost River, ID

It also reveals several errors in USGS HR beta data, mainly from inadvertently using the wrong GNIS IDs for streams that have the same name.

[andest01]

Green: Fixed via shitty two-part union query

Blue: Needs to be split in two (e.g. flathead river, and another river that dips into Canada from Oregon)

Red: Still no solution. Maybe just split them up for now with a flag, run the numbers on em, and then look at the results.

Two part fix part I

with result_1 as (
with ordered_down_lengths as (
    with split_lengths as (
        with split_items as (
            with streams_with_geomnum_2 as (
            select gid, gnis_id, gnis_name, lengthkm, geom, ST_Length(geom) as length from nhdplus_disolved_candidates where numgeom = 2
            )
            select (ST_Dump(geom)).geom as geom, gid, gnis_id, gnis_name, lengthkm, length from streams_with_geomnum_2
        ) select * from split_items
    )
    select length as original_length,
    st_length(geom) as piece_length,
    gid, 
    gnis_id,
    gnis_name,
    lengthkm,
    geom
    from split_lengths
    order by  gnis_id asc, piece_length asc
)
select ST_MakeLine(geom) as geom,
gid, gnis_id, gnis_name, lengthkm, original_length,
ST_Length(ST_MakeLine(geom)) as new_length
from ordered_down_lengths
group by gid, gnis_id, gnis_name, lengthkm, original_length

)

select * from result_1 where new_length / original_length  < 1.1
order by original_length / new_length

two part fix part II

with result_1 as (
with ordered_down_lengths as (
    with split_lengths as (
        with split_items as (
            with streams_with_geomnum_2 as (
            select gid, gnis_id, gnis_name, lengthkm, geom, ST_Length(geom) as length from nhdplus_disolved_candidates where numgeom = 2
            )
            select (ST_Dump(geom)).geom as geom, gid, gnis_id, gnis_name, lengthkm, length from streams_with_geomnum_2
        ) select * from split_items
    )
    select length as original_length,
    st_length(geom) as piece_length,
    gid, 
    gnis_id,
    gnis_name,
    lengthkm,
    geom
    from split_lengths
    order by  gnis_id asc, piece_length desc
)
select ST_MakeLine(geom) as geom,
gid, gnis_id, gnis_name, lengthkm, original_length,
ST_Length(ST_MakeLine(geom)) as new_length
from ordered_down_lengths
group by gid, gnis_id, gnis_name, lengthkm, original_length

)

select * from result_1 where new_length / original_length  < 1.1
order by original_length / new_length

The only difference between the two parts is the asc vs desc. WHO CARES.

[andest01]

Removed all bad geometries. we are go for state-by-state selection.