I have a simple SHP line file - where each line has a unique reference. I have a related text csv file using the same reference, in a one-to-many situation. I need a map with the lines (in the 'one' side of the relationship) colour coded according to the number of matching entries (from the 'many' side of the relationship).
Practically this represents sections of route and repeated dated route inspection reports - and the map should show the total number of inspections for each bit of route. As it happens I also have the same route reports available with the addition of exactly matching geometries in a SHP or TAB file if I need them, but I don't need the geometry information as this is duplicated.
How can this map be produced (given that the format that the data is supplied in is fixed as Mapinfo TAB files which I'd assume to convert to SHP anyway - and that this needs to be an easily repeated process as data is updated).
Answer
In the CSV file you need a group by function to count the number of records for each id.
This is a typical database query. I would use Spatialite, where you can build a view (join) between your geometry and non geometry data (your CSV), where the non geometry data has been grouped and counted first. A virtual table like this can be shown with a graduation style.
I you don't like the database concept, you can use the Group Stat plugin to create a new CSV file, where the id's in the CSV file has been grouped and counted. Save the new CSV file, open it in QGIS. On the line layer add a join (Properties > Joins) between the line id and csv id (this is now a 1-1 join). Style the line layer with a graduated style choosing the joined count value for graduation style field.
Below some images to help you through Group Stat plugin.
The line table:
The CSV layer:
Group Stat Plugin: (but note suggested change in the comments to the pictured settings) 
Save the Group Stat output as CSV, open it in QGIS and add as join on the line layer.
Count column from csv joined onto line table. Don't mind the id and count values are the same in my sample:
Create your graduated style from the count column.
Line layer styled on number of occurrence in CSV. Top line hard to see:
No comments:
Post a Comment