2

To run a simple interest query in Mapinfo I would use

Select * from _1_10000_map_sheet_key_map, Carpentaria_Shire where _1_10000_map_sheet_key_map.Obj Intersects Carpentaria_Shire.Obj into Selection

In QGIS in Add--Virtual Layer and in DB Manager I keep getting errors

select * from _1_10000_map_sheet_key_map where st_intersects (_1_10000_map_sheet_key_map.geometry, Carpentaria_Shire.geometry) as Selection

gives the following error

Query execution error on CREATE TEMP VIEW _tview AS select * from _1_10000_map_sheet_key_map where st_intersects (_1_10000_map_sheet_key_map.geometry, Carpentaria_Shire.geometry) as Selection: 1 - near "as": syntax error

The same from Virtual Layers and trying to use geom_from_wkt I get

Query preparation error on PRAGMA table_info(_tview): no such column: Carpentaria_Shire.geom_from_wkt

What's the expected structure of the query in QGIS SQL implementation?

Additionally can this be done in the Advanced Filter expression?

  • PS I know I can do this very easily in QGIS--Vector--Research--Select by location but I want to get more familiar with the Virtual Layer queries in QGIS. – GeorgeC Aug 2 '19 at 2:11
  • 2
    Please do not comment on your own question. Instead Edit to make clarifications. – Vince Aug 2 '19 at 2:27
4

The SQL Select was one of the few things I missed about MapInfo when my work changed to QGIS.

You need to include the table "Carpentaria_Shire" in the FROM clause. Try this:

select A.*
from _1_10000_map_sheet_key_map AS A, Carpentaria_Shire as B
where st_intersects(A.geometry, B.geometry)

or for a different syntax:

SELECT A.*
FROM _1_10000_map_sheet_key_map AS A
JOIN Carpentaria_Shire AS B ON st_intersects(A.geometry, B.geometry)

To do it as an advanced filter expression:

intersects($geometry, aggregate('Carpentaria_Shire', 'collect', $geometry))
| improve this answer | |
  • Brilliant - as to the 2nd part of my question. Is this possible to do using the Expression based filter on a table? I tried using the Map_Layer attributes but it doesn't work - 'Carpentaria_Shire_540e7bd7_f775_4e8e_8e96_61f2dcbe36bb' intersects 'Grid_3bbf8f0c_9cdb_453b_8a92_784f328945ee' – GeorgeC Aug 2 '19 at 3:44
  • 2
    Try:intersects($geometry,aggregate('Carpentaria_Shire','collect',$geometry)) – M Bain Aug 2 '19 at 4:12
  • Works perfectly -thank you. – GeorgeC Aug 2 '19 at 4:59

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.