new SQL functions introduced since version 4.2.0
TOC - Table Of Contents
- General purpose
- DMS / DD conversions
- Geometry processing
- Forcing Layer Statistics to be recalculated
- ROWID checks
- building/updating a MetaCatalog
- GEOS / LWGEOM errors and warnings
- GPKG (GeoPackage) support
- How-to handle a MetaCatalog (practical example)
- How-to fully identify Invalid Geometries (practical example)
1 - General purpose
SELECT spatialite_target_cpu(); --- x86_64-redhat-linuxthe select_target_cpu() function will return a text string identifying the intended Target CPU for the current library; the actual value of this string is usually passed by the gcc compiler at build time. Useful in order to quickly check if you are using a 32- or 64-bit library.
2 - DMS / DD conversionsGeographic coordinates based as longitude and latitude angles are usually expressed in one of these two alternative notations:
- DMS (Degree-Minute-Second) e.g. 22° 45' 00"
- DD (Decimal Degrees) e.g. 22.75
SELECT LongitudeFromDMS( a.dms ), LatitudeFromDMS( a.dms ) FROM (SELECT '43°28′24″N 11°52′12″E' AS dms) AS a; ------------------------- 11.870000 43.473333the LongitudeFromDMS(dms-string) and LatitudeFromDMS(dms-string) functions will attempt to parse a DMS string then returning the corresponding DD coordinate.
SELECT LongLatToDMS(11.870000, 43.473333); ---------------------- 43°28′24″N 011°52′12″Ethe LongLatToDMS(longitude, latitude) function will perform the opposite conversion, i.e. will return a DMS expression starting from a couple of DD coordinates.
All these functions will return NULL on invalid input arguments.
3 - Geometry processing
SELECT ASText( MakePolygon( GeomFromText( 'LINESTRING(0 0, 100 0, 100 100, 0 100, 0 0)' ) ) ); ---------------- POLYGON((0 0, 100 0, 100 100, 0 100, 0 0)) SELECT ASText( MakePolygon( GeomFromText( 'LINESTRING(0 0, 100 0, 100 100, 0 100, 0 0)' ), GeomFromText( 'LINESTRING(50 50, 60 50, 60 60, 50 60, 50 50)' ) ) ); ---------------- POLYGON((0 0, 100 0, 100 100, 0 100, 0 0), (50 50, 60 50, 60 60, 50 60, 50 50)) SELECT ST_ASText( ST_MakePolygon( ST_GeomFromText( 'LINESTRING(0 0, 100 0, 100 100, 0 100, 0 0)' ), ST_GeomFromText( 'MULTILINESTRING((50 50, 60 50, 60 60, 50 60, 50 50), (10 10, 40 30, 30 40, 10 10))' ) ) ); ---------------- POLYGON((0 0, 100 0, 100 100, 0 100, 0 0), (50 50, 60 50, 60 60, 50 60, 50 50), (10 10, 40 30, 30 40, 10 10))the MakePolygon() aka ST_MakePolygon() is kind of lightweight/simplified ST_BuildArea(), and will attempt to create a Polygon starting from its Ring(s) represented as Linestring(s).
Note well: all Linestring(s) are always expected to be correctly closed, i.e. the first and last vertices must exactly coincide.
Absolutely no topological validation will be performed, so this function is expected to be really fast. Anyway it could eventually return an invalid Polygon.
- the first argument is always expected to be a Linestring, and will be assumed to correspond to the Polygon's Exterior Ring.
- the second optional argument if eventually present could be both a Linestring or a MultiLinestring, and will be interpreted ad a collection of Interior Ring(s).
- NULL will be returned on invalid arguments.
SELECT ST_AsText( ST_Node( ST_GeomFromText( 'LINESTRINGZ(0 0 0, 10 10 10, 0 10 5, 10 0 3)' ) ) ); ---------------- MULTILINESTRING Z((0 0 0, 5 5 4.5), (5 5 4.5, 10 10 10, 0 10 5, 5 5 4.5), (5 5 4.5, 10 0 3))the ST_Node() function will attempt to fully node a set of linestrings using the least possible number of nodes while preserving all of the input ones. NULL will be returned if the input Geometry isn't a set of Linestrings or if any other error occurs.
Please note: this function strictly requires the LWGEOM support to be configured at build time.
This functions is assumed to be an exact equivalent of the same function available on PostGIS.
SELECT ST_AsText( ST_SelfIntersections( ST_GeomFromText( 'LINESTRING(0.5 1, 1 2, 2 0, 3 2, 3.5 1, 0.5 1)' ) ) ); ----------------- MULTIPOINT(2.5 1, 1.5 1)the SelfIntersections() aka ST_SelfIntersections() function will return a MultiPoint Geometry representing any self-intersection found within the input geometry always expected to be of the Linestring or MultiLinestring type. NULL will be returned for invalid arguments, or when no self-intersections were found.
Please note: this function strictly requires the LWGEOM support to be configured at build time.
4 - Forcing Layer Statistics to be recalculatedLayer Statistics are supported starting since version 4.0.0; please see the corresponding wiki page.
Very short recall:
- the UpdateLayerStatistics() function is intended to calculate/update Layer Statistics.
- several Triggers and TimeStamps are purposely defined, thus allowing to immediately skip any update request about data not modified since the latest update.
- anyway, as it emerged by practical on-the-field experience, there are few events effectively modifying table contents but conceptually impossible to be intercepted by Trigger actions.
And in this case a paradoxical condition will arise: the TimeStamps will report no change at all thus forbidding any attempt to update the Statistics; but the Statistics will be anyway no longer valid.
Such self-contradictory condition usually arises immediately after executing an SQL statement like e.g. ALTER TABLE ... ADD COLUMN ...
SELECT InvalidateLayerStatistics(); --------- 1 SELECT InvalidateLayerStatistics('mytable'); --------- 1 SELECT InvalidateLayerStatistics('mytable', 'geom'); --------- 1This new InvalidateLayerStatistics() allows to immediately mark Layer Statistics as no longer valid. So the next call to UpdateLayerStatistics() will then correctly recalculate yet again Layer Statistics starting from scratch.
You can selectively invalidate all Statistics, or just for a single Table aka Layer.
5 - ROWID checksThe SpatiaLite's own Spatial Index implementation critically depends on ROWID values in order to keep correctly synchronized the main Geometry table and the corresponding R*Tree table.
Anyway there several potentially harmful conditions related to ROWIDs that could cause severe damages to the overall DB consistency; these SQL functions could effectively help to prevent and/or identify many apparently inexplicable troubles.
CREATE TABLE alpha ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, value INTEGER NOT NULL); SELECT CheckShadowedRowid('alpha'); -------------- 0 CREATE TABLE beta ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, rowid INTEGER NOT NULL); SELECT CheckShadowedRowid('beta'); --------------- 1The first table (alpha) has nothing wrong, and will effectively support ROWID values uniquely identifying every row.
The second table (beta) explicitly declares a column named rowid; this one is a legitimate SQLite option, but in this case the ROWID values will not necessarily uniquely identify every row, because they will simply correspond to the actual values set for that column, not to physical row identifiers. Any column explicitly named rowid will shadow the expected ROWIDs, and thus will probably cause a broken / malfunctioning Spatial Index.
The CheckShadowedRowid(tablename) function is intended to identify such potentially dangerous conditions.
CREATE TABLE gamma ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, value INTEGER NOT NULL); SELECT CheckWithoutRowid('gamma'); ----------------- 0 CREATE TABLE delta ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, value INTEGER NOT NULL) WITHOUT ROWID; SELECT CheckWithoutRowid('delta'); ----------------- 1Starting since version 3.8.2 SQLite introduced the WITHOUT ROWID clause; a table created by declaring such option will never have a ROWID, and will then be absolutely not compatible with Spatial Index requirements. The CheckWithoutRowid(tablename) function is intended to identify such harmful conditions.
Please note well: starting since version 4.2 all SQL functions handling Geometry columns and Spatial Indices has been updated so to correctly recognize both these dangerous issues related with ROWIDs.
Anyway this doesn't excludes that some already existing DB created using any previous version could be possibly affected by such ROWID-related issues.
If you simply suspect something like this, a simple query as the following one will definitely clarify any possible doubt:
SELECT f_table_name, CheckShadowedRowid(f_table_name), CheckWithoutRowid(f_table_name) FROM geometry_columns;
6 - building/updating a MetaCatalogSometimes it could be useful creating a MetaCatalog, i.e. a Table automatically reporting all Tables and Columns with their individual attributes.
SQLite supports many useful PRAGMAs, but it's never possible obtaining a synoptic and comprehensive overview.
SELECT CreateMetaCatalogTables(1); ------------- 1By executing the CreateMetaCatalogTables() function this task will be performed automatically. A table named splite_metacatalog will be created, and its content will look something like this:
table_name column_name type not_null primary_key foreign_key unique_value ----------------------------------------------------------------------------- ..... geonames feature_class TEXT 1 0 0 0 geonames feature_code TEXT 1 0 0 0 geonames geom POINT 0 0 0 0 geonames geonameid INTEGER 1 1 0 0 geonames latitude DOUBLE 1 0 0 0 geonames longitude DOUBLE 1 0 0 0 .....That's not all: a further splite_metacatalog_statistics table will be created as well, but will be initially empty.
SELECT UpdateMetaCatalogStatistics(1, 'geonames', 'feature_code');After invoking the UpdateMetaCatalogStatistics() function the splite_metacatalog_statistics table will contain the frequency count for each individual value found in the required table/column.
table_name column_name value count --------------------------------------------- geonames feature_code PPL 71242 geonames feature_code PPLA 3478 geonames feature_code PPLA2 13040 geonames feature_code PPLA3 26495 geonames feature_code PPLA4 26496 geonames feature_code PPLC 241 geonames feature_code PPLCH 1 geonames feature_code PPLF 5 geonames feature_code PPLG 12 geonames feature_code PPLH 3 geonames feature_code PPLL 245 geonames feature_code PPLQ 18 geonames feature_code PPLR 6 geonames feature_code PPLS 14 geonames feature_code PPLW 1 geonames feature_code PPLX 1161 geonames feature_code STLMT 1Please note: attempting to execute UpdateMetaCatalogStatistics() on behalf of any table/column containing strongly dispersed values isn't a good option, because it will create lots of rows without adding any real information.
This function is mainly intended as a tool allowing to quickly analyze strongly concentrated value distributions (typically: columns based on code values).
You could eventually use UpdateMetaCatalogStatistics() in a more sophisticated and advanced way: if you are anyway interested to discover more about all this you can read this tutorial.
Short conclusion: using the MetaCatalog presumably will never be an interesting activity for many ordinary users; if you are mainly interested in consulting a DB coming from third-party sources the MetaCatalog will certainly be of little or no interest.
But for any power user mainly interested in producing datasets to be externally distributed the MetaCatalog will surely be a powerful and highly useful tool supporting many validation activities.
7 - GEOS / LWGEOM errors and warningsBoth the GEOS and the LWGEOM libraries usually emits very detailed messages each time that some abnormal condition is encountered. Anyway all these diagnostic messages will be easily pass unnoticed simply because they'll be dumped on the standard output; so in the case of any GUI application they'll probably be completely invisible.
- each single DB connection keeps trace of the most recent message received from GEOS or LWGEOM
- the standard assumption is to get NULL messages; this simply means that the most recent operation being performed hasn't encountered any problem.
- a warning message simply warns the user about some abnormal (but substantially harmless) condition.
- an error message instead is a signal that some severe failure occurred.
- error and warning messages are intrinsically transient by their nature; each time a new operation will be performed the previous messages will be reset and thus irremediably lost. Quickly intercepting all messages is always the best possible option.
All the following functions now allow to directly query such messages at SQL level.
SELECT GEOS_GetLastWarningMsg(); SELECT GEOS_GetLastErrorMsg(); SELECT GEOS_GetLastAuxErrorMsg(); SELECT GEOS_GetCriticalPointFromMsg(); SELECT LWGEOM_GetLastWarningMsg(); SELECT LWGEOM_GetLastErrorMsg();
- the GEOS_GetLastWarningMsg() and GEOS_GetLastErrorMsg() are intended to intercept any GEOS-related message.
- the LWGEOM_GetLastWarningMsg() and LWGEOM_GetLastErrorMsg() are intended to intercept any LWGEOM-related message.
- the GEOS_GetLastAuxErrorMsg() function will intercept any message not directly coming from GEOS, but produced by SpatiaLite itself in order to skip any dangerous operation potentially causing GEOS to crash.
This is the typical case of unclosed rings or of badly degenerated geometries (e.g. a Linestring declaring just one single point).
- all these functions will return:
- NULL if there is nothing exceptional to report (the standard expectation).
- some appropriate diagnostic message (text string) if any abnormal event was eventually encountered.
- the GEOS_GetCriticalPointFromMsg() function is some way exceptional because it will eventually return a POINT Geometry identifying some self-intersection.
SELECT ST_IsValidReason(ST_GeomFromText( 'POLYGON((0 0, 10 0, 0 10, 10 10, 0 0))')); ----------- Self-intersection[5 5] SELECT ST_AsText(ST_IsValidDetail(ST_GeomFromText( 'POLYGON((0 0, 10 0, 0 10, 10 10, 0 0))'))); ----------- POINT(5 5)Two further strictly related functions are supported, mainly in order to mimic what's available on PostGIS:
- the ST_IsValidReason() is some way a complement to ST_IsValid(); a generic Geometry will be checked for validity, then returning a clear text message and eventually reporting the invalidity cause.
Please note: this functions is clearly misnamed. A better name should had surely be ST_IsInvalidReason(); anyway this function has its main rationale in ensuring a close PostGIS compatibility, so the original name has been preserved.
- the ST_IsValidDetail() function is only partially equivalent to the corresponding function implemented by PostGIS due to architecture constraints:
- a Geometry (namely: a POINT) will be returned if the invalidity cause is in some self-intersection.
- NULL will be returned in any other case.
8 - GPKG (GeoPackage) supportGeneral support:
- HasGeoPackage(): will return TRUE if the library was built by enabling the GeoPackage support.
- CheckGeoPackageMetaData(): will recognize if the currently connected DB-file presents a GPKG layout.
- gpkgCreateBaseTables(): will initialize all metadata tables required by the GPKG layout.
- gpkgInsertEpsgSRID(): allows to populate the gpkg_spatial_ref_sys table.
Raster (tiles) support:
- gpkgCreateTilesTable(), gpkgCreateTilesZoomLevel(), gpkgAddTileTriggers(), gpkgGetNormalZoom(), gpkgGetNormalRow(), gpkgGetImageType()
Vector (Geometry) support:
- gpkgAddGeometryColumn(), gpkgAddGeometryTriggers(), gpkgAddSpatialIndex(), gpkgMakePoint(), gpkgMakePointZ(), gpkgMakePointM(), gpkgMakePointZM(), GPKG_IsAssignable(), IsValidGPB(), AsGPB(), GeomFromGPB(), CastAutomagic()
- The most interesting if this group surely are:
- IsValidGPB(): will recognize if a BLOB does actually corresponds to a valid GPKG binary geometry.
- AsGPB(): will transform a SpatiaLite's own binary Geometry into a GPKG binary Geometry.
- GeomFromGPB(): will transform a GPKG own binary Geometry into a SpatiaLite's own binary Geometry.
- CastAutomagic(): will indifferently accept both GPKG and SpatiaLite BLOB Geometries, then returning anyway a SpatiaLite's own BLOB Geometry.
Please note: the SpatiaLite's support to GPKG covers all Triggers defined in Annexes L/M/N:
- gpkgAddGeometryTriggers(): will add all safeguard Triggers defined in Annexes M/N (GeometryType and SRID validation).
- gpkgAddSpatialIndex() will create the corresponding rtree_<t>_<c> Spatial Index, and will add all related triggers defined in Annex L.
- all GPKG triggers necessarily require some kind of library support
for the following SQL functions:
- ST_MinX(), ST_MinY(), ST_MaxX(), ST_MaxY() (BBOX / MBR)
- ST_GeometryType(), GPKG_IsAssignable()
- ST_Is3D(), ST_IsMeasured(), ST_MinZ(), ST_MaxZ(), ST_MinM(), ST_MaxM() (3D support)
- SpatiaLite is intended to be capable to support this kind of minimalistic SQL core even in the case of GPKG binary geometries.
New VirtualGPKG Virtual Tables:
- any GPKG table containing a Geometry column can now be wrapped by a corresponding VirtualGPKG table.
After this you simply have to target your SQL queries on behalf of the VirtualGPKG table: the VirtualGPKG logic will silently serialize/deserialize BLOB Geometries as appropriate, and a VirtualGPKG table could be then used exactly in the same way as if it was a native SpatiaLite table.
- VirtualGPKG supports unconstrained SELECT, INSERT, UPDATE and DELETE operations; so, thanks to VirtualVPKG intermediation, you are now allowed to perform any possible kind of Spatial Analysis and Spatial Data Processing even when using a GPKG target.
SQL auxiliary functions supporting VirtualGPKG:
- AutoGPKGStart(): this function will automatically scan the gpkg_geometry_columns metadata table, then automatically creating a VirtualGPKG wrapper for each GPKG table.
- AutoGPKGStop(): this function will automatically scan the gpkg_geometry_columns metadata table, then automatically destroying any VirtualGPKG wrapper eventually found.
- So you can invoke AutoGPKGStart() immediately after connecting to a GPKG datasource, then invoking AutoGPKGStop() immediately before quitting.
Following this approach the GPKG db-file will never permanently incorporate any SpatiaLite specific item, but you'll be anyway able to effectively target a GPKG datasource using SpatiaLite in the most painless way.