Add spatial functions to a geopackage template

Question

I would like to dump data from my survey software to a geopackage file through an ODBC connection but I need a minimum of spatial functions to execute my requests: ST_IsEmpty(geom), ST_MinX(geom), ST_MaxX(geom), ST_MinY(geom), ST_MaxY(geom), GeomFromEwkt, St_Multi.

Here's a exemple of my PostGIS request through ODBC:

|   INSERT INTO myTab
|   ( name,
|   date_cmp,
|   geom)
|   VALUES
|   ([myPoint],
|   to_timestamp([[myPoint]:DT]),
|   ST_SetSRID(GeomFromEwkt('JS_WKT([myPoint],P3)'),3163)
|   );

JS_WKT(component,type) is producing an EWKT.

My gpkg is a template file made by qgis 2.18.17 my software will copy an fill with Insert requests.

My question is a little similar to geopackage-update-query-generates-an-error-no-such-function-st-isempty

Answer 1

New clue:

starting since 4.2.0 loading the dynamic extension on the top of SQLite3 is now based on "mod_spatialite" (a pure extension module, not a classic shared library). You can eventually read more technical details from here.

After some tests, I found a way to make it work:

• download le lib spatialite-4.3.0a-win-x86.7z from here ;
• unzip it in C:\Windows\System32\;
• create a SQLite ODBC source on a geopackage;

• load the extension from a request;

  SELECT load_extension('mod_spatialite');

• use my spatial request.

  SELECT ST_MinX(geom), ST_MinX(geom), ST_IsEmpty(geom) FROM "block";

And it work fine!

Answer 2

It seems that a sqlite database cannot have stored spatial functions.

Spatial library are dynamicaly linked to the software opening the database through the loading of extensions.Spatially enable an SQLite database

The the SQLite3 ODBC driver (sqliteodbc) I use have an option to load a folder of extensions. How to load Spatialite extension via SQLite3 ODBC Driver?

A google group explains it on Tech Note: SpatiaLite and language connectors (aka: SQLite's loadable dynamic extensions) for more recent work on this.