QGIS – Resolving Blank Layers in Virtual Layers Query with ST_Intersection

qgisvirtual-layer

I am trying to create a virtual layer in QGIS that is the intersection of two polygon layers, "Cowardin" and "Wetlands". Both layers are in the map canvas in the same projection (EPSG:6597). I entered the query in the virtual layers dialog:

SELECT Cowardin.*
FROM Cowardin, Wetlands
WHERE st_intersection(Cowardin.geometry, Wetlands.geometry)

And it returns no errors, but when I add the layer to my project it adds a blank layer. What am I doing wrong?

I also tried using the Query Builder in DB manager and got the same query with quotes around layer names, but this was also blank.

Best Answer

To get only the intersections, you would have to select the intersection where geometries intersect...

SELECT st_intersection(Cowardin.geometry, Wetlands.geometry) as geometry, 
       Cowardin.id as cid, Wetlands.id as wid
FROM Cowardin, Wetlands
WHERE st_intersects(Cowardin.geometry, Wetlands.geometry)
Related Question