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 standards [I9][I10][I11][I12] 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) [I12] geometry model shown in Figure 1 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.

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

• 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. ^[K8]

• 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

Requirement 18

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

Requirement 19

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;           (4)
  double[] envelope;      (5)
}

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

1. 'GP' in ASCII

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

3. see bit layout of GeoPackageBinary flags byte

4. the SRS ID, with the endianness specified by the byte order flag

5. see envelope contents indicator code below, with the endianness specified by the byte order flag

6. per OGC 06-103r4 [I9] ^{[K9] [K10]}

Warning

The axis order in WKB stored in a GeoPackage follows the de facto standard for axis order in WKB and is therefore 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. This ordering explicitly overrides the axis order as specified in the SRS metadata, applying Case 4 from OGC 08-038r7, Revision to Axis Order Policy and Recommendations[K11]. This was done to maintain consistency with previous implementations of WKB that predated the OGC policy.

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. For all core and extended geometry types. See [geometry_types].

  • 1: ExtendedGeoPackageBinary. For user-defined geometry types. 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 SRS_ID and envelope values in header (1-bit Boolean)

  • 0: Big Endian (most significant byte first)

  • 1: Little Endian (least significant byte first)

Warning

The endianness specified by the B flag technically only pertains to header values, and not to the endianness of the WKBGeometry values themselves. The WKBGeometry has its own endianness flag as described in OGC 06-103r4. These values SHOULD be the same for consistency reasons.

Requirement 152

When the WKBGeometry in a GeoPackageBinary is empty, the following SHALL be done:

1. The empty geometry flag SHALL be set.

2. The envelope contents indicator code SHALL be 0 indicating no envelope.

3. If the geometry is a Point, it SHALL be encoded with each coordinate value 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. (This is because Well-Known Binary as defined in OGC 06-103r4 [I9] does not provide a standardized encoding for an empty point set, i.e., 'Point Empty' in Well-Known Text.)

Warning

In GeoPackage versions 1.2.1 and prior, the requirements for handling an empty geometry were underspecified. Some otherwise compliant GeoPackages before version 1.3.0 may not comply with all three sub-requirements. This may cause false negative results when querying for empty geometries.

SQL Geometry Types

Data

Core Types

Requirement 20

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

Geometry Columns

Data

Table Definition

Requirement 21

A GeoPackage with a gpkg_contents table row with a "features" data_type SHALL contain a gpkg_geometry_columns table per Table 1 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 Definition

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

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 MAY be used to provide compatibility with the SQL/MM [I12] [sqlmm_gpkg_geometry_columns_sql] and OGC Simple Features SQL [I9][I10][I11] [sfsql_gpkg_geometry_columns_sql] specifications.

See [gpkg_geometry_columns_sql].

Table Data Values

Requirement 22

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

Requirement 23

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'.

Requirement 24

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.

Requirement 25

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

Requirement 26

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.

Requirement 146

The srs_id value in a gpkg_geometry_columns table row SHALL match the srs_id column value from the corresponding row in the gpkg_contents table.

Requirement 27

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

Requirement 28

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. ^[K12]

Requirement 29

A GeoPackage MAY contain tables containing vector features. Every such feature table SHALL be structured consistently with Table 2 and [example_feature_table_sql]. A feature table SHALL have a primary key column of type INTEGER and that column SHALL act as a rowid alias.

Note	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.

Requirement 150

A GeoPackage MAY contain views containing vector features. Every such feature view SHALL have a first column of type INTEGER and that column SHALL contain unique values for each row.

Note	Since the concept of primary keys does not exist for views in SQLite, this requirement provides a way to produce a compliant feature view with a discoverable key-like column[K17].

Requirement 30

A feature table or view SHALL have only one geometry column.

Note

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.

Requirement 31

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

Table 3. Sample Feature Table Definition

Column Name	Type	Description	Null	Default	Key
id	INTEGER	Autoincrement[K6a] primary key	N/A[K6b]		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 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].

Requirement 32

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 ^[K13].

Allowed geometry types are defined in [geometry_types] and shown in part in Figure 1. If the geometry type_name value is "GEOMETRY" then the feature table geometry column MAY contain geometries of any allowed geometry type. This type name is to be used when the geometry type is unknown or if multiple geometry types will be present in the same table (e.g., POLYGON and MULTIPOLYGON). If the geometry type_name value is "GEOMETRYCOLLECTION" then the feature table geometry column MAY contain geometries of type GeometryCollection containing zero or more geometries of any allowed 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.

Requirement 33

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.