2

I have a shapefile with the following attributes:

ID    Region
1     Region1
2     Region2
3     Region3

Also I have the following record of events in a CSV file

EventID     Year     Month     RegionID     Quantity
001         2015     12        1            6
002         2015     12        2            7
003         2015     12        3            3
004         2015     11        1            4
005         2015     11        3            3
006         2015     10        2            6
007         2014     12        3            3
008         2014     11        2            2
009         2014     10        1            7
...         ...      ...       ...          ...

I would like to color my map acording to a filtered record. For instance, say I want to display all the events of November 2015. Then I would have:

ID    Region    Quantity
1     Region1   4
2     Region2   0
3     Region3   3

Or say I would like to display all the events of 2015. Then I would have the sum of the events:

ID    Region    Quantity
1     Region1   10
2     Region2   13
3     Region3   6

Is there a way to solve this problem dynamically, without rewriting the attribute table of the shp every time?

Consider that the CSV file is updated frequently.

7
  • Will the attributes be colored as graduated colors or by category? – the_darkside Jan 16 '17 at 1:21
  • @the_darkside graduated is what i'm looking for. – Ivan Jan 16 '17 at 1:28
  • I don't think there is a way - you want QGIS to automatically know how many classes to split your attributes into? Will there always be the same number of classes? – the_darkside Jan 16 '17 at 1:37
  • @the_darkside I could color them by category, say all zeros in black all greater than zero in red. The problem I'm having is with filtering, summarizing and assigning attributes from the CSV to the shp. – Ivan Jan 16 '17 at 1:46
  • I see, unfortunately it sounds like a manual task. How many maps will have to be produced in the end? – the_darkside Jan 16 '17 at 2:00
3

Starting from QGIS 2.14, it is possible to create virtual layers that are the results of an SQL query. In your example that would be:

enter image description here

You can edit the virtual layer definition to quickly change to other months.

Note that I've prefixed the year and month column with an "x". It seems like year and month are reserved words and the query didn't work correctly.

The virtual layer can be styled based on the quantity sum:

enter image description here

1
  • I shall give it a go! – Ivan Jan 17 '17 at 1:21
0

You could to do a virtual layer. SQL similar like this:

SELECT eve.RegionID, eve.year, COUNT(*) as count, SUM(eve.Quantity)
FROM regions AS reg
INNER JOIN events AS eve ON reg.ID=eve.RegionID GROUP BY eve.RegionID, eve.year ORDER BY eve.RegionID, eve.year

And then, you could to do a join in Region's properties.

-1

I believe you could create a field (or a virtual field) in the field calculator for the polygon layer using the function editor to get the result you desire. Unfortunately, I do not have the proper coding skills to give an example, yet! But if you could define a function that sums the number of times the instance "RegionID"='1' and "Year"="2015' is true from the CSV layer, and print the result to virtual field (call it 2015count), you could then colorize it by that field. I hope this helps someone else to a better answer.

1
  • I think you are misunderstandig my question. Every row in the csv is related to a polygon in the shapefile (a different layer). I want to easilly query and summarize the data in the record of events and update the map. – Ivan Jan 16 '17 at 2:58
-1

This isn't possible. QGIS can't do joins on the fly and incorporate those joins into a query.

It's sounds like you are coming from a relational database, so the quickest way to get the map you want would be to write the queries in SQL on your server and output the aggregate results as a single table and dump that as a CSV rather than outputting the tables as separate CSV's and trying to do the queries in QGIS.

Once that's done you still have some manual work left. You need to import the CSV, do the join on the polygon Shapefile and apply the symbology.

If it needs to be automated, that whole process could be scripted with pyqgis:

  1. Fetch CSV table based on dates regions etc from endpoint on your server
  2. Get QGIS to open the file
  3. Do the join
  4. Apply stored symbology
1
  • Thank you. I will try to write some python code to automate the process. – Ivan Jan 17 '17 at 1:22

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.