AWS 2-5: Ingest Global Basins

[rajadain]

Ingest the TDX Hydro Global Basins dataset into Postgres. This should match the additional fields added to the streams in #3646.

The source data will be made available by @ptomasula at s3://data.mmw.azavea.com/. Once it is:

• [x] Ingest it locally into Postgres using ogr2ogr
• [x] Export it to SQL using pg_dump
• [x] Import it on staging

[rajadain]

Should use the following script to ingest them:

ogr2ogr -progress -if "Parquet" \
          -f "PostgreSQL" PG:"host=localhost user=mmw dbname=mmw password=XXX" \
          -nln tdxbasins \
          -lco GEOMETRY_NAME=geom \
          -lco FID=LINKNO \
          -append -skipfailures \
          TDX_streamreach_basins_mnsi_\*.parquet

Once imported, we should create this index to help with delineation:

CREATE INDEX idx_tdxbasins_root_discover_finish
ON tdxbasins (root_id, discover_time, finish_time);

[rajadain]

Once that is in place, we can use the following query to delineate a watershed, given a basin clicked by the user:

WITH target AS (SELECT *
FROM tdxbasins
WHERE linkno = ?)

SELECT ST_Union(geom)
FROM tdxbasins
WHERE root_id = (SELECT root_id FROM target)
  AND discover_time <= (SELECT discover_time FROM target)
  AND finish_time >= (SELECT finish_time FROM target);

[rajadain]

Data has been ingested on Staging successfully. Now exporting for reuse using:

env PGPASSWORD=xxx pg_dump --clean --if-exists --no-owner --dbname=modelmywatershed --username=modelmywatershed --host=database.service.mmw.internal --table=tdxbasins --compress=9 --verbose > tdxbasins.sql.gz

[rajadain]

Data exported to s3://data.mmw.azavea.com/tdxbasins.sql.gz