3

I have run into a problem that I see has been posted previously, but none of the posted solutions seem to solve my problem.

I have:

  1. A shapefile (with geometry) with a single row for many counties.
  2. A CSV file (without geometry) that has multiple rows (representative of different time points) for each county.

Shapefile Attribute Table CSV Attribute Table

Each of these files has an identifier in common “CQID”, which is unique to the county. As such, the shapefile has 1 of each CQID. The CSV has nine of each of the CQID’s (since I have nine time points).

I would like to join the CSV file into the shapefile. So far, I have tried and failed, to solve this problem doing the following:

  1. Using the Join Feature in the Layer Property of the shapefile.
  2. Creating a “Relation” of the two files, via Project properties

I expect to look at the attribute table of my shapefile, and see that where I had a single line for each county, there are now nine lines for each.

How do I achieve this is QGIS?

Alternatively, is it easier to do so in ArcMap? Any good tutorials for that?

nmtoken
  • 13,355
  • 5
  • 38
  • 87
user65148
  • 171
  • 1
  • 8
  • This sounds like a type of relate. How do you expect to use this type of cardinality in QGIS? I dont think it exists like it works in ArcGIS, though there is an ArcGIS solution. For example, once the relate is made, you could go into the csv, select records based on a query (by year?), then the shapefile could highlight those records based on the selected records in the csv. – SaultDon Aug 27 '16 at 05:41

1 Answers1

4

Joins

You can do it with a join in the layer properties, but you will have to do it the reverse way.

If you open the layer properties of your representative (csv) layer and join the counties (shapefile) to it, you will have all its attributes available for each entry in the csv layer.

Virtual layers

A similar approach is using a virtual layer. Via this it is also possible to include the geometry in the final table

Create a new virtual layer with

SELECT * FROM county_layer c
LEFT JOIN representative_layer r ON c.CQID2 = r.CQID2

Relations

For the sake of completeness, relations do not duplicate any data. But they let you inspect the joined representative features for a given county feature and add new representatives in the counties feature form.

Aggregate functions

This is a new functionality in QGIS 2.16 which allows calculating derived statistics on top of the counties. So you can e.g. create a new virtual field with the mean Y value per county.

relation_aggregate( 'my_relation', 'mean', "Y" )

More information in the changelog

Matthias Kuhn
  • 27,780
  • 3
  • 88
  • 129