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.
2 Answers
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!
-
1If the dataset is as massive as the OP claims, it might not be desirable (or technically possible) to add a new column. Is there a solution that allows for selection on a subset of a field?– VinceSep 16, 2015 at 10:56
-
@Vince - Thanks buddy, you're right that this might not be ideal with large datasets. Not sure if there's a simple option to select and export features based on a subset, but the answer by Steven Kay is definitely more suitable.– JosephSep 16, 2015 at 11:05
-
1
-
@AndreJ - Yes, good point. That should certainly help atleast a little :)– JosephMay 8, 2017 at 9:18
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!)
-
+1, I think this is the correct answer particularly if the dataset is large!– JosephSep 16, 2015 at 11:06