cwtaxi

#!/bin/bash
# Script name: Taxi_Application.txt
# Written by: Matei Chiorescu, Alex Hope

##############################################################################################################################################################

# Before creating the application, setup of postgres and postgis need to be added to your system first.

# 1. Installation of PostgreSQL in terminal

# 1.1. Install PostgreSQL under Debian or Ubuntu

	$ sudo apt-get install postgresql

# 1.2. Install PostgreSQL under RedHat, Fedora or CentOS

	$ yum install postgresql-server.x86_64

##############################################################################################################################################################

# 1. Installation of PostGIS
# PostGIS is a spatial add-on for the PostgreSQL relational DBMS. Its official website of PostGIS is http://postgis.refractions.net/.

# 1.1. Installation
# For Linux users you can either install from source by yourself or use package manager for installation.
# Best way to do this is to add repository via :
# sudo add-apt-repository ppa:ubuntugis/ppa
# sudo apt-get update
# And installing via sudo apt-get install postgis
#
# For Windows users, we have two options to download and install PostGIS, either from the Application stack Builder (Find it in the Startup menu) or from the prebuilt binary installer which can be downloaded from http://ftp.postgresql.org/pub/postgis/pg9.4/v2.2.0/win64/. Usually, as an add-on, PostGIS is installed in the directory of PostgreSQL and typically there are two things to do. The first is the configuration of database connection. Set the user name and password the same to those of PostgreSQL superuser: postgres and 12345678 (Refer to Tutorial 1). The second is click YES to all the pop-up windows appeared thereafter.

##############################################################################################################################################################

# Next within Postgres, you will now be able to build the application

# 0. Switch to postgres user in terminal and then open psql

	$ sudo su postgres
	input your password;
	$ psql

# 1. Database creation

	Create database cwtaxi;

# 2. Connect to database

	\c cwtaxi

# 3. Add postgis

	CREATE EXTENSION postgis;

# 4. Create table with command
	create table cw_taxiod_postgis(VendorID int,lpep_pickup_datetime timestamp,lpep_dropoff_datetime timestamp,Store_and_fwd_flag char,RateCodeID integer,Pickup_longitude double precision,Pickup_latitude double precision,Dropoff_longitude double precision,Dropoff_latitude double precision,Passenger_count integer,Trip_distance double precision,Fare_amount double precision,Extra double precision,MTA_tax double precision,Tip_amount double precision,Tolls_amount double precision,Ehail_fee double precision,improvement_surcharge double precision,Total_amount double precision,Payment_type integer,Trip_type integer);

# 5. Import data to table with command

# Note! the data has been zipped up with this file, please change the below file location to match where you have saved this file

	 COPY cw_taxiod_postgis FROM ‘/Users/AlexHope/Downloads/green_tripdata_2016-01.csv’ WITH csv HEADER;

# 6. Add geometry columns to the table with commands

	SELECT AddGeometryColumn('cw_taxiod_postgis', 'pickup_geom', 4326, 'POINT', 2);
	SELECT AddGeometryColumn('cw_taxiod_postgis', 'dropoff_geom', 4326, 'POINT', 2);

# 7. Populate the columns above with data from the existing table with commands

	UPDATE cw_taxiod_postgis SET pickup_geom = ST_Transform( ST_GeomFromText('POINT('||pickup_longitude||' '||pickup_latitude||')', 4326), 4326);
	UPDATE cw_taxiod_postgis SET dropoff_geom = ST_Transform( ST_GeomFromText('POINT('||dropoff_longitude||' '||dropoff_latitude||')', 4326), 4326);

# 8. Creating an index for the select statement to use

	create index pickup_geom_index on cw_taxiod_postgis using btree(pickup_geom);

# 9. Get dropoff locations for the trips that have their start in a pickup range
# Note! a random pick up location is generated each time to make up for not having a user input for the pick up location

	DROP table if exists heatmap_dropoffs;
	DROP table if exists heatmap_pickup;
	CREATE TABLE heatmap_pickup AS SELECT pickup_geom FROM cw_taxiod_postgis
	ORDER BY RANDOM()
	LIMIT 1;
	CREATE TABLE heatmap_dropoffs AS SELECT dropoff_geom FROM (SELECT pickup_geom, dropoff_geom FROM cw_taxiod_postgis
	WHERE ST_Distance(pickup_geom, (SELECT pickup_geom FROM heatmap_pickup)) <0.005
	AND DATE_PART('hour',lpep_pickup_datetime) - DATE_PART('hour', CURRENT_TIMESTAMP) < 1
	AND DATE_PART('hour',lpep_pickup_datetime) - DATE_PART('hour', CURRENT_TIMESTAMP) >= 0
	AND DATE_PART('minute',lpep_pickup_datetime) - DATE_PART('minute', CURRENT_TIMESTAMP) < 10
	AND DATE_PART('minute',lpep_pickup_datetime) - DATE_PART('minute', CURRENT_TIMESTAMP) >= 0)
	AS foo;

# Note! at this point you now have all the data to retreave all of the drop off locations from the sigle pick up location. The next steps will be showing you how to visualise the data using a heatmap.

##############################################################################################################################################################

# Instillation of QGIS

# Download the application from the website: http://www.qgis.org/en/site/

# 1. Run the QGIS application

# 2. Go to plugins option at the top and add the following plugins: "Heatmap", "OpenLayers Plugin".

# 3. Go to postgis layer, and click new, and add the following information:

# Name = postgres
# Host = Localhost
# Port = 5432
# Database = cwtaxi
# username = postgres
# password = what password you set in the terminal.

# 4. Select postgres and press connect, Select heatmap drop off and pick up tables, The, press add

# 5. Right click on heatmap_dropoff, select properties, select general, in coordinate reference system press update extends, then press ok

# 5. Right click on heatmap_dropoff, select zoom to layer extent

# 6. Select raster at the top, select heatmap, select you output folder, change radius to 400 meters, click advanced and change rows to 2000, the press enter and the heatmap will show up.

# 7. Right click on the heatmap that you have made, select properties, select style, change render type to "singleband pseudocolor", in generate new colour map select "YLOrBr", select min max radio button, press the load button, then press the classify button, select the transparency tav on the left, change transparency to 25%, then press ok

# 8. Now you will see the compleated heatmap of the possible drop off locations.