Using a virtual layer, display polygons based on location of another polygon layer

Question

QGIS 3.4

I have a .gml file of every freehold title in a city outlined red below. I want to create a virtual layer of the freeholds which are contained within the blue-green isochrones. Even if a small portion of the freehold title intersects the isochrone, I need the entire freehold to be displayed.

my code so far:

SELECT *
FROM Freeholds, Isochrones
WHERE st_within(Freeholds.geometry, Isochrones.geometry) = 1

I am a novice at SQL and recieve the error 'Referenced table Freeholds in query not found!' How can I correct the query?

Answer 1

• When creating/editing a Virtual Layer using either the

  • <QGIS> | Layer | Create Layer | New Virtual Layer or

  • <QGIS> | Layer | Add Layer | Add/Edit Virtual Layer
    
    

  dialogue, you need to Import (or Add from source) all referenced layers in the Embedded Layers panel, and use the (editable) names in the SQL; it's a good idea to always "" enclose relation names with characters other than lowercase_123, e.g. "tAbLe N@m€"

• Alternatively, when using the DB Manager, you simply need to connect to the Virtual Layers provider in the Providers panel and refer to the layer names as seen in e.g. the Project Layers tab. Layers created via the DB Manager are automatically considered a Virtual Layer.

---

Use ST_Intersects instead of ST_Within to include freeholds sharing only a portion with the isochrone polygons.