1

How can I merge a SpatiaLite database to a PostGIS database?

I tried to use pgloader with the following command:

pgloader db.sqlite3 postgresql:///mydb

But it is not working. (I guess it is not supported). Please see the error output:

KABOOM!
FATAL error: Could not prepare an sqlite statement.
Code ERROR: no such module: VirtualSpatialIndex.
Database: /tmp/db.sqlite3
SQL: PRAGMA table_info(`SpatialIndex`)

An unhandled error condition has been signalled:
   Could not prepare an sqlite statement.
Code ERROR: no such module: VirtualSpatialIndex.
Database: /tmp/db.sqlite3
SQL: PRAGMA table_info(`SpatialIndex`)




FATAL: Failed to start the monitor thread.

error opening #P"/tmp/pgloader/pgloader.log": Permission denied

I use in SpatialLite only the simple POINT field. Nothing else from SpatialLite. (With Lat/Lng values)

EDIT1: @Corion

If I try to do your way I get no error doing this:

pgloader --before load_spatialite.sql db.sqlite3

But loading it to PostGIS with this command:

pgloader --before load_spatialite.sql db.sqlite3 postgresql:///mydb

gives me the following error message:

2018-10-10T11:29:16.056000Z ERROR Database error 42883: function load_extension(unknown) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT load_extension('mod_spatialite')
KABOOM!
FATAL error: Database error 42883: function load_extension(unknown) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT load_extension('mod_spatialite')
An unhandled error condition has been signalled:
   Database error 42883: function load_extension(unknown) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT load_extension('mod_spatialite')




What I am doing here?

Database error 42883: function load_extension(unknown) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT load_extension('mod_spatialite')

What is here the problem?

Philipp S.
  • 827
  • 17
  • 41

1 Answers1

1

SpatiaLite is an extension built into SQLite. You will need an SQLite binary / library that is built with SpatiaLite linked statically or load the SpatiaLite dynamic extension (see link) while importing.

From staring at the pgloader manpage, it seems that you can prepend SQL commands to load the SpatiaLite extension into SQLite like this:

pgloader --before load_spatialite.sql  /tmp/db.sqlite3

and load_spatialite.sql contains

SELECT load_extension('mod_spatialite');

You may or may not need to set up additional environment variables such that dynamic libraries are found for your process.

Corion
  • 3,855
  • 1
  • 17
  • 27
  • Thank you for the fast respond. I get a new error now (I edited my post because the error message is long) – Philipp S. Oct 10 '18 at 11:32
  • `ERROR Database error 42883: function load_extension(unknown) does not exist` - maybe your SQLite library was compiled without `SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION` , which makes that function available. You can only find that by finding out how your SQLite library was compiled. – Corion Oct 10 '18 at 11:41
  • I use Debian 9 and installed everything from the repositories. How can I see if it was compiled with this option? I guess it is not compiled with this option, so what can I do? – Philipp S. Oct 10 '18 at 11:50
  • I don't really know - either take it up with the `pgloader` developer(s) or with the package maintainer(s) of the Debian SQLite package, or with the package maintainer(s) of the Debian `pgloader` package, or maybe compile your own `libsqlite` and use that for building `pgloader` from source. I don't think any of these options are really great options, so maybe exporting from SQLite to CSV and then importing into Pg is a better option?! – Corion Oct 10 '18 at 12:18
  • 1
    I tried it with the docker image from pgloader website but there is the same problem. I will ask the developers of pgloader. Maybe there are going to fix this problem ... – Philipp S. Oct 10 '18 at 12:46
  • According to pgloader documentation (see man pgloader) the before commands are performed on target database... so the function load_extension would not be recognized because you are performing it on postgresql target database, not on sqlite source database. – manuele Jun 16 '20 at 15:15