1

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?

2

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 ST_MakeValid and RTTOPO

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

5
  • Indeed for the PostGIS background, thanks for the clarifications. But ST_MakePoint was more a placeholder. The function I'm after is ST_MakeValid(geometry) which seems to be written as such in sqlite3 (i.stack.imgur.com/tWwAj.png gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html). And which still not available. – s.k Feb 21 at 0:19
  • Edited answer ;) – ThomasG77 Feb 21 at 0:29
  • Hmm, interesting. I've read many times that liblwgeom was not much appreciated (don't know why) and people struggled compiling sqlite/spatialite with rttopo support.... I will give this apt package a try then! And will come back here. – s.k Feb 21 at 0:45
  • No, installing librttopo-dev, librttopo1 and libsqlite3-mod-spatialite in order to load_extension('mod_spatialite'); was not sufficient. I thought it would be. So, there is probably a moment, in between version 4.3 and version 5.0 where the sqlite3 (or gdal?) devs have incorporated the spatial functions in a more "native" way, I have no other explanations. I strangely but happily didn't not even have to actually load that spatialite module with the working solution I have shown in my answer; sqlite3 is naturally geo-aware in a way! Starting from Ubuntu 20.10. – s.k Feb 21 at 1:06
  • It's in the Ubuntu packages that changes happened e.g Groovy (20.10) packages.ubuntu.com/groovy/libsqlite3-mod-spatialite vs Focal (20.04) packages.ubuntu.com/focal/libsqlite3-mod-spatialite What is weird is previous versions of Spatialite were supposed to already be able to run ST_MakeValid by depending previously on LWGEOM instead of RTTOPO e.g the doc gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html and all your attempts failed... – ThomasG77 Feb 21 at 1:15
1

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.

1
  • I gave it a try without any conviction because, as you said (and also as I printed at the bottom of the question), libspatialite-dev and libspatialite7 in ubuntu-full are at the same versions than the ones I already tested. Therefore, it falls back on the starting point. Still no ST_* functions. – s.k Feb 20 at 23:21
0

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.

3
  • I tried the Windows version of spatialite-gui that has SpatiaLite 4.3a and it does have ST_MakeValid. That version is compiled with lwgeom (select haslwgeom() returns true). – user30184 Feb 21 at 10:51
  • And my OSGeo4W installation has SpatiaLite 4.3.0 with lwgeom and ST_MakeValid is there. – user30184 Feb 21 at 10:59
  • I guess OSGeo people have nicely tight all geo-libraries together and that the GDAL gurus had to make some choices (for any good reasons probably?) about installing older spatialite stuff in their Docker images. I really don't know. From there, we can only hope that they will embed a newest version of spatialite, including support for rttopo as well, in the future releases of these GDAL images. But it more sounds like it's a Debian/Ubuntu default packages problem, so a "simple" switch to a base Ubuntu 20.10 image when building the related GDAL images should solve it. – s.k Feb 21 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.