I have a vector layer in a spatialite DB structured as follows:

enter image description here

In PyQGIS I try to create a derived vector layer from this like

from PyQt4.QtSql import *
db = QSqlDatabase.addDatabase('QSPATIALITE')
q = QSqlQuery('create table nat2000_hetzles_11 as select * from nat2000_hetzles where maes_2_06 = 11', db)

(note that the query is just a simple example, as a matter of fact it might consist of far more complicated conditions, joins, etc.)

The table is created but I'm facing some issues. GEOMETRY column is empty and QGIS just recognizes the result as a table (not vector layer).

The latter can be fixed by registering the geometry column in geometry_columns table like

insert into geometry_columns values ('nat2000_hetzles_11', 'geometry', 3, 2, 31468, 0)

The GEOMETRY seems to need special treatmant, but I could not figure out what.

How is the GEOMETRY created in statements like create my_vec as select * from my_source_vec where...?


Editing the geometry_columns table is not a good idea.

The table should be created from scratch like

create table if not exists nat2000_hetzles_11 (
    ogc_fid integer not null primary key autoincrement,
    uid integer not null,
    maes_4_06 integer not null)

and adding a geometry column by (cp. https://www.gaia-gis.it/spatialite-2.3.0/spatialite-sql-2.3.0.html#p16)

select addgeometrycolumn('nat2000_hetzles_11', 'geometry', 31468, 'POLYGON', 2, 1)

and then inserting the selection in the newly created table

insert into nat2000_hetzles_11 (uid, maes_4_06, geometry)
select uid, maes_4_06, geometry
from nat2000_hetzles
where maes_2_06 = 11

resulting in

enter image description here

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.