7

I am using QGIS 3.10.0

I am attempting to join a spatial vector layer to a non-spatial layer (CSV).

Each polygon on the spatial layer has a unique id attribute. The CSV also has this id attribute but it is not unique for each record. So for each polygon on the spatial layer the are 1-to-N associated records in the CSV.

I was hoping that when you select an object on the spatial layer it will show all the associated records from the CSV.

I initially used the layer join option but that only shows one record from the CSV.

I then read about virtual layers. I created a virtual layer of both existing layers and this does what I want... however this is so slow it is unusable! After creating the layer it takes 10 minutes for QGIS to become responsive. If I do anything with that layer (select an object or just zoom!) I have to wait another 5 to 10 minutes! This is on 64-bit machine core i7 16gb ram.

The spatial layer has 7988 records and the csv has 8341 records.

Does anyone have any other options or know how to speed the virtual layer rendering up?

Improve this question
4
  • 1
    I would think it best to move to a PostGIS, SpatiaLite (or even GeoPackage) database and create a view there instead of the virtual layer in the QGIS project. Then the view should load as a layer and function as a virtual layer (I'm not sure about it, maybe a "virtual view" is required). The advantage of the database is to be able to update the statistics to improve performance. Similarly, performance may be improved in your project, indexes manteinance is the key I think. What type of files are the layers?
    – Gabriel De Luca
    Commented Dec 9, 2019 at 1:08
  • 2
    If the updates are done not too frequently, I would merge the 2 files permanently into a Shapefile for example, that should help.
    – GforGIS
    Commented Dec 9, 2019 at 3:23
  • Thanks for the replies Gabriel De Luca and MyGIS. I created the virtual layer then exported that to a shape file. I then created a new project and just added that shape file and it works as hoped! Clicking on an object displays all the records and the response is almost instant!
    – jcrow
    Commented Dec 9, 2019 at 5:16
  • @jcrow I think if you re-posted that answer with a little bit more detail, it would be very helpful to other people with a similar question. Eg, include the exact SQL query you used to create the virtual layer. It's useful to have more than one solution available.
    – csk
    Commented Dec 11, 2019 at 17:14

1 Answer 1

Reset to default
9

For a 1-n relationship it's better to use a relation rather than a join (a join is good for 1-1 relation). A relation should be faster than virtual layer and doesn't require you to create a new layer that wont update with your data.

The parent/child relation is displayed in the attribute form and let you easily update both at the same time (in your case a CSV won't be editable, you will need to convert to another format if you need to edit from QGIS). See link at the bottom for more information on how it work

The setting for relation is not in layer properties but at the project level (in the project properties), the setting are similar for relation and join but for the "Relationship strength" setting (The default Association type means that the parent layer is simply linked to the child one while the Composition type allows you to duplicate also the child features when duplicating the parent ones)

enter image description here

More on relation : QGIS user manual

Improve this answer

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.