SpatiaLite Cookbook : Administration

Short answer:
    after a : AddGeometryColumn or a RecoverGeometryColumn

---

Long answer:

A SpatialTable is initialy created as a normal SQLite-TABLE, but without a geometry-column:
(after removing the VIEW created in Creation of the VIEW admin_cities)

-- DROP the VIEW we created in Chapter 4
DROP VIEW IF EXISTS admin_cities;
-- DROP (possible) previous versions of our masterpiece
DROP TABLE IF EXISTS admin_cities;

---

CREATE TABLE admin_cities
(
 -- the id of the City
 id_city INTEGER NOT NULL PRIMARY KEY,
 -- the name of the City
 name_city TEXT NOT NULL,
 -- Population of City, as whole persons
 population_city INTEGER DEFAULT 0,
 -- id of the Province the City belongs to
 id_province INTEGER DEFAULT 0,
 -- name of the Province the City belongs to
 name_province TEXT DEFAULT '',
 -- The Car Plate of the Province the City belongs to
 car_plate_code TEXT DEFAULT '',
 -- id of the Region the Province belongs to
 id_region INTEGER DEFAULT 0,
 -- name of the Region the Province belongs to
 name_region TEXT DEFAULT '',
 -- id of Country the Region belongs to [default: 39, Italy]
 id_country INTEGER DEFAULT 39,
 -- name of Country the Region belongs to [default: Italy]
 name_country TEXT DEFAULT 'Italy'
);

At this point in time, 'admin_cities' is still a normal SQLite-TABLE.

---

Now we will add a geometry-column called 'geom_city' to the SQLite-TABLE 'admin_cities',
  with all the information needed for geometry_columns (srid, geometry_type and coord_dimension) Now 'admin_cities' is a SpatialTable

SELECT
 AddGeometryColumn('admin_cities', 'geom_city', 32632, 'MULTIPOLYGON', 'XY');

---

For a practicable sample how RecoverGeometryColumn is used see: Creation of the TABLE admin_provinces

---

The following is true for AddGeometryColumn:

• checking is done if the TABLE exist and supports ROWID
• checking is done if the srid, geometry_type and coord_dimension parameters are valid
• The Database is NOT a FDO or GeoPackage
• With a ALTER TABLE command a Column will be added
• With a INSERT INTO geometry_columns command the names of the feature table and column,
    as well as the srid, geometry_type and coord_dimension will be inserted into geometry_columns
• Creation of entries for interal tables geometry_columns_auth/ fields_infos/statistics

The following is true RecoverGeometryColumn:

• any previous entry in geometry_columns will be removed
• otherwise the same as AddGeometryColumn, with the exception of the ALTER TABLE command,
    since it is assumed that the geometry-column already exists.

With that, the OGC-SFS conditions for the geometry_columns entries are complete.

---

But how will the srid, geometry_type and coord_dimension restrictions be enforced, to conform to the OGC-SFS specification. ?

For this, the SQLite concept of TRIGGERs will be used

The following is true for both AddGeometryColumn and RecoverGeometryColumn:

• checking is done if older (version < 2.4) TRIGGERs exist that need to be replaced
• adding of TRIGGERs, based on Database version (Legacy, version ≥ 4.0), for the following events:
  
  • BEFORE INSERT
  • BEFORE UPDATE
  checking for valid geometry_type and srid as found in geometry_columns, using the internal function GeometryConstraints().

---

Note:
  These TRIGGERs are the cause of the 'violates Geometry constraint [geom-type or SRID not allowed]' errors
   (I find these 'I know what it is, but will not tell you' messages dreadful)
   In most cases they are caused by one (or more) of the following:

• an invalid geometry-type (a POLYGON instead of a MULTIPOLYGON, use CastToMulti() to resolve)
• an invalid dimension (a POINTXY instead of POINTZ), use CastToXYZ() to resolve)
• an invalid srid (0 instead of 4326, use SetSRID() to resolve)

   or all of the above.

With that, the OGC-SFS conditions for the srid, geometry_type and coord_dimension entries are complete.

---

The SpatialTable can now be filled with:

INSERT INTO admin_cities
(id_city, name_city, population_city, id_province, name_province, car_plate_code, id_region, name_region, geom_city)
SELECT
 -- the id of the City
 c.pro_com AS id_city,
 -- the name of the City
 c.comune AS name_city,
 -- Insure whole numbers ('Hanged, drawn and quartered' has been abolished, no longer need for quarter sums)
 CAST (c.pop_2011 AS INTEGER) AS population_city,
 -- id of Province the City belongs to
 c.cod_pro AS id_province,
 -- name of Province the City belongs to
 p.provincia AS name_province,
 -- The Car Plate of the Province the City belongs to
 p.sigla AS car_plate_code,
 -- id of region the Province belongs to
 c.cod_reg AS id_region,
 -- id of region the Province belongs to
 r.regione AS name_region,
 -- The Geometry ofthe City
 c.geometry AS geom_city
 -- contains the columns 'cod_pro' and 'cod_reg'
FROM com2011_s AS c
 -- contains the columns 'cod_pro' and 'cod_reg'
JOIN prov2011_s AS p USING (cod_pro)
 -- contains the column and 'cod_reg'
JOIN reg2011_s AS r USING (cod_reg);

---

Reminder:

SQLite does not permit:

• dropping columns is not supported.
• Starting with SQLite 3.25.0, the renaming of a column is supported.

Spatialite does allow you to revert what it created with AddGeometryColumn or RecoverGeometryColumn commands

With the DiscardGeometryColumn command, the entries in the geometry_columns TABLE and the created TRIGGERs will be removed,
  but the column 'geom_city', with its data in the 'admin_cities' TABLE will still exist.

---

Let us assume, you forgot to add 'Z' support to the geometries. The following would be possible:

-- remove the TRIGGERs checking for MULTIPOLYGON XY geometry-type
SELECT DiscardGeometryColumn('admin_cities','geom_city');
-- Cast to Z: no 'violates Geometry constraint [geom-type or SRID not allowed]' error
UPDATE admin_cities SET geom_city=CastToXYZ(geom_city);
-- redefine as the MULTIPOLYGON XYZ geometry-type (Z-Values are set to 0)
SELECT RecoverGeometryColumn('admin_cities', 'geom_city', 32632, 'MULTIPOLYGON', 'XYZ');
-- always a good idea [will fail if 'geom_city' is not properly registered]
SELECT UpdateLayerStatistics('admin_cities', 'geom_city');

---

The most effective way to clean up SpatialTables that are no longer needed is:

SELECT DropGeoTable('admin_cities');

This will have the same effect as a DROP TABLE IF EXISTS admin_cities;

• removing any entries in the geometry_columns TABLE and the created TRIGGERs
• removing any other dependencies (such as any registered SpatialViews)

---

Conclusions:

• SQLite commands should be avoided on SpatialTables, when Spatialite versions for the same task exist
• trust the Spatialite implementations, as you would (hopefully) avoid 'the reinvention of the Wheel' in your projects

---

Last, but not least:

  NEVER, EVER, 'play around' with these default settings !

and if you do, remember what

SELECT RecoverGeometryColumn('my_messed_up_table','my_messed_up_geometry',32632,'MULTIPOLYGON','XY');

was (also) designed to do.

Short answer:
    after a valid VIEW has been created with : CREATE VIEW and registered in the views_geometry_columns TABLE

---

For SQLite, a valid VIEW is considered to be:

• when used columns of the VIEW are defined. See SQLite: CREATE VIEW syntax explicitly declaring columns for further details

For Spatialite, a valid SpatialView is considered to be:

• when the geometry column of the underlining TABLE has been created with AddGeometryColumn or RecoverGeometryColumn
      thus, a geometry column cannot be dynamically created
  
• only 1 geometry column is supported. Any other geometry column, defined in the VIEW, will be treated as a BLOB
• a column representing the Primary-Key of the underlining TABLE containing the geometry column must be declared in the VIEW and as the view_rowid value in the views_geometry_columns entry

Conclusion: No joy will fall upon you when:

• the SpatialView is not (properly) registered in the views_geometry_columns TABLE
• your geometry column is dynamically created (ST_Transform(geometry,4326) would be a dynamically created geometry)
• the Primary-Key of the underlining TABLE is based on more than 1 column (and thus cannot be contained as a value of the view_rowid in the views_geometry_columns entry)
• the Primary-Key is defined as ROWID and the underlining TABLE has be defined with WITHOUT ROWID (otherwise considered a valid entry)
• the SpatialView has been defined as writable (i.e. readonly=0) and no corresponding TRIGGERs exist (or do not work correctly)

(end of Short answer)

---

Long answer:

A typical registration in the views_geometry_columns TABLE will look like this:

INSERT INTO views_geometry_columns
(view_name,view_geometry,view_rowid,f_table_name,f_geometry_column,read_only)
VALUES ('middle_earth_farthings','eur_polygon','id_rowid','middle_earth_polygons','eur_polygon',0);

• View-Name: Name used with the CREATE VIEW statement
• Geometry-Name: defined Column-Name in the VIEW
• Primary-Key: column representing the Primary-Key of the underlining TABLE
• underlining TABLE-Name: used in the FROM portion of the CREATE VIEW statement
• geometry column: used in the underlining TABLE
• readonly (0 or 1): defining if the VIEW is expected to be READONLY or if corresponing TRIGGERs exist for one or all of the following:
  • INSTEAD OF INSERT defining how DATA should be added to the underlining TABLE
  • INSTEAD OF UPDATE defining how DATA should be changed in the underlining TABLE
  • INSTEAD OF DELETE defining how DATA should be removed from the underlining TABLE
  
  Note: Reading applications (such as QGIS) should check for the existance of such TRIGGERs to determine the Adding, Modifing and Delete capabilities when readonly=0
      As of QGIS 3.1, when using the Spatialite-Provider: these TRIGGER checks are not being done, but run correctly when the TRIGGERs exist.
      As of Gdal-Ogr 2.4.0: writable SpatialViews are not supported. Will allways be treated as READONLY, despite existing TRIGGER support.
  

---

Sample Triggers for SpatialView Sample Triggers:

DROP VIEW IF  EXISTS 'middle_earth_farthings';
CREATE VIEW IF NOT EXISTS 'middle_earth_farthings'
(id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01,
 id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon) AS
 SELECT
  id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01,
  id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon
 FROM "middle_earth_polygons"
 WHERE
 ( -- 4=farthings, counties, provinces
  (admin_level IN (4))
 )
 ORDER BY name;

The following TRIGGERs support the SpatialView registered in the views_geometry_columns TABLE above:

---

  INSTEAD OF INSERT defining how DATA should be added to the underlining TABLE

CREATE TRIGGER IF NOT EXISTS 'vw_ins_middle_earth_farthings'
 INSTEAD OF INSERT ON "middle_earth_farthings"
BEGIN
 INSERT OR REPLACE INTO "middle_earth_polygons"
 (id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01,
  id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon)
 VALUES
 ( 
  NEW.id_rowid,
  NEW.id_admin,
  NEW.name,
  -- maps=1, continents=2, realms=3, farthings=4
  CASE WHEN NEW.rule_type IS NULL THEN 3 ELSE NEW.rule_type END,
  NEW.valid_since,
  NEW.valid_until,
  NEW.id_belongs_to,
  NEW.belongs_to_01,
  NEW.id_belongs_to_02,
  NEW.belongs_to_02,
  CASE WHEN NEW.admin_level IS NULL THEN 4 ELSE NEW.admin_level END,
  NEW.order_selected,
  CASE WHEN NEW.rule_text IS NULL THEN 'farthings' ELSE NEW.rule_text END,
  CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Length(ST_LinesFromRings(NEW.eur_polygon)) ELSE 0 END,
  CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Area(NEW.eur_polygon) ELSE 0 END,
  NEW.notes,
  NEW.text,
  CastToMultiPolygon(NEW.eur_polygon)
 );
END;

Notes:

• meters_length will be calculated using the Spatial-Functions ST_Length(ST_LinesFromRings(..)) and returns the length of the POLYGON External-Ring
• meters_area will be calculated using the Spatial-Function ST_Area and returns the area of the POLYGONs External-Ring minus the Internal-Rings
• admin_level if not overided, the default value for this VIEW (4) will be used
• eur_polygon will be casted to a MULTIPOLYGON, using the Spatial-Function CastToMultiPolygon

---

  INSTEAD OF UPDATE defining how DATA should be changed in the underlining TABLE

CREATE TRIGGER IF NOT EXISTS 'vw_upd_middle_earth_farthings'
 INSTEAD OF UPDATE OF
  id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01,
  id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon
 ON "middle_earth_farthings"
BEGIN
 UPDATE "middle_earth_polygons"
 SET
  id_rowid = NEW.id_rowid,
  id_admin = NEW.id_admin,
  name = NEW.name,
  admin_level = CASE WHEN NEW.admin_level IS NULL THEN 4 ELSE NEW.admin_level END,
  valid_since = NEW.valid_since,
  valid_until = NEW.valid_until,
  id_belongs_to = NEW.id_belongs_to,
  belongs_to_01 = NEW.belongs_to_01,
  id_belongs_to_02 = NEW.id_belongs_to_02,
  belongs_to_02 = NEW.belongs_to_02,
  order_selected = NEW.order_selected,
  -- maps=1, continents=2, realms=3, farthings=4
  rule_type = CASE WHEN NEW.rule_type IS NULL THEN 3 ELSE NEW.rule_type END,
  rule_text = CASE WHEN NEW.rule_text IS NULL THEN 'farthings' ELSE NEW.rule_text END,
  meters_length = CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Length(ST_LinesFromRings(NEW.eur_polygon)) ELSE 0 END,
  meters_area = CASE WHEN NEW.eur_polygon IS NOT NULL THEN ST_Area(NEW.eur_polygon) ELSE 0 END,
  notes = NEW.notes,
  text = NEW.text,
  eur_polygon = CastToMultiPolygon(NEW.eur_polygon)
 WHERE id_rowid = OLD.id_rowid;
END;

Different syntax, performing the same tasks as above,using the column representing the Primary-Key of the underlining TABLE in the WHERE portion of the statement.

---

  INSTEAD OF DELETE defining how DATA should be removed from the underlining TABLE

CREATE TRIGGER IF NOT EXISTS 'vw_del_middle_earth_farthings'
 INSTEAD OF DELETE ON middle_earth_farthings
BEGIN
 -- the primary key known to the view must be used !
 DELETE FROM middle_earth_polygons WHERE id_rowid = OLD.id_rowid;
END;

The following should be assumed :		Short summary for wise and prudent men Writable views implemented via Triggers are like an acrobat walking on a flimsy wire suspended over the deepest abyss. There are no safety margins at all, and even the slightest error will cause immediate death. Anyway an exceptionally able (and lucky) acrobat could sometimes survive unharmed. Alessandro Furieri, Firenze, Toscana spatialite-date: 7.4.17 [2015-08-07]
A Writable-SpatialView that has no TRIGGERs is	Useless
A TRIGGER that has not been properly tested is called a	¡¡¡ D I S A S T E R !!!
The slightest Syntax-Error can lead to	Hard to trace, unexpected behaviour
Since only the original author knows the purpose of the TRIGGER	No validity checks can be made
The name by which such peaple, who do not abide by these simple rules, are known by		shall not be named in the presence of Children or other persons of a sensitive nature