Skip to content

Instantly share code, notes, and snippets.

@stevefaeembra
Last active August 29, 2015 13:58
Show Gist options
  • Save stevefaeembra/9979287 to your computer and use it in GitHub Desktop.
Save stevefaeembra/9979287 to your computer and use it in GitHub Desktop.
Import STATS19 vehicle/casualty data into SQLite
/** 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment