ImportGeoJSON not found in Spatialite 5.0.1

[Luís]

Dear all,

I am trying to import a couple of GeoJSON files into a SQLite database. The system is Ubuntu 22.04, with SQLite 3.37.2. Invoking the ImportGeoJSON function results in an error, as the log below shows.

I wonder if I am missing some package or library. Thank you for reading.

```bash

$ sqlite3 db.sqlite
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> SELECT spatialite_version();
Error: in prepare, no such function: spatialite_version (1)
sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT spatialite_version();
5.0.1
sqlite> SELECT ImportGeoJSON('diamond.geojson', 'diamond');
Error: in prepare, no such function: ImportGeoJSON (1)
sqlite>

```

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

On Wed, 11 Jan 2023 02:35:46 -0800 (PST), Luís wrote:
> Dear all,
>
> I am trying to import a couple of GeoJSON files into a SQLite
> database. The system is Ubuntu 22.04, with SQLite 3.37.2. Invoking
> the ImportGeoJSON function results in an error, as the log below
> shows.
>

> sqlite> SELECT ImportGeoJSON('diamond.geojson', 'diamond');
> Error: in prepare, no such function: ImportGeoJSON

> sqlite>

>
> I wonder if I am missing some package or library. Thank you for
> reading.
>

Hi Luis,

you have simply forgot to read the documentation for the
Import GeoJSON() SQL function that clearly states:

"Please note well: this SQL function opens the
door to many potential security issues, and thus
is always disabled by default.
Explicitly setting the environment variable
SPATIALITE_SECURITY=relaxed
is absolutely required in order to enable this
function"

please see:

https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

bye Sandro

[Luís]

Dear Sandro,

thank you for the swift reply. This morning I understood that restriction to apply only with the second version of the function (more parameters). The HTML could perhaps be built in a more explicit structure. A different error message could also help the user in this case. Say, something like "Current security settings do not allow the function to execute."

With the SPATIALITE_SECURITY variable set the function still fails, but now with a different error message (log below). Is it still related to security?

Thank you.

```bash

$ export SPATIALITE_SECURITY=relaxed

$ sqlite3 db.sqlite
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.

sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT ImportGeoJSON('diamond.geojson', 'diamond');

AddGeometryColumn() error: unexpected metadata layout
GeoJSON import: INSERT INTO error (table diamond has 2 columns but 3 values were supplied)

```

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

On Wed, 11 Jan 2023 06:00:12 -0800 (PST), Luís wrote:
> Dear Sandro,
>
> thank you for the swift reply. This morning I understood that
> restriction to apply only with the second version of the function
> (more parameters). The HTML could perhaps be built in a more explicit
> structure.
>

as a general rule all SQL functions involving reading or writing
some external file are always considered potentially harmful and
are consequently subject to SPATIALITE_SECURITY

A different error message could also help the user in this
> case. Say, something like "Current security settings do not allow the
> function to execute."
>

note: the error message is raised by SQLite, not by SpatiaLite, and
SQLite can't have any idea about extended functions if they are not
explicitly defined and enabled.
SQLite only knows that you are trying to execute an undefined function.

> With the SPATIALITE_SECURITY variable set the function still fails,
> but now with a different error message (log below). Is it still
> related to security?
>

no, this is genuire functional error.

> sqlite> SELECT ImportGeoJSON('diamond.geojson', 'diamond');
> AddGeometryColumn() error: unexpected metadata layout
>

this means that your DB lacks all the metadata tables specifically
required by SpatiaLite (as geometry_columns and friends)
so it's impossibile to create a Geometry column.

> GeoJSON import: INSERT INTO error (table diamond has 2 columns but 3
> values were supplied)
>

and consequently the INSERT then fails just because of the
previous error.

Hint:
-------------------------------------
Every time you create a new DB you must always create all the
tables required by SpatiaLite.

SELECT InitSpatialMetaDataFull(1);

note: this is not required when using the GUI tool, but is
strictly mandatory when using CLI interfaces.

bye Sandro

[Luís]

Thank you once more Sandro. That makes it all clear now.

Regards.