extension_geometry_srsid_triggers.adoc

Appendix A: Geometry SRS ID Triggers Extension (Normative)

Extension Title

Geometry SRS_ID Triggers

Introduction

Geometry SRS_ID triggers prevent the storage of geometries with spatial reference system identifiers that are not specified in the gpkg_geometry_columns table in the geometry columns of the specified tables.

Extension Author

GeoPackage SWG, author_name gpkg.

Extension Name or Template

gpkg_srs_id_trigger

Extension Type

New Requirement dependent on clauses [gpb_format] and 3.1.2.

Applicability

This extension applies to any column specified in the gpkg_geometry_columns table.

Scope

Write-only

Requirements

The <t> and <c> template parameters in the SRS_ID trigger definition SQL template in Table xx below are to be replaced with the names of the feature table and geometry column being inserted or updated

GeoPackage

CREATE TRIGGER fgsi_<t> _<c> BEFORE INSERT ON '<t>' FOR EACH ROW
BEGIN
  SELECT RAISE (ABORT, 'insert on <t>violates constraint: ST_SRID(<c>) does not match gpkg_geometry_columns.srs_id value')
  WHERE (SELECT srs_id FROM gpkg_geometry_columns
         WHERE Lower(table_name) = Lower('<t>')
	       AND   Lower(column_name) = Lower('<c>')
	       AND   ST_SRID(NEW.'<c>') <> srs_id) ;
END

CREATE TRIGGER fgsu_<t>_<c> BEFORE UPDATE OF '<c>' ON '<t>' FOR EACH ROW
BEGIN
  SELECT RAISE (ABORT, 'update of <c> on <t> violates constraint: ST_SRID(<c>) does not match gpkg_geometry_columns.srs_id value')
  WHERE (SELECT srs_id FROM gpkg_geometry_columns
         WHERE Lower(table_name) = Lower('<t>')
	       AND   Lower(column_name) = Lower('<c>')
	       AND   ST_SRID(NEW.'<c>') <> srs_id);
END

GeoPackage SQLite Configuration

None

GeoPackage SQLite Extension

Definition of SQL functions

SQL Function	Description	Use	ST_SRID(geom. Geometry) : INTEGER

The SQL function on geometries in this SQLite Extension SHALL operate correctly on extended geometry types specified by Annex K and/or Annex L when those extensions are also implemented.