Import STATS19 vehicle/casualty data into SQLite

import-stats19.sql

/** Copyright (C) 2014 Steven Kay

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

CREATE TABLE vehicles (	
	Acc_Index TEXT PRIMARY_KEY NOT NULL,
	Vehicle_Reference TEXT,
	Vehicle_Type TEXT,
	Towing_and_Articulation TEXT,
	Vehicle_Manoeuvre TEXT,
	Vehicle_Location_Restricted_Lane TEXT,
	Junction_Location TEXT,
	Skidding_and_Overturning TEXT,
	Hit_Object_in_Carriageway TEXT,
	Vehicle_Leaving_Carriageway TEXT,
	Hit_Object_off_Carriageway TEXT,
	First_Point_of_Impact TEXT,
	Was_Vehicle_Left_Hand_Drive TEXT,
	Journey_Purpose_of_Driver TEXT,
	Sex_of_Driver TEXT,
	Age_Band_of_Driver TEXT,
	Engine_Capacity_CC TEXT,
	Propulsion_Code TEXT,
	Age_of_Vehicle TEXT,
	Driver_IMD_Decile TEXT,
	Driver_Home_Area_Type TEXT
);

.separator ','
.import "./Vehicles.csv" vehicles

CREATE TABLE accidents (
	Accident_Index TEXT PRIMARY_KEY NOT NULL,
	Location_Easting_OSGR TEXT,
	Location_Northing_OSGR TEXT,
	Longitude TEXT,
	Latitude TEXT,
	Police_Force TEXT,
	Accident_Severity TEXT,
	Number_of_Vehicles TEXT,
	Number_of_Casualties TEXT,
	Date TEXT,
	Day_of_Week TEXT,
	Time TEXT,
	Local_Authority_District TEXT,
	Local_Authority_Highway TEXT,
	First_Road_Class TEXT,
	First_Road_Number TEXT,
	Road_Type TEXT,
	Speed_limit TEXT,
	Junction_Detail TEXT,
	Junction_Control TEXT,
	Second_Road_Class TEXT,
	Second_Road_Number TEXT,
	Pedestrian_Crossing_Human_Control TEXT,
	Pedestrian_Crossing_Physical_Facilities TEXT,
	Light_Conditions TEXT,
	Weather_Conditions TEXT,
	Road_Surface_Conditions TEXT,
	Special_Conditions_at_Site TEXT,
	Carriageway_Hazards TEXT,
	Urban_or_Rural_Area TEXT,
	Did_Police_Officer_Attend_Scene_of_Accident TEXT,
	LSOA_of_Accident_Location TEXT
);

.separator ','
.import "./Accidents.csv" accidents

# get ready to output data
.mode tabs
.output "./data.csv"
.headers ON
# run a query here