error geometry constraint when inserting geojson

[Guen LM]

Hi,

I try to insert (in spatialite_gui but same with php) a new record (or update existing one) in a table geoilot with this parameter :

1. field geom : GEOMETRY
2. geometry type : multipolygone (6)
3. srid : 4326 (WGS84)
4. coord dimension : 2

the geometry come from geojson so I have to convert it.

my query is :

INSERT INTO geoilot (ilot_id,GEOMETRY)

VALUES (12345,

GeomFromGeoJSON('{"type":"MultiPolygon",

"coordinates":[[[[4.96155079044088,45.77560724373348],

[4.962346534809607,45.77570845150045],[4.962250134932329,

45.77537893550701],[4.961608743451676,45.77527504348121],

[4.96155079044088,45.77560724373348]]]]}

')

)

I have en error geom-type or srid not allowed...

The SELECT query (without insert) is good :

SELECT GeomFromGeoJSON('{"type":"MultiPolygon",

"coordinates":[[[[4.96155079044088,45.77560724373348],

[4.962346534809607,45.77570845150045],[4.962250134932329,

45.77537893550701],[4.961608743451676,45.77527504348121],

[4.96155079044088,45.77560724373348]]]]}

') as geom

I tried to add CASTOMULTI for my geom but not better...

Do you know where is the problem please ?

Thank's a lot

Guen

[Guen LM]

I forgot : spatialite_version=4.4.0-RC0

[Peter Johnson]

Calling SRID() on that geometry returns 0 for me, you'll probably need to explicitly call SetSRID(geom, 4326).

I'm not sure of the reasons behind this but I believe the spec used to allow alternate projections, but that has since been deprecated and may exist for historical purposes.
https://macwright.com/2015/03/23/geojson-second-bite.html#projections

It would probably be fairly safe these days for spatialite to assume 4326 for GeoJSON.

SELECT spatialite_version();
5.0.0

spatialite> SELECT SRID( GeomFromGeoJSON('{"type":"MultiPolygon",

"coordinates":[[[[4.96155079044088,45.77560724373348],
[4.962346534809607,45.77570845150045],[4.962250134932329,
45.77537893550701],[4.961608743451676,45.77527504348121],
[4.96155079044088,45.77560724373348]]]]}

') ) as geom;
0

-P

--
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/5d4fb668-3941-4590-a800-974af685c767n%40googlegroups.com.

[Guen LM]

I don't find a new version for spatialite_gui (http://www.gaia-gis.it/gaia-sins/spatialite-gui-sources/spatialite_gui-2.1.0-beta1.zip doesn't work...) or file to install on windows.

and it is 4.3.0.a on my linux server (but same problem).

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

On Wed, 8 Sep 2021 11:07:26 -0700 (PDT), Guen LM wrote:
> I don't find a new version for spatialite_gui
>
> (http://www.gaia-gis.it/gaia-sins/spatialite-gui-sources/spatialite_gui-2.1.0-beta1.zip
> doesn't work...)
>

Guen,

you are looking in the wrong place; you are attempting to download
the source code when you actually need an executable binary.

you can find the prebuilt binaries for Windows from here (32 bit)
http://www.gaia-gis.it/gaia-sins/windows-bin-x86-prev/

or from here (64 bit)
http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-prev/

bye Sandro

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

On Wed, 8 Sep 2021 10:41:29 -0700 (PDT), Guen LM wrote:
> Do you know where is the problem please ?
>

how to correctly (and quickly) resolve this kind of
problems.

the golden rule: always test your SQL code step by step
carefully avoiding too much complex statements.

so we'll start by testing GeomFromGeoJSON() alone

SELECT GeomFromGeoJSON('{"type":"MultiPolygon",
"coordinates":[[[[4.96155079044088,45.77560724373348],
[4.962346534809607,45.77570845150045],[4.962250134932329,
45.77537893550701],[4.961608743451676,45.77527504348121],

[4.96155079044088,45.77560724373348]]]]}');

ok, it returns a Geometry; but calling the BLOB exporer
widget supported by the GUI we'll soon discover that
it actually is a MULTIPOLYGON, but the SRID has the
default value (0).
this doesn matches the geometry constraints, and the
INSERT will obviously fail.

the solution now becomes evident; we need to set
SRID=4326

correctly rewriting the SQL statement:

INSERT INTO geoilot (ilot_id,GEOMETRY)
VALUES (12345,

SetSrid(GeomFromGeoJSON('{"type":"MultiPolygon",

"coordinates":[[[[4.96155079044088,45.77560724373348],
[4.962346534809607,45.77570845150045],[4.962250134932329,
45.77537893550701],[4.961608743451676,45.77527504348121],

[4.96155079044088,45.77560724373348]]]]}'), 4326)

note: exactly as previously suggested by Peter.

bye Sandro

[Guen LM]

OK good ! I saw the SRID=0 on the Blob explorer but I didn't try SetSrid..and it work's

thank's a lot (and for the download link too) !

guen