Lost geometry when joining spatial and non-spatial tables

Question

I have two tables I am joining: 1 spatial (Table 1) and one not spatial (Table 2).

Table 1 looks like this:

| ID | geometry |

Table 2:

| ID | ... |

When I perform the query:

CREATE TABLE AS SELECT * FROM t1, t2 WHERE t1.ID = t2.ID;

I get a table with the geometry column included. But unfortunately it is no longer recognized as a spatial table (spatialite_gui at least). Also, this mean I can't do what I really want to do, which is dump the table as shapefile.

So my question is: Why is the new table not recognised as a spatial table with a geometry column?

Answer 1

Run:

SELECT RecoverGeometryColumn(<new table>, <geom column name>, <SRID>, <geom type>);