<commit_before>#!/bin/bash

set -e
set -x

usage="$(basename "$0") [-h] [-b] [-s] \n
--Sets up a postgresql database for MMW \n
\n
where: \n
    -h  show this help text\n
    -b  load/reload boundary data\n
    -f  load a named boundary sql.gz\n
    -s  load/reload stream data\n
    -d  load/reload DRB stream data\n
    -m  load/reload mapshed data\n
    -p  load/reload DEP data\n
    -c  load/reload nhdplus catchment data
    -q  load/reload water quality data\n
    -x  purge s3 cache for given path\n
"

# HTTP accessible storage for initial app data
FILE_HOST="https://s3.amazonaws.com/data.mmw.azavea.com"
load_boundary=false
file_to_load=
load_stream=false
load_hires_stream=false
load_mapshed=false
load_water_quality=false
load_catchment=false

while getopts ":hbsSdpmqcf:x:" opt; do
    case $opt in
        h)
            echo -e $usage
            exit ;;
        b)
            load_boundary=true ;;
        s)
            load_stream=true ;;
        S)
            load_hires_stream=true ;;
        d)
            load_drb_streams=true ;;
        p)
            load_dep=true ;;
        m)
            load_mapshed=true ;;
        q)
            load_water_quality=true ;;
        c)
            load_catchment=true ;;
        f)
            file_to_load=$OPTARG ;;
        x)
            path_to_purge=$OPTARG ;;
        \?)
            echo "invalid option: -$OPTARG"
            exit ;;
    esac
done

# Export settings required to run psql non-interactively
export PGHOST=$(cat /etc/mmw.d/env/MMW_DB_HOST)
export PGDATABASE=$(cat /etc/mmw.d/env/MMW_DB_NAME)
export PGUSER=$(cat /etc/mmw.d/env/MMW_DB_USER)
export PGPASSWORD=$(cat /etc/mmw.d/env/MMW_DB_PASSWORD)
export PUBLIC_HOSTED_ZONE_NAME=$(cat /etc/mmw.d/env/MMW_PUBLIC_HOSTED_ZONE_NAME)

# Ensure that the PostGIS extension exists
psql -c "CREATE EXTENSION IF NOT EXISTS postgis;"
psql -c "ALTER TABLE spatial_ref_sys OWNER TO ${PGUSER};"

# Create pg_trgm extension for faster LIKE matches
psql -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

# Run migrations
envdir /etc/mmw.d/env /opt/app/manage.py migrate

function download_and_load {
    for f in "${FILES[@]}"; do
        curl -s $FILE_HOST/$f | gunzip -q | psql --single-transaction
    done
}

function purge_tile_cache {
    for path in "${PATHS[@]}"; do
        aws s3 rm --recursive "s3://tile-cache.${PUBLIC_HOSTED_ZONE_NAME}/${path}/"
    done
}

function create_trgm_indexes {
    # Creates a trgm index on the 'name' column for each table.
    for table in "${TRGM_TABLES[@]}"; do
        psql -c "CREATE INDEX IF NOT EXISTS trgm_idx_${table}_name ON ${table}"`
                `" USING gin (name gin_trgm_ops);"
    done
}

if [ ! -z "$file_to_load" ] ; then
    FILES=("$file_to_load")
    download_and_load $FILES
fi

if [ ! -z "$path_to_purge" ] ; then
    PATHS=("$path_to_purge")
    purge_tile_cache $PATHS
fi

if [ "$load_dep" = "true" ] ; then
    # Fetch DEP layers
    FILES=("dep_urban_areas.sql.gz" "dep_municipalities.sql.gz")
    PATHS=("urban_areas" "municipalities")

    download_and_load $FILES
    purge_tile_cache $PATHS
fi


if [ "$load_boundary" = "true" ] ; then
    # Fetch boundary layer sql files
    FILES=("boundary_county.sql.gz" "boundary_school_district.sql.gz" "boundary_district.sql.gz" "boundary_huc12_deduped.sql.gz" "boundary_huc10.sql.gz" "boundary_huc08.sql.gz")
    PATHS=("county" "district" "huc8" "huc10" "huc12" "school")
    TRGM_TABLES=("boundary_huc08" "boundary_huc10" "boundary_huc12")

    download_and_load $FILES
    create_trgm_indexes $TRGM_TABLES
    purge_tile_cache $PATHS
fi

if [ "$load_stream" = "true" ] ; then
    # Fetch stream network layer sql files
    FILES=("nhdflowline_1513899196.sql.gz")
    PATHS=("nhd_streams_v2")

    download_and_load $FILES
    purge_tile_cache $PATHS
fi

if [ "$load_hires_stream" = "true" ] ; then<commit_msg>Update boundary county dataset format<commit_after>#!/bin/bash

set -e
set -x

usage="$(basename "$0") [-h] [-b] [-s] \n
--Sets up a postgresql database for MMW \n
\n
where: \n
    -h  show this help text\n
    -b  load/reload boundary data\n
    -f  load a named boundary sql.gz\n
    -s  load/reload stream data\n
    -d  load/reload DRB stream data\n
    -m  load/reload mapshed data\n
    -p  load/reload DEP data\n
    -c  load/reload nhdplus catchment data
    -q  load/reload water quality data\n
    -x  purge s3 cache for given path\n
"

# HTTP accessible storage for initial app data
FILE_HOST="https://s3.amazonaws.com/data.mmw.azavea.com"
load_boundary=false
file_to_load=
load_stream=false
load_hires_stream=false
load_mapshed=false
load_water_quality=false
load_catchment=false

while getopts ":hbsSdpmqcf:x:" opt; do
    case $opt in
        h)
            echo -e $usage
            exit ;;
        b)
            load_boundary=true ;;
        s)
            load_stream=true ;;
        S)
            load_hires_stream=true ;;
        d)
            load_drb_streams=true ;;
        p)
            load_dep=true ;;
        m)
            load_mapshed=true ;;
        q)
            load_water_quality=true ;;
        c)
            load_catchment=true ;;
        f)
            file_to_load=$OPTARG ;;
        x)
            path_to_purge=$OPTARG ;;
        \?)
            echo "invalid option: -$OPTARG"
            exit ;;
    esac
done

# Export settings required to run psql non-interactively
export PGHOST=$(cat /etc/mmw.d/env/MMW_DB_HOST)
export PGDATABASE=$(cat /etc/mmw.d/env/MMW_DB_NAME)
export PGUSER=$(cat /etc/mmw.d/env/MMW_DB_USER)
export PGPASSWORD=$(cat /etc/mmw.d/env/MMW_DB_PASSWORD)
export PUBLIC_HOSTED_ZONE_NAME=$(cat /etc/mmw.d/env/MMW_PUBLIC_HOSTED_ZONE_NAME)

# Ensure that the PostGIS extension exists
psql -c "CREATE EXTENSION IF NOT EXISTS postgis;"
psql -c "ALTER TABLE spatial_ref_sys OWNER TO ${PGUSER};"

# Create pg_trgm extension for faster LIKE matches
psql -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

# Run migrations
envdir /etc/mmw.d/env /opt/app/manage.py migrate

function download_and_load {
    for f in "${FILES[@]}"; do
        curl -s $FILE_HOST/$f | gunzip -q | psql --single-transaction
    done
}

function purge_tile_cache {
    for path in "${PATHS[@]}"; do
        aws s3 rm --recursive "s3://tile-cache.${PUBLIC_HOSTED_ZONE_NAME}/${path}/"
    done
}

function create_trgm_indexes {
    # Creates a trgm index on the 'name' column for each table.
    for table in "${TRGM_TABLES[@]}"; do
        psql -c "CREATE INDEX IF NOT EXISTS trgm_idx_${table}_name ON ${table}"`
                `" USING gin (name gin_trgm_ops);"
    done
}

if [ ! -z "$file_to_load" ] ; then
    FILES=("$file_to_load")
    download_and_load $FILES
fi

if [ ! -z "$path_to_purge" ] ; then
    PATHS=("$path_to_purge")
    purge_tile_cache $PATHS
fi

if [ "$load_dep" = "true" ] ; then
    # Fetch DEP layers
    FILES=("dep_urban_areas.sql.gz" "dep_municipalities.sql.gz")
    PATHS=("urban_areas" "municipalities")

    download_and_load $FILES
    purge_tile_cache $PATHS
fi


if [ "$load_boundary" = "true" ] ; then
    # Fetch boundary layer sql files
    FILES=("boundary_county_20210910.sql.gz" "boundary_school_district.sql.gz" "boundary_district.sql.gz" "boundary_huc12_deduped.sql.gz" "boundary_huc10.sql.gz" "boundary_huc08.sql.gz")
    PATHS=("county" "district" "huc8" "huc10" "huc12" "school")
    TRGM_TABLES=("boundary_huc08" "boundary_huc10" "boundary_huc12")

    download_and_load $FILES
    create_trgm_indexes $TRGM_TABLES
    purge_tile_cache $PATHS
fi

if [ "$load_stream" = "true" ] ; then
    # Fetch stream network layer sql files
    FILES=("nhdflowline_1513899196.sql.gz")
    PATHS=("nhd_streams_v2")

    download_and_load $FILES
    purge_tile_cache $PATHS
fi

if [ "$load_hires_stream" = "true" ] ; then
{'fraction_non_alphanumeric': 0.11779935275080906, 'fraction_numerical': 0.009579288025889967, 'mean_word_length': 2.815308641975309, 'pattern_counts': {'":': 2, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1321351', 'n_tokens_mistral': 3022, 'n_tokens_neox': 2842, 'n_words': 675}