SHARE
TWEET

cwtaxi

a guest May 22nd, 2017 92 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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.
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top