-- Import the roads network as a shape first -- Also if you haven't imported the westminster, borough and ward boundaries you'll need to do so -- Now create and import the three tables DROP TABLE IF EXISTS major_roads_direction; CREATE TABLE major_roads_direction( Year Integer, CP Integer, ONS_GOR_Name text, ONS_LA_Name text, Road text, RCat text, iDir text, S_Ref_E text, S_Ref_N text, A_Junction text, B_Junction text, LenNet Double Precision, LenNet_miles Double Precision, FdPC Double Precision, Fd2WMV Double Precision, FdCAR Double Precision, FdBUS Double Precision, FdLGV Double Precision, FdHGVR2 Double Precision, FdHGVR3 Double Precision, FdHGVR4 Double Precision, FdHGVA3 Double Precision, FdHGVA5 Double Precision, FdHGVA6 Double Precision, FdHGV Double Precision, FdAll_MV Double Precision ); COPY major_roads_direction FROM 'C:\csvimport\AADF-data-by-direction-major-roads.csv' WITH CSV HEADER; DROP TABLE IF EXISTS major_roads; CREATE TABLE major_roads( Year Integer, CP Integer, ONS_GOR_Name text, ONS_LA_Name text, Road text, RCat text, S_Ref_E text, S_Ref_N text, A_Junction text, B_Junction text, LenNet Double Precision, LenNet_miles Double Precision, FdPC Double Precision, Fd2WMV Double Precision, FdCar Double Precision, FdBUS Double Precision, FdLGV Double Precision, FdHGVR2 Double Precision, FdHGVR3 Double Precision, FdHGVR4 Double Precision, FdHGVA3 Double Precision, FdHGVA5 Double Precision, FdHGVA6 Double Precision, FdHGV Double Precision, FdAll_MV Double Precision ); COPY major_roads FROM 'C:\csvimport\AADF-data-major-roads.csv' WITH CSV HEADER; DROP TABLE IF EXISTS minor_roads; CREATE TABLE minor_roads( Year Integer, CP Integer, ONS_GOR_Name text, ONS_LA_Name text, Road text, RCat text, S_Ref_E text, S_Ref_N text, FdPC Double Precision, Fd2WMV Double Precision, FdCar Double Precision, FdBUS Double Precision, FdLGV Double Precision, FdHGVR2 Double Precision, FdHGVR3 Double Precision, FdHGVR4 Double Precision, FdHGVA3 Double Precision, FdHGVA5 Double Precision, FdHGVA6 Double Precision, FdHGV Double Precision, FdAll_MV Double Precision ); COPY minor_roads FROM 'C:\csvimport\AADF-data-minor-roads.csv' WITH CSV HEADER; DROP TABLE IF EXISTS cptolocate; -- make a simple collated table of all the count points we need to find CREATE TABLE cptolocate AS SELECT DISTINCT CP, S_Ref_E, S_Ref_N from ( SELECT CP, S_Ref_E, S_Ref_N FROM major_roads_direction UNION SELECT CP, S_Ref_E, S_Ref_N FROM minor_roads UNION SELECT CP, S_Ref_E, S_Ref_N FROM major_roads) as grouped; -- Add a geometry column to the locations we are finding SELECT AddGeometryColumn ('public','cptolocate', 'geom_gb', 27700, 'POINT', 2); -- Create geometry points for the tables in the 27700 SRID UPDATE cptolocate SET geom_gb=ST_GeomFromText('POINT('||S_Ref_E||' '||S_Ref_N||')',27700); -- Lets add columns to the table for: lat, long ALTER TABLE cptolocate ADD COLUMN latitude text, ADD COLUMN longitude text; -- Extract the latitude and longitude from that point UPDATE cptolocate SET longitude=st_x(st_transform(geom_gb,4326)), latitude=st_y(st_transform(geom_gb,4326)); -- Add the spatial index CREATE INDEX gist_rgb ON cptolocate USING gist (geom_gb); -- As used before , these regions are shapefiles imported into postgresql using the shapefile importeer (SRID 27700) CREATE TABLE RoadCPLocationsWestminster AS SELECT lo.cp, wm.name, wm.code FROM cptolocate as lo JOIN westminster_const_region as wm on ST_Within(lo.geom_gb, wm.geom); CREATE TABLE RoadCPLocationsBorough AS SELECT lo.cp, bo.name, bo.code FROM cptolocate as lo JOIN district_borough_unitary_region as bo on ST_Within(lo.geom_gb, bo.geom); CREATE TABLE RoadCPLocationsWard AS SELECT lo.cp, wa.name, wa.code FROM cptolocate as lo JOIN district_borough_unitary_ward_region as wa on ST_Within(lo.geom_gb, wa.geom); CREATE TABLE RoadCPLocationsFound AS SELECT lo.cp, lo.latitude, lo.longitude, wm.name as westminstername,wm.code as westminstercode, bo.name as boroughname, bo.code as boroughcode, wa.name as wardname, wa.code as wardcode, geom_gb FROM cptolocate lo LEFT OUTER JOIN RoadCPLocationsWestminster as wm ON lo.cp = wm.cp LEFT OUTER JOIN RoadCPLocationsBorough as bo ON lo.cp = bo.cp LEFT OUTER JOIN RoadCPLocationsWard as wa ON lo.cp = wa.cp; -- create the PCU counts and rename the vehicle counts to something meaningful. This uses a subselect as the main query which is a union of the tables in the non-directed major roads data and the minor roads data DROP TABLE IF EXISTS pcu_roads; CREATE TABLE pcu_roads AS SELECT year, cp, Road, RCat, FdPC as cycles, Fd2WMV as p2w, FdCAR as cars, FdBUS as buses, FdLGV as lgvs, FdHGVR2 as mgvs, (FdHGVR3 + FdHGVR4 + FdHGVA3 + FdHGVA5 + FdHGVA6) as hgvs, (Fd2WMV + FdCAR + FdBUS + FdLGV + FdHGVR2 + (FdHGVR3 + FdHGVR4 + FdHGVA3 + FdHGVA5 + FdHGVA6)) as all_motors, FdPC * 0.2 as cycle_pcu, Fd2WMV * 0.4 as p2w_pcu, FdCAR as car_pcu, FdBUS * 2 as bus_pcu, FdLGV as lgv_pcu, FdHGVR2 * 1.5 as mgv_pcu, (FdHGVR3 + FdHGVR4 + FdHGVA3 + FdHGVA5 + FdHGVA6) * 2.3 as hgv_pcu, Fd2WMV * 0.4 + FdCAR + FdBUS * 2 + FdLGV + FdHGVR2 * 1.5 + (FdHGVR3 + FdHGVR4 + FdHGVA3 + FdHGVA5 + FdHGVA6) * 2.3 as all_motor_pcu FROM (SELECT year, cp, Road, RCat, FdPC, Fd2WMV, FdCAR, FdBUS, FdLGV, FdHGVR2, FdHGVR3, FdHGVR4, FdHGVA3, FdHGVA5, FdHGVA6 FROM major_roads UNION SELECT year, cp, Road, RCat, FdPC, Fd2WMV, FdCAR, FdBUS, FdLGV, FdHGVR2, FdHGVR3, FdHGVR4, FdHGVA3, FdHGVA5, FdHGVA6 FROM minor_roads) AS major_minor_roads; -- a conversion table to make the field RCat meaningful CREATE TABLE x_road_cat ( RCat text, road_type text ); INSERT INTO x_road_cat VALUES ('PM','M or Class A Principal Motorway'), ('PR','Class A Principal road in Rural area'), ('PU','Class A Principal road in Urban area'), ('TM','M or Class A Trunk Motorway'), ('TR','Class A Trunk road in Rural area'), ('TU','Class A Trunk road in Urban area'), ('BR','Class B road in Rural area'), ('BU','Class B road in Urban area'), ('CR','Class C road in Rural area'), ('CU','Class C road in Urban area'), ('UR','Class U road in Rural area'), ('UU','Class U road in Urban area'); -- make a table to contain all the political details DROP TABLE IF EXISTS pcu_roads_political; CREATE TABLE pcu_roads_political AS SELECT lo.westminstername, lo.boroughname, lo.wardname, pcu.cp, pcu.road, road_type, pcu.year, cycles, p2w, cars, buses, lgvs, mgvs, hgvs, all_motors, cycle_pcu, p2w_pcu, car_pcu, bus_pcu, lgv_pcu, mgv_pcu, hgv_pcu, all_motor_pcu, lo.latitude, lo.longitude, ST_AsKML(ST_Transform(road.geom,4326)) as road_geom FROM pcu_roads pcu INNER JOIN RoadCPLocationsFound lo ON pcu.cp = lo.cp INNER JOIN x_road_cat ON pcu.rcat = x_road_cat.rcat LEFT JOIN major_road_link_network road ON pcu.cp = road.cp; -- do cross tabs for: cycles, p2w, cars, buses, lgvs, mgvs, hgvs, all_motors, all_motor_pcu DROP TABLE IF EXISTS aadf_cycles_years; Create TABLE aadf_cycles_years AS SELECT * FROM crosstab( 'SELECT cp, year, cycles FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, cycles_00 int, cycles_01 int, cycles_02 int, cycles_03 int, cycles_04 int, cycles_05 int, cycles_06 int, cycles_07 int, cycles_08 int, cycles_09 int, cycles_10 int, cycles_11 int, cycles_12 int); DROP TABLE IF EXISTS aadf_p2w_years; Create TABLE aadf_p2w_years AS SELECT * FROM crosstab( 'SELECT cp, year, p2w FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, p2w_00 int, p2w_01 int, p2w_02 int, p2w_03 int, p2w_04 int, p2w_05 int, p2w_06 int, p2w_07 int, p2w_08 int, p2w_09 int, p2w_10 int, p2w_11 int, p2w_12 int); DROP TABLE IF EXISTS aadf_cars_years; Create TABLE aadf_cars_years AS SELECT * FROM crosstab( 'SELECT cp, year, cars FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, cars_00 int, cars_01 int, cars_02 int, cars_03 int, cars_04 int, cars_05 int, cars_06 int, cars_07 int, cars_08 int, cars_09 int, cars_10 int, cars_11 int, cars_12 int); DROP TABLE IF EXISTS aadf_buses_years; Create TABLE aadf_buses_years AS SELECT * FROM crosstab( 'SELECT cp, year, buses FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, buses_00 int, buses_01 int, buses_02 int, buses_03 int, buses_04 int, buses_05 int, buses_06 int, buses_07 int, buses_08 int, buses_09 int, buses_10 int, buses_11 int, buses_12 int); DROP TABLE IF EXISTS aadf_lgvs_years; Create TABLE aadf_lgvs_years AS SELECT * FROM crosstab( 'SELECT cp, year, lgvs FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, lgvs_00 int, lgvs_01 int, lgvs_02 int, lgvs_03 int, lgvs_04 int, lgvs_05 int, lgvs_06 int, lgvs_07 int, lgvs_08 int, lgvs_09 int, lgvs_10 int, lgvs_11 int, lgvs_12 int); DROP TABLE IF EXISTS aadf_mgvs_years; Create TABLE aadf_mgvs_years AS SELECT * FROM crosstab( 'SELECT cp, year, mgvs FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, mgvs_00 int, mgvs_01 int, mgvs_02 int, mgvs_03 int, mgvs_04 int, mgvs_05 int, mgvs_06 int, mgvs_07 int, mgvs_08 int, mgvs_09 int, mgvs_10 int, mgvs_11 int, mgvs_12 int); DROP TABLE IF EXISTS aadf_hgvs_years; Create TABLE aadf_hgvs_years AS SELECT * FROM crosstab( 'SELECT cp, year, hgvs FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, hgvs_00 int, hgvs_01 int, hgvs_02 int, hgvs_03 int, hgvs_04 int, hgvs_05 int, hgvs_06 int, hgvs_07 int, hgvs_08 int, hgvs_09 int, hgvs_10 int, hgvs_11 int, hgvs_12 int); DROP TABLE IF EXISTS aadf_motors_years; Create TABLE aadf_motors_years AS SELECT * FROM crosstab( 'SELECT cp, year, all_motors FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, motors_00 int, motors_01 int, motors_02 int, motors_03 int, motors_04 int, motors_05 int, motors_06 int, motors_07 int, motors_08 int, motors_09 int, motors_10 int, motors_11 int, motors_12 int); DROP TABLE IF EXISTS aadf_motor_pcu_years; Create TABLE aadf_motor_pcu_years AS SELECT * FROM crosstab( 'SELECT cp, year, all_motor_pcu FROM pcu_roads ORDER BY cp ASC', 'SELECT DISTINCT year FROM pcu_roads ORDER BY year ASC') AS ct(cp int, motor_pcu_00 double precision, motor_pcu_01 double precision, motor_pcu_02 double precision, motor_pcu_03 double precision, motor_pcu_04 double precision, motor_pcu_05 double precision, motor_pcu_06 double precision, motor_pcu_07 double precision, motor_pcu_08 double precision, motor_pcu_09 double precision, motor_pcu_10 double precision, motor_pcu_11 double precision, motor_pcu_12 double precision); -- make a table of max year and CP DROP TABLE IF EXISTS aadf_uk_cp_maxyear; CREATE TABLE aadf_uk_cp_maxyear as SELECT cp as maxyear_cp, max(year) as maxyear FROM pcu_roads_political GROUP BY cp; -- make a big table of all the count points, this combines all of the pivoted years and uses as the main table (aliased as pol) a subselect query which makes sure you only get the full data for the last year it had a full count. This enables us to create a very flat table ideally suited to google fusion tables. DROP TABLE IF EXISTS aadf_uk_counts_pcu_2003_2012; CREATE TABLE aadf_uk_counts_pcu_2003_2012 AS SELECT pol.westminstername, pol.boroughname, pol.wardname, pol.cp, pol.road, pol.road_type, pol.maxyear, pol.cycle_pcu, pol.p2w_pcu, pol.car_pcu, pol.bus_pcu, pol.lgv_pcu, pol.mgv_pcu, pol.hgv_pcu, pol.latitude, pol.longitude, pol.road_geom, cycles_00, cycles_01, cycles_02, cycles_03, cycles_04, cycles_05, cycles_06, cycles_07, cycles_08, cycles_09, cycles_10, cycles_11, cycles_12, p2w_00, p2w_01, p2w_02, p2w_03, p2w_04, p2w_05, p2w_06, p2w_07, p2w_08, p2w_09, p2w_10, p2w_11, p2w_12, cars_00, cars_01, cars_02, cars_03, cars_04, cars_05, cars_06, cars_07, cars_08, cars_09, cars_10, cars_11, cars_12, buses_00, buses_01, buses_02, buses_03, buses_04, buses_05, buses_06, buses_07, buses_08, buses_09, buses_10, buses_11, buses_12, lgvs_00, lgvs_01, lgvs_02, lgvs_03, lgvs_04, lgvs_05, lgvs_06, lgvs_07, lgvs_08, lgvs_09, lgvs_10, lgvs_11, lgvs_12, mgvs_00, mgvs_01, mgvs_02, mgvs_03, mgvs_04, mgvs_05, mgvs_06, mgvs_07, mgvs_08, mgvs_09, mgvs_10, mgvs_11, mgvs_12, hgvs_00, hgvs_01, hgvs_02, hgvs_03, hgvs_04, hgvs_05, hgvs_06, hgvs_07, hgvs_08, hgvs_09, hgvs_10, hgvs_11, hgvs_12, motors_00, motors_01, motors_02, motors_03, motors_04, motors_05, motors_06, motors_07, motors_08, motors_09, motors_10, motors_11, motors_12, motor_pcu_00, motor_pcu_01, motor_pcu_02, motor_pcu_03, motor_pcu_04, motor_pcu_05, motor_pcu_06, motor_pcu_07, motor_pcu_08, motor_pcu_09, motor_pcu_10, motor_pcu_11, motor_pcu_12 FROM (SELECT * FROM pcu_roads_political pol INNER JOIN aadf_uk_cp_maxyear my on pol.cp = my.maxyear_cp AND my.maxyear = pol.year) as pol LEFT JOIN aadf_cycles_years on pol.cp = aadf_cycles_years.cp LEFT JOIN aadf_p2w_years on pol.cp = aadf_p2w_years.cp LEFT JOIN aadf_cars_years on pol.cp = aadf_cars_years.cp LEFT JOIN aadf_buses_years on pol.cp = aadf_buses_years.cp LEFT JOIN aadf_lgvs_years on pol.cp = aadf_lgvs_years.cp LEFT JOIN aadf_mgvs_years on pol.cp = aadf_mgvs_years.cp LEFT JOIN aadf_hgvs_years on pol.cp = aadf_hgvs_years.cp LEFT JOIN aadf_motors_years on pol.cp = aadf_motors_years.cp LEFT JOIN aadf_motor_pcu_years on pol.cp = aadf_motor_pcu_years.cp WHERE (pol.road_geom is not null OR pol.latitude is not null) -- done!