2a_features.adoc

Features

Simple Features SQL Introduction

Vector feature data represents geolocated entities including conceptual ones such as districts, real world objects such as roads and rivers, and observations thereof. International specifications [9][10][11][12] have standardized practices for the storage, access and use of vector geospatial features and geometries via SQL in relational databases. The first component of the SQL schema for vector features in a GeoPackage is the gpkg_spatial_ref_sys table defined in clause [spatial_ref_sys] above. Other components are defined below.

In a GeoPackage, “simple” features are geolocated using a linear geometry subset of the SQL/MM (ISO 13249-3) [12] geometry model shown in Core Geometry Model below.

Figure 1. Core Geometry Model

The instantiable (not abstract) geometry types defined in this Standard are restricted to 0, 1 and 2-dimensional geometric objects that exist in 2, 3 or 4-dimensional coordinate space (R2, R3 or R4). Geometry values in R2 have points with coordinate values for x and y. Geometry values in R3 have points with coordinate values for x, y and z or for x, y and m. Geometry values in R4 have points with coordinate values for x, y, z and m. The interpretation of the coordinates is subject to the coordinate reference systems associated to the point. All coordinates within a geometry object should be in the same coordinate reference systems.

Geometries MAY include z coordinate values. The z coordinate value traditionally represents the third dimension (i.e. 3D). In a Geographic Information System (GIS) this may be height above or below sea level. For example: A map might have a point identifying the position of a mountain peak by its location on the earth, with the x and y coordinate values, and the height of the mountain, with the z coordinate value.

Geometries MAY include m coordinate values. The m coordinate value allows the application environment to associate some measure with the point values. For example: A stream network may be modeled as multilinestring value with the m coordinate values measuring the distance from the mouth of stream.

All geometry types described in this standard are defined so that instances of Geometry are topologically closed, i.e. all represented geometries include their boundary as point sets. This does not affect their representation, and open version of the same classes MAY be used in other circumstances, such as topological representations.

A brief description of each geometry type is provided below. A more detailed description can be found in ISO 13249-3 [12].

• Geometry: the root of the geometry type hierarchy.

• Point: a single location in space. Each point has an X and Y coordinate. A point MAY optionally also have a Z and/or an M value.

• Curve: the base type for all 1-dimensional geometry types. A 1-dimensional geometry is a geometry that has a length, but no area. A curve is considered simple if it does not intersect itself (except at the start and end point). A curve is considered closed its start and end point are coincident. A simple, closed curve is called a ring.

• LineString: A Curve that connects two or more points in space.

• Surface: the base type for all 2-dimensional geometry types. A 2-dimensional geometry is a geometry that has an area.

• CurvePolygon: A planar surface defined by an exterior ring and zero or more interior ring. Each ring is defined by a Curve instance.

• Polygon: A restricted form of CurvePolygon where each ring is defined as a simple, closed LineString.

• GeometryCollection: A collection of zero or more Geometry instances. ^[1]

• MultiSurface: A restricted form of GeometryCollection where each Geometry in the collection must be of type Surface.

• MultiPolygon: A restricted form of MultiSurface where each Surface in the collection must be of type Polygon.

• MultiCurve: A restricted form of GeometryCollection where each Geometry in the collection must be of type Curve.

• MultiLineString: A restricted form of MultiCurve where each Curve in the collection must be of type LineString.

• MultiPoint: A restricted form of GeometryCollection where each Geometry in the collection must be of type Point.

Contents

Data

Contents Table – Features Row

The gpkg_contents table SHALL contain a row with a lowercase data_type column value of “features” for each vector features user data table or view.

Geometry Encoding

Data

BLOB Format

A GeoPackage SHALL store feature table geometries with or without optional elevation (Z) and/or measure (M) values in SQL BLOBs using the Standard GeoPackageBinary format specified in table GeoPackage SQL Geometry Binary Format and clause BLOB Format.

GeoPackage SQL Geometry Binary Format

GeoPackageBinaryHeader {
  byte[2] magic = 0x4750; (1)
  byte version;           (2)
  byte flags;             (3)
  int32 srs_id;
  double[] envelope;      (4)
}

StandardGeoPackageBinary {
  GeoPackageBinaryHeader header; (5)
  WKBGeometry geometry;          (6)
}

1. 'GP' in ASCII

2. 8-bit unsigned integer, 0 = version 1

3. see bit layout of GeoPackageBinary flags byte

4. see flags envelope contents indicator code below

5. The X bit in the header flags field must be set to 0.

6. per OGC 06-103r4 [9] ^[2][3][4]

Table 1. bit layout of GeoPackageBinary flags byte

bit	7	6	5	4	3	2	1	0
use	R	R	X	Y	E	E	E	B

flag bits use:

• R: reserved for future use; set to 0

• X: GeoPackageBinary type

  • 0: StandardGeoPackageBinary. See below

  • 1: ExtendedGeoPackageBinary. See [extension_geometry_encoding].

• Y: empty geometry flag

  • 0: non-empty geometry

  • 1: empty geometry

• E: envelope contents indicator code (3-bit unsigned integer)

  • 0: no envelope (space saving slower indexing option), 0 bytes

  • 1: envelope is [minx, maxx, miny, maxy], 32 bytes

  • 2: envelope is [minx, maxx, miny, maxy, minz, maxz], 48 bytes

  • 3: envelope is [minx, maxx, miny, maxy, minm, maxm], 48 bytes

  • 4: envelope is [minx, maxx, miny, maxy, minz, maxz, minm, maxm], 64 bytes

  • 5-7: invalid

• B: byte order for header values (1-bit Boolean)

  • 0: Big Endian (most significant byte first)

  • 1: Little Endian (least significant byte first)

Well-Known Binary as defined in OGC 06-103r4 [9] does not provide a standardized encoding for an empty point set (i.e., 'Point Empty' in Well-Known Text). In GeoPackages these points SHALL be encoded as a Point where each coordinate value is set to an IEEE-754 quiet NaN value. GeoPackages SHALL use big endian 0x7ff8000000000000 or little endian 0x000000000000f87f as the binary encoding of the NaN values.

When the WKBGeometry in a GeoPackageBinary is empty, either the envelope contents indicator code SHALL be 0 indicating no envelope, or the envelope SHALL have its values set to NaN as defined for an empty point.

SQL Geometry Types

Data

Core Types

A GeoPackage SHALL store feature table geometries with the basic simple feature geometry types (Geometry, Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeomCollection) in [geometry_types] [geometry_types_core] in the GeoPackageBinary geometry encoding format.

Geometry Columns

Data

Table Definition

A GeoPackage with a gpkg_contents table row with a “features” data_type SHALL contain a gpkg_geometry_columns table or updateable view per Geometry Columns Table or View Definition and [gpkg_geometry_columns_sql].

The second component of the SQL schema for vector features in a GeoPackage is a gpkg_geometry_columns table that identifies the geometry columns and geometry types in tables that contain user data representing features.

Table 2. Geometry Columns Table or View Definition

Column Name	Type	Description	Key
table_name	TEXT	Name of the table containing the geometry column	PK, FK
column_name	TEXT	Name of a column in the feature table that is a Geometry Column	PK
geometry_type_name	TEXT	Name from [geometry_types_core] or [geometry_types_extension] in [geometry_types]
srs_id	INTEGER	Spatial Reference System ID: gpkg_spatial_ref_sys.srs_id	FK
z	TINYINT	0: z values prohibited; 1: z values mandatory; 2: z values optional
m	TINYINT	0: m values prohibited; 1: m values mandatory; 2: m values optional

The FK on gpkg_geometry_columns.srs_id references the PK on gpkg_spatial_ref_sys.srs_id to ensure that geometry columns are only defined in feature tables for defined spatial reference systems.

Views of this table or view MAY be used to provide compatibility with the SQL/MM [12] [sqlmm_gpkg_geometry_columns_sql] and OGC Simple Features SQL [9][10][11] [sfsql_gpkg_geometry_columns_sql] specifications.

See [gpkg_geometry_columns_sql].

Table Data Values

The gpkg_geometry_columns table or updateable view SHALL contain one row record for the geometry column in each vector feature data table (clause Vector Feature User Data Tables) in a GeoPackage.

Values of the gpkg_geometry_columns table_name column SHALL reference values in the gpkg_contents table_name column for rows with a data_type of 'features'.

The column_name column value in a gpkg_geometry_columns row SHALL be the name of a column in the table or view specified by the table_name column value for that row.

The geometry_type_name value in a gpkg_geometry_columns row SHALL be one of the uppercase geometry type names specified in [geometry_types].

The srs_id value in a gpkg_geometry_columns table row SHALL be an srs_id column value from the gpkg_spatial_ref_sys table.

The z value in a gpkg_geometry_columns table row SHALL be one of 0, 1, or 2.

The m value in a gpkg_geometry_columns table row SHALL be one of 0, 1, or 2.

Vector Feature User Data Tables

Data

Table Definition

The third component of the SQL schema for vector features in a GeoPackage described in clause Simple Features SQL Introduction above are tables that contain user data representing features. Feature attributes are columns in a feature table, including geometries. Features are rows in a feature table. ^[5]

A GeoPackage MAY contain tables or updateable views containing vector features. Every such feature table or view in a GeoPackage SHALL have a column with column type INTEGER and 'PRIMARY KEY AUTOINCREMENT' column constraints per EXAMPLE : Sample Feature Table or View Definition and [example_feature_table_sql].

The integer primary key of a feature table allows features to be linked to row level metadata records in the gpkg_metadata table by rowid [B5] values in the gpkg_metadata_reference table as described in clause [_metadata_reference_table] below.

A feature table SHALL have only one geometry column.

Feature data models [B23] from non-GeoPackage implementations that have multiple geometry columns per feature table MAY be transformed into GeoPackage implementations with a separate feature table for each geometry type whose rows have matching integer primary key values that allow them to be joined in a view with the same column definitions as the non-GeoPackage feature data model with multiple geometry columns.

The declared SQL type of the geometry column in a vector feature user data table SHALL be the uppercase geometry type name from Annex E specified by the geometry_type_name column for that column_name and table_name in the gpkg_geometry_columns table.

Table 3. EXAMPLE : Sample Feature Table or View Definition

Column Name	Type	Description	Null	Default	Key
id	INTEGER	Autoincrement primary key	no		PK
geometry	GEOMETRY	GeoPackage Geometry	yes
text_attribute	TEXT	Text attribute of feature	yes
real_attribute	REAL	Real attribute of feature	yes
boolean_attribute	BOOLEAN	Boolean attribute of feature	yes
raster_or_photo	BLOB	Photograph of the area	yes

See [example_feature_table_sql].

Table Data Values

A feature geometry is stored in a geometry column specified by the lowercase geometry_column value for the feature table in the gpkg_geometry_columns table defined in clause [_geometry_columns] above.

The geometry type of a feature geometry column specified in the gpkg_geometry_columns table geometry_type_name column is a name from [geometry_types].

Feature table geometry columns SHALL contain geometries of the type or assignable for the type specified for the column by the gpkg_geometry_columns table geometry_type_name uppercase column value ^[6].

Geometry subtypes are assignable as defined in [geometry_types] and shown in part in Core Geometry Model. For example, if the geometry_type_name value in the gpkg_geometry_columns table is for a geometry type like POINT that has no subtypes, then the feature table geometry column MAY only contain geometries of that type. If the geometry type_name value in the gpkg_geometry_columns table is for a geometry type like GEOMCOLLECTION that has subtypes, then the feature table geometry column MAY only contain geometries of that type or any of its direct or indirect subtypes. If the geometry type_name is GEOMETRY (the root of the geometry type hierarchy) then the feature table geometry column MAY contain geometries of any geometry type.

The presence or absence of optional elevation (Z) and/or measure (M) values in a geometry does not change its type or assignability. The unit of measure for optional elevation(Z) values is determined by the CRS of the geometry; it is as-defined by a 3D CRS, and undefined for a 2D CRS. The unit of measure for optional measure (M) values is determined by the CRS of the geometry.

The spatial reference system type of a feature geometry column specified by a gpkg_geometry_columns table srs_id column value is a code from the gpkg_spatial_ref_sys table srs_id column.

Feature table geometry columns SHALL contain geometries with the srs_id specified for the column by the gpkg_geometry_columns table srs_id column value.

---

1. GeometryCollection is a generic term for the ST_GeomCollection type defined in [12], which uses it for the definition of Well Known Text (WKT) and Well Known Binary (WKB) encodings. The SQL type name GEOMCOLLECTION defined in [10] and used in Clause 1.1.2.1.1 and Annex E below refers to the SQL BLOB encoding of a GeometryCollection.

2. OGC WKB simple feature geometry types specified in [9] are a subset of the ISO WKB geometry types specified in [12]

3. WKB geometry types are are restricted to 0, 1 and 2-dimensional geometric objects that exist in 2, 3 or 4-dimensional coordinate space; they are not geographic or geodesic geometry types.

4. The axis order in WKB is always (x,y{,z}{,m}) where x is easting or longitude, y is northing or latitude, z is optional elevation and m is optional measure.

5. A GeoPackage is not required to contain any feature data tables. Feature data tables in a GeoPackage MAY be empty.

6. GeoPackage applications MAY use SQL triggers or tests in application code to meet this requirement