Import plow data to postgis

config.mk

MAKEFLAGS += --warn-undefined-variables
SHELL := /bin/bash
.SHELLFLAGS := -eu -o pipefail
.DEFAULT_GOAL := all
.DELETE_ON_ERROR:
.SUFFIXES:

# Variables specific to this build
PG_HOST="127.0.0.1"
PG_USER="postgres"
PG_DB="plows"
PG_PORT="5433"
# BASE_DIR=$(dir $(lastword $(MAKEFILE_LIST)))
# PROCESSORS=$(BASE_DIR)data/processors

Makefile

include config.mk

plows.db:
        @wget -O $@ https://s3.amazonaws.com/clearstreets-data/02-08-2015/plow-02-08-2015.db

route_points.table: plows.db
        @createdb -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) plows
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "CREATE EXTENSION postgis"
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "DROP TABLE IF EXISTS route_points"
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "CREATE TABLE route_points ( \
                        object_id INTEGER, \
                        posting_time TIMESTAMP, \
                        direction INTEGER, \
                        x DOUBLE PRECISION, \
                        y DOUBLE PRECISION, \
                        PRIMARY KEY (object_id, posting_time) \
                )"
        @sqlite3 -noheader -csv $< \
                "SELECT * FROM route_points" | \
                psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "COPY route_points FROM STDIN WITH CSV HEADER DELIMITER','"
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "SELECT AddGeometryColumn ('route_points','geom',3435,'POINT',2)"
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "CREATE INDEX posting_idx ON route_points (posting_time)"
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "UPDATE route_points SET \
                        geom=ST_SetSRID(ST_MakePoint(s.x, s.y), 3435) \
                FROM (SELECT * FROM route_points) AS s \
                WHERE route_points.object_id = s.object_id \
                        AND route_points.posting_time = s.posting_time"
        @psql -U $(PG_USER) -h $(PG_HOST) -p $(PG_PORT) -d plows -c \
                "CREATE INDEX geom_idx ON route_points USING GIST (geom)"
        @touch $@