SQL/SpatiaLite: how to declare a column as geometry?

Question

I am creating a new table through a SQL query from a spatial table:

CREATE TABLE SomeShapes AS
SELECT ash.id, ash.Geometry
FROM AllShapes ash
WHERE ash.id = 30

However, this returns a "normal" table, so when I try to load it in a GIS program (QGIS), it doesn't show the geometry. How do I declare that the geometry column contains, well, geometry?

Answer 1

You need to create a "non-spatial" table, and then add the Geometry column to it.

Then, you can insert data into your table.

It can't be done in one single step (create table as select). From the documentation:

Creating a Geometry-type at the same time the corresponding table is created isn't allowed. You always must first create the table, then adding the Geometry-column in a second time and as a separate step.

CREATE TABLE test_geom (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  measured_value DOUBLE NOT NULL);

SELECT AddGeometryColumn('test_geom', 'Geometry', 4326, 'POINT', 'XY');

Also, take into account that you may want to use spatial indexes to improve the performance

SELECT CreateSpatialIndex('test_geom', 'Geometry');