Run query between PostGIS layer and Shapefile layer with DB Manager

Question

Is it possible to use the DB Manager in QGIS (I am using version 3.4) to run a SQL query between a project layer (e.g. shapefile) and a PostGIS layer?

For example, I have a polygon shapefile in my QGIS project in the layers panel and I want to select all point features (saved in a PostGIS database but not loaded in my layers panel) that are within the shapefile polygon.

Answer 1

You can achieve it via a virtual layer. Go to the Layer menu, add layer / add-edit virtual layer. You can then use all your layers (Shapefile, db etc) in a query. The layers must be in the table of content, and the output will be a new virtual layer.

Ex: a is a Shapefile, b is a PostGIS layer:

select a.*
from a,b
where st_intersects(a.geometry, b.geometry)