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

1. Geometry processing
2. dot macros replacement
3. CloneTable
4. VirtualElementary
5. Miscellaneous
6. VirtualNetwork reloaded
7. 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.

quadrantsegments=30 default setting	quadrantsegments=1	quadrantsegments=2	quadrantsegments=3

---

2 - dot macros replacement

Since 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
.chkdupl	CheckDuplicateRows()
.remdupl	RemoveDuplicateRows()
.elemgeo	ElementaryGeometries()
.dropgeo	DropGeoTable()
.loadshp	ImportSHP()
.dumpshp	ExportSHP()
.loaddbf	ImportDBF()
.dumpdbf	ExportDBF()
.dumpkml	ExportKML()
.dumpgeojson	ExportGeoJSON()
.loadxl	ImportXLS()
.loadwfs	ImportWFS()
.loaddxf	ImportDXF()
	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 - CloneTable

The new CloneTable() SQL function is fully explained in a separate tutorial

---

4 - ElementaryGeometries

You'll find a detailed explanation about the VirtualElementary driver in this Wiki page

---

5 - Miscellaneous

Starting 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      20

In 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,Prato

The 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; Prato

same as above: this time using a semicolon delimiter and introducing a further white space between Province names.

---

6 - VirtualNetwork reloaded

Starting 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-tools

Starting since version 4.2.1 two further CLI tools are now supported; the corresponding code is available from the spatialite-tools repository:

1. 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.
2. 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

---