D 2013-05-30T14:26:33.303
L 4.1.0\sChanges
P 32419adfa3ab732954bd46b25ced39d6ab95c4c7
U sandro
W 19539
back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.1.0-doc">4.1.0-doc</a>
<h2>New SQL functions introduced in 4.1.0</h2>
Table of contents:
<ul>
<li>SQL functions supporting <a href="#cast">data-type casting</a></li>
<li>SQL functions supporting <a href="#uuid">UUID generation</a></li>
<li>Spatial SQL functions supporting <a href="#geom">Geometries</a><ul>
<li>SQL functions supporting <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?circles-ellipses">Circles and Ellipses</a></li></ul></li>
<li>SQL functions supporting <a href="#unsafe">unsafe</a> direct Import/Export</li>
</ul>
<h3><a name="generic">cast</a> SQL functions - type casting</h3>
SQLite offers just a very basic type checking; this is sometimes useful, but could be really annoying very often.<br>
<verbatim>
SELECT '1', TypeOf( '1' ), CAST ( '1' AS INTEGER ), TypeOf( CAST ( '1' AS INTEGER ) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>'1'</th><th>TypeOf( '1' )</th><th>CAST ( '1' AS INTEGER )</th><th>TypeOf( CAST ( '1' AS INTEGER ) )</th></tr>
<tr><td>1</td><td>text</td><td align="right">1</td><td>integer</td></tr></table><br>
The SQL <b>CAST</b> 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.<br>
<u>Please note</u>: some subtle differences exists here and there; please carefully read this documentation page.
<verbatim>
SELECT CastToInteger( 1 ), CastToInteger( 1.4 ), CastToInteger( 1.6 ),
CastToInteger( '123' ), CastToInteger( 'alpha' ), CastToInteger ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToInteger( 1 )</th><th>CastToInteger( 1.4 )</th><th>CastToInteger( 1.6 )</th><th>CastToInteger( '123' )</th><th>CastToInteger( 'alpha' )</th><th>CastToInteger ( zeroblob(4) )</th></tr>
<tr><td align="right">1</td><td align="right">1</td><td align="right">2</td><td align="right">123</td><td>NULL</td><td>NULL</td></tr></table><br>
The <b>CastToInteger()</b> SQL function will attempt to return a value of the <b>Integer</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will be returned absolutely unaffected.</li>
<li>a Double input argument will return the <u>nearest</u> Integer value (after applying <b>4/5</b> rounding).</li>
<li>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.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul><br>
<hr>
<verbatim>
SELECT CastToDouble( 1 ), CastToDouble( 1.23 ), CastToDouble( '123.45' ),
CastToDouble( 'alpha' ), CastToDouble ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToDouble( 1 )</th><th>CastToDouble( 1.23 )</th><th>CastToDouble( '123.45' )</th><th>CastToDouble( 'alpha' )</th><th>CastToDouble ( zeroblob(4) )</th></tr>
<tr><td align="right">1.000000</td><td align="right">1.230000</td><td align="right">123.45000</td><td>NULL</td><td>NULL</td></tr></table><br>
The <b>CastToDouble()</b> SQL function will attempt to return a value of the <b>Double</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return the corresponding Double value.</li>
<li>a Double input argument will be returned absolutely unaffected.</li>
<li>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.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul><br>
<hr>
<verbatim>
SELECT CastToText( 1 ), CastToText( 1.5 ), CastToText( 'alpha' ), CastToText ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToText( 1 )</th><th>CastToText( 1.5 )</th><th>CastToText( 'alpha' )</th><th>CastToText ( zeroblob(4) )</th></tr>
<tr><td>1</td><td>1.5</td><td>alpha</td><td>NULL</td></tr></table><br>
The <b>CastToText()</b> SQL function will attempt to return a value of the <b>Text</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return the corresponding Text value.</li>
<li>a Double input argument will be return the corresponding Text value; no significant decimal digit will be suppressed.</li>
<li>a Text input argument will return the corresponding Double value.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<verbatim>
SELECT CastToText( 1, 4 ), CastToText( 1234, 2 ), CastToText( 1, 8 ), CastToText( 1.5, 4 );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToText( 1, 4 )</th><th>CastToText( 1234, 2 )</th><th>CastToText( 1, 8 )</th><th>CastToText( 1.5, 4 )</th></tr>
<tr><td>0001</td><td>1234</td><td>00000001</td><td>0001.5</td></tr></table><br>
An overloaded version of <b>CastToText()</b> 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 <b>zero-padding-length</b>.<br>
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.<br><br>
<hr>
<verbatim>
SELECT CastToBlob( 1 ), CastToBlob( 1.5 ), Hex( CastToBlob( 'alpha' ) ), Hex ( CastToBlob ( zeroblob(4) ) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToBlob( 1 )</th><th>CastToBlob( 1.5 )</th><th>Hex( CastToBlob( 'alpha' ) )</th><th>Hex( CastToText ( zeroblob(4) ) )</th></tr>
<tr><td>NULL</td><td>NULL</td><td>616C706861</td><td>00000000</td></tr></table><br>
The <b>CastToBlob()</b> SQL function will attempt to return a value of the <b>BLOB</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return a NULL value.</li>
<li>a Double input argument will return a NULL value.</li>
<li>a Text input argument will return the corresponding BLOB value.</li>
<li>any BLOB input argument will return the corresponding BLOB value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<verbatim>
SELECT Hex( CastToBlob( '0123456789aBcDeFfEdCbA9876543210', 1 ) );
-----
0123456789ABCDEFFEDCBA9876543210
SELECT CastToBlob( '12f', 1 );
-----
NULL
SELECT CastToBlob( '12HF', 1 );
-----
NULL
</verbatim>
A second overloaded version of <b>CastToBlob()</b> is supported as well; by specifying a further <b>hex_input</b> <u>boolean</u> argument set as <b>TRUE</b> the input string will be assumed to be <u>Hexadecimally encoded</u>. In this case NULL will be returned if the input string doesn't corresponds to a valid Hexadecimal notation.<br><br>
<hr>
<verbatim>
SELECT ForceAsNull( 'a', 'b' ), ForceAsNull( 'abcd', 'abcd' ), ForceAsNull( 1, 1), ForceAsNull( 1.1, 1 );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>ForceAsNull( 'a', 'b' )</th><th>ForceAsNull( 'abcd', 'abcd' )</th><th>ForceAsNull( 1, 1)</th><th>ForceAsNull( 1.1, -9999 )</th></tr>
<tr><td>a</td><td>NULL</td><td>NULL</td><td align="right">1.1</td></tr></table><br>
The <b>ForceAsNull()</b> SQL function will evaluate two arbitrary arguments.<br>
If they are equal and exactly of the same data-type NULL will be returned; otherwise the first argument value will be returned.<br>
Useful e.g. while processing data imported from any <b>Shapefile</b> or <b>DBF file</b> (such formats doesn't support any real NULL value, often being replaced by some conventional value e.g. <b>0</b> or <b>-9999</b> and alike).<br><br>
<hr>
<h3><a name="uuid">Generic</a> SQL function - UUID generator</h3>
An <a href="http://en.wikipedia.org/wiki/Universally_unique_identifier">Universally unique identifier</a> (aka <b>UUID</b>) simply is a <u>128 bit</u> Integer; accordingly to <a href="http://en.wikipedia.org/wiki/UUID#Random_UUID_probability_of_duplicates">theory</a>, 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 <u>universally unique IDs</u>.<br>
Usually UUIDs are represented in their <u>canonical form</u>, i.e. as a sequence of 32 hexadecimal digits separated into five distinct block, accordingly to a <b>8-4-4-4-12</b> schema.
<verbatim>
SELECT CreateUUID();
--------
fff5faf1-dcc0-4391-8054-6e7de75d85b1
</verbatim>
The <b>CreateUUID()</b> SQL function is built on the top the <i>High Quality Pseudo-Random Number Generator</i> <a href="http://www.sqlite.org/c3ref/randomness.html">internally implemented</a> by SQLite.<br>
The returned UUID is conformant to <u>Version 4 (random)</u> (i.e. the first digit into the third block will always be <b>4</b> and the first digit into the fourth block will always be <b>8</b>).<br><br>
<hr>
<h3><a name="geom">Spatial</a> SQL functions</h3>
Few more Spatial SQL functions are now supported.
<verbatim>
SELECT ST_AsText( ST_AddPoint(
ST_GeomFromText( 'LINESTRING( 0 0, 1 0 )' ),
ST_GeomFromText( 'POINT( 1 1 )' ) ) );
--------
LINESTRING(0 0, 1 0, 1 1)
</verbatim>
The <b>ST_AddPoint()</b> 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.
<verbatim>
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)
</verbatim>
If an optional third argument (of the Integer type) is passed to <b>ST_AddPoint()</b> it's intended to specify the <u>index</u> of the new Vertex (first Vertex has index 0, second Vertex has index 1 and so on).<br>
Referencing a not existing index is an error, and will return NULL.
<verbatim>
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)
</verbatim>
Passing a negative index is a supported option, and simply corresponds to the default behaviour (i.e. <i>appending the Point to the end of the Linestring</i>).<br><br>
<hr>
<verbatim>
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)
</verbatim>
The <b>ST_SetPoint()</b> 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 <u>index</u> of the affected Vertex and the third one must be a Point.<br>
Referencing a not existing index is an error, and will return NULL.<br><br>
<hr>
<verbatim>
SELECT ST_AsText( ST_RemovePoint(
ST_GeomFromText( 'LINESTRING( 0 0, 1 0, 1 1 )' ), 1 ) );
---------
LINESTRING(0 0, 1 1)
</verbatim>
The <b>ST_RemovePoint()</b> 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 <u>index</u> of the affected Vertex.<br>
Referencing a not existing index is an error, and will return NULL.<br><br>
<hr>
<verbatim>
SELECT ST_AsText( ST_Point(10, 20) );
----------
POINT(10 10)
</verbatim>
The <b>ST_Point()</b> SQL function simply is yet another <i>alias-name</i> corresponding to <b>MakePoint()</b>; this further alias has been introduced so to support a strict PostGIS conformance.<br>
<u>Please note</u>: differently from MakePoint(), ST_Point() don't supports the optional <b>SRID</b> argument.<br><br>
<hr>
<verbatim>
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)
</verbatim>
Not really a brand new SQL function; more simply a further overloaded flavor of the already existing <b>MakeLine()</b>.<br>
Now the input Geometry (first argument) could be of the MULTIPOINT type and the second argument if of the <b>Boolean</b> type; if the second argument corresponds to <b>TRUE</b> the returned LINESTRING will be oriented accordingly to the Point-sequence as specified by the MultiPoint, otherwise <u>reverse order</u> will be assumed, thus returning a Linestring of opposite orientation.<br><br>
<hr>
<verbatim>
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
</verbatim>
Once again, simply a further extension of the already existing <b>ST_Area()</b>.<br>
Now if the input Geometry adopts <u>geographic coordinates</u> (i.e. based on <u>longitude</u> and <u>latitude</u> angles), a second argument of the <b>Boolean</b> data-type could be optionally specified.<br>
In this case the returned Area will be measured in <b>meters</b>.
<ul>
<li>if the second arg is <b>TRUE</b> the Area will be measured <u>on the Ellipsoid</u> (more accurate, but slower).</li>
<li>if the second arg is <b>FALSE</b> the Area will be measure <u>on the Sphere</u> (faster, but less accurate).</li>
<li>NULL will be returned on invalid args.</li>
<li><u>Please note</u>: this specific flavor of <b>ST_Area()</b> necessarily requires the <b>LWGEOM</b> support to be activated at build time.</li>
</ul><br>
<hr>
<verbatim>
SELECT ST_AsText( ST_Project( MakePoint( 11.52, 42.38, 4326 ), 50000, Radians( 45 ) ) );
----------
POINT(11.951479 42.697467)
</verbatim>
The <b>ST_Project()</b> SQL function will return the destination Point <u>on the ellipsoid</u> corresponding to a <b>start_point</b>, a <b>distance</b> and a <b>bearing</b> (aka <u>azimuth</u> aka <u>direction</u> aka <u>heading</u>).
<ul>
<li>the <b>start_point</b> is expected to adopt <u>geographic coordinates</u> (i.e. based on <u>longitude</u> and <u>latitude</u> angles).</li>
<li>the <b>distance</b> is always expected to be expressed in <b>meters</b>.</li>
<li>the <b>azimuth</b> angle is expected to be expressed in <b>radians</b>:
<ul>
<li>North corresponds to 0 degrees (0 radians).</li>
<li>East corresponds to 90 degrees (PI/2 radians).</li>
<li>South corresponds to 180 degrees (PI radians).</li>
<li>West corresponds to 270 degrees (3PI/2 radians).</li>
<li>i.e. it has the same identical meaning as in <b>ST_Azimuth()</b>.</li>
</ul></li>
<li>this function exactly corresponds to the one supported by <b>PostGIS</b>.</li>
<li><u>Please note</u>: <b>ST_Project()</b> necessarily requires the <b>LWGEOM</b> support to be activated at build time.</li>
</ul><br>
<hr>
<h3><a name="unsafe">Unsafe</a> (but useful) Import/Export SQL functions</h3>
All the following SQL functions could be really useful in order to support Import/Export operations.<br>
Anyway you should be <u>well aware</u> that all them could be potentially <u>unsafe</u> and could be potentially used by some <u>malicious hacker</u> in order to create and exploit some <u>security breach</u>.<br>
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 <u>disabled by default</u>.
<verbatim>
export "SPATIALITE_SECURITY=relaxed"
</verbatim>
In order to really enable these functions the user is required to take an explicit action; i.e. the <u>environment variable</u> <b>SPATIALITE_SECURITY=relaxed</b> has to be defined.
<verbatim>
SELECT CountUnsafeTriggers();
---------
0
</verbatim>
The <b>CountUnsafeTriggers()</b> actually checks if the connected Database does contains any suspect Trigger; checking this version before enabling the unsafe SQL Functions is always highly recommended.<br>
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:
<verbatim>
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%');
</verbatim>
this will give a full report for any malicious Trigger eventually found.<br><br>
<hr>
<verbatim>
SELECT BlobFromFile( 'C:/mypictures/sunset.jpg' );
---------
BLOB-value
</verbatim>
The <b>BlobFromFile()</b> SQL function allows to import a whole file in a single step; the full file payload will be returned as a BLOB value.<br>
If the argument (<b>file-path</b>) doesn't corresponds to a valid file or if the external file cannot be accessed for any reason NULL will be returned.<br><br>
<hr>
<verbatim>
SELECT BlobToFile( someBlob , 'C:/mypictures/sunset.jpg' );
---------
1
</verbatim>
The <b>BlobToFile()</b> 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 <b>file-path</b> identifying the file to be created or overwritten.<br><br>
<u>Please note</u>: both <b>BlobFromFile()</b> and <b>BlobToFile()</b> were already supported by 4.0.0, but a quick recall is anyway useful so to recall a clearer context.<br><br>
<hr>
<verbatim>
SELECT XB_LoadXML( 'C:/mydocs/isometadata-sample.xml' );
--------
BLOB-value
SELECT XB_LoadXML( 'http://www.acme.com/public/isometadata-sample.xml' );
--------
BLOB-value
</verbatim>
The <b>XB_LoadXML()</b> SQL function allows to import an XML Document in a single step: the full XML Document payload will be returned as a BLOB value.<br>
The passed argument can indifferently be a <b>file-path</b> or a <b>URL</b>; if the datasource cannot be accessed, or if it doesn't contains a <u>well-formed</u> XML Document NULL will be returned.<br><br>
<hr>
<verbatim>
SELECT XB_StoreXML( someXmlBlob, 'C:/mydocs/isometadata-sample.xml' );
---------
1
</verbatim>
The <b>XB_StoreXML()</b> 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 <b>file-path</b> identifying the file to be created or overwritten.
<verbatim>
SELECT XB_StoreXML( someXmlBlob, 'C:/mydocs/isometadata-sample.xml', 4 );
---------
1
</verbatim>
A further optional <b>indentation</b> argument can be eventually specified; this argument will be handled exactly in the same way already specified for both <b>XB_GetPayload()</b> and <b>XB_GetDocument()</b> functions.<br><br>
<hr><br>
back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.1.0-doc">4.1.0-doc</a>
Z e3095227ab5d6423146d1d8a033afb90