LTx2F5vZ

1#!/bin/bash
2# Script name: Taxi_Application.txt
3# Written by: Matei Chiorescu, Alex Hope
4
5##############################################################################################################################################################
6
7# Before creating the application, setup of postgres and postgis need to be added to your system first.
8
9# 1. Installation of PostgreSQL in terminal
10
11# 1.1. Install PostgreSQL under Debian or Ubuntu
12
13	$ sudo apt-get install postgresql
14
15# 1.2. Install PostgreSQL under RedHat, Fedora or CentOS 
16
17	$ yum install postgresql-server.x86_64
18
19##############################################################################################################################################################
20
21# 1. Installation of PostGIS
22# PostGIS is a spatial add-on for the PostgreSQL relational DBMS. Its official website of PostGIS is http://postgis.refractions.net/. 
23
24# 1.1. Installation
25# For Linux users you can either install from source by yourself or use package manager for installation.
26# Best way to do this is to add repository via : 
27# sudo add-apt-repository ppa:ubuntugis/ppa 
28# sudo apt-get update
29# And installing via sudo apt-get install postgis
30#
31# 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.
32
33##############################################################################################################################################################
34
35# Next within Postgres, you will now be able to build the application
36
37# 0. Switch to postgres user in terminal and then open psql
38	
39	$ sudo su postgres
40	input your password;
41	$ psql
42
43# 1. Database creation 
44
45	Create database cwtaxi;
46
47# 2. Connect to database
48
49	\c cwtaxi 
50
51# 3. Add postgis
52
53	CREATE EXTENSION postgis;
54
55# 4. Create table with command
56	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);
57
58# 5. Import data to table with command
59
60# Note! the data has been zipped up with this file, please change the below file location to match where you have saved this file
61
62	 COPY cw_taxiod_postgis FROM ‘/Users/AlexHope/Downloads/green_tripdata_2016-01.csv’ WITH csv HEADER; 
63
64# 6. Add geometry columns to the table with commands
65
66	SELECT AddGeometryColumn('cw_taxiod_postgis', 'pickup_geom', 4326, 'POINT', 2);
67	SELECT AddGeometryColumn('cw_taxiod_postgis', 'dropoff_geom', 4326, 'POINT', 2);
68
69# 7. Populate the columns above with data from the existing table with commands
70
71	UPDATE cw_taxiod_postgis SET pickup_geom = ST_Transform( ST_GeomFromText('POINT('||pickup_longitude||' '||pickup_latitude||')', 4326), 4326);
72	UPDATE cw_taxiod_postgis SET dropoff_geom = ST_Transform( ST_GeomFromText('POINT('||dropoff_longitude||' '||dropoff_latitude||')', 4326), 4326);
73
74# 8. Creating an index for the select statement to use
75
76	create index pickup_geom_index on cw_taxiod_postgis using btree(pickup_geom);           
77
78# 9. Get dropoff locations for the trips that have their start in a pickup range
79# Note! a random pick up location is generated each time to make up for not having a user input for the pick up location
80
81	DROP table if exists heatmap_dropoffs;
82	DROP table if exists heatmap_pickup;
83	CREATE TABLE heatmap_pickup AS SELECT pickup_geom FROM cw_taxiod_postgis
84	ORDER BY RANDOM()
85	LIMIT 1;
86	CREATE TABLE heatmap_dropoffs AS SELECT dropoff_geom FROM (SELECT pickup_geom, dropoff_geom FROM cw_taxiod_postgis
87	WHERE ST_Distance(pickup_geom, (SELECT pickup_geom FROM heatmap_pickup)) <0.005
88	AND DATE_PART('hour',lpep_pickup_datetime) - DATE_PART('hour', CURRENT_TIMESTAMP) < 1
89	AND DATE_PART('hour',lpep_pickup_datetime) - DATE_PART('hour', CURRENT_TIMESTAMP) >= 0
90	AND DATE_PART('minute',lpep_pickup_datetime) - DATE_PART('minute', CURRENT_TIMESTAMP) < 10
91	AND DATE_PART('minute',lpep_pickup_datetime) - DATE_PART('minute', CURRENT_TIMESTAMP) >= 0)
92	AS foo;
93
94# 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.
95
96##############################################################################################################################################################
97
98# Instillation of QGIS
99
100# Download the application from the website: http://www.qgis.org/en/site/
101
102# 1. Run the QGIS application
103
104# 2. Go to plugins option at the top and add the following plugins: "Heatmap", "OpenLayers Plugin".
105
106# 3. Go to postgis layer, and click new, and add the following information:
107
108# Name = postgres
109# Host = Localhost
110# Port = 5432
111# Database = cwtaxi
112# username = postgres
113# password = what password you set in the terminal.
114
115# 4. Select postgres and press connect, Select heatmap drop off and pick up tables, The, press add
116
117# 5. Right click on heatmap_dropoff, select properties, select general, in coordinate reference system press update extends, then press ok
118
119# 5. Right click on heatmap_dropoff, select zoom to layer extent
120
121# 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.
122
123# 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
124
125# 8. Now you will see the compleated heatmap of the possible drop off locations.