I have:

  • a non-spatial table of multiple test results with reference to non-unique locations, and

  • a spatial layer that has all the locations (multiple results per location)

I can do table joins between the locations layer to the results, but I get stuck because each location has multiple results...

I suspect I need to make a virtual layer assigning the geometry to each result, but I get lost at the "query" stage. The QGIS manual goes deep into database SQL queries but I am hoping there is a simpler solution for the non-initiated?

Note: I can create virtual "x" and "y" attribute columns and join them to my table, but still missing a step to use the coordinates in creating the virtual layer

  • Please add a sample of your tests / locations and the desired output.
    – JGH
    Oct 16 '19 at 11:44
  • Sorry @JGH - I should have. I am still struggling a bit with the formatting of questions, inserting tables and images, etc. The example Luis provided below is perfect thought, and his answer is spot on. Oct 16 '19 at 22:41

Virtual layer is your answer :

for example two tables,

nonspatial table:
1 | Rest1    | george
2 | Rest1    | Peter
3 | Rest2    | grace
4 | Rest2    | Peter

spatial table:
id | Restaurant | geometry
1  | Rest1      | Point(1,0)
2  | Rest2      | Point(1,2)
3  | Rest3      | Point(2,0)

your query

select "nonspatial".*, "spatial"."geometry" 
from "nonspatial", "spatial"
where "nonspatial"."Restaurant"="spatial"."Restaurant"


1 | Rest1    | george |Point(1,0)
2 | Rest1    | Peter  |Point(1,0)
3 | Rest2    | grace  |Point(1,2)
4 | Rest2    | Peter  |Point(1,2)

tip - if your tables and columns are in lowercase, quotes are not necessary

  • This is awesome Luis, thank you so much. The query is extremely easy to read and I replicated with the actual data on first try. I did not realise accessing the geometry of the spatial table was that easy! I really need to learn more about spatial query - is there a gentle introduction / manual that I could start with? Oct 16 '19 at 22:37
  • 1
    you are welcome.. my sql knowledge came from postgis and postgres but for start i think spatialite is very powerful. [gaia-gis link]( gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html) Oct 17 '19 at 12:24

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.