Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#4092 closed task (fixed)

Spatialite updates : 3D geometries

Reported by: Even Rouault Owned by: Even Rouault
Priority: normal Milestone: 1.9.0
Component: OGR_SF Version: unspecified
Severity: normal Keywords:
Cc: aperi2007@…, pcorti@…, pcav


Pasting an email from Alessandro Furieri

This time the patch I'm submitting to your attention
for further evaluation simply implements 3D geometries
[XYM, XYZ and XYZM] support
(this corresponding to Step 1, as per your suggestions).

Any other enhancement (supporting spatialite's own DB
creation in a truly effective way, libsqlite updating
and so on) is absolutely ignored for now.
Limiting our efforts to a single and well confined
problem (3D geoms support) surely allows an easier
and simpler code inspection and debugging.

please see the attached zipfile, containing both
patched sources and svn-diff

just a quick narrative recapitulation:

a) ogr_sqlite.h
I've added an OGRSpatialiteGeomType enumeration,
so to avoid using anonymous "magic numbers" to
identify any Geometry class internally supported
by SpatiaLite

b) ogrsqlitedatasource.cpp
I've added a simple test for spatialite's own
GEOMETRY_COLUMN table, so to retrieve the declared
CoordDims (XY, XYZ, XYM or XYZM): please note, this
test safely works for both "old-styled" (<= v.2.3.1)
and "new-styled" (>= v.2.4.0) geometries.

c) ogrsqlitedatasource.cpp
while performing further tests I've discovered a
really insidious potential issue:
- OGR never supports the M coordinate
- but SpatiaLite does

so, while attempting to perform INSERT or UPDATE on
behalf of some already defined table declaring XYM or
XYZM coord-dims, a failure will surely arise simply
because any new geom created by OGR completely ignores
at all M values.

resolving such issue is anyway reasonably simple:
- I've added a boolean bHasM flag to the "layer"
   and "table-layer" classes.
   this flag is absolutely ignored if not spatialite.
   but for any spatialite table the appropriate
   value will be initialized while opening the
- after this initialization step, then the datasource
   code will take care of silently append M-values
   to any spatialite binary geometry requiring their
   in this case ZERO will be constantly set as M value;
   there is no better way to accommodate for this,
   because OGR ignores M values at all; anyway, this
   is enough to prevent any coord-dims related
   constraint violation forbidding spatialite to
   successfully complete INSERT or UPDATE.

d) ogrsqlitedatasource.cpp
I've added a boolean bSpatialiteReadOnly flag
to the "table-layer" class.
this one is absolutely ignored for FDO RFC16.
but for "true" spatialite this gracefully allows
to enforce "read-only mode" when no libspatialite
binary support is actually available.

For now this one simply is a basic "interim"
solution, so to effectively ensure that at least
test_ogrsf could successfully complete.
Obviously full read/write support for "true"spatialite
will be restored in the next step, when we'll
implement actual support to spatialite DB initialization
and table creation.

e) ogrsqlitedatasource.cpp
I've added a boolean bSpatialiteLoaded flag
and iSpatialiteVersion variable to the
"table-layer" class.

simply a consequence of the above point.
when OGR is connected to any spatialite's table
created using v.2.4.0, libspatialite v.2.4.0
support is absolutely required for already
defined TRIGGERs.
so, when OGR is actually linked to libspatialite
v.2.3.1 we must gracefully degrade to ReadOnly.

f) ogrsqlitelayer.cpp
I've added several hundredth lines of code here.
But they simply implement 3D geometries support
accordingly to the above general design rules.
and I've inserted a short "contributor" disclaimer
into the boilerplate.

scope and testing:
after applying this patch OGR acquires the ability
to fully support SpatiaLite's own 3D geometries.
this applies to OGR RFC16 in both read and write

for "true" spatialite this is fully supported even
when no libspatialite at all is linked to OGR, but
in this case a read-only mode is enforced anyway.
in order to enable write mode for "true" spatialite
actual support of libspatialite is absolutely required,
because several TRIGGERs should be defined into the DB,
and all them critically depend on binary support
based on the library itself.

no libspatialite linked to OGR

FDO RFC16 (loading):
ogr2ogr -f SQLite fdo-wkb.sqlite shp_dir
ogr2ogr -f SQLite fdo-wkt.sqlite shp_dir -lco FORMAT=WKT
ogr2ogr -f SQLite fdo-wkt.sqlite shp_dir -lco FORMAT=SPATIALITE

FDO RFC16 (dumping):
ogr2ogr -f "ESRI Shapefile" shp_dir fdo-wkb.sqlite
ogr2ogr -f "ESRI Shapefile" shp_dir fdo-wkt.sqlite
ogr2ogr -f "ESRI Shapefile" shp_dir fdo-splite.sqlite

FDO RFC16 (testing):
test_ogrsf fdo-wkb.sqlite
test_ogrsf fdo-wkt.sqlite
test_ogrsf fdo-splite.sqlite

SpatiaLite DB (dumping):
ogr2ogr -f "ESRI Shapefile" shp_dir v23.sqlite
ogr2ogr -f "ESRI Shapefile" shp_dir v24-2d.sqlite
ogr2ogr -f "ESRI Shapefile" shp_dir v24-3d.sqlite
ogr2ogr -f "ESRI Shapefile" shp_dir v24-2dcompr.sqlite
ogr2ogr -f "ESRI Shapefile" shp_dir v24-3dcompr.sqlite

SpatiaLite DB (testing / ReadOnly mode):
ogrsf v23.sqlite
ogrsf v24-2d.sqlite
ogrsf v24-3d.sqlite
ogrsf v24-2dcompr.sqlite
ogrsf v24-3dcompr.sqlite

libspatialite v.2.4.0 linked to OGR

same as above

SpatiaLite DB (dumping):
same as above

SpatiaLite DB (testing):
ReadWrite mode enabled.
the library fully supports TRIGGERs for
XY, XYZ, XYM and XYZM geometries

libspatialite v.2.3.1 linked to OGR

same as above

SpatiaLite DB (dumping):
same as above

SpatiaLite DB (testing):
ReadWrite mode enabled if DB was actually
created using v.2.3.1 (or any prior version)
otherwise (DB created using v.2.4.0 or later)
defaulting to ReadOnly mode.

bye Sandro

Attachments (1) (37.2 KB) - added by Even Rouault 6 years ago.
Patch sent by Sandro on 2011-05-24

Download all attachments as: .zip

Change History (8)

Changed 6 years ago by Even Rouault

Attachment: added

Patch sent by Sandro on 2011-05-24

comment:1 Changed 6 years ago by Even Rouault

Summary: Spatialite updatesSpatialite updates : 3D geometries

comment:2 Changed 6 years ago by aperi2007

Cc: aperi2007@… added

comment:3 Changed 6 years ago by Even Rouault

Component: defaultOGR_SF
Milestone: 1.9.0
Resolution: fixed
Status: newclosed

I've applied Sandro's patch with minor adjustements :

  • I've removed the change about the #include "sqlite3.h" for now. Will need to be reexamined later
  • I've added initialization for bHasM, bSpatialiteReadOnly, bSpatialiteLoaded, iSpatialiteVersion in OGRSQLiteLayer::OGRSQLiteLayer() for the sake of completeness
  • I've changed a bit OGRSQLiteDataSource::CreateLayer?(), at line 744, to set the coord_dimension to "XYZ" (instead of 3 previously) for 2.5D geometries. This was needed for the autotest I've added in r22429 to work

r22428 /trunk/gdal/ogr/ogrsf_frmts/sqlite/ (4 files): Spatialite: add support for 3D geometries (patch contributed by Alessandro Furieri, #4092)

r22429 /trunk/autotest/ogr/ Test writing and reading back spatialite geometries (#4092)

comment:4 Changed 6 years ago by capooti

Cc: pcorti@… added

comment:5 Changed 6 years ago by pcav

Cc: pcav added

comment:6 Changed 6 years ago by Even Rouault

r22432 /trunk/autotest/ogr/ Skip 3D geometry tests for Spatialite 2.3.1 (doesn't like XYZ coord_dimension) (#4092)

comment:7 Changed 6 years ago by aperi2007

Hi, I'm try to test this patch. I would like to use the gdal trunk version compiled from szekeres to this url.

In that package there a "spatialite.dll" but I don't think it is the needed library (libspatialite v.2.4.0) to enable the read/write spatialite2.4 db.

To enable the 2.4.0 is sufficient to copy the "libspatialite-2.dll" available in the spatialite site or is needed to recompile all the gdal using that ?

Note: See TracTickets for help on using tickets.