SpatiaLite: 4.1.0 Changes

New SQL functions introduced in 4.1.0

Table of contents:

SQL functions supporting data-type casting
SQL functions supporting UUID and MD5
Spatial SQL functions supporting Geometries
- SQL functions supporting Circles and Ellipses
SQL functions supporting unsafe direct Import/Export

cast SQL functions - type casting

SQLite offers just a very basic type checking; this is sometimes useful, but could be really annoying very often.

SELECT '1', TypeOf( '1' ), CAST ( '1' AS INTEGER ), TypeOf( CAST ( '1' AS INTEGER ) );

'1'	TypeOf( '1' )	CAST ( '1' AS INTEGER )	TypeOf( CAST ( '1' AS INTEGER ) )
1	text	1	integer

The SQL CAST operator already supports the possibility to explicitly set a data-type; now starting since 4.1.0 SpatiaLite supports few SQL functions performing the same task.
Please note: some subtle differences exists here and there; please carefully read this documentation page.

SELECT CastToInteger( 1 ), CastToInteger( 1.4 ), CastToInteger( 1.6 ), 
    CastToInteger( '123' ), CastToInteger( 'alpha' ), CastToInteger ( zeroblob(4) );

CastToInteger( 1 )	CastToInteger( 1.4 )	CastToInteger( 1.6 )	CastToInteger( '123' )	CastToInteger( 'alpha' )	CastToInteger ( zeroblob(4) )
1	1	2	123	NULL	NULL

The CastToInteger() SQL function will attempt to return a value of the Integer data-type, or NULL if no conversion is possible.

an Integer input argument will be returned absolutely unaffected.
a Double input argument will return the nearest Integer value (after applying 4/5 rounding).
a Text input argument will return the corresponding Integer value only if the text string represents a valid number (even a decimal one): if the text string cannot be converted to a number NULL will be returned.
any BLOB input argument will return a NULL value.
any NULL input argument will return a NULL value.

SELECT CastToDouble( 1 ), CastToDouble( 1.23 ), CastToDouble( '123.45' ),
    CastToDouble( 'alpha' ), CastToDouble ( zeroblob(4) );

CastToDouble( 1 )	CastToDouble( 1.23 )	CastToDouble( '123.45' )	CastToDouble( 'alpha' )	CastToDouble ( zeroblob(4) )
1.000000	1.230000	123.45000	NULL	NULL

The CastToDouble() SQL function will attempt to return a value of the Double data-type, or NULL if no conversion is possible.

an Integer input argument will return the corresponding Double value.
a Double input argument will be returned absolutely unaffected.
a Text input argument will return the corresponding Double value only if the text string represents a valid number: if the text string cannot be converted to a number NULL will be returned.
any BLOB input argument will return a NULL value.
any NULL input argument will return a NULL value.

SELECT CastToText( 1 ), CastToText( 1.5 ), CastToText( 'alpha' ), CastToText ( zeroblob(4) );

CastToText( 1 )	CastToText( 1.5 )	CastToText( 'alpha' )	CastToText ( zeroblob(4) )
1	1.5	alpha	NULL

The CastToText() SQL function will attempt to return a value of the Text data-type, or NULL if no conversion is possible.

an Integer input argument will return the corresponding Text value.
a Double input argument will be return the corresponding Text value; no significant decimal digit will be suppressed.
a Text input argument will return the corresponding Double value.
any BLOB input argument will return a NULL value.
any NULL input argument will return a NULL value.

SELECT CastToText( 1, 4 ), CastToText( 1234, 2 ), CastToText( 1, 8 ), CastToText( 1.5, 4 );

CastToText( 1, 4 )	CastToText( 1234, 2 )	CastToText( 1, 8 )	CastToText( 1.5, 4 )
0001	1234	00000001	0001.5

An overloaded version of CastToText() is supported as well, but only when the input argument is of the Integer or Double data-type: in this case a second Integer argument could be optionally specified, and will be interpreted ad zero-padding-length.
i.e. the returned Text string in this case will have an integer part at least of the specified length, and eventually padded by inserting not significant leading ZEROes.

SELECT CastToBlob( 1 ), CastToBlob( 1.5 ), Hex( CastToBlob( 'alpha' ) ), Hex ( CastToBlob ( zeroblob(4) ) );

CastToBlob( 1 )	CastToBlob( 1.5 )	Hex( CastToBlob( 'alpha' ) )	Hex( CastToText ( zeroblob(4) ) )
NULL	NULL	616C706861	00000000

The CastToBlob() SQL function will attempt to return a value of the BLOB data-type, or NULL if no conversion is possible.

an Integer input argument will return a NULL value.
a Double input argument will return a NULL value.
a Text input argument will return the corresponding BLOB value.
any BLOB input argument will return the corresponding BLOB value.
any NULL input argument will return a NULL value.

SELECT Hex( CastToBlob( '0123456789aBcDeFfEdCbA9876543210', 1 ) );
-----
0123456789ABCDEFFEDCBA9876543210

SELECT CastToBlob( '12f', 1 );
-----
NULL

SELECT CastToBlob( '12HF', 1 );
-----
NULL

A second overloaded version of CastToBlob() is supported as well; by specifying a further hex_input boolean argument set as TRUE the input string will be assumed to be Hexadecimally encoded. In this case NULL will be returned if the input string doesn't corresponds to a valid Hexadecimal notation.

SELECT ForceAsNull( 'a', 'b' ), ForceAsNull( 'abcd', 'abcd' ), ForceAsNull( 1, 1), ForceAsNull( 1.1, 1 );

ForceAsNull( 'a', 'b' )	ForceAsNull( 'abcd', 'abcd' )	ForceAsNull( 1, 1)	ForceAsNull( 1.1, -9999 )
a	NULL	NULL	1.1

The ForceAsNull() SQL function will evaluate two arbitrary arguments.
If they are equal and exactly of the same data-type NULL will be returned; otherwise the first argument value will be returned.
Useful e.g. while processing data imported from any Shapefile or DBF file (such formats doesn't support any real NULL value, often being replaced by some conventional value e.g. 0 or -9999 and alike).

Generic SQL functions - UUID generator

An Universally unique identifier (aka UUID) simply is a 128 bit Integer; accordingly to theory, the probability that two randomly generated UUIDs could assume the same value is near to zero for many practical purposes. And consequently UUIDs are widely used as universally unique IDs.
Usually UUIDs are represented in their canonical form, i.e. as a sequence of 32 hexadecimal digits separated into five distinct block, accordingly to a 8-4-4-4-12 schema.

SELECT CreateUUID();
--------
fff5faf1-dcc0-4391-8054-6e7de75d85b1

The CreateUUID() SQL function is built on the top the High Quality Pseudo-Random Number Generator internally implemented by SQLite.
The returned UUID is conformant to Version 4 (random) (i.e. the first digit into the third block will always be 4 and the first digit into the fourth block will always be 8).

MD5 checksum

The MD5 algorithm is well known and widely used in order to compute hash / checksum digests, and it corresponds to a formally defined specification d (RFC1321).

Any MD5 checksum always corresponds to a 128-bit number, and is usually printed in its hexadecimal representation.
Accordingly to theory, it's very improbable that two different messages could collide generating the same identical checksum; and even slightly different messages (just differing for few bits) are expected to produce strikingly different checksums.
So the MD5 checksum can be safely assumed to represent a digital signature uniquely identifying any generic binary object.

SELECT MD5Checksum(GeomFromText('LINESTRING(11 41, 12 42)', 4326));
------
701f2c231a5fb489be1c784e241ea958

The MD5Checksum() SQL function will return the checksum corresponding to any generic BLOB (this obviously including Geometries) or TEXT string.
The underlying implementation is directly based on the code developed by Alexander Peslyak and released on the Public Domain.

SELECT MD5TotalChecksum(geometry)
FROM com2011;
------
8eb002658990022a6f0c1cb24a60fbf6

The MD5TotalChecksum() SQL function will perform exactly the same identical task but is implemented as an aggregate function.

Spatial SQL functions

Few more Spatial SQL functions are now supported.

SELECT ST_AsText( ST_AddPoint( 
  ST_GeomFromText( 'LINESTRING( 0 0, 1 0 )' ), 
  ST_GeomFromText( 'POINT( 1 1 )' ) ) );
--------
LINESTRING(0 0, 1 0, 1 1)

The ST_AddPoint() SQL function adds a further Point/Vertex at the end of a Linestring; the first argument is always expected to be of the Linestring type, the second one must be a Point.

SELECT ST_AsText( ST_AddPoint( 
  ST_GeomFromText( 'LINESTRING( 1 0, 1 1 )' ), 
  ST_GeomFromText( 'POINT( 0 0 )' ), 0 ) );
---------
LINESTRING(0 0, 1 0, 1 1)

If an optional third argument (of the Integer type) is passed to ST_AddPoint() it's intended to specify the index of the new Vertex (first Vertex has index 0, second Vertex has index 1 and so on).
Referencing a not existing index is an error, and will return NULL.

SELECT ST_AsText( ST_AddPoint( 
  ST_GeomFromText( 'LINESTRING( 0 0, 1 0 )' ), 
  ST_GeomFromText( 'POINT( 1 1 )' ), -1 ) );
--------
LINESTRING(0 0, 1 0, 1 1)

Passing a negative index is a supported option, and simply corresponds to the default behaviour (i.e. appending the Point to the end of the Linestring).

SELECT ST_AsText( ST_SetPoint( 
  ST_GeomFromText( 'LINESTRING( 0 0, 1 0, 1 1 )' ), 
  1, ST_GeomFromText( 'POINT( 2 1 )' ) ) );
---------
LINESTRING(0 0, 2 1, 1 1)

The ST_SetPoint() SQL function can replace a Point/Vertex from within a Linestring; the first argument is always expected to be of the Linestring type, the second is an Integer specifying the index of the affected Vertex and the third one must be a Point.
Referencing a not existing index is an error, and will return NULL.

SELECT ST_AsText( ST_RemovePoint( 
  ST_GeomFromText( 'LINESTRING( 0 0, 1 0, 1 1 )' ), 1 ) );
---------
LINESTRING(0 0, 1 1)

The ST_RemovePoint() SQL function can remove a Point/Vertex from within a Linestring; the first argument is always expected to be of the Linestring type, the second is an Integer specifying the index of the affected Vertex.
Referencing a not existing index is an error, and will return NULL.

SELECT ST_AsText( ST_Point(10, 20) );
----------
POINT(10 10)

The ST_Point() SQL function simply is yet another alias-name corresponding to MakePoint(); this further alias has been introduced so to support a strict PostGIS conformance.
Please note: differently from MakePoint(), ST_Point() don't supports the optional SRID argument.

SELECT ST_AsText( MakeLine( ST_GeomFromText( 'MULTIPOINT(0 0, 1 0, 1 1)' ) ) , 1 );
----------
LINESTRING(0 0, 1 0, 1 1)

SELECT ST_AsText( MakeLine( ST_GeomFromText( 'MULTIPOINT(0 0, 1 0, 1 1)' ) ) , 0 );
----------
LINESTRING(1 1, 1 0, 0 0)

Not really a brand new SQL function; more simply a further overloaded flavor of the already existing MakeLine().
Now the input Geometry (first argument) could be of the MULTIPOINT type and the second argument if of the Boolean type; if the second argument corresponds to TRUE the returned LINESTRING will be oriented accordingly to the Point-sequence as specified by the MultiPoint, otherwise reverse order will be assumed, thus returning a Linestring of opposite orientation.

SELECT ST_Area( ST_GeomFromText( 'POLYGON((20 20, 21 20, 21 21, 20 21, 20 20))', 4326 ), 1 );
----------
11548555926.647736


SELECT ST_Area( ST_GeomFromText( 'POLYGON((20 20, 21 20, 21 21, 20 21, 20 20))', 4326 ), 0 );
----------
11581377623.114189

Once again, simply a further extension of the already existing ST_Area().
Now if the input Geometry adopts geographic coordinates (i.e. based on longitude and latitude angles), a second argument of the Boolean data-type could be optionally specified.
In this case the returned Area will be measured in meters.

if the second arg is TRUE the Area will be measured on the Ellipsoid (more accurate, but slower).
if the second arg is FALSE the Area will be measure on the Sphere (faster, but less accurate).
NULL will be returned on invalid args.
Please note: this specific flavor of ST_Area() necessarily requires the LWGEOM support to be activated at build time.

SELECT ST_AsText( ST_Project( MakePoint( 11.52, 42.38, 4326 ), 50000, Radians( 45 ) ) );
----------
POINT(11.951479 42.697467)

The ST_Project() SQL function will return the destination Point on the ellipsoid corresponding to a start_point, a distance and a bearing (aka azimuth aka direction aka heading).

the start_point is expected to adopt geographic coordinates (i.e. based on longitude and latitude angles).
the distance is always expected to be expressed in meters.
the azimuth angle is expected to be expressed in radians:
- North corresponds to 0 degrees (0 radians).
- East corresponds to 90 degrees (PI/2 radians).
- South corresponds to 180 degrees (PI radians).
- West corresponds to 270 degrees (3PI/2 radians).
- i.e. it has the same identical meaning as in ST_Azimuth().
this function exactly corresponds to the one supported by PostGIS.
Please note: ST_Project() necessarily requires the LWGEOM support to be activated at build time.

Unsafe (but useful) Import/Export SQL functions

All the following SQL functions could be really useful in order to support Import/Export operations.
Anyway you should be well aware that all them could be potentially unsafe and could be potentially used by some malicious hacker in order to create and exploit some security breach.
The cause is very easy to be understood: these functions allow to transfer arbitrary payloads from / to the Database and the local File-System. A malicious attack could be thus implemented e.g. by executing some SQL script or by defining a purposely forged Trigger, and the end users would be absolutely unaware of what is really happening. So in order to effectively shield users against these potentially dangerous security pitfalls all these SQL functions are always disabled by default.

export "SPATIALITE_SECURITY=relaxed"

In order to really enable these functions the user is required to take an explicit action; i.e. the environment variable SPATIALITE_SECURITY=relaxed has to be defined.

SELECT CountUnsafeTriggers();
---------
0

The CountUnsafeTriggers() actually checks if the connected Database does contains any suspect Trigger; checking this version before enabling the unsafe SQL Functions is always highly recommended.
A result different from ZERO implies that some malicious Trigger has been identified; in this case you can perform a deepest inspection by executing the following SQL query:

SELECT type, name, tbl_name, sql 
FROM sqlite_master 
WHERE type IN ('trigger', 'view') AND (sql LIKE '%BlobFromFile%' 
   OR sql LIKE '%BlobToFile%' OR sql LIKE '%XB_LoadXML%' 
   OR sql LIKE '%XB_StoreXML%');

this will give a full report for any malicious Trigger eventually found.

SELECT BlobFromFile( 'C:/mypictures/sunset.jpg' );
---------
BLOB-value

The BlobFromFile() SQL function allows to import a whole file in a single step; the full file payload will be returned as a BLOB value.
If the argument (file-path) doesn't corresponds to a valid file or if the external file cannot be accessed for any reason NULL will be returned.

SELECT BlobToFile( someBlob , 'C:/mypictures/sunset.jpg' );
---------
1

The BlobToFile() SQL function performs the opposite task, i.e. it exports a whole BLOB into an external file; the first argument must correspond to some BLOB, the second one is the file-path identifying the file to be created or overwritten.

Please note: both BlobFromFile() and BlobToFile() were already supported by 4.0.0, but a quick recall is anyway useful so to recall a clearer context.

SELECT XB_LoadXML( 'C:/mydocs/isometadata-sample.xml' );
--------
BLOB-value

SELECT XB_LoadXML( 'http://www.acme.com/public/isometadata-sample.xml' );
--------
BLOB-value

The XB_LoadXML() SQL function allows to import an XML Document in a single step: the full XML Document payload will be returned as a BLOB value.
The passed argument can indifferently be a file-path or a URL; if the datasource cannot be accessed, or if it doesn't contains a well-formed XML Document NULL will be returned.

SELECT XB_StoreXML( someXmlBlob, 'C:/mydocs/isometadata-sample.xml' );
---------
1

The XB_StoreXML() SQL function performs the opposite task, i.e. it exports a whole XmlBLOB into an external file as an XML Document; the first argument must correspond to some valid XmlBLOB, the second one is the file-path identifying the file to be created or overwritten.

SELECT XB_StoreXML( someXmlBlob, 'C:/mydocs/isometadata-sample.xml', 4 );
---------
1

A further optional indentation argument can be eventually specified; this argument will be handled exactly in the same way already specified for both XB_GetPayload() and XB_GetDocument() functions.

A further unsafe SQL function is ExportDXF(), which is separately documented into the more appropriate DXF Wiki page.

back