AWS 2-5: Update TDX Hydro Dataset

[rajadain]

Folks at LimnoTech have a version with custom attributes that connects the streams to the basins and allows for quick watershed delineation. We need to replace the current version with that, so that each stream has the necessary attributes for that work.

@ptomasula will upload the dataset to s3://data.mmw.azavea.com/.

Once the dataset is available:

• Ingest it locally into postgres using ogr2ogr
• Export it into sql using pg_dump
• Import it on staging

[rajadain]

After ingest, run the following to shape the table:

-- Step 1: Drop the existing primary key constraint
ALTER TABLE tdxstreams DROP CONSTRAINT tdxstreams_pkey;

-- Step 2: Drop the `ogc_fid` column
ALTER TABLE tdxstreams DROP COLUMN ogc_fid;

-- Step 3: Add a new primary key constraint on `linkno`
ALTER TABLE tdxstreams ADD CONSTRAINT tdxstreams_pkey PRIMARY KEY (linkno);

-- Step 4: Rename columns to match other stream tables
ALTER TABLE tdxstreams RENAME COLUMN strmorder TO stream_order;
ALTER TABLE tdxstreams RENAME COLUMN geometry TO geom;

-- Step 5: Create stream order index
CREATE INDEX tdxstreams_stream_order_idx ON tdxstreams USING btree (stream_order);

-- Step 6: Create index on root_id, discover_time, finish_time combination
CREATE INDEX idx_tdxstreams_root_discover_finish ON tdxstreams (root_id, discover_time, finish_time);

[rajadain]

This is now done and on production.