D 2014-08-27T12:32:21.716 L switching-to-4.0 P b695ef46beaea19ad5cedc977a4e22f5236dd2e1 U sandro W 22053 Back to main SpatiaLite Wiki page

Relevant changes introduced in version 4.0.0

SpatiaLite Version 4.0.0 introduces several relevant changes; many of these may potentially pose severe cross-version compatibility issues.
Accordingly to the above premise, a good comprehension of any related detail will surely allow you to successfully master and resolve any transition issue.

You are warmly encouraged to carefully read this short note.

Changes affecting the "spatial_ref_sys" Metadata table

old layout
Version 3.1.0
and any earlier		 new layout
Version 4.0.0
and any subsequent		 Notes
srs_wkt
TEXT		srtext
TEXT
  • this column has simply changed its name.
  • the intended payload always is the same (i.e. WKT spatial reference system definition).
  • changing the column name was strictly requested by the most recent international standards.
  • SpatiaLite itself makes very little use of these column.
    currently the unique scope is supporting *.PRJ files generation when exporting some Shapefile.

Please note well: there is a further relevant change affecting "spatial_ref_sys" which is absolutely worth to be noticed.
Now any SpatiaLite database always supports the two following SRS implicit definitions:

srid auth_name auth_srid ref_sys_name proj4text srtext
-1NONE -1Undefined - CartesianUndefined
0NONE 0Undefined - Geographic Long/Lat Undefined


Please note: now the default SRID isn't any longer assumed to be -1 as in any earlier version.
Now the default SRID is always assumed to be 0, accordingly to the most recent international standard specifications.

Changes affecting the "geometry_columns" Metadata table

old layout
Version 3.1.0
and any earlier		 new layout
Version 4.0.0
and any subsequent		 Notes
type
TEXT		geometry_type
INTEGER
  • this column hasn't simply changed its name.
    the data-type is changed as well, and the whole semantic is completely revolutionized.
    Please see below for more details.
  • this change was strictly requested by the most recent international standards.
coord_dimension
TEXT		coord_dimension
INTEGER
  • this change too was dictated by standard compliance reasons.
    Please note well: this value represents now an unnecessary redundancy, and will be absolutely ignored by SpatiaLite itself. Anyway, the expected value will be exposed accordingly to standard specs.

old layout

new layout

very important notice

Please note well: now both f_table_name and f_geometry_column columns (and any similar column used for views and virtual tables metadata tables) are always assumed to be expressed as lowercase values. And they are expected not to contain any single or double quote character.
A full set of Triggers enforces such constraints: this is obviously intended as a mean effectively guaranteeing a strong relational consistency.
When using the SpatiaLite's own C APIs (or SQL functions) this facet will be transparently handled, but this could be a strong issue for third party components attempting to directly access the metadata tables by skipping the C APIs or the SQL supporting functions.

In this case adopting the following approach as a general rule will surely be a good solution:
INSERT INTO geometry_columns (f_table_name, f_geometry_column, ...)
VALUES (Lower('MyTable'), Lower('MyGeom'), ..);
SELECT *
FROM geomety_columns
WHERE f_table_name = Lower('MyTable');

Changes affecting the "views_geometry_columns" Metadata table

old layout
Version 3.1.0
and any earlier		 new layout
Version 4.0.0
and any subsequent		 Notes
n.a.read_only
INTEGER
  • new introduction; now SpatiaLite supports Updatable Views, so we cannot assume any longer that all views are necessarily of the read-only type.

Changes affecting the "virts_geometry_columns" Metadata table

old layout
Version 3.1.0
and any earlier		 new layout
Version 4.0.0
and any subsequent		 Notes
type
TEXT		geometry_type
INTEGER
  • exactly corresponding to the same column in geometry_columns.
n.a.coord_dimension
INTEGER
  • new introduction; mainly justified in order to be fully symmetric with geometry_columns.

Metadata and statistic infos available in 4.0.0

Version 4.0.0 now supports a richer set of metadata and statistics information supporting Spatial Tables.
You can learn more about this topic by reading this Wiki page

A closely related topic (although not being metadata or statics infos at all, strictly speaking) is the brand new SQL Log facility: you can learn more on this by reading this Wiki page

Changes affecting the VirtualSpatialIndex interface

Version 4.0.0 is now able to directly access a Spatial Index supporting a Spatial View. Imagine a View defined as follows:

CREATE TABLE groups (
    group_id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_name TEXT NOT NULL);
CREATE TABLE items (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id INTEGER NOT NULL,
    item_name TEXT NOT NULL,
    CONSTRAINT fk_item FOREIGN KEY (group_id) REFERENCES groups (group_id));
SELECT AddGeometryColumn('items', 'geom', 4326, 'POINT', 'XY');
SELECT CreateSpatialIndex('items', 'geom');
CREATE VIEW items_view AS
SELECT i.ROWID AS ROWID, i.item_id AS item_id, i.item_name AS item_name, i.group_id AS group_id, g.group_name AS group_name, i.geom AS Geometry
FROM items AS i
JOIN groups AS g ON (g.group_id = i.group_id);
INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES ('items_view', 'Geometry', 'ROWID', 'items', 'geom', 1);

When using version 4.0.0 both the following Spatial queries are exactly equivalent, and take full profit from the Spatial Index:

indirectly accessing the Table's Spatial Index
(as already supporting on earlier versions)		 SELECT *
FROM items_view
WHERE ROWID IN (
    SELECT ROWID
    FROM SpatialIndex
    WHERE f_table_name = 'items' AND f_geometry_column = 'geom' AND
        search_frame = BuildMbr(11, 42, 12, 43, 4326) );
directly accessing the View's Spatial Index
(new feature introduced in 4.0.0)		 SELECT *
FROM items_view
WHERE ROWID IN (
    SELECT ROWID
    FROM SpatialIndex
    WHERE f_table_name = 'items_view' AND f_geometry_column = 'Geometry' AND
        search_frame = BuildMbr(11, 42, 12, 43, 4326) );

Changes affecting Spatial SQL Functions


Any other SQL function has the same identical signature as before, and will automagically detect the actual format of Metadata tables, thus consequently applying the appropriate actions:
Please note: any Spatial SQL function creating a new Geometry (e.g. ST_GeomFromText() and friends) will now assume SRID = 0 if no SRID value was explicitly specified.

Important notice

There is a small change affecting both AddGeometryColumn and RecoverGeometryColumn; now the coord_dimension argument is considered an optional one. Anyway the old syntax supported by previos versions is still considered to be fully valid.
The following table enumerates all the legal declarations accepted by version 4.0.0 and assumed to be exactly equivalent (only the POINT case is shown, but you can easily expand this schema to any other Geometry Type).
Please note: the new syntax introduced by version 4.0.0 is represented as bold. The old syntax already supported by earlier versions is in italic.

POINT2D, XYAddGeometryColumn('tbl', 'geom', 4326, 'POINT', 2);
AddGeometryColumn('tbl', 'geom', 4326, 'POINT', 'XY')
AddGeometryColumn('tbl', 'geom', 4326, 'POINT');
3D, XYZAddGeometryColumn('tbl', 'geom', 4326, 'POINT', 3);
AddGeometryColumn('tbl', 'geom', 4326, 'POINT', 'XYZ')
AddGeometryColumn('tbl', 'geom', 4326, 'POINTZ')
AddGeometryColumn('tbl', 'geom', 4326, 'POINTZ', 3);
AddGeometryColumn('tbl', 'geom', 4326, 'POINTZ', 'XYZ');
2D + measure, XYMAddGeometryColumn('tbl', 'geom', 4326, 'POINT', 'XYM');
AddGeometryColumn('tbl', 'geom', 4326, 'POINTM');
AddGeometryColumn('tbl', 'geom', 4326, 'POINTM', 'XYM');
3D + measure, XYZMAddGeometryColumn('tbl', 'geom', 4326, 'POINT', 4);
AddGeometryColumn('tbl', 'geom', 4326, 'POINT', 'XYZM')
AddGeometryColumn('tbl', 'geom', 4326, 'POINTZM');
AddGeometryColumn('tbl', 'geom', 4326, 'POINTZM', 4);
AddGeometryColumn('tbl', 'geom', 4326, 'POINTZM', 'XYZM');

Conclusion

When using any appropriate Spatial SQL function the transition toward version 4.0.0 will be absolutely smooth and painless:

The spatialite_convert CLI tool

In order to ensure as far as possible a smooth and painless transition between different versions, a further CLI tool is now supported.

Syntax: spatialite_convert -d db_pathname -v 4

Executing this command will perform the following actions:
spatialite_convert -d db_pathname -v 3
spatialite_convert -d db_pathname -v 2

Conversion will work the opposite way as well:

Changes affecting GUI and CLI tools

You can learn more about this topic by reading this Wiki page

New Spatial SQL functions supporting tessellations

You can learn more about this topic by reading this Wiki page

New Spatial SQL functions based on liblwgeom

liblwgeom is an open source library developed in C language, and released under the GPLv2 license terms. This library was initially intended simply as the generic geometry handling section of PostGIS.
Anyway, starting since PostGIS 2.0, liblwgeom is now installed as a self-standing library. In other words: PostGIS depends on liblwgeom, but liblwgeom doesn't depends at all on PostGIS.
So any other open source software can now freely re-use many interesting methods implemented in liblwgeom.

You can learn more about this topic by reading this Wiki page

Connecting SpatiaLite to the most exoteric datasources using VirtualOGR

You can learn more about this topic by reading this Wiki page

Comparative Benchmark: the Clipping Contest

You can learn more about this topic by reading this Wiki page


Back to main SpatiaLite Wiki page Z 70181a5fc02e16e38501ba9954d7aa62