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

back

new SQL functions introduced since version 4.2.0

TOC - Table Of Contents

  1. General purpose
  2. DMS / DD conversions
  3. Geometry processing
  4. Forcing Layer Statistics to be recalculated
  5. ROWID checks
  6. building/updating a MetaCatalog
  7. GEOS / LWGEOM errors and warnings
  8. GPKG (GeoPackage) support

Tutorials

  1. How-to handle a MetaCatalog (practical example)
  2. How-to fully identify Invalid Geometries (practical example)

1 - General purpose

SELECT spatialite_target_cpu();
---
x86_64-redhat-linux
the select_target_cpu() function will return a text string identifying the intended Target CPU for the current library; the actual value of this string is usually passed by the gcc compiler at build time. Useful in order to quickly check if you are using a 32- or 64-bit library.

2 - DMS / DD conversions

Geographic coordinates based as longitude and latitude angles are usually expressed in one of these two alternative notations: 
SELECT LongitudeFromDMS( a.dms ), LatitudeFromDMS( a.dms )
FROM (SELECT '43°28′24″N 11°52′12″E' AS dms) AS a;
-------------------------
11.870000	43.473333
the LongitudeFromDMS(dms-string) and LatitudeFromDMS(dms-string) functions will attempt to parse a DMS string then returning the corresponding DD coordinate. 
SELECT LongLatToDMS(11.870000, 43.473333);
----------------------
43°28′24″N 011°52′12″E
the LongLatToDMS(longitude, latitude) function will perform the opposite conversion, i.e. will return a DMS expression starting from a couple of DD coordinates.
All these functions will return NULL on invalid input arguments.

3 - Geometry processing

SELECT ASText( MakePolygon( 
    GeomFromText( 'LINESTRING(0 0, 100 0, 100 100, 0 100, 0 0)' ) ) );
----------------
POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))

SELECT ASText( MakePolygon( 
    GeomFromText( 'LINESTRING(0 0, 100 0, 100 100, 0 100, 0 0)' ),
    GeomFromText( 'LINESTRING(50 50, 60 50, 60 60, 50 60, 50 50)' ) ) );
----------------
POLYGON((0 0, 100 0, 100 100, 0 100, 0 0), (50 50, 60 50, 60 60, 50 60, 50 50))

SELECT ST_ASText( ST_MakePolygon( 
ST_GeomFromText( 'LINESTRING(0 0, 100 0, 100 100, 0 100, 0 0)' ),
ST_GeomFromText( 'MULTILINESTRING((50 50, 60 50, 60 60, 50 60, 50 50), 
                                  (10 10, 40 30, 30 40, 10 10))' ) ) );
----------------
POLYGON((0 0, 100 0, 100 100, 0 100, 0 0), (50 50, 60 50, 60 60, 50 60, 50 50), (10 10, 40 30, 30 40, 10 10))
the MakePolygon() aka ST_MakePolygon() is kind of lightweight/simplified ST_BuildArea(), and will attempt to create a Polygon starting from its Ring(s) represented as Linestring(s).
Note well: all Linestring(s) are always expected to be correctly closed, i.e. the first and last vertices must exactly coincide.
Absolutely no topological validation will be performed, so this function is expected to be really fast. Anyway it could eventually return an invalid Polygon. This functions is assumed to be an exact equivalent of the same function available on PostGIS.

SELECT ST_AsText( ST_Node(
    ST_GeomFromText( 'LINESTRINGZ(0 0 0, 10 10 10, 0 10 5, 10 0 3)' ) ) );
----------------
MULTILINESTRING Z((0 0 0, 5 5 4.5), (5 5 4.5, 10 10 10, 0 10 5, 5 5 4.5), (5 5 4.5, 10 0 3))
the ST_Node() function will attempt to fully node a set of linestrings using the least possible number of nodes while preserving all of the input ones. NULL will be returned if the input Geometry isn't a set of Linestrings or if any other error occurs.
Please note: this function strictly requires the LWGEOM support to be configured at build time.
This functions is assumed to be an exact equivalent of the same function available on PostGIS.

SELECT ST_AsText( ST_SelfIntersections(
    ST_GeomFromText( 'LINESTRING(0.5 1, 1 2, 2 0, 3 2, 3.5 1, 0.5 1)' ) ) );
-----------------
MULTIPOINT(2.5 1, 1.5 1)
the SelfIntersections() aka ST_SelfIntersections() function will return a MultiPoint Geometry representing any self-intersection found within the input geometry always expected to be of the Linestring or MultiLinestring type. NULL will be returned for invalid arguments, or when no self-intersections were found.
Please note: this function strictly requires the LWGEOM support to be configured at build time.

4 - Forcing Layer Statistics to be recalculated

Layer Statistics are supported starting since version 4.0.0; please see the corresponding wiki page.
Very short recall: 
SELECT InvalidateLayerStatistics();
---------
1

SELECT InvalidateLayerStatistics('mytable');
---------
1

SELECT InvalidateLayerStatistics('mytable', 'geom');
---------
1
This new InvalidateLayerStatistics() allows to immediately mark Layer Statistics as no longer valid. So the next call to UpdateLayerStatistics() will then correctly recalculate yet again Layer Statistics starting from scratch.
You can selectively invalidate all Statistics, or just for a single Table aka Layer.

5 - ROWID checks

The SpatiaLite's own Spatial Index implementation critically depends on ROWID values in order to keep correctly synchronized the main Geometry table and the corresponding R*Tree table.
Anyway there several potentially harmful conditions related to ROWIDs that could cause severe damages to the overall DB consistency; these SQL functions could effectively help to prevent and/or identify many apparently inexplicable troubles. 
CREATE TABLE alpha (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  value INTEGER NOT NULL);
SELECT CheckShadowedRowid('alpha');
--------------
0

CREATE TABLE beta (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  rowid INTEGER NOT NULL);
SELECT CheckShadowedRowid('beta');
---------------
1
The first table (alpha) has nothing wrong, and will effectively support ROWID values uniquely identifying every row.
The second table (beta) explicitly declares a column named rowid; this one is a legitimate SQLite option, but in this case the ROWID values will not necessarily uniquely identify every row, because they will simply correspond to the actual values set for that column, not to physical row identifiers. Any column explicitly named rowid will shadow the expected ROWIDs, and thus will probably cause a broken / malfunctioning Spatial Index.
The CheckShadowedRowid(tablename) function is intended to identify such potentially dangerous conditions. 

CREATE TABLE gamma (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  value INTEGER NOT NULL);
SELECT CheckWithoutRowid('gamma');
-----------------
0

CREATE TABLE delta (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  value INTEGER NOT NULL) WITHOUT ROWID;
SELECT CheckWithoutRowid('delta');
-----------------
1
Starting since version 3.8.2 SQLite introduced the WITHOUT ROWID clause; a table created by declaring such option will never have a ROWID, and will then be absolutely not compatible with Spatial Index requirements. The CheckWithoutRowid(tablename) function is intended to identify such harmful conditions.

Please note well: starting since version 4.2 all SQL functions handling Geometry columns and Spatial Indices has been updated so to correctly recognize both these dangerous issues related with ROWIDs.
Anyway this doesn't excludes that some already existing DB created using any previous version could be possibly affected by such ROWID-related issues.
If you simply suspect something like this, a simple query as the following one will definitely clarify any possible doubt: 
SELECT f_table_name, CheckShadowedRowid(f_table_name), 
    CheckWithoutRowid(f_table_name)
FROM geometry_columns;

6 - building/updating a MetaCatalog

Sometimes it could be useful creating a MetaCatalog, i.e. a Table automatically reporting all Tables and Columns with their individual attributes.
SQLite supports many useful PRAGMAs, but it's never possible obtaining a synoptic and comprehensive overview. 
SELECT CreateMetaCatalogTables(1);
-------------
1
By executing the CreateMetaCatalogTables() function this task will be performed automatically. A table named splite_metacatalog will be created, and its content will look something like this: 
table_name column_name   type   not_null primary_key foreign_key unique_value
-----------------------------------------------------------------------------
.....
geonames   feature_class TEXT          1           0           0            0
geonames   feature_code  TEXT          1           0           0            0
geonames   geom          POINT         0           0           0            0
geonames   geonameid     INTEGER       1           1           0            0
geonames   latitude      DOUBLE        1           0           0            0
geonames   longitude     DOUBLE        1           0           0            0
.....
That's not all: a further splite_metacatalog_statistics table will be created as well, but will be initially empty. 
SELECT UpdateMetaCatalogStatistics(1, 'geonames', 'feature_code');
After invoking the UpdateMetaCatalogStatistics() function the splite_metacatalog_statistics table will contain the frequency count for each individual value found in the required table/column. 
table_name      column_name     value   count
---------------------------------------------
geonames	feature_code	PPL	71242
geonames	feature_code	PPLA     3478
geonames	feature_code	PPLA2	13040
geonames	feature_code	PPLA3	26495
geonames	feature_code	PPLA4	26496
geonames	feature_code	PPLC      241
geonames	feature_code	PPLCH       1
geonames	feature_code	PPLF        5
geonames	feature_code	PPLG       12
geonames	feature_code	PPLH        3
geonames	feature_code	PPLL      245
geonames	feature_code	PPLQ       18
geonames	feature_code	PPLR        6
geonames	feature_code	PPLS       14
geonames	feature_code	PPLW        1
geonames	feature_code	PPLX     1161
geonames	feature_code	STLMT       1
Please note: attempting to execute UpdateMetaCatalogStatistics() on behalf of any table/column containing strongly dispersed values isn't a good option, because it will create lots of rows without adding any real information.
This function is mainly intended as a tool allowing to quickly analyze strongly concentrated value distributions (typically: columns based on code values).

You could eventually use UpdateMetaCatalogStatistics() in a more sophisticated and advanced way: if you are anyway interested to discover more about all this you can read this tutorial.

Short conclusion: using the MetaCatalog presumably will never be an interesting activity for many ordinary users; if you are mainly interested in consulting a DB coming from third-party sources the MetaCatalog will certainly be of little or no interest.
But for any power user mainly interested in producing datasets to be externally distributed the MetaCatalog will surely be a powerful and highly useful tool supporting many validation activities.

7 - GEOS / LWGEOM errors and warnings

Both the GEOS and the LWGEOM libraries usually emits very detailed messages each time that some abnormal condition is encountered. Anyway all these diagnostic messages will be easily pass unnoticed simply because they'll be dumped on the standard output; so in the case of any GUI application they'll probably be completely invisible.
All the following functions now allow to directly query such messages at SQL level. 
SELECT GEOS_GetLastWarningMsg();

SELECT GEOS_GetLastErrorMsg();

SELECT GEOS_GetLastAuxErrorMsg();

SELECT GEOS_GetCriticalPointFromMsg();

SELECT LWGEOM_GetLastWarningMsg();

SELECT LWGEOM_GetLastErrorMsg();

SELECT ST_IsValidReason(ST_GeomFromText(
'POLYGON((0 0, 10 0, 0 10, 10 10, 0 0))'));
-----------
Self-intersection[5 5]

SELECT ST_AsText(ST_IsValidDetail(ST_GeomFromText(
'POLYGON((0 0, 10 0, 0 10, 10 10, 0 0))')));
-----------
POINT(5 5)
Two further strictly related functions are supported, mainly in order to mimic what's available on PostGIS: If you are anyway interested to discover more about all this you can read this tutorial.

8 - GPKG (GeoPackage) support

General support:
Raster (tiles) support:
Vector (Geometry) support:
Please note: the SpatiaLite's support to GPKG covers all Triggers defined in Annexes L/M/N:
New VirtualGPKG Virtual Tables:
SQL auxiliary functions supporting VirtualGPKG:

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