# ------------------------------------------------
# Read_LR_PPD.sql
# ------------------------------------------------
# Code provided as is and can be used or modified freely. 
#
# ------------------------------------------------
# Author: BIN CHI
# UCL Centre for Advanced Spatial Analysis
# Date: 19/12/2019

#Create a pricepaid table
CREATE TABLE pricepaid
(
  transactionid text NOT NULL,
  price bigint,
  dateoftransfer date,
  postcode text,
  propertytype text,
  oldnew text,
  duration text,
  paon text,
  saon text,
  street text,
  locality text,
  towncity text,
  district text,
  county text,
  categorytype text,
  recordstatus text
)

#Read in the Land Registry PPD dataset 
#(1)Using the code below to read in pp-complete.csv from https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads
COPY pricepaid FROM 'D:/pp-complete.csv' DELIMITERS ',' CSV QUOTE '"';
##Query returned successfully: 24852949 rows affected, 04:01 minutes execution time.

#(2)Using the code below to read in the in pp-complete.csv from UKDA
COPY pricepaid FROM 'D:/pp-complete.csv' DELIMITERS ',' CSV HEADER;


#Add in a new column yearchi to records the year of the tranction.
ALTER TABLE pricepaid ADD yearchi int;

UPDATE pricepaid SET yearchi= date_part('year', dateoftransfer);
#Query returned successfully: 24852949 rows affected, 08:41 minutes execution time.