Keeping QGIS from writing all CRS to Spatialite database?

Question

I am wanting to move from shapefiles to spatialite database files, but am new to QGIS and spatialite.

How do I keep QGIS from creating 4924 records in the spatial_ref_sys table and 4873 records in the spatial_ref_sys_aux table when it creates a spatialite database?

In effect a completely empty spatialite database created by QGIS is 5,460 KB in size. And it appears that the bulk of that is in the two spatial_ref_sys tables.

All I am needing for this data is epsg:4326 WGS 84.

I found an article that mentioned deleting all records from the table except what was needed, but when I try that I get a foreign key error and they will not delete.

DELETE FROM "spatial_ref_sys" where srid!= 4326;

Returns

FOREIGN KEY constraint failed: DELETE FROM "spatial_ref_sys" where srid!= 4326;

Ideally, I would like to know if QGIS has an option to only include certain CRS in the database at time of creation.

Or at worst, how to actually delete the unwanted records.

Answer 1

As @ianturton points out, a geopackage only contains the CRS info needed. You can confirm this using sqlite (Geopackages are sqlite databases)

sqlite3 my.gpkg
$sqlite > select count(*) from gpkg_spatial_ref_sys;
4
$sqlite > .quit

(in my example it added 2 'null' CRSes, WGS84 and the projection I was using)

If you need to save it as Spatialite, there's a secret option hiding in the save dialog, called 'INIT_WITH_EPSG'. It's quite well hidden, I only just spotted this - you need to scroll down. See screenshot. Set that to No and save.

You should now find that only the required projection is saved.

Answer 2

You can create a minimal SpatiaLite database with GDAL by using the datastore creation option "INIT_WITH_EPSG=NO" as described in http://www.gdal.org/drv_sqlite.html.

ogr2ogr -f sqlite -dsco SPATIALITE=YES -dsco INIT_WITH_EPSG=NO small.sqlite one_point.shp

This way there will be only one line that defines the SRS of the source data in the spatial_ref_sys table. In my test I stored one point into SpatiaLite and that minimal database takes only about 228 kB on disk.