4.3.0 functions
Not logged in
Home Timeline Branches Tags Tickets Wiki Login
Help

back

new SQL functions introduced since version 4.3.0

TOC - Table Of Contents

  1. Inherited from the aborted 4.2.1 Release Candidate
  2. Persistent Connection Modifiers
  3. Trigonometry
  4. Pathname manipulation
  5. URL manipulation
  6. Affine Transformations support
  7. Ground Control Points support
  8. SE Styling helpers
  9. Extended SRID Inspection

1 - Inherited from the aborted 4.2.1 Release Candidate

During November 2014 an earlier 4.2.1-RC was released, but no 4.2.1-STABLE will be never released simply because 4.2.1 progressively continued to evolve step by step until it finally become 4.3.0.
So all new SQL functions introduced by 4.2.1-RC have not to be considered as integral part of 4.3.0; anyway for the sake of clarity you can check the corresponding documentation from this separate Wiki page

2 - Persistent Connection Modifiers

Starting since version 4.3.0 libspatialite has the capability to support few modifiers affecting the behavior of several SQL functions.
Such modifiers are connection-persistent, i.e. they'll be active during all the life-cycle of the current connection (unless explicitly reset); anyway they'll never propagate outside the specific context of the current connection, and when the connection will be closed they'll completely disappear leaving absolutely no persistent trace.

ModifierIntended TargetEffectNotes
GPKG mode
  • all SQL functions accepting one or more arguments of the Geometry type
  • all SQL functions returning a Geometry result
When GPKG mode is actively enabled the following changes will happen:
  • all Geometry arguments (input) will be always expected to be formatted accordingly to the GeoPackage's own binary format.
    Passing an eventual Geometry argument formatted accordingly to the native SpatiaLite's binary format will immediately raise an exception.
  • all return values (output) of the Geometry type will be always formatted accordingly to the GeoPackage's own binary format.
GPKG mode can be effectively enabled only when the current connection target is a fully qualified GeoPackage database.
If the current database target is not a GeoPackage any attempt to activate the GPKG mode will be always forbidden.
GPKG amphibious mode
  • all SQL functions accepting one or more arguments of the Geometry type
When GPKG amphibious mode is actively enabled the following changes will happen:
  • all Geometry arguments (input) could be formatted indifferently either as native SpatiaLite's binaries or as GeoPackage's own binaries.
    An automatic format conversion will be silently applied whenever required.
  • all return values of the Geometry type will always continue to be formatted accordingly to the SpatiaLite's native binary format.
Simply intended to be an universally compatible mode for unsophisticated readers.
GPKG mode and GPKG amphibious mode are mutually exclusive options.
Decimal Precision
  • ST_AsText()
  • spatialite_gui
Explicitly selects the preferred number of decimal digits to be represented when printing floating point values (coordinates) in their textual representation.
The implicit default setting is using 6 decimal digits.		spatialite_gui will honor this setting for all floating point values shown on the screen and coming from some SQL resultset.

SQL examples

SELECT EnableGpkgMode();
SELECT DisableGpkgMode();
SELECT GetGpkgMode();                <-- will return 1 or 0 accordingly to the current setting


SELECT EnableAmphibiousGpkgMode();
SELECT DisableAmphibiousGpkgMode();
SELECT GetAmphibiousGpkgMode();      <-- will return 1 or 0 accordingly to the current setting


SELECT SetDecimalPrecision(8); 
SELECT SetDecimalPrecision(.1);      <-- any negative value will reset to the default initial setting       
SELECT GetDecimalPrecision();        <-- will return the current setting

3 - Trigonometry

SELECT atan2 ( y double, x double );
The two args variant of arc tangent is now supported.
Returns the principal value of the arc tangent of y/x in radians, using the signs of the two arguments to determine the quadrant of the result. The return value is in the range (-pi, pi).
For more detail please check man atan2

4 - Pathname manipulation

MS-DOS / Windows paths: 
SELECT DirNameFromPath( 'C:\users\sandro\my_data\arezzo\strade.shp' );
------
C:\users\sandro\my_data\arezzo\

SELECT FullFileNameFromPath( 'C:\users\sandro\my_data\arezzo\strade.shp' );
------
strade.shp

SELECT FileNameFromPath( 'C:\users\sandro\my_data\arezzo\strade.shp' );
------
strade

SELECT FileExtFromPath( 'C:\users\sandro\my_data\arezzo\strade.shp' );
------
shp
Unix / Linux paths: 
SELECT DirNameFromPath( '/home/sandro/my_data/arezzo/strade.shp' );
------
/home/sandro/my_data/arezzo/

SELECT FullFileNameFromPath( '/home/sandro/my_data/arezzo/strade.shp' );
------
strade.shp

SELECT FileNameFromPath( '/home/sandro/my_data/arezzo/strade.shp' );
------
strade

SELECT FileExtFromPath( '/home/sandro/my_data/arezzo/strade.shp' );
------
shp
These functions are mainly intended to facilitate the task of writing complex SQL scripts performing massive import / export operations.
The following could be a rather realistic example: 
-- creating a table intended to store many digital pictures
CREATE TABLE my_pics (
    id INTEGER PRIMARY KEY,
    photo BLOB,
    input_path TEXT NOT NULL);

-- populating the list of all pictures to be loaded into the table
BEGIN;
INSERT INTO my_pics VALUES (NULL, NULL, '../my_pics/nikon/pic0001.JPG');
...
INSERT INTO my_pics VALUES (NULL, NULL, '../my_pics/canon/IMG000001.jpeg');
...
INSERT INTO my_pics VALUES (NULL, NULL, '../my_pics/misc/florence.jpg');
INSERT INTO my_pics VALUES (NULL, NULL, '../my_pics/misc/Rome.jpeg');
INSERT INTO my_pics VALUES (NULL, NULL, '../my_pics/misc/VENICE.JPG');
COMMIT;

-- loading all pictures into the DB
UPDATE my_pics SET photo = BlobFromFILE(input_path);

-- exporting all pictures into the same dir with normalized names
SELECT BlobToFile(photo, './output_dir/' || Lower(FileNameFromPath(input_path)) || '.jpg');

5 - URL manipulation

Two new SQL functions are now available supporting URL percent encoding: The intended scope mainly is supporting complex SQL scripts and/or DataSeltzer

The following could be a rather realistic example: 
CREATE TABLE cgi_requests (
    id INTEGER PRIMARY KEY,
    arg1 TEXT NOT NULL,
    arg2 TEXT NOT NULL,
    request_url TEXT);

INSERT INTO cgi_requests VALUES (NULL, 'Den Haag', 'fist class', NULL);
INSERT INTO cgi_requests VALUES (NULL, 'Antwerpen', '***any***', NULL);
INSERT INTO cgi_requests VALUES (NULL, '''s-Hertogenbosch', 'second class', NULL);

UPDATE cgi_requests SET request_url = 'http://www.someserver.org/cgi-bin/someservice?arg1=' || EncodeURL(arg1) || '&arg2=' || EncodeURL(arg2);

SELECT request_url FROM cgi_requests;
------
http://www.someserver.org/cgi-bin/someservice?arg1=Den+Haag&arg2=fist+class
http://www.someserver.org/cgi-bin/someservice?arg1=Antwerpen&arg2=%2a%2a%2aany%2a%2a%2a
http://www.someserver.org/cgi-bin/someservice?arg1=%27s-Hertogenbosch&arg2=second+class

SELECT DecodeURL(request_url) FROM cgi_requests;
------
http://www.someserver.org/cgi-bin/someservice?arg1=Den Haag&arg2=fist class
http://www.someserver.org/cgi-bin/someservice?arg1=Antwerpen&arg2=***any***
http://www.someserver.org/cgi-bin/someservice?arg1='s-Hertogenbosch&arg2=second class

6 - Affine Transformations support

The new ATM (Affine Tranformation Matrix) module is fully documented in this separate Wiki page

7 - Ground Control Points support

The new GCP (Ground Control Points) module is fully documented in this separate Wiki page

8 - SE Styling helpers

The following SQL functions are intended to fully support SE Styling.
Note: any direct interaction with SE Styling meta-tables should be always considered a risky and dangerous operation, and should be always carefully avoided.
This is because the meta-tables physical layout could easily change in future versions; anyway the supporting SQL functions can be safely assumed to be stable during the time and will adapt to any future change in the most appropriate way.
The following list enumerates all SE Styling related SQL function by homogeneous groups:

8 - Extended SRID Inspection

This new module is fully documented in this separate Wiki page


back
Keep in touch ... join the community, subscribe the SpatiaLite Users mailing list
SpatiaLite is developed and maintained by Alessandro Furieri a.furieri@lqt.it
Fossil version 2.13 [e7bba4ff36] 2020-11-01 00:13:51