The SpatiaLite extension enables SQLite to support spatial data too (aka GIS), in a way conformant to OpenGis specifications
The VirtualShape extension enables SQLite to access shapefiles as VIRTUAL TABLEs
The VirtualText extension enables SQLite to access CSV/TxtTab files as VIRTUAL TABLEs
Spatialite comes in two flavours (since version 4.2.0, see https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite for details):
libspatialite is a classic shared library. It depends on some external libsqlite3. It cannot be used as a dynamic extension via the SELECT load_extension SQLite mechanism.
mod_spatialite is a loadable SQLite module/extension (lacking any explicit SQLite3 dependency). To load and activate this module, call SELECT load_extension in SQLite.
So, in order to use Spatialite from Tcl, you just need the Tcl SQLite3 package and the mod_spatialite library. Then do the following:
package require sqlite3 sqlite3 db someDBfile.db db enable_load_extension true db eval {select load_extension('/path/to/spatialsqlite/libdir/mod_spatialite')} # Note: do not specify the file extension (.so, .dylib, ...) as SQLite does this automagically
Harm Olthof: using tdbc it works like this:
package require tdbc::sqlite3 set db_path {/path/to/sqlitefile.sqlite} tdbc::sqlite3::connection create db $db_path set db_handle [db getDBhandle] $db_handle enable_load_extension 1 # The directory containing dll's for geos, proj4, etc must be added to the PATH environment append ::env(PATH) {;} {D:\pshare\bin\spatialite\bin\spatialite-3.0.1-DLL-win-x86} db allrows {SELECT load_extension('/path/to/spatialsqlite/libraryfile')}
and if it's a new or non-spatialite database file, you should initialize it:
db allrows {SELECT InitSpatialMetaData()}
Another example, now showing how to create a new spatial db and adding some points to it:
package require sqlite3 sqlite3 db :memory: db enable_load_extension true db eval {SELECT load_extension('mod_spatialite')} db eval {SELECT InitSpatialMetaData()} db eval {CREATE table hal (id integer)} db eval {SELECT AddGeometryColumn('hal','geometry',-1,'POINT','XY')} db eval {SELECT CreateSpatialIndex('hal','geometry')} set n 0 set points [list 1.0 2.0 3.0 4.0 5.0 6.0] db transaction { foreach {x y} $points { db eval { INSERT into hal ('id','geometry') VALUES (:n, MakePoint(CastToDouble(:x), CastToDouble(:y),-1)) } incr n } } puts [db eval {SELECT id, AsText(geometry) from hal}] ==> 0 {POINT(1 2)} 1 {POINT(3 4)} 2 {POINT(5 6)}
... set lib [file join $env(LD_LIBRARY_PATH) libmod_spatialite] db eval {SELECT load_extension(:lib)} ...