5

I have two layers. One is a shapefile of U.S. states where each state is labeled with the attribute STATE_NAME, and the other is a CSV data layer named rid with many points that are labeled with a State attribute. In this CSV data layer, there are many points for each state.

I want to create a column in the shapefile that contains the count of points per state.

Using the field calculator in the shapefile, I've attempted

aggregate(layer:= 'rid',
          aggregate:= 'count',
          expression:='State',
          filter:=attribute($currentfeature, 'STATE_NAME'))

but this results in 0 for each state. If I attempt without the filter, I get the total number of points regardless of state, resulting in the same number for each state.

Is there a filter that will allow me to count the number of points per state?

2
  • is CSV layer a point vector layer or a table? Feb 27, 2020 at 22:27
  • The CSV layer is a table @KadirŞahbaz
    – brienna
    Feb 27, 2020 at 22:29

1 Answer 1

7

What I ended up doing:

  1. Layer > Create Layer > New Virtual Layer…
  2. Import rid CSV layer. Set query as select "State", count(*) AS “TERPS” from rid group by "State".
  3. Click 'Add'. This creates a virtual layer that counts the number of points per date. Close dialog.
  4. Go to the properties of the shapefile layer. Create a new join that joins to the virtual layer on "State", with the target field being STATE_NAME. This moves that data from the virtual layer over to the shapefile.

Your Answer

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

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