extension_crs_wkt.adoc

WKT for Coordinate Reference Systems

Introduction

The OGC GeoPackage standard was adopted prior to the adoption of "12-063r5 OGC Well known text representation of Coordinate Reference Systems" [I34], in 13 August, 2014. As a result, the OGC GeoPackage standard references an older document [I32] which has known ambiguities that are being encountered in the field. This extension establishes a new column to contain values that conform to the new standard. OGC later adopted "18-010r7 OGC Well known text representation of Coordinate Reference Systems" [I36], in 6 June, 2019 as a revision to 12-063r5.

Extension Author

GeoPackage SWG, author_name gpkg.

Extension Name or Template

gpkg_crs_wkt

Extension Type

Extension of Existing Requirement in clause [spatial_ref_sys_data_table_definition].

Applicability

Applies to the gpkg_spatial_ref_sys table.

Scope

Read-write

Requirements

Table Definition

gpkg_spatial_ref_sys

Requirement 115

For GeoPackages conforming to this extension, the gpkg_spatial_ref_sys table SHALL have an additional column called definition_12_063 as per Table 1 and gpkg_spatial_ref_sys Table Definition SQL (CRS WKT Extension).

Table 1. Spatial Ref Sys Table Definition

Column Name	Column Type	Column Description	NOT NULL flag	Key
srs_name	TEXT	Human readable name of this SRS	true
srs_id	INTEGER	Unique identifier for each Spatial Reference System within a GeoPackage	true	PK
organization	TEXT	Case-insensitive name of the defining organization e.g. EPSG or epsg	true
organization_coordsys_id	INTEGER	Numeric ID of the Spatial Reference System assigned by the organization	true
definition	TEXT	Well-known Text [I32] Representation of the Spatial Reference System	true
description	TEXT	Human readable description of this SRS	false
definition_12_063	TEXT	Well-known Text [I34] or [I36] Representation of the Spatial Reference System	true

Note	Previous versions of this extension specified default values for definition and definition_12_063. Those defaults have been removed for interoperability reasons but implementers should be aware that some GeoPackages may have these defaults in place.

Table Data Values

gpkg_extensions

Requirement 145

GeoPackages with a row in the gpkg_extensions table with an extension_name of "gpkg_crs_wkt" SHALL comply with this extension. GeoPackages complying with this extension SHALL have a row in the gpkg_extensions table as described in Table 2 (below).

Warning

Requirement 145 has been updated as part of GeoPackage 1.2.1. In 1.1.0 and 1.2.0, the table_name and column_name column values of the required gpkg_extensions row were inadvertently left unspecified. While the executable test suite running on an older GeoPackage version will not generate a failure due to missing gpkg_extensions column values, it is recommended to update these values to comply with the updated requirement on older versions as well.

Table 2. Extension Table Records

table_name	column_name	extension_name	definition	scope
gpkg_spatial_ref_sys	definition_12_063	gpkg_crs_wkt	see note below	read-write

Note

For the definition column, use a hyperlink that describes the current implementation of this extension. While a URL like http://www.geopackage.org/spec/#extension_crs_wkt is acceptable, permalinks to specific versions are provided upon publication using the URL pattern http://www.geopackage.org/specMmP/#extension_crs_wkt where M is the major version, m is the minor version, and P is the patch. For example http://www.geopackage.org/spec121/#extension_crs_wkt is the permalink for this extension for GeoPackage 1.2.1.

gpkg_spatial_ref_sys

Requirement 116

Values of the definition_12_063 column SHALL be constructed per the WKT syntax in [I34] and [I36].

Note	For backwards compatibility, only use common WKT values between 12-063 and 18-010. Additional WKT values in 18-010r7 can be used when required in the CRS definition.

Requirement 117

At least one definition column SHALL be defined with a valid definition unless the value of the srs_id column is 0 or -1. Both columns SHOULD be defined. If it is not possible to produce a valid [I32] definition then the value of the definition column MAY be undefined. If it is not possible to produce a valid [I34] or [I36] definition then the value of the definition_12_063 column MAY be undefined.

Note	If, for a particular row, both the definition and definition_12_063 columns are populated, the value in the definition_12_063 column takes priority.

Abstract Test Suite

Table Definition

Table Definition

Test Case ID	/extension_crs_wkt/table_def
Test Purpose	Verify that the gpkg_spatial_ref_sys table exists and has the correct definition. Extends /base/core/gpkg_spatial_ref_sys/data/table_def.
Test Method	PRAGMA table_info('gpkg_spatial_ref_sys') Fail if returns an empty result set Fail if result set does not include a column named 'definition_12_063' or if the column is not of type 'TEXT', notnull 1, and dflt_value 'undefined'. Pass if no failures.
Reference	Annex F.10 Req 115
Test Type	Basic

Table Data Values

Test Case ID	/extensions/crs_wkt/extensions/data_values
Test Purpose	Verify that the gpkg_extensions table has the required row.
Test Method	SELECT table_name, column_name, scope FROM gpkg_extensions WHERE extension_name = 'gpkg_crs_wkt'; Not testable if returns an empty result set Fail if there is not exactly one row Fail if scope is not "read-write" Fail if column_name is not "definition_12_063" Fail if table_name is not "gpkg_spatial_ref_sys" Pass if no fails
Reference	Annex F.10 Req 145
Test Type:	Capabilities

Test Case ID	/extension_crs_wkt/data_values_default
Test Purpose	Verify that the gpkg_spatial_ref_sys table contains the required default contents. Extends /base/core/gpkg_spatial_ref_sys/data_values_default.
Test Method	SELECT organization, organization_coordsys_id, definition, definition_12_063 FROM gpkg_spatial_ref_sys WHERE srs_id = -1 Confirm that this returns "NONE" -1 "undefined" "undefined" SELECT srs_id, organization, organization_coordsys_id, definition, definition_12_063 FROM gpkg_spatial_ref_sys WHERE srs_id = 0 Confirm that this returns "NONE" 0 "undefined" "undefined" SELECT definition FROM gpkg_spatial_ref_sys WHERE organization IN ("epsg","EPSG") AND organization_coordsys_id 4326 Confirm that this is a valid CRS SELECT definition_12_063 FROM gpkg_spatial_ref_sys WHERE organization IN ("epsg","EPSG") AND organization_coordsys_id 4326 Confirm that this is a valid 12-063 CRS Pass if tests 1-4 are met Fail otherwise
Reference	Annex F.10 Req 116
Test Type	Capability

Test Case ID	/extension_crs_wkt/data_values_required
Test Purpose	Verify that the spatial_ref_sys table contains rows to define all srs_id values used by features and tiles in a GeoPackage. Extends /base/core/gpkg_spatial_ref_sys/data_values_required.
Test Method	SELECT definition, definition_12_063 FROM gpkg_spatial_ref_sys WHERE srs_id NOT IN (0, -1) For each result Fail if both definition values are 'undefined' Pass if no failures
Reference	Annex F.10 Req 117
Test Type	Capability

Table Definition SQL

gpkg_spatial_ref_sys

gpkg_spatial_ref_sys Table Definition SQL (CRS WKT Extension)

CREATE TABLE gpkg_spatial_ref_sys (
  srs_name TEXT NOT NULL,
  srs_id INTEGER NOT NULL PRIMARY KEY,
  organization TEXT NOT NULL,
  organization_coordsys_id INTEGER NOT NULL,
  definition  TEXT NOT NULL,
  description TEXT,
  definition_12_063 TEXT NOT NULL
);