spatialite doesn't include init_spatialite which is needed for SRID transformations & geometry_columns

[Rory McCann]

I have installed the spatialite-bin and libspatialite2 packages and can use the spatialite binary for simple things. however I can't find the file "init_spatialite-2.x.sql" on my system and dpkg says neither of those packages have that file.

This file has data that's needed to perform some geographic calculations, such as converting between spatial reference/coordinate systems. If you are making a spatial database from scratch you need to import this file first. Various spatialite tutorials tell you to import this file first (e.g. http://www.bostongis.com/?content_name=spatialite_tut01).

Not having this file limits the functionality of spatialite, ergo I'm filing a bug. This file should be included in one of the packages.

ProblemType: Bug
DistroRelease: Ubuntu 10.04
Package: libspatialite2 2.3.0-1
ProcVersionSignature: Ubuntu 2.6.32-24.43-generic 2.6.32.15+drm33.5
Uname: Linux 2.6.32-24-generic i686
Architecture: i386
Date: Wed Sep 29 17:24:29 2010
ProcEnviron:
 PATH=(custom, user)
 LANG=en_IE.utf8
 SHELL=/bin/bash
SourcePackage: spatialite

[Alex Mandel (wildintellect)]

My understanding is that version 2.4+ which is available upstream (and in the ubuntugis ppa) no longer requires this file in order to create a spatial database. Can't seem to find the source of that information right now and need to test that.
The file in question I believe is http://www.gaia-gis.it/spatialite-2.3.0/init_spatialite-2.3.sql.gz

[Mike Ryan (mikeryan+launchpad)]

Alex: I believe you are incorrect. Spatialite ships an init script for 2.4 at http://www.gaia-gis.it/spatialite-2.4.0/init_spatialite-2.4.sql.gz

I can confirm that I am unable to create a spatialite database using 2.4.0~rc2-5 in Maverick (10.10) until I run the init script.

[Alex Mandel (wildintellect)]

So I did the following on the commandline:
spatialite newdatabase.sqlite

The new database created by spatialite appears to have the correct metadata tables that would be created by the init sql. However I could see how if you wanted to convert an existing sqlite db you would need the script. I'll recommend to the Debian maintainers the inclusion of the script as an extra utility.

Tested on Lucid using 2.4.0~rc2-5 from Ubuntugis PPA

[Rory McCann (rorymcc)]

I have the same problem. I tried to create a database with "spatialite ireland.sqlite" and it did not create any additional tables. There is also no init_spatialite script, so I have to use extra files. The spatialite_osm programme included with spatialite doesn't work with out an initialised database

$ spatialite ireland.sqlite
SpatiaLite version ..: 2.4.0 Supported Extensions:
        - 'VirtualShape' [direct Shapefile access]
        - 'VirtualDbf' [direct DBF access]
        - 'VirtualText' [direct CSV/TXT access]
        - 'VirtualNetwork' [Dijkstra shortest path]
        - 'RTree' [Spatial Index - R*Tree]
        - 'MbrCache' [Spatial Index - MBR cache]
        - 'VirtualFDO' [FDO-OGR interoperability]
        - 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.2.2-CAPI-1.6.2
SQLite version ......: 3.7.7
Enter ".help" for instructions
spatialite> .tables
spatialite>

[Brad Hards (bradh)]

Note that you won't need this step (or the associated script) with more recent versions of spatialite (3.x).

[Bas Couwenberg (sebastic)]

SpatiaLite 4.x is in Debian & Ubuntu for quite some time now, and as Brad Hards mentioned since 3.x the SQL init script is no longer needed.