[Qgis-user] Re: File-based Mastermap

[Jukka Rahkonen]

Andreas Neumann <a.neumann at ...> writes:

> 
>  Perhaps you could also look into SpatiaLite.
> 
>  It comes without the limitations of Shapefiles (column names, file 
>  sizes) and is fast and a database without installation (file based).
> 
>  Also you need to copy only one file instead of multiple ones.
> 
>  SpatiaLite is not yet supported by FME (will be supported from FME 2013 
>  on I heard) - but you can use ogr2ogr for file translations.
> 
>  I believe the new DB-Manager Plugin from the last Google Summer of Code 
>  can also assist in converting data from Postgis to SpatiaLite, but I am 
>  not 100% sure.
> 
>  Andreas

GDAL SQLite driver does not make the most valid Spatialite databases. Alessandro
Furieri (man behind Spatialite)wrote:
"The current version of the spatialite's OGR driver 
has many severe issues.
Shortly said, you cannot safely use OGR to create a 
SpatiaLite own DB, because this will simply produce 
a broken (invalid, inconsistent) DB.
I've already developed a full patchset for OGR, and 
I hope to be able to release the code to the GDAL
project ASAP" 

While waiting for the patches there are two usable methods for making valid,
consistent DB:
1. Import shapefiles directly with Spatialite tools or with Spatialite-gui.
2. Use ogr2ogr first with options
   ogr2ogr -f "SQLite" -dsco SPATIALITE=yes -lco SPATIAL_INDEX=no temp.sqlite
source_data
3. Clean up the temporary spatialite database with Openlite utility by creating
a new empty database and copying the tables from the temp.sqlite into it.

Spatialite=yes option needs gdal 1.7.0 or higher
http://www.gdal.org/ogr/drv_sqlite.html

The troubles with Spatialite databases created by ogr2ogr are not very big. Some
foreign key constraints and triggers are not OK which makes it harder to make
properly working spatial views into database etc. For simple read-only access to
the tables the databases created with ogr2ogr are OK.