5

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 2

5

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)

Field calculator

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:

Split Vector Layer

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

4
  • 1
    If 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?
    – Vince
    Sep 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.
    – Joseph
    Sep 16, 2015 at 11:05
  • 1
    What about creating a virtual field, or select by expression?
    – AndreJ
    May 8, 2017 at 5:50
  • @AndreJ - Yes, good point. That should certainly help atleast a little :)
    – Joseph
    May 8, 2017 at 9:18
5

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
  • +1, I think this is the correct answer particularly if the dataset is large!
    – Joseph
    Sep 16, 2015 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.