No ST_* spatial functions are available in SQLite3

Question

I'm struggling when using SQLite3 inside a docker container build on this (Ubuntu 20.04 based) GDAL image osgeo/gdal:ubuntu-small-3.2.1.

I have to install sqlite3 first by:

# apt-get install -q sqlite

it went fine:

# sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

But then, for whatever reason, I'm not able to use any spatial function, e.g.:

sqlite> SELECT ST_Buffer(ST_MakePoint(0, 0), 1, 1);
Error: no such function: ST_MakePoint

I also tried to install the spatialite-bin package, hopping they are hidden in there, but it didn't change anything, even if I entered the following into the SQLite3 command line:

sqlite> SELECT load_extension('mod_spatialite');

which results in an empty line. And still no ST_* functions.
Same thing after installing these in a last hope:

librttopo1 is already the newest version (1.1.0-2).
libspatialite-dev is already the newest version (5.0.0-1).
libspatialite7 is already the newest version (5.0.0-1).

How do we get the spatial functions in SQLite3?

Answer 1

You simply did not read the reference doc from Spatialite as I suppose you were expecting portability between PostGIS and Spatialite spatial SQL syntaxes. You are correctly loading your extension but in Spatialite, there is no ST_MakePoint function e.g http://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

You will see that SELECT ST_AsText(ST_Buffer(MakePoint(0, 0), 1, 1)); does work. For your particular query, ST_MakePoint can be replaced with MakePoint or ST_Point.

Update:

As you want in particular functions like ST_MakeValid, you need to build Spatialite with RTTOPO (at least for Spatialite 5.0) support e.g

Code for RTTOPO available at https://git.osgeo.org/gitea/rttopo/librttopo

You can find a Docker recipe to build your own spatialite with RTTOPO https://hub.docker.com/r/chrkaatz/node-spatialite/dockerfile

For better understanding of the context, RTTOPO is a replacement for LWGEOM due to https://www.gaia-gis.it/fossil/libspatialite/tktview/adbde604ba08971a134d02ec6df78789b5c2bfec

Answer 2

The ubuntu-small dockerfile is built without SpatiaLite. Use ubuntu-full instead https://github.com/OSGeo/gdal/blob/master/gdal/docker/ubuntu-full/Dockerfile

It seems to download libspatialite-dev and libspatialite7.

Answer 3

I ended up manually installing some packages (lowest dependency on top) from the Groovy Gorilla repos:

apt-get install -y --no-install-recommends wget \
   proj-bin proj-data gdal-bin sqlite3 libgeos-dev librttopo-dev \
   zlib1g libpq5 libc-bin libc6 libtiff-dev libsqlite3-dev libcurl4-openssl-dev \
&& wget -q http://archive.ubuntu.com/ubuntu/pool/universe/p/proj/proj-data_7.1.0-1_all.deb \
&& wget -q http://archive.ubuntu.com/ubuntu/pool/universe/p/proj/libproj19_7.1.0-1_amd64.deb \
&& wget -q http://archive.ubuntu.com/ubuntu/pool/universe/p/proj/libproj-dev_7.1.0-1_amd64.deb \
&& wget -q http://archive.ubuntu.com/ubuntu/pool/universe/s/spatialite/libspatialite7_5.0.0-1_amd64.deb \
&& wget -q http://archive.ubuntu.com/ubuntu/pool/universe/s/spatialite/libspatialite-dev_5.0.0-1_amd64.deb \
&& dpkg -i proj-data_7.1.0-1_all.deb \
&& dpkg -i libproj19_7.1.0-1_amd64.deb  \
&& dpkg -i libproj-dev_7.1.0-1_amd64.deb \
&& dpkg -i libspatialite7_5.0.0-1_amd64.deb \
&& dpkg -i libspatialite-dev_5.0.0-1_amd64.deb \
&& rm --interactive=never proj-data_7.1.0-1_all.deb libproj19_7.1.0-1_amd64.deb \
   libproj-dev_7.1.0-1_amd64.deb libspatialite7_5.0.0-1_amd64.deb libspatialite-dev_5.0.0-1_amd64.deb \
&& apt-get -y autoremove --purge && apt-get -y autoclean && ldconfig \

(yes, awful, but working).

My tests where these two function calls, especially the second one:

ogrinfo -dialect "SQLite" db.sqlite -sql "SELECT sqlite_version();"
ogrinfo -dialect "SQLite" db.sqlite -sql "SELECT spatialite_version();"

Which results in the following when using osgeo/gdal:ubuntu-full-3.2.1:

INFO: Open of `db.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
sqlite_version(): String (0.0)
OGRFeature(SELECT):0
  sqlite_version() (String) = 3.31.1

and

INFO: Open of `db.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
spatialite_version(): String (0.0)
OGRFeature(SELECT):0
  spatialite_version() (String) = 4.3.0a

respectively. And there, ST_MakeValid() was not known.

And it results the following with the workaround shown here above, and using osgeo/gdal:ubuntu-small-3.2.1 (~700MB lighter than the full version):

    - 'VirtualXPath'    [XML Path Language - XPath]
INFO: Open of `db.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
sqlite_version(): String (0.0)
OGRFeature(SELECT):0
  sqlite_version() (String) = 3.31.1

and

    - 'VirtualXPath'    [XML Path Language - XPath]
INFO: Open of `db.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
spatialite_version(): String (0.0)
OGRFeature(SELECT):0
  spatialite_version() (String) = 5.0.0

respectively. And there, ST_MakeValid() was actually available!

It seems these functions where not available with spatialite versions < 5.x. But I'm quite sure I'm wrong somewhere because on the doc, it's written version 4.2 and ST_MakeValid() was already there.