Back to main 4.3.0 Wiki page
new SQL functions introduced since version 4.2.1-RC
Important notice: version 4.2.1 simply existed for a short period of time as a Release Candidate, but no 4.2.1-STABLE will be never released.|
So all the following SQL functions must now be considered to form integral part of the final 4.3.0
TOC - Table Of Contents
- Geometry processing
- dot macros replacement
- VirtualNetwork reloaded
- new spatialite-tools
1 - Geometry Processing
SELECT ST_SetStartPoint(linestring_geom, point_geom); SELECT ST_SetEndPoint(linestring_geom, point_geom);Both the ST_SetStartPoint() and ST_SetEndPoin() functions will return a new Linestring by replacing its first (or respectively last) point.
Useful e.g. in order to enforce a strict topological consistency on a Graph (aka Roads Network).
SELECT ST_Buffer(MakePoint(0, 0), 1); SELECT ST_Buffer(MakePoint(0, 0), 1, 1); SELECT ST_Buffer(MakePoint(0, 0), 1, 2); SELECT ST_Buffer(MakePoint(0, 0), 1, 3);There is small change affecting ST_Buffer():
- the traditional and well established function signature still continues to be supported exactly as it was in any previous version.
ST_Buffer( geom Geometry , dist Double precision ) : Geometry
- anyway a new further function signature is now supported.
ST_Buffer( geom Geometry , dist Double precision , quadrantsegments Integer ) : Geometry
Its intended scope is allowing to freely choose the number of points to be interpolated so to approximate circular sectors.
The quadrantsegments argument exactly corresponds to the number of segments to be interpolated for a 90 degrees circular arc.
2 - dot macros replacementSince long time the spatialite CLI tool supports many useful dot macros; starting since version 4.2.1 the same functionalities are now directly available as standard SQL functions.
The following table shows the correspondences between the dot macros and the new SQL functions:
|dot macro||SQL function||notes|
|ImportDXFfromDir()||this SQL function has no dot macro equivalent.|
|ExportDXF()||this SQL function has no dot macro equivalent.|
Please consult the SQL functions documentation for more details about the supported syntaxes.
3 - CloneTableThe new CloneTable() SQL function is fully explained in a separate tutorial
4 - ElementaryGeometriesYou'll find a detailed explanation about the VirtualElementary driver in this Wiki page
5 - MiscellaneousStarting since version 4.2.1 BLOBs containing a Jpeg2000 image will be correctly identified as such.
SELECT IsJp2Blob(blob); ----------------------- 1 SELECT GetMimeType(blob); ------------------------- image/jp2
A brand new eval() SQL function is now supported, and strictly corresponds to the code implementation available in the ext/misc/eval.c branch of SQLite.
SELECT f_table_name, eval('select count(*) from ' || f_table_name) FROM geometry_columns; --------------------- com2011 8092 prov2011 110 reg2011 20In its simpler form eval(X) (just a single argument) it will recursively execute the SQL statement defined by X.
In this simple example the "geometry_columns" metadata table will be queried so to identify all GeoTables defined within the currently connected DB-file.
eval() will then dynamically query each single table so to determine the total number of rows it contains.
SELECT eval('select nome from prov2011 where cod_reg = 9', ','); ---------------- Massa Carrara,Lucca,Pistoia,Firenze,Livorno,Pisa,Arezzo,Siena,Grosseto,PratoThe alternative eval(X, Y) form (two arguments) allows to freely set a delimiter string so to separate an item from the following one. (by default just a single white space will be used as a delimiter).
In this second example we'll use eval(X,Y) so to build a single text string containing a comma separated list of all Tuscan Provinces (WHERE cod_reg = 9).
SELECT eval('select nome from prov2011 where cod_reg = 9', '; '); ----------------- Massa Carrara; Lucca; Pistoia; Firenze; Livorno; Pisa; Arezzo; Siena; Grosseto; Pratosame as above: this time using a semicolon delimiter and introducing a further white space between Province names.
6 - VirtualNetwork reloadedStarting since version 4.2.1 VirtualNetwork and related tools (aka Routing) support several interesting enhancements.
Please consult the appropriate Wiki page for more detailed informations.
7 - new spatialite-toolsStarting since version 4.2.1 two further CLI tools are now supported; the corresponding code is available from the spatialite-tools repository:
- spatialite_xml_validator: fully based on libxml2 and intended to perform a full Schema validation for any generic XML document internally declaring an XSD Schema.
More XML documents can be eventually validated in a single pass.
- spatialite_osm_overpass: this too is fully based on libxml2 and is intended to directly download and process OSM datasets delimited by a selected Bounding Box via the web Overpass API.
There is a specific Wiki page about this tool.
Back to main 4.3.0 Wiki page