How to add a geometry column for an existing table

[Marcel Gangwisch]

Hi *

I'm using sqlite3 + spatialite + GDAL (Windows 7 32 Bit, C#). My aim is to read different shape files into the database.
By now I can read a shape file into a virtual OGR Table (via GDAL).

With the sql-query:

create table <name> as select * from <virtualname>

I'm also so far, that I have the shape file in the database, BUT:

the table holds also geometries which are not considered in the geometry_columns table.
The type of them are blob. RecoverGeometryColumn() does not work... I dont know why.

Maybe you have an idea, how to add the geometry columns to the geometry_columns table.

Have a nice day!
- Marcel

[a.fu...@lqt.it]

Hi Marcel,

> Maybe you have an idea, how to add the geometry columns to the
> geometry_columns table.
>

please check the documentation about these SQL functions:
AddGeomertyColumn()
RecoverGeometryColumn()

you could usefully read this quick tutorial:
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/new-geom.html

bye Sandro

[Marcel Gangwisch]

Hi Sandro,

thank you for your fast answer!

I already checked these two SQL Functions. And I already tried them, but I think they dont work?
Especially I considered RecoverGeometryColumn(). I think it should work.

Maybe I can give you more infos:

My table has a column geometry with BLOB sz = 228 GEOMETRY.
Maybe it is the problem that the type of this columns is empty... no type and not BLOB. But I dont understand why this got changed by my sql-query... because before the type in the virtual table is BLOB.

[a.fu...@lqt.it]

Hi Marcel,

> I already checked these two SQL Functions. And I already tried them,
> but I think they dont work?
> Especially I considered RecoverGeometryColumn(). I think it should
> work.
>

both AddGeometryColumn() and RecoverGeometryColumn() are the foundation
pillars sustaining all the SpatiaLite architecture.
so you should always safely assume that they are surely working as
they are intended to work, and if something goes wrong this certainly
means that you are using them in the wrong way.

> My table has a column geometry with BLOB sz = 228 GEOMETRY.
> Maybe it is the problem that the type of this columns is empty... no
> type and not BLOB. But I dont understand why this got changed by my
> sql-query... because before the type in the virtual table is BLOB.
>

every GEOMETRY is a BLOB, but not all BLOBs are GEOMETRIES.
spatialite_gui correctly reports "BLOB sz = 228 GEOMETRY"
because it has detected that this BLOB actually encapsulates
a valid Geometry value.

anyway, until you don't appropriately register the Geometry
column within the metadata tables (the specific task performed
by AddGeometrtColumn and/or RecoverGeometryColum) spatialite
will refuse to recognize this column as a "genuine geometry".

if you are using spatialite_gui the simplest approach you can
adopt is the following one (please see the attached figures):

a) right click on the geometry column; a context menu will appear
b) first check the column, so to discover its SRID, Type and Dims
c) and finally use the Recover dialog so to register the column;
you need to exactly insert the same identical values ad reported
by the Check tool

you could obviously get the same result directly using some
SQL statement, but using the GUI tools is surely easier ;-)

bye Sandro

[Marcel Gangwisch]

Hi Sandro,

both AddGeometryColumn() and RecoverGeometryColumn() are the foundation
pillars sustaining all the SpatiaLite architecture.
so you should always safely assume that they are surely working as
they are intended to work, and if something goes wrong this certainly
means that you are using them in the wrong way.

sorry I haven't meant it like that :-). I just wanted to say, that it doesn't work in my case... I didn't want to say that the functions are wrong

Now I figured out, why the functions were not working...
I passed a wrong SRID number...

Thanks Sandro for the hint with checking with the spatialite - gui!