Polygon intersection using QGIS virtual layers

Question

I'm trying to intersect polygon geometries in QGIS by using a virtual layer:

SELECT
    sbqry.rowid AS gid,
    sbqry.geom
FROM
    (SELECT
        ST_Intersection(land_parcels.geometry, flood_zone.geometry) AS geom
    FROM land_parcels, flood_zone
    WHERE ST_Intersects(land_parcels.geometry, flood_zone.geometry) AND NOT ST_Touches(land_parcels.geometry, flood_zone.geometry))
    AS sbqry;

Unfortunately, sbqry.rowid AS gid returns NULL instead of auto-incrementing values.

Does anybody know how to create an unique identifier column named 'gid'? As far as I know virtual layers are based on SQLite/Spatialite.

Answer 1

In PostGIS, if you just want the row number you can do this:

SELECT
    row_number() over () as gid,
    sbqry.geom
FROM
    (SELECT
        ST_Intersection(land_parcels.geometry, flood_zone.geometry) AS geom
    FROM land_parcels, flood_zone
    WHERE ST_Intersects(land_parcels.geometry, flood_zone.geometry) AND NOT ST_Touches(land_parcels.geometry, flood_zone.geometry))
    AS sbqry;

Answer 2

SELECT  f.rowid AS gid, f.geom
FROM
(SELECT sbqry.geom
FROM (SELECT ST_Intersection(land_parcels.geometry, flood_zone.geometry) AS geom FROM land_parcels, flood_zone 
WHERE ST_Intersects(land_parcels.geometry, flood_zone.geometry) AND NOT ST_Touches(land_parcels.geometry, flood_zone.geometry)) AS sbqry) as f