4.4.0 functions
Not logged in


new SQL functions introduced (or changed) since version 4.4.0

TOC - Table Of Contents

  1. ImportSHP(), ExportSHP(), ImportDBF() and ExportDBF()
  2. SQL functions manipulating Sequences

1 - ImportSHP(), ExportSHP(), ImportDBF() and ExportDBF()

The default behavior implemented by the C API supporting import or export operations involving an external DBF file has changed since version 4.4.0 (August 2016).

A short rationale: practical experience shows that too many users (and/or may be several software tools) tend to adopt lowercase or uppercase names more or less at random.
Usually this is not necessarily a big issue from a pure SQL perspective, but it could easily become very disturbing when you regularly update the same dataset by importing subsequent versions.
More specifically, a sudden switch from lower to uppercase (or the opposite) can mysteriously stop many OWS-based WEB services simply because OWS names are case-sensitive.

The signatures of ImportSHP(), ExportSHP(), ImportDBF() and ExportDBF() have consequently been extended by introducing an additional argument, so as to enable users to finely control this specific facet as they wish.
Here are the new extended signatures:

ImportSHP ( filename Text , table Text , charset Text , srid Integer , geom_column Text , pk_column Text , geometry_type Text , coerce2D Integer ,
        compressed Integer , spatial_index Integer , text_dates Integer , colname_case Text , update_statistics Integer , verbose Integer ) : Integer
ExportSHP ( table Text , geom_column Text , filename Text , charset Text , geom_type Text , colname_case Text ) : Integer
ImportDBF ( filename Text , table Text , charset Text , pk_column Text , text_date Integer , colname_case Text ) : Integer
ExportDBF ( table Text , filename Text , charset Text , colname_case Text ) : Integer

The new colname_case argument accepts the following values:
Note: ImportSHP() can now directly update Layer Statistics, depending on the update_statistics argument value (always set to 1 aka TRUE by default).

1a - spatialite CLI support

The same options are now also supported by the corresponding dot macros available on the spatialite CLI frontend, as shown by the following examples:

.loadshp ./myshp my_table CP1252 geom pk_uid MULTIPOLYGON 2d no yes yes UPPERCASE
.dumpshp my_table ./myshp CP1252 POLYGON UPPER
.loaddbf ./my.dbf my_table CP1252 pk_uid yes SAMECASE
.dumpdbf mytable ./my.dbf CP1252 SAME

1b - spatialite_gui support

Please read the appropriate documentation

2 - SQL functions manipulating Sequences

Intro: the PostgreSQL DBMS supports Sequences, an interesting feature often useful in many SQL-driven processes. Basically, a Sequence is a number generator producing integer values increasing by 1 for each subsequent call.
SQLite lacks an equivalent capability, so SpatiaLite (starting since version 4.4.0 - August 2016) now supports its own Sequence implementation closely modeled on PostgreSQL, except for a very important aspect that should not be overlooked:
SQL functionDescription
sequence_nextval ( seq_name Text ) : Integer Advances the Sequence identified by seq_name to its next value and then returns the value.
If the Sequence is referenced for the first time it will be created on-the-fly.
NULL will be returned on any error.
sequence_currval ( seq_name Text ) : Integer Returns the value most recently obtained by sequence_nextval() for the Sequence identified by seq_name.
NULL will be returned on any error or if the Sequence doesn't exist.
sequence_lastval ( void ) : Integer Returns the value most recently obtained by sequence_nextval() (any Sequence).
NULL will be returned on any error or when no Sequence has yet been used.
sequence_setval ( seq_name Text , value Integer ) : Integer Sets the current value for the Sequence identified by seq_name.
If the Sequence doesn't exist it will be created and initialized on-the-fly.
A negative value will always be treated as a positive (absolute) value.
Will return value on success or NULL on failure.

Unnamed Sequence and Named Sequences

You are absolutely free to define as many Sequences as you wish, each one of them being identified by its own name (Sequence names are always considered to be case-insensitive).
A special case exists: when the argument seq_name is NULL, or when it's of the INTEGER, DOUBLE or BLOB type (non TEXT), the Unnamed Sequence will always be intended.

Examples #1

SELECT sequence_lastval(), sequence_nextval(NULL), sequence_currval('SeqA');
SELECT sequence_nextval('SeqA'), sequence_nextval(1), sequence_setval('SeqB', 1000);
1, 2, 1000
SELECT sequence_nextval('SEQB'), sequence_currval('seqa'), sequence_nextval('SEQA'), sequence_lastval();
1001, 1, 2, 2

Example #2

The Problem
  1. We'll start with a dataset representing Italian Regions, Provinces and Municipalities (2015).
    you can download the required Shapefiles from here.
  2. the goal is to create a new Municipalities Table. For each Municipality the following Columns must be supplied:
    • an unique code (Primary Key, of the TEXT type), formed by concatenating the Province short name and a progressive number (e.g. FI001, FI002, FI003, ...., PA001, PA002, PA003 and so on).
      Codes must be numbered accordingly to lexicographic order of the Municipality name within each Province.
    • Region name, Province short name and Municipality name
    • area (in Sq.Km)
    • relative ranking by area (in descending order); three different ranking levels must be supplied:
      • nation-wide
      • region-wide
      • province-wide
You would imagine that resolving this problem would only require a trivial CREATE TABLE ... AS SELECT ... ORDER BY ... SQL query, thus conveniently ordering the resultset while calling sequence_nextval() during the insertion to the output table.
Unhappily SQLite doesn't work this way; you'll soon discover that sequence_nextval() is being called during the fetching of each row and not after the ordering of the resultset. Such a simplistic approach will unfortunately fail.

A more sophisticated (and more complex) approach is required; we have to use several temporary tables in order to store some intermediate resultsets before being able to correctly populate the output table.
The best strategy is to deploy a short SQL script, as the following one (commented step by step for your convenience).
-- starting a Transaction

-- creating the output table
CREATE TABLE rank_comuni (
    nat_rank INTEGER NOT NULL,
    reg_rank INTEGER NOT NULL,    
    prov_rank INTEGER NOT NULL,
    name TEXT NOT NULL,
    reg TEXT NOT NULL,
    prov TEXT NOT NULL,
    sup_km2 DOUBLE);

-- first temporary table:
--    * pre-computing areas
--    * ordering by national rank 
SELECT c.comune AS name, r.regione AS reg, p.sigla AS prov, ST_Area(c.geometry) / 1000000.0 AS sup_km2
FROM com2015 AS c
JOIN reg2015 AS r ON (c.cod_reg = r.cod_reg)
JOIN prov2015 AS p ON (c.cod_pro = p.cod_pro)
ORDER BY sup_km2 DESC;

-- second temporary table:
--    * setting national rank values
--    * ordering by regional rank
SELECT sequence_nextval('italy') AS nat_rank, name, reg, prov, sup_km2
FROM tmp_comuni1
ORDER BY reg, sup_km2 DESC;

-- third temporary table:
--    * setting regional rank values
--    * ordering by provincial rank
SELECT nat_rank, sequence_nextval(reg) AS reg_rank, name, reg, prov, sup_km2
FROM tmp_comuni2
ORDER BY prov, sup_km2 DESC;

-- fourth temporary table:
--    * setting national rank values
--    * ordering by region, province and name
SELECT nat_rank, reg_rank, sequence_nextval(prov) AS prov_rank, name, reg, prov, sup_km2
FROM tmp_comuni3
ORDER BY reg, prov, name;

-- resetting the ?prov? sequence
SELECT sequence_setval(prov, 0)
FROM (SELECT DISTINCT prov FROM tmp_comuni3);

-- populating the output table
INSERT INTO rank_comuni    
SELECT prov || CastToText(sequence_nextval(prov), 3),
       nat_rank, reg_rank, prov_rank, name, reg, prov, sup_km2
FROM tmp_comuni4;

-- dropping all temporary tables
DROP TABLE tmp_comuni1;
DROP TABLE tmp_comuni2;
DROP TABLE tmp_comuni3;
DROP TABLE tmp_comuni4;

-- committing the pending Transaction