Options for odbc.ini: load 'mod_spatialite'

[Tobias Schula]

Hi at all!

Does anyone here have experience using SpatiaLite through ODBC?

How can I load the 'mod_spatialite' extension with unixODBC in the ~.odbc.ini?

I set up a database connection with unixODBC to a SpatiaLite database. I then
use the LibreOffice database connection to generate (pivot-)tables and
(pivot-)charts in Calc. However, I don't get a connection to a SQLite database
with 'mod_spatialite' enabled. So I can't use the SpatiaLite functions in Calc
which is a huge setback.

I can run SELECT load_extension('mod_spatialite'); in Base, but that is only
on runtime, so it doesn't get transferred over when I use the database
connection in Calc. Right now, when the data changes, I regenerate static,
pure SQLite tables. But this is always an extra-step in Base that I often
forget. Then I have the wrong calculations in my reports.

Best regards
--
Tobias A Schula
Forestry Expert

geosp...@schula.org

[a.fu...@lqt.it]

Hi Tobias,

I personally have never worked on ODBC connections,
so I don't know what to tell you about that.

I hope there is someone on this mailing list who
can give you the assistance you are looking for,
but you may also be the first and only human being
to venture into these adventurous shores.

I can't seem to recall anyone else using SpatiaLite
via Calc.

bye Sandro

[Tobias Schula]

Hi Sandro!

On Thursday, 13 March 2025, 21:15:21 CET a.fu...@lqt.it wrote:
> I hope there is someone on this mailing list who
> can give you the assistance you are looking for,
> but you may also be the first and only human being
> to venture into these adventurous shores.

Thank you for your kind words! It truly is an adventure. But it works! I don't
know what Base is doing in the background, I think they might have their own
SQL parser, as my usual SQL is not working. But looking at the SQL their
visual designer gives me, I can translate my queries. Usually, for getting the
area of different subdivisions in a forest enterprise I would write something
like that:

```
select
n1."ogc_fid",
d1."distrikt" as 'Distrikt',
d1."name" as 'Distriktname',
a1."abteilung" as 'Abteilung',
a1."name" as 'Abteilungsname',
n1."unterabt" as 'Unterabteilung',
st_area(st_union(n1."geometry"))/10000 as 'Fläche [ha]',
st_union(n1."geometry") as geometry
from
"nutzungsarten" as n1
left join "abteilungen" as a1 on n1."abteilung_id"=a1."ogc_fid"
left join "distrikte" as d1 on n1."distrikt_id"=d1."ogc_fid"
group by
n1."unterabt",
n1."abteilung",
n1."distrikt"
order by
n1."distrikt",
n1."abteilung",
n1."unterabt"
```

Now, in Base, I need to write it like this, sans the geometry, because I don't
need to display polygons:

```
SELECT
"distrikte"."distrikt" "Distrikt",
"distrikte"."name" "Distriktname",
"abteilungen"."abteilung" "Abteilung",
"abteilungen"."name" "Abteilungsname",
"nutzungsarten"."unterabt" "Unterabteilung",
st_area( st_union( "nutzungsarten"."geometry" ) ) / 10000 "Fläche
[ha]"
FROM {
oj "nutzungsarten"
LEFT OUTER JOIN "abteilungen" ON "nutzungsarten"."abteilung_id" =
"abteilungen"."ogc_fid"
LEFT OUTER JOIN "distrikte" ON "nutzungsarten"."distrikt_id" =
"distrikte"."ogc_fid"
}
GROUP BY
"Unterabteilung",
"abteilungen"."abteilung",
"distrikte"."distrikt"
ORDER BY
"Distrikt" ASC,
"Abteilung" ASC,
"Unterabteilung" ASC
```

The views I created in QGIS DB Manager and spatialite_gui load without
problems however and all ST_* commands work! I'll write to the creator of the
SQLite-ODBC driver. I vaguely remember being able to load extensions on
Windows through the graphical ODBC interface.

Alternatively, I might set up a PostGIS locally with docker, as Libreoffice
has a native Postgres driver. This might actually work without tinkering, but
the tinkering is way more fun!

Generally, data-sources in Calc/Base are quite powerful. I can set up a
worksheet with pivot-tables and pivot-charts once and have them regenerate
when the data-source changes. Still less comfortable than R with GDAL, but the
client insists on office documents and "selling" them LibreOffice instead of
Excel and Word was enough work already.

Even now, in 2025, Writer is still ahead of Word in terms of structuring a
document and separating content from layout. If I can't write LaTeX, I always
choose Writer. I'm using Writer since 97, so I might be biased. I'm also
amazed how well Base works as an interface to databases, it feels a lot like
working with Access in the late 90s and early 2000s.

> I can't seem to recall anyone else using SpatiaLite
> via Calc.

I found someone else :-D :
> https://groups.google.com/g/spatialite-users/c/GZfqHJqh_YQ/m/Lq0SyjKk3w4J

bye and best regards

[Tobias Schula]

On Wednesday, 19 March 2025, 23:00:26 CET Tobias Schula wrote:
> I can run SELECT load_extension('mod_spatialite'); in Base, but that is only
> on runtime, so it doesn't get transferred over when I use the database
> connection in Calc.

I found a solution for my problem. I just need to leave base open in the
background, then the spatialite extension also stays in memory and I can do
all my spatial operations from calc and writer!

Thankfully I'm writing my current report with R and LaTeX, there a connection
to spatialite and postgis is no problem!

[a.fu...@lqt.it]

Hi Thomas,

I'm very pleased that you finally managed to solve your problems,
even if in a somewhat tortuous way.

As a general rule, SELECT load_extension('mod_spatialite');
should always be the most direct way to dynamically load the
SpatiaLite extension at runtime.

It's a universal mechanism that depends exclusively on SQLite
and that should always work in all the most varied contexts.

Unfortunately the real world is always more complicated than
theory says, but I see that in the end with calm and patience
you managed to overcome all the obstacles.
BRAVO!!!

By the way: this thread is a great example of how an initial
request for help ended up turning into a solution that could
be useful to many others. ;-)

bye Sandro