Last active
June 12, 2020 10:24
-
-
Save dominijk/70f7dc4161165cede997783f15859520 to your computer and use it in GitHub Desktop.
SQL for taking doogals excellent postcodes into SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Head to doogal and get the data you need | |
--https://www.doogal.co.uk/PostcodeDownloads.php | |
--then create the table as below, metadata as | |
--https://www.doogal.co.uk/PostcodeCsvFields.php | |
CREATE TABLE doogal_data.postcodes ( | |
postcode character varying(8) PRIMARY KEY, | |
in_use character varying(3), | |
latitude numeric(9,6), | |
longitude numeric(9,6), | |
easting integer, | |
northing integer, | |
grid_ref character varying(8), | |
county text, | |
district text, | |
ward text, | |
district_code character varying(9), | |
ward_code character varying(9), | |
country character varying(17), | |
county_code character varying(9), | |
constituency character varying(43), | |
introduced date, | |
terminated date, | |
parish character varying(54), | |
national_park character varying(50), | |
population integer, | |
households integer, | |
built_up_area character varying(54), | |
built_up_subdivision character varying(41), | |
lower_layer_super_output_area character varying(63), | |
ruralurban character varying(100), | |
region character varying(24), | |
altitude integer, | |
london_zone character varying(30), | |
lsoa_code character varying(9), | |
local_authority character varying(30), | |
msoa_code character varying(9), | |
middle_layer_super_output_area character varying(58), | |
parish_code character varying(30), | |
census_output_area character varying(9), | |
constituency_code character varying(9), | |
index_of_multiple_deprivation integer, | |
quality integer, | |
user_type bit(1), | |
last_updated date, | |
nearest_station_name text, | |
distance_to_station numeric, | |
postcode_area varchar(3), | |
postcode_district varchar(4), | |
police_force (text), | |
water_company (text), | |
plus_code varchar(7), | |
avg_income integer | |
); | |
--then copy the data into the table | |
COPY doogal_data.postcodes FROM 'file path to where you put the downloaded csv' WITH CSV HEADER; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
New columns added