fwapg/load.sh at main · smnorris/fwapg · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
#!/bin/bash
set -euxo pipefail

PSQL="psql $DATABASE_URL -v ON_ERROR_STOP=1"


# ---------------------
# load these directly from object storage parquet files
# ---------------------
tables=(
  assessment_watersheds_poly
  bays_and_channels_poly
  coastlines_sp
  glaciers_poly
  islands_poly
  lakes_poly
  manmade_waterbodies_poly
  named_point_features_sp
  named_watersheds_poly
  obstructions_sp
  rivers_poly
  watershed_groups_poly
  watersheds_xborder_poly
  wetlands_poly
)

for table in "${tables[@]}"; do
  $PSQL -c "truncate whse_basemapping.fwa_$table"
  ogr2ogr \
    -f PostgreSQL \
    PG:$DATABASE_URL \
    --config PG_USE_COPY YES \
    -append \
    -update \
    -preserve_fid \
    -nln whse_basemapping.fwa_$table \
    /vsicurl/https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_$table.parquet
done

WSD_GROUPS=$(ogr2ogr -f CSV /vsistdout/ \
  /vsicurl/https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_watershed_groups_poly.parquet \
  -sql "select distinct watershed_group_code from fwa_watershed_groups_poly order by watershed_group_code" | tail -n +2
)

# ---------------------
# for larger tables, load partitions one by one
# (could use /vsis3/ and point at the prefix but that requires s3 credentials)
# ---------------------
tables=(
  linear_boundaries_sp
  watersheds_poly
)
for table in "${tables[@]}"; do
  $PSQL -c "truncate whse_basemapping.fwa_$table"
  for WSG in $WSD_GROUPS; do
    ogr2ogr \
      -f PostgreSQL \
      PG:$DATABASE_URL \
      --config PG_USE_COPY YES \
      -append \
      -update \
      -preserve_fid \
      -nln whse_basemapping.fwa_$table \
      /vsicurl/https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_$table/$WSG.parquet
  done
done


# ---------------------
# streams are loaded to a temp table (for adding measures to the geometries)
# ---------------------
$PSQL -c "truncate whse_basemapping.fwa_stream_networks_sp;"
for WSG in $WSD_GROUPS; do
  ogr2ogr \
    -f PostgreSQL \
    PG:$DATABASE_URL \
    --config PG_USE_COPY YES \
    -preserve_fid \
    -overwrite \
    -lco GEOMETRY_NAME=geom \
    -nln fwapg.fwa_stream_networks_sp \
    /vsicurl/https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_stream_networks_sp/$WSG.parquet
  $PSQL -f load/fwa_stream_networks_sp.sql  # load to output table, drop temp table
done


# ---------------------
# load non-spatial csv tables with COPY
# ---------------------
tables=(
  edge_type_codes
  streams_20k_50k
  waterbodies_20k_50k
  waterbody_type_codes
  watershed_type_codes
)
for table in "${tables[@]}"; do
  $PSQL -c "truncate whse_basemapping.fwa_$table"
  $PSQL -c "\copy whse_basemapping.fwa_$table FROM PROGRAM 'curl -s https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_$table.csv.gz | gunzip' delimiter ',' csv header"
done

# ---------------------
# apply fixes that have not yet made it in to data source
# ---------------------
$PSQL -f fixes/fixes.sql

# ---------------------
# load smaller value added tables
# ---------------------
tables=(
  approx_borders
  basins_poly
  bcboundary
  named_streams
  stream_networks_order_max
  waterbodies
)
for table in "${tables[@]}"; do
  echo "Loading whse_basemapping.fwa_$table"
  $PSQL -f load/fwa_$table.sql
done

# ---------------------
# load larger value added tables
# ---------------------
tables=(
  stream_networks_order_parent
  streams_watersheds_lut
)
groups=$(ogr2ogr -f CSV /vsistdout/ \
  /vsicurl/https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_watershed_groups_poly.parquet \
  -sql "select distinct watershed_group_code from fwa_watershed_groups_poly order by watershed_group_code" | tail -n +2
)
for table in "${tables[@]}"; do
  echo "Loading whse_basemapping.fwa_$table"
  $PSQL -c "truncate whse_basemapping.fwa_$table"
  for wsg in $groups; do
    echo $wsg
    $PSQL -f load/fwa_$table.sql -v wsg=$wsg
  done
done

# ---------------------
# load xborder watersheds into the general watersheds table
# ---------------------
$PSQL -f load/fwa_watersheds_xborder_poly.sql

# ---------------------
# for larger lookups/datasets (generated via scripts in /extras), download cached pre-processed data
# ---------------------
tables=(
  fwa_stream_networks_channel_width
  fwa_stream_networks_discharge
  fwa_assessment_watersheds_lut
  fwa_assessment_watersheds_streams_lut
  fwa_waterbodies_upstream_area
  fwa_watersheds_upstream_area
  fwa_stream_networks_mean_annual_precip
  fwa_streams_pse_conservation_units_lut
)
for table in "${tables[@]}"; do
  echo $table
  $PSQL -c "truncate whse_basemapping.$table"
  $PSQL -c "\copy whse_basemapping.$table FROM PROGRAM 'curl -s https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/$table.csv.gz | gunzip' delimiter ',' csv header"
done
# materialize above data to whse_basemapping.fwa_streams, holding the value-added data (for fast upstr/dnstr queries)
$PSQL -f load/fwa_streams.sql


# clean up
$PSQL -c "VACUUM ANALYZE"