3

I have a points dataset of fire incidents, where each incident's date is recorded.

enter image description here

I am currently counting the number of incidents per year, by selecting features by expression - using the expression ACQ_DATE LIKE '%2001%' , ACQ_DATE LIKE '%2002%' etc for each year from 2001-2021.

I wish to do this automatically using one expression, which outputs a CSV that looks like the following.

enter image description here

0
4

The easiest way would be to use a virtual layer :

  • Go to : > Layer > Add Layer > Add/Edit Virtual Layer...
  • In the "Create virtual Layer" dialog click on import and choose your point dataset
  • In the query part make an SQL query like this one : SELECT year(ACQ_DATE) as year, COUNT(ACQ_DATE) as 'number of incidents' FROM YOUR_LAYER GROUP BY year(ACQ_DATE); (replace "YOUR_LAYER" by the actual layer name of your point dataset)
  • Click the "Add" button

This will add a table with two column : year and number of incident, you could easily export this table as CSV by right clicking on it and choose Export > Save Feature As...

enter image description here

1
  • I have need replace year(ACQ_DATE) with strftime('%Y', ACQ_DATE) for it to work. My date field is type date in a postgres db
    – BERA
    Sep 26 at 10:10
2

Use this expression. Normalle, if using field calculator, it calculates the same list (each year + number of entries per year) separately for each feature. To avoid that, simply select one feature and then run field calculator with the box checked next to Only update 1 selected features.

If your date is indeed formatted as date (not as text), instead of left (ACQ_DATE,4) you can use year (ACQ_DATE).

replace (
    array_to_string (
        array_sort (
            array_distinct (
                array_agg (
                    left (ACQ_DATE,4)  || ': ' || 
                    array_length(
                        array_agg( 
                            to_int (left (ACQ_DATE,4)), 
                            group_by:=to_int(left (ACQ_DATE,4))
                    ))  || '\n'
    )))),
    ',',
    ''
)

enter image description here

2
  • When I click ok nothing happens? The counts appear in the preview however
    – Stuey17
    Sep 28 at 15:23
  • 1
    Are you sure nothing happens? Did you set Output field type to text (and make sure it's long enough)?
    – Babel
    Sep 28 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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