Splitting vector file by date (year) but column containing dates in Day/Month/Year format?

Question

I am using national accident data in QGIS and quite frankly the dataset is huge as it contains every recorded accident between 2005 and 2014. I've split datasets by field for a number of vector layers. The issue i'm having is splitting the accident data by year as the date is contained as a field in the DD/MM/YYYY format. I've tried a number of things but as the dataset is so large it typically crashes QGIS.

Answer 1

From the Field Calculator, create a new integer field (let's call it Year) and assuming your date column is called Date, enter the following expression which takes 4 characters from the right-hand side of the value:

right("Date", 4)

Your new column should only contain the year. Now we should be able to use the Split Vector Layer tool from Vector> Data Management Tools. Then select the Year column as your Unique ID Field:

You should now have shapefiles split into different years. Hope this helps!

Answer 2

Is this the STATS19 police accident open data? I've struggled with this before - as you say it's a huge data set.

Joseph's answer is spot-on, but if you don't have the horsepower to deal with the data in the first place, I'd suggest importing into PostgreSQL and working with that, rather than trying your luck with the delimited files (I was just about able to load the accidents table in 4Gb RAM, didn't even try to add or join the other two tables)

I've got a Gist to import Stats19 to postgres which you might find useful. It works by importing the .csv files you'd have extracted from the zip file. (Correction: it's for importing into SQLite, not Postgres - but the process would be similar, see this question)

Using postgres you can convert the (text) date field to a year using something like this (not tested)

ALTER TABLE accidents ADD COLUMN year integer;
UPDATE accidents SET year = cast(substr("time",6,4) as integer);

You can import just a subset of the data by writing a postgres SQL query and importing it using the postgis sql editor plugin. (Tip: set your temp directory at the bottom of the dialog box before running your query, or it'll crash!)