GeomFromGeoJSON returning NULL in Qspatialite

[Chris Scheele]

Hi All,

I am writing a python script to import GeoJSON polygons into a spatialite DB (v4.3). Before I do this, I wanted to test my queries in QGIS using the Qspatialite GUI. Using the examples from the spatialite cookbook, I am unable to insert GeoJSON using the GeomFromGeoJSON() function supposedly supported in this version of spatialite. The result after inserting is a NULL geometry. Any thoughts?

INSERT INTO test (name, geom) VALUES ( 'point', GeomFromGeoJSON('{''type'':''Point'',''crs'':{''type'':''name'',''properties'':{''name'':''EPSG:4326''}},''coordinates'':[-1.23455679,9.87654321]}'))

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

On Thu, 6 Jul 2017 13:42:06 -0700 (PDT), Chris Scheele wrote:
> INSERT INTO test (name, geom) VALUES ( 'point',
>
> GeomFromGeoJSON('{''type'':''Point'',''crs'':{''type'':''name'',''properties'':{''name'':''EPSG:4326''}},''coordinates'':[-1.23455679,9.87654321]}'))
>

Hi Chris,

your GeoJSON expression fails on spatialite_gui as well, because
it's incorrectly formatted.

note: JSON's own text strings are always expected to be delimited
by DOUBLE QUOTES (") and not by SINGLE QUOTES (') repeated twice
as in your sample.
once correctly rewritten your SQL snippet works exactly as expected:

SELECT

GeomFromGeoJSON('{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-1.23455679,9.87654321]}')

just to be pedantically clear:
- the SQL function expects an argument of the TEXT type, so
you must delimit a constant string value within SINGLE
QUOTES (') accordingly to the SQL own syntax:
GeomFromGeoJSON(' <geoJSON string> ')
- but the actual payload of the argument is expected to be a
valid GeoJSON expression, and the GeoJSON own syntax dictates
to enclose any text constant within DOUBLE QUOTES (").

bye Sandro