4

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.

enter image description here enter image description here

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?

  • Note that you explicitly do not want ST_Within, but ST_Intersects! You need to import the layers in the top section of the Virtual Layer dialog, and use the then displayed name exactly; use "" enclosing for names with characters other than lower case letters. – geozelot Aug 5 '19 at 20:14
  • @ThingumaBob cheers, seems I just needed to import the layers...following other guides they managed to perform simple queries without importing file paths which was misleading me. By all means post an answer and I'll accept – James B Aug 6 '19 at 9:49
2
  • 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.

| improve this answer | |

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.