Import GeoJSON in Spatialite

[Detry Geoffroy]

Hi all,

I try to import GeoJSON data to a spatialite database. According to the doc on the Wiki that said that Spatialite offers full supports to GeoJSON since version 5.0.0 ans as an Windows user, I downloaded the Next Generation Windows Binaries

My configuration is the following (confirm by the "select spatialite_version();" sql statement) and I guess It's the last one

I ran the simple statement : SELECT ImportGeoJSON('F:/Spatialite/shop.geojson', 'shop'); but it's returned me Error: SQL Error no such function: ImportGeoJSON. Note here that I've the same issueusing the ExportGeoJSON2 function

Following the doc, I ran the Windows cmd.exe and set the spatialite security variable by typing "set SPATIALITE_SECURITY=relaxed ". I cross check if the varible has been set by executing "echo %SPATIALITE_SECURITY%" (response = relaxed).

Reopening spatialite_gui.exe and re-executing the SQL statement, the error response is still there.

Is there something wrong on my actions?

Thanks very much

Geoffroy

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

On Thu, 25 Jul 2019 08:06:48 -0700 (PDT), Detry Geoffroy wrote:
> Hi all,
>
> I try to import GeoJSON data to a spatialite database. According to

> the doc [1]on the Wiki that said that Spatialite offers full

> supports
> to GeoJSON since version 5.0.0 ans as an Windows user, I downloaded
> the Next Generation Windows Binaries
>

> ----------------- <snip> -----------------

>
> Is there something wrong on my actions?
>

Hi Geoffroy,

the new GeoJSON implementation has been added very recently
(on February 2019), so it's not available on the pre-built
binaries for Windows that were realeased on August 2018.

bye Sandro

[Detry Geoffroy]

Thanks Sandro for your feedback.

So I keep up-to-date on this issue and the evolution of the GeoJSON support.

In the meantime I will import my spatial data by another way like QGIS DB Manager Extension

Geoffroy

[Peter Johnson]

It's possible to import GeoJson files using the sqlite3 command-line:

echo '{ "type": "Point", "coordinates": [ 1.1, 2.2 ] }' > example.geojson

SELECT load_extension("mod_spatialite");

SELECT InitSpatialMetaData(1);

WITH file AS ( SELECT readfile('example.geojson') as json )

SELECT AsText( GeomFromGeoJson( json_object(

'type', json_extract(( SELECT json FROM file ), '$.type' ),

'coordinates', json_extract(( SELECT json FROM file ), '$.coordinates' )

)));

POINT(1.1 2.2)

It's a little more complex than you might require but it works around a bug which was fixed at the end of 2016.

Peter Johnson CTO Geocode Earth Core Contributor Pelias Open Source Geocoder

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/6c9e7076-efec-4a72-af54-6c06cdd18864%40googlegroups.com.

[Detry Geoffroy]

Dear Peter,

Thanks for your answer.

Although is not the preferred way to load GeoJSON features to the SpatialiteDB, I had wanted to try your solution in case I could use it in the future.

Through SqLite3 console, I loaded the spatialite extension (mod_spatialite) to DB successfully. Then, I run the SQL sentence and I receive the following error

Error: no such function: json_object



It's mean I have to use the Json1 loadable extension in the sqlite3 shell. So, I looked in the JSON1 Extension Page and the Run-Time Loadable Extensions Page, downloaded the json1.c extension and compiled it using MinGW and the CLI

gcc -g -shared json1.c -o json1.dll

(I also downloaded both sqlite3ext.h and sqlite3.h files from the SQLite source and copied them in the relevant folder). Compilation works well and I'm able to recover json1.dll file.

With the sqlite3 shell started in the same directory as the compiled code, I get the following error when I load the compiled extension:

sqlite> SELECT load_extension("json1");

Error: %1 is not a valid Win32 application.

At this stage, I'm a bit blocked.

If somebody notes something wrong on the step I followed, I'm interested about any feedback

Geoffroy

To unsubscribe from this group and stop receiving emails from it, send an email to spatiali...@googlegroups.com.

[Alessandro Furieri]

 I get the following error when I load the compiled extension:

sqlite> SELECT load_extension("json1");

Error: %1 is not a valid Win32 application.

At this stage, I'm a bit blocked.

If somebody notes something wrong on the step I followed, I'm interested about any feedback

Hi Geoffroy,

an error like this usually means that you are attempting to load a 64bit extension

from a 32bit sqlite3.exe (or the opposite).

if you've already installed the MSYS2 shell you can easily check for this by

calling the following command from the command line:

file *.exe *.dll

bye Sandro

[Detry Geoffroy]

Hi Sandro,

Thanks for the feedback. Indeed, I worked with a 64bits Sqlite3 and generated the 32bits Json extension.

Two things here

• Json extension loading

As I downloaded the 64bits mod_spatialite binaries version including Sqlite3, I generated a 64bits json extension using MSYS2 MinGW 64-bit. It’s worked well and I retrieve all the dll files for 64 bits (including json1.dll).

When I try to load json1.dll through Sqlite3 by executing

SELECT load_extension('json1');

64bits Sqlite3 shell suddenly closes without any warning or error message.

For testing purpose, I also downloaded the 32bits mod_spatialitebinaries and generated 32bits json extension. Trying to load this extension, I note the same behaviour (32bits Sqlite3 shell closes).

I did not succeed to solve this problem. Maybe do you know this problem.

Then, to test the solution proposed by Peter, I downloaded the bundleof 32 bits command-line tools from SQLite Home Page. Json1 extension is loaded by default and I can manage GeoJSON files from this shell.

• Import GeoJSON to Spatialite (aim of the thread)

I built upon the Peter’s proposal. The GeoJSON file I have is more complex given that it contains a Feature collection with many feature objects. The type of the spatial objects can be points, polylines or polygons as well as null geometry. I share here an example of GeoJson file. Each point/vertex have a Z value.

To store the spatial objects, I created 3 tables on my spatialite DB (one/geometry type). Then, I run the following INSERT INTO SQL statement (example for polyline. It must be adapt for points/polygon) using Json_tree table-valued function)

INSERT INTO MYGEOMTABLE_LINE(geom)

SELECT
GeomFromText(AsText(GeomFromGeoJson(

json_object('type',json_extract(value,'$.type'),'coordinates',json_extract(value,'$.coordinates')))

),4326)

FROM
json_tree(readfile('PATH_TO_GEOJSON\test.geojson'))
WHERE
key ='geometry' and value is not NULL and value LIKE '%Line%'

Maybe it's not the most efficient way to import GeoJSON to Spatialite but It works like a charm. I’m going to go forward to build on it.

Thanks for your feedback

[Riya Francis]

Is the ImportFromGeoJson function available in latest release?

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

On Wed, 4 Nov 2020 03:50:46 -0800 (PST), Riya Francis wrote:
> Is the ImportFromGeoJson function available in latest release?
>

certainly yes.

please see:
http://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.0.html

note: as for many others SQL functions accessing external files
from the file-system you must explicitly set the environment
variable "SPATIALITE_SECURITY=relaxed" in order to authorize
import/export operations possibly causing security concerns.

bye Sandro

[Riya Francis]

Thanks for the quick response. Is it available as a binary for ubuntu or do we have to build it from source?
Installing via apt gives me 4.3.0a version and unknown function while trying to use import function.

Thanks

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

On Wed, 4 Nov 2020 04:39:31 -0800 (PST), Riya Francis wrote:
> Thanks for the quick response. Is it available as a binary for ubuntu
> or do we have to build it from source?
> Installing via apt gives me 4.3.0a version and unknown function while
> trying to use import function.
>

It seems to be on its way to being supported on Ubuntu,
but a binary package is probably not available yet.

https://launchpad.net/ubuntu/+source/spatialite

note: building from sources isn't any difficult
on any Linux

bye Sandro