Trying to import geometries from gpkg files into single spatialite database

[Joshua Lugo Mclellan]

Hi,

Was hoping that I could pick the brains of people on this mailing list. My goal is to take three different gpkg files (specifically one table with geometries from each) and move them into a singular spatialite database in order to facilitate more efficient data processing down the road. Unfortunately I keep running into "Parse error: unsafe use of GeometryConstraints()" errors and I'm not sure how to do it correctly. Most recently I tried using the virtual table to get well known text representation of the geometry and then use GeomFromText to build the geometries into my table but i continue to run into the same error. I was wondering if I'm taking a conceptually incorrect approach or if there is a piece of minutiae that I'm tripping up on.

To reduce the question down: what would be the best way to take a gpkg package and import/re-create the table as a native spatialite table?

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

On Sun, 1 Oct 2023 09:59:02 -0700 (PDT), Joshua Lugo Mclellan wrote:
> Hi,

>
> Unfortunately I keep running into
> "Parse error: unsafe use of GeometryConstraints()"
> errors and I'm not sure> how to do it correctly. Most recently I
> tried using
> the virtual table to get well known text representation of the geo
>

Hi Joshua,

you are most likely experiencing the same effects described
in this fairly recent ticket:

https://www.gaia-gis.it/fossil/libspatialite/tktview?name=d8b5703db6

a very quick explanation:

1. A few years ago SQLite introduced PRAGMA trusted_schema, a directive
intended to improve security

2. when trusted_schema=1 SQLite forbids all the following operations:
2.a) using SQL functions defined by any loadable extension in the
context of VIEWs and TRIGGERs
2.b) using VirtualTables

unfortunately SpatiaLite is entirely based on the extensive use of
extra SQL functions which are continuously called by Triggers.
in short: when trusted_schema=1 SpatiaLite completely stops working.

usally all the most common SQLite distributions still continue to be
compiled in such a way as to keep trusted_schema=0, and so everything
continues to work as always.

in recent times, however, it seems that some distributions have
started to adopt the opposite choice (i.e. trusted_schema=1)
I suspect it's a problem especially in the Mac world, but it's
just a suspicion.

the simplest solution: try running this command every time you
open a connection to a DB

PRAGMA trusted_schema=0

this will immediately restore SQLite to its "traditional/historical"
mode and SpatiaLite will restart working as expected.

bye Sandro

[Yvan B]

Hello,

I've had a similar issue yesterday after I've updated sqlite (from 3.38.5 to 3.43.1).  I am on Windows 10 and still use spatialite 4.4.0.

When importing a shapefile with version 3.43.1, I now get this error :

load shapefile error: <unsafe use of GeometryConstraints()>

ROLLBACK;

If I set pragma trusted_schema = 1 before importing the shapefile, it works without error

best regards, yvan

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

On Sun, 1 Oct 2023 23:09:08 -0700 (PDT), Yvan B wrote:
> Hello,
>
> I've had a similar issue yesterday after I've updated sqlite (from
> 3.38.5 to 3.43.1).  I am on Windows 10 and still use spatialite
> 4.4.0.
>

note: SpatiaLite passively suffers the negative effects of the new
SQLite settings; all versions released to date are affected in the
exact same way.

> If I set pragma trusted_schema = 1 before importing the shapefile, it
> works without error
>

I apologize; in my previous reply I unintentionally swapped the
PRAGMA trusted_schema settings.
the correct version is this one below:

PRAGMA trusted_schema=1

this is the new setting that prevents SpatiaLite from working
correctly

-----------------

PRAGMA rtusted_schema=0

this instead is the traditional "historical" setting that
does not block SpatiaLite

good news: the next version of SpatiaLite that will be released
in the near future will automatically fix this issue.

bye Sandro

[Jukka Rahkonen]

Hi Sandro,

Unfortunately your correction is wrong. By the old behavior the trusted schema is ON (1) by default and SpatiaLite does not have issues. If SQLite is compiled so that the setting is OFF (0) by default, it must be turned on with the PRAGMA command

PRAGMA trusted_schema=1;

The original SQLite documentation is in https://www.sqlite.org/pragma.html#pragma_trusted_schema

-Jukka Rahkonen-