7

I've started using DB Browser for SQLite and I liked its interface. However, even though I can open SpatiaLite databases in there, I can't use any spatial function while running queries from there.

My assumption is that I'd probably have to load the SpatiaLite module in the first line of my query in order to make DB Browser for SQLite use spatial functions. A related question is this one. I thought that using something like select load_extension('/usr/lib/x86_64-linux-gnu/libspatialite.so.7'); would solve this issue (the libspatialite.so.7 is a file that exists on my system). But although it seems to load the module successfully, when I try to use spatial functions like:

select load_extension('/usr/lib/x86_64-linux-gnu/libspatialite.so.7');
select asText(geometry) from mytable;

I receive the error:

Execution finished with errors.
Result: no such function: asText
At line 2:
select asText(geometry) from mytable;

Why can't I use SpatiaLite functions inside DB Browser for SQLite?

2
  • Best test for seeing if spatialite is available is probably select spatialite_version();.
    – user30184
    Commented Dec 24, 2021 at 9:19
  • @user30184 In my case, using select spatialite_version(); works fine when I use it inside spatialite-gui, but it doesn't work inside DB Browser for SQLite after using select load_extension(...);... I'm probably getting the wrong file, I've searched for the files named spatialite.so on my system and the one from my question is the one that appeared. If spatialite is using a file that is not named spatialite.so as a module, I'm not sure how can I search for it.
    – raylight
    Commented Dec 24, 2021 at 23:25

2 Answers 2

7
+50

Ensure you're not running a sandboxed version (snap or flatpak), install it from your distribution repositories instead. Sandboxed apps can't see the real /usr/lib directory (e.g. when accessing /usr/lib, snap apps are actually accessing /snap/{app name}/current/usr/lib).

In sqlitebrowser Preferences - Extensions tick the last checkbox "Allow loading extensions from SQL code"

Use mod_spatialite.so not libspatialite.so.7

select load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');
select spatialite_version(); 

You don't even need the full path:

select load_extension('mod_spatialite');
select spatialite_version(); 

5.0.1

enter image description here

0
4

Additional Info for Windows users: all files from "mod_spatialite-5.0.1-win-amd64.7z" (including "mod_spatialite.dll") available at gaia-gis.it must be in the same directory as DB Browser for SQLite.exe (not in "extensions")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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