10

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.

enter image description here enter image description here

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

  • What data type are you using for the virtual layer? – DPSSpatial Jun 29 '16 at 19:11
  • The input layers are .shp files, the output geometry type is 'polygon'. – eclipsed_by_the_moon Jun 29 '16 at 19:14
  • I didn't know you could run spatial functions (ST_*) on shapefiles! That's great!!! – DPSSpatial Jun 29 '16 at 19:25
  • have you tried removing sbqry from 'SELECT rowid as gid'... I found another post with such an example. – kttii Jun 29 '16 at 19:26
  • 'rowid AS gid' works with simple SELECT statements like creating a buffer but not with a subquery. – eclipsed_by_the_moon Jun 30 '16 at 16:23
3

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;
  • 'row_number() over()' seems to work in PostGIS but not in SpatiaLite (QGIS virtual layers). – eclipsed_by_the_moon Jun 29 '16 at 19:15
2
+25
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

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.