[Qgis-user] How-to: add spatial reference to SQLite DBs

[Tobias Wendorff]

Hi there.

I'm new to this mailing list, so let me give some knowledge from my
work in QGIS (I don't want to take only, hehe). Maybe it helps someone
or leads to a nice discussion, how to make it even better.

Often, it's more useful to store geodata in small SQLite. Sure, it
doesn't have all the geo-functions or even a spatial index, like
SpatiaLite or Geopackage does. But SQLite is a very lightweight way
to store and transmit information via E-Mail. You can store multiple
layers and ... argh, you know what I'm talking about.

A very annoying problem I had with QGIS is that it doesn't add the
correct information about CRS/SRS (the coordinate system being used)
to the SQLite file. The problem results into QGIS complaining about
a missing CRS/SRS on re-opening at file and forces it to be EPSG:4326.
That's good for OpenStreetMap data, but not really usefull for
anything else.

To add the missing CRS/SRS, keep in mind to store metadata in QGIS and
select "SQLite" as export format (not "SpatiaLite"!). In the following
example, I'll set SRID to 3035 used in Europe for maps about statistics:

1. Open the SQLite file, f.e. with sqlite3:
# sqlite3 database.sqlite
2. Add the information about spatial reference to the geometry column
(table is "demo_data"):
# UPDATE geometry_columns SET srid = 3035 WHERE table = 'demo_data';
3. Add the definition of the spatial reference: [left out here]

You can look up the string for spatial reference, f.e. on this website:
http://spatialreference.org/ref/epsg/3035/postgis/

But this is important:
1. Remove "proj4text," from the bracketed part and remove "+proj=..."
from the second one.
2. make the first number of the second bracketed part be equal to your
EPSG-code, f.e. "3035".

So the beginning should look like:
# INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext)
VALUES (3035, 'epsg', 3035,

I hope this helps other people with the same problem.

Best regards,
Tobias