RunKeeper export data to PostGIS script

gistfile1.sh

#!/bin/bash

# RunKeeper data to PostGIS
# =========================
# 1. Download your RunKeeper data from http://runkeeper.com/exportDataForm
# 2. Extract the zip file and cd into the extracted folder
# 3. Run this script, adapting the config parameters to your setup
#    Note: the database must be PostGIS enabled
#
# You may need to change the way psql is called depending on your Postgres 
# authorization setup.


# Config
# ======
# Tweak to your own needs

DB_NAME="db_test"
OGR_PG_CONNECTION_STRING="PG:dbname='$DB_NAME' host='localhost' port='5432' user='XXXXXX' password='YYYYYY'"

TABLE_NAME="activities"
TEMP_TABLE_NAME="activities_geom"
GEOM_FIELD_NAME="the_geom"

BASE_DIR=$(pwd) 

# SQL commands
# ============

SQL_1="
CREATE TABLE $TABLE_NAME (
    id serial,
    \"date\" timestamp,
    \"type\" text,
    route_name text,
    distance double precision,
    duration time,
    average_pace interval MINUTE TO SECOND,
	average_speed double precision,
	calories_burned double precision,
	climb double precision,
	average_heart_rate double precision,
	notes text,
	gpx_file text
);

SELECT AddGeometryColumn('$TABLE_NAME', '$GEOM_FIELD_NAME', 4326, 'MULTILINESTRING', 2);

COPY $TABLE_NAME (\"date\",\"type\",route_name,distance,duration,average_pace,average_speed,calories_burned,climb,average_heart_rate,notes,gpx_file)
    FROM '$BASE_DIR/cardioActivities.csv' DELIMITERS ',' CSV HEADER;

CREATE TABLE $TEMP_TABLE_NAME (
	gpx_file text
);

SELECT AddGeometryColumn('$TEMP_TABLE_NAME', 'wkb_geometry', 4326, 'MULTILINESTRING', 2);
"

SQL_2="
UPDATE $TABLE_NAME a SET $GEOM_FIELD_NAME = b.wkb_geometry FROM $TEMP_TABLE_NAME b WHERE a.gpx_file = b.gpx_file;

DROP TABLE $TEMP_TABLE_NAME;
"

# ==================

echo "Creating tables and importing fields from CSV file..."
sudo -u postgres psql -c "$SQL_1" $DB_NAME
echo "Importing geometries from GPX files..."
for i in $(ls *.gpx);
    do ogr2ogr -f PostgreSQL "$OGR_PG_CONNECTION_STRING" $i -nln $TEMP_TABLE_NAME -update -append -sql "Select '$i' as gpx_file from tracks";
done
echo "Merging geometries to fields table..."
sudo -u postgres psql -c "$SQL_2" $DB_NAME
echo "Done"