Revision 11558
Added by Paul Sarando about 11 years ago
| derived/biengeo/geoscrub.sh | ||
|---|---|---|
| 14 | 14 |
|
| 15 | 15 |
DB_NAME="geoscrub" |
| 16 | 16 |
DB_USER="bien" |
| 17 |
DB_HOST="localhost"
|
|
| 17 |
DB_HOST_OPT=""
|
|
| 18 | 18 |
SCRIPT_DIR="$(dirname $0)" |
| 19 | 19 |
|
| 20 | 20 |
function run_sql_script {
|
| 21 | 21 |
local SCRIPT=$1 |
| 22 | 22 |
|
| 23 |
psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" --set ON_ERROR_STOP=1 < "$SCRIPT"
|
|
| 23 |
psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" --set ON_ERROR_STOP=1 < "$SCRIPT"
|
|
| 24 | 24 |
if [[ $? != 0 ]]; then |
| 25 | 25 |
echo "Error while executing SQL script ${SCRIPT}"
|
| 26 | 26 |
exit 1 |
| derived/biengeo/load-geoscrub-input.sh | ||
|---|---|---|
| 25 | 25 |
|
| 26 | 26 |
DB_NAME="geoscrub" |
| 27 | 27 |
DB_USER="bien" |
| 28 |
DB_HOST="localhost"
|
|
| 28 |
DB_HOST_OPT=""
|
|
| 29 | 29 |
SCRIPT_DIR="$(dirname $0)" |
| 30 | 30 |
DATA_URL="http://fs.vegpath.org/exports/geoscrub_input.no_header.cols=country,stateProvince,county,decimalLatitude,decimalLongitude.csv" |
| 31 | 31 |
DATADIR="${SCRIPT_DIR}/input"
|
| ... | ... | |
| 54 | 54 |
echo "Loading vegbien data from ${DATAFILE}"
|
| 55 | 55 |
|
| 56 | 56 |
# clear previous data |
| 57 |
psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" --set ON_ERROR_STOP=1 < "${SCRIPT_DIR}/truncate.vegbien_geoscrub.sql"
|
|
| 57 |
psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" --set ON_ERROR_STOP=1 < "${SCRIPT_DIR}/truncate.vegbien_geoscrub.sql"
|
|
| 58 | 58 |
if [[ $? != 0 ]]; then |
| 59 | 59 |
echo "Could not clear data from vegbien_geoscrub tables." |
| 60 | 60 |
exit 1 |
| 61 | 61 |
fi |
| 62 | 62 |
|
| 63 | 63 |
# load vegbien_geoscrub table with input data |
| 64 |
psql -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" -c "\COPY vegbien_geoscrub FROM '${DATAFILE}' WITH CSV"
|
|
| 64 |
echo "Copying vegbien_geoscrub from ${DATAFILE}"
|
|
| 65 |
psql -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" -c "\COPY vegbien_geoscrub FROM '${DATAFILE}' WITH CSV"
|
|
| 65 | 66 |
|
| derived/biengeo/update_validation_data.sh | ||
|---|---|---|
| 14 | 14 |
|
| 15 | 15 |
DB_NAME="geoscrub" |
| 16 | 16 |
DB_USER="bien" |
| 17 |
DB_HOST="localhost"
|
|
| 17 |
DB_HOST_OPT=""
|
|
| 18 | 18 |
SCRIPT_DIR="$(dirname $0)" |
| 19 | 19 |
|
| 20 | 20 |
function run_sql_script {
|
| 21 | 21 |
local SCRIPT=$1 |
| 22 | 22 |
|
| 23 |
psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" --set ON_ERROR_STOP=1 < "$SCRIPT"
|
|
| 23 |
psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" --set ON_ERROR_STOP=1 < "$SCRIPT"
|
|
| 24 | 24 |
if [[ $? != 0 ]]; then |
| 25 | 25 |
echo "Error while executing SQL script ${SCRIPT}"
|
| 26 | 26 |
exit 1 |
| derived/biengeo/update_geonames_data.sh | ||
|---|---|---|
| 22 | 22 |
|
| 23 | 23 |
DB_NAME="geoscrub" |
| 24 | 24 |
DB_USER="bien" |
| 25 |
DB_HOST="localhost"
|
|
| 25 |
DB_HOST_OPT=""
|
|
| 26 | 26 |
SCRIPT_DIR="$(dirname $0)" |
| 27 | 27 |
GEONAMES_DUMP_URL="http://download.geonames.org/export/dump" |
| 28 | 28 |
ALL_COUNTRIES_ZIP=allCountries.zip |
| ... | ... | |
| 36 | 36 |
function run_sql_cmd {
|
| 37 | 37 |
local SQL_CMD=$1 |
| 38 | 38 |
|
| 39 |
psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" -c "$SQL_CMD"
|
|
| 39 |
psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" -c "$SQL_CMD"
|
|
| 40 | 40 |
if [[ $? != 0 ]]; then |
| 41 | 41 |
echo "Error while executing SQL command." |
| 42 | 42 |
exit 1 |
| ... | ... | |
| 46 | 46 |
function run_sql_script {
|
| 47 | 47 |
local SCRIPT=$1 |
| 48 | 48 |
|
| 49 |
psql --set ON_ERROR_STOP=1 -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" < "$SCRIPT"
|
|
| 49 |
psql -e --set ON_ERROR_STOP=1 -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" < "$SCRIPT"
|
|
| 50 | 50 |
if [[ $? != 0 ]]; then |
| 51 | 51 |
echo "Error while executing SQL script ${SCRIPT}"
|
| 52 | 52 |
exit 1 |
| ... | ... | |
| 87 | 87 |
fi |
| 88 | 88 |
|
| 89 | 89 |
# import data |
| 90 |
echo "Copying geonames from ${DATADIR}/${ALL_COUNTRIES_TXT}"
|
|
| 90 | 91 |
run_sql_cmd "\COPY geonames FROM '${DATADIR}/${ALL_COUNTRIES_TXT}' DELIMITER E'\t' NULL ''"
|
| 91 | 92 |
|
| 92 | 93 |
# |
| ... | ... | |
| 107 | 108 |
fi |
| 108 | 109 |
|
| 109 | 110 |
# import data |
| 111 |
echo "Copying alternateNames from ${DATADIR}/${ALTERNATE_NAMES_TXT}"
|
|
| 110 | 112 |
run_sql_cmd "\COPY alternateNames FROM '${DATADIR}/${ALTERNATE_NAMES_TXT}' DELIMITER E'\t' NULL ''"
|
| 111 | 113 |
run_sql_script "${SCRIPT_DIR}/update.alternateNames.sql"
|
| 112 | 114 |
|
| ... | ... | |
| 126 | 128 |
fi |
| 127 | 129 |
|
| 128 | 130 |
# import geonames country table |
| 131 |
echo "Copying countries from ${DATADIR}/${COUNTRY_INFO_TXT}"
|
|
| 129 | 132 |
grep -ve '^#' "${DATADIR}/${COUNTRY_INFO_TXT}" | \
|
| 130 |
psql -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" -c \
|
|
| 133 |
psql -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" -c \
|
|
| 131 | 134 |
"\COPY countries FROM STDIN DELIMITER E'\t' NULL ''" |
| 132 | 135 |
if [[ $? != 0 ]]; then |
| 133 | 136 |
echo "Could not copy countries table data." |
| ... | ... | |
| 154 | 157 |
fi |
| 155 | 158 |
|
| 156 | 159 |
# import geonames country table |
| 160 |
echo "Copying hierarchy from ${DATADIR}/${HIERARCHY_TXT}"
|
|
| 157 | 161 |
run_sql_cmd "\COPY hierarchy FROM '${DATADIR}/${HIERARCHY_TXT}' DELIMITER E'\t' NULL ''"
|
| 158 | 162 |
run_sql_script "${SCRIPT_DIR}/update.hierarchy.sql"
|
| 159 | 163 |
|
| 160 | 164 |
# augment geonames with some additional places we need, and update |
| 161 | 165 |
# hierarchy table accordingly |
| 166 |
echo "Updating geonames and hierarchy tables..." |
|
| 162 | 167 |
run_sql_script "${SCRIPT_DIR}/update.geonames.sql"
|
| 163 | 168 |
|
| 164 | 169 |
echo "Update geonames.org tables successfully completed." |
| derived/biengeo/setup.sh | ||
|---|---|---|
| 23 | 23 |
|
| 24 | 24 |
DB_NAME="geoscrub" |
| 25 | 25 |
DB_USER="bien" |
| 26 |
DB_HOST="localhost"
|
|
| 26 |
DB_HOST_OPT=""
|
|
| 27 | 27 |
SCRIPT_DIR="$(dirname $0)" |
| 28 | 28 |
|
| 29 | 29 |
function run_sudo_sql_cmd {
|
| 30 | 30 |
local SQL_CMD="$1" |
| 31 | 31 |
|
| 32 |
sudo -u postgres psql -e -h "$DB_HOST" -d "$DB_NAME" -c "$SQL_CMD"
|
|
| 32 |
sudo -u postgres psql -e $DB_HOST_OPT -d "$DB_NAME" -c "$SQL_CMD"
|
|
| 33 | 33 |
if [[ $? != 0 ]]; then |
| 34 | 34 |
echo "Error while executing SQL command." |
| 35 | 35 |
echo -n "You may drop the ${DB_NAME} database"
|
| ... | ... | |
| 41 | 41 |
function run_sql_script {
|
| 42 | 42 |
local SCRIPT="$1" |
| 43 | 43 |
|
| 44 |
psql -e --set ON_ERROR_STOP=1 -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" < "$SCRIPT"
|
|
| 44 |
psql -e --set ON_ERROR_STOP=1 -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" < "$SCRIPT"
|
|
| 45 | 45 |
if [[ $? != 0 ]]; then |
| 46 | 46 |
echo "Error while executing SQL script ${SCRIPT}"
|
| 47 | 47 |
exit 1 |
| ... | ... | |
| 62 | 62 |
|
| 63 | 63 |
# create postgis db from template |
| 64 | 64 |
echo "Creating the ${DB_NAME} database..."
|
| 65 |
createdb -U "$DB_USER" -h "$DB_HOST" "$DB_NAME" -T template_postgis
|
|
| 65 |
createdb --no-password -U "$DB_USER" $DB_HOST_OPT "$DB_NAME" -T template_postgis
|
|
| 66 | 66 |
if [[ $? != 0 ]]; then |
| 67 | 67 |
echo "Could not create ${DB_NAME} database."
|
| 68 | 68 |
exit 1 |
| 69 | 69 |
fi |
| 70 | 70 |
|
| 71 |
echo "Updating ${DB_NAME} table permissions..."
|
|
| 71 | 72 |
run_sudo_sql_cmd "ALTER TABLE spatial_ref_sys OWNER TO ${DB_USER}"
|
| 72 | 73 |
run_sudo_sql_cmd "ALTER VIEW geography_columns OWNER TO ${DB_USER}"
|
| 73 | 74 |
run_sudo_sql_cmd "ALTER VIEW geometry_columns OWNER TO ${DB_USER}"
|
| derived/biengeo/update_gadm_data.sh | ||
|---|---|---|
| 25 | 25 |
|
| 26 | 26 |
DB_NAME="geoscrub" |
| 27 | 27 |
DB_USER="bien" |
| 28 |
DB_HOST="localhost"
|
|
| 28 |
DB_HOST_OPT=""
|
|
| 29 | 29 |
SCRIPT_DIR="$(dirname $0)" |
| 30 | 30 |
|
| 31 | 31 |
# GADM data originally available at http://www.gadm.org/data2/gadm_v2_shp.zip |
| ... | ... | |
| 62 | 62 |
# create and populate gadm2 table |
| 63 | 63 |
# |
| 64 | 64 |
|
| 65 |
psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" -c "DROP TABLE IF EXISTS gadm2"
|
|
| 65 |
psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" -c "DROP TABLE IF EXISTS gadm2"
|
|
| 66 | 66 |
if [[ $? != 0 ]]; then |
| 67 | 67 |
echo "Could not drop GADM2 table in ${DB_NAME} database."
|
| 68 | 68 |
exit 1 |
| ... | ... | |
| 74 | 74 |
|
| 75 | 75 |
# load gadm2 data (took 4.7 minutes on willow, 26-Oct-2012) |
| 76 | 76 |
shp2pgsql -s 4326 -W latin1 gadm2 gadm2 "$DB_NAME" | \ |
| 77 |
psql --set ON_ERROR_STOP=1 -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME"
|
|
| 77 |
psql --set ON_ERROR_STOP=1 -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME"
|
|
| 78 | 78 |
if [[ $? != 0 ]]; then |
| 79 | 79 |
popd |
| 80 | 80 |
echo "Could not load GADM2 data into ${DB_NAME} database."
|
| ... | ... | |
| 84 | 84 |
popd |
| 85 | 85 |
|
| 86 | 86 |
# create indexes and additional columns |
| 87 |
psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" --set ON_ERROR_STOP=1 < "${SCRIPT_DIR}"/update.gadm2.sql
|
|
| 87 |
psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" --set ON_ERROR_STOP=1 < "${SCRIPT_DIR}"/update.gadm2.sql
|
|
| 88 | 88 |
if [[ $? != 0 ]]; then |
| 89 | 89 |
echo "Could not update GADM2 data and indexes in ${DB_NAME} database."
|
| 90 | 90 |
exit 1 |
Also available in: Unified diff
Fix biengeo script password prompt for postgres user.
Changed the DB_HOST variables in the biengeo bash scripts to a
DB_HOST_OPT variable that is blank by default.
Updated all psql calls that used "-h $DB_HOST" to use just $DB_HOST_OPT
instead.
This means that to specify a different db host, the DB_HOST_OPT
variables must include the "-h " flag.
For example:
DB_HOST_OPT="-h localhost"
Added some additional logging statements for long running SQL statements
so the user knows what is currently processing.