batch script to create table and add a geometry column into a sqlite DB

[Paulo Eduardo Cardoso]

I'm trying to script a .bat file (WINDOWS DOS) with a command to create a sqlite DB, add a table and a geometry column to that table.

I've successfully scripted and obtained a sqlite database with a table but I'm not being able to deal with the AddGeometryColumn to the table.

I'm using sqlite3.exe and spatialite_tools.exe doing the following:

1. Create a .bat file with the code to create my sqlite database

@echo off

setlocal

:: Global Variables here

set _engine=C:/path/to/sqlitetools/sqlite3.exe

call %_engine% C:/path/to/sqliteDB/mydb0.sqlite < C:/path/to/sqlitetools/sqlcreate_vantage_table.sql

2. create a .sql file to build a table inside mydb0.sqlite

CREATE TABLE table0 (uid_tab varchar(30) NOT NULL,

uid_obs varchar(30) NOT NULL,

uid_proj varchar(30) NOT NULL,

date varchar(30) NOT NULL

);

3. Create a .bat file with the code to add the geometry column to to table0 with something like:

@echo off

setlocal

:: Global Variables here

set _engine1=C:/path/to/sqlitetools/spatialite_tool.exe

call %_engine1%  C:/path/to/sqliteDB/mydb0.sqlite < SELECT AddGeometryColumn('table0', 'Geometry', 4326, 'POINT', 2);

I'm not being able to achieve this.

Any help will be more than welcome

All the best,

Paulo

[Brad Hards]

> 2. create a .sql file to build a table inside mydb0.sqlite
>
> CREATE TABLE table0 (uid_tab varchar(30) NOT NULL,
> uid_obs varchar(30) NOT NULL,
> uid_proj varchar(30) NOT NULL,
> date varchar(30) NOT NULL
> );

Just add the required command to the .sql file.

SELECT AddGeometryColumn('table0', 'Geometry', 4326, 'POINT', 2);

Or use another .sql file, whatever suits you.

Brad

[mj10777]

On Tuesday, 27 September 2016 00:42:20 UTC+2, Paulo Eduardo Cardoso wrote:

I'm trying to script a .bat file (WINDOWS DOS) with a command to create a sqlite DB, add a table and a geometry column to that table.

I've successfully scripted and obtained a sqlite database with a table but I'm not being able to deal with the AddGeometryColumn to the table.

I'm using sqlite3.exe and spatialite_tools.exe doing the following:

1. Create a .bat file with the code to create my sqlite database

@echo off

setlocal

:: Global Variables here

set _engine=C:/path/to/sqlitetools/sqlite3.exe

call %_engine% C:/path/to/sqliteDB/mydb0.sqlite < C:/path/to/sqlitetools/sqlcreate_vantage_table.sql

I assume this worked correctly, thus the bat syntax is correct. 

2. create a .sql file to build a table inside mydb0.sqlite

CREATE TABLE table0 (uid_tab varchar(30) NOT NULL,

uid_obs varchar(30) NOT NULL,

uid_proj varchar(30) NOT NULL,

date varchar(30) NOT NULL

);

3. Create a .bat file with the code to add the geometry column to to table0 with something like:

@echo off

setlocal

:: Global Variables here

set _engine1=C:/path/to/sqlitetools/spatialite_tool.exe

call %_engine1%  C:/path/to/sqliteDB/mydb0.sqlite < SELECT AddGeometryColumn('table0', 'Geometry', 4326, 'POINT', 2);

I'm not being able to achieve this.

spatialite_tool.exe is not what you want.

You must use spatialite.exe

@echo off

setlocal

:: Global Variables here

set _engine=C:/path/to/sqlitetools/spatialite.exe

call %_engine% C:/path/to/sqliteDB/mydb0.sqlite < C:/path/to/sqlitetools/sqlcreate_vantage_table.sql

sqlcreate_vantage_table.sql

- should be changed:

CREATE TABLE table0 (uid_tab varchar(30) NOT NULL,

uid_obs varchar(30) NOT NULL,

uid_proj varchar(30) NOT NULL,

date varchar(30) NOT NULL

);

SELECT AddGeometryColumn('table0', 'Geometry', 4326, 'POINT', 2);

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

The difference sqlite3.exe and spatialite.exe

- spatialite.exe will build the needed connection to the spatialite-libraries

-- so spatial-specific sql an also be executed (AddGeometryColumn, CreateSpatialIndex)

Since spatialite.tools is installed, you should also have spatialite.exe

Mark

[Paulo Eduardo Cardoso]

sql from script 2 is called by sqlite3.exe which do not have AddGeometryColumn function. I'm convinced that I'll need spatialite_tools.exe to achieve this.

[mj10777]

On Tuesday, 27 September 2016 01:02:24 UTC+2, Paulo Eduardo Cardoso wrote:

sql from script 2 is called by sqlite3.exe which do not have AddGeometryColumn function. I'm convinced that I'll need spatialite_tools.exe to achieve this.

spatialite_tools.exe is for importing shape file

call spatialite_tools.exe --help

Also this syntax will not work:

call %_engine1%  C:/path/to/sqliteDB/mydb0.sqlite < SELECT AddGeometryColumn('table0', 'Geometry', 4326, 'POINT', 2);

place the sql-command in a file 

or

place quotes around the command

- but call spatialite.exe (sqlite3.exe cannot be used for spatial-sql queries)

call %_engine1%  C:/path/to/sqliteDB/mydb0.sqlite < "SELECT AddGeometryColumn('table0', 'Geometry', 4326, 'POINT', 2);"

Mark

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

On Mon, 26 Sep 2016 16:02:24 -0700 (PDT), Paulo Eduardo Cardoso wrote:
> sql from script 2 is called by sqlite3.exe which do not
> have AddGeometryColumn function. I'm convinced that I'll need
> spatialite_tools.exe to achieve this.
>

Hi Paulo,

you simply have to load SpatiaLite as an extensions to SQLite,
and after this sqlite3.exe will be immediately able to correctly
support all SpatiaLite's own functions such ad AddGeometryColumn.
just insert this line at the very beginning of your SQL scripts:

SELECT load_extension('mod_spatialite');

bye Sandro

[Paulo Eduardo Cardoso]

Dear Mark,

It works fine! Nevertheless the table with geometry is not 'seen' as a geometry by QGIS.

[Paulo Eduardo Cardoso]

Dear Sandro, which .dll, if any, will be called with load_extension() option? libsqlite3 and libspatialite ? from where exactly should I obtain the complete set of libraries to be called by sqlite3.exe? 

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

On Mon, 26 Sep 2016 16:41:46 -0700 (PDT), Paulo Eduardo Cardoso wrote:
> Dear Sandro, which .dll, if any, will be called with load_extension()
> option? libsqlite3 and libspatialite ? from where exactly should I
> obtain the complete set of libraries to be called by sqlite3.exe? 
>

all DLLs for Win32:
http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/mod_spatialite-4.4.0-RC0-win-x86.7z

all DLLs for Win64:
http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/mod_spatialite-4.4.0-RC0-win-amd64.7z

please note: libspatialite is nowadays not longer intended
for dynamic loading: use mod_spatialite instead.

bye Sandro