1

In QGIS 3.18, I have two data-only layers (csv) - the table coordinates containing lat/lon values, the table data containing attribute data. Both tables have a common unique id: 1 (data) : n (coordinates). I now want to visualize the data on the map.

What I tried: I made a table join to add the coordinates to the data table - that works. Than I created a virtual layer with this query:

select d.field_1, d.field_2, d.field_3, d.field_4, d.field_5,d.field_6, d.coordinates_lat, d.coordinates_lon, make_point(d.coordinates_lon, d.coordinates_lat) as geometry
from data as d 

This works fine.

Now I would like to do table join in the virtual layer as well. How is the correct syntax to achieve that?

Screenshot: creating point geometries from table join:

enter image description here

2

The general form of the query to join the tables would be something like:

    SELECT d.field_1, d.field_2, d.field_3, d.field_4, d.field_5,d.field_6,
           c.Lat, c.lon, make_point(c.lon, c.lat) as geometry
    FROM data AS d 
    JOIN coordinates AS c
    ON d.id = c.id

I'm not sure what the name of the common unique id is - in the query above I used "id" ON d.id = c.id you may need to change that to suit.

You said there is 1 to n relationship with data being the one and coordinates the many. So you would end up with multiple objects with the same id, I'm wondering if this will cause any issues for displaying/selecting/info-ing on the map. If it proves to be an issue you could use the sqlite row_number function to add a unique number for each row - add this to the field list in the select: ROW_NUMBER() OVER (ORDER BY c.field_1) as uid

4
  • This works, however QGIS gets quite slow. So probably for larger tables still the two-step approach (first table join, than virtual layer) is more practicable. – Babel Mar 23 at 23:01
  • Yeah, its a shame the virtual layers are cool but sometimes not practical, especially with joins it seems. I wonder if SELECT d.field_1, d.field_2, d.field_3, d.field_4, d.field_5,d.field_6, (SELECT make_point(lon, lat) FROM coordinates WHERE id = d.id) as geometry FROM data AS d would be faster/slower/the same? This query would only make sense if there was a single matching coordinate row for each data row - I think is the case because you > made a table join to add the coordinates to the data table – M Bain Mar 24 at 0:03
  • 1
    No, unfortunately the second query is not significantly faster. There are several points in the same location, so each coordinate is used multiple times. – Babel Mar 24 at 16:47
  • Thanks for humouring me by taking the time to try. :) – M Bain Mar 25 at 20:12

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.