Annex E: Draft GeoPackage Extension Template (Informative)

Extension Title

GeoPackage cslt_3dtiles

Introduction

Building upon work conducted in OGC Testbed 13, this extension will represent 3D Tiles within the GeoPackage specification and format. The 3D data is stored in GeoPackages using a relational data model in SQLite that mirrors the 3D Tiles spatial data structure. This extension will allow source 3D datasets such as OGC CDB, ESRI GDB, 3D Tiles, and DEMs to be used within a GeoPackage and displayed in web and mobile applications. External 3D datasets are processed into a 3D Tiles format for use with the GeoPackage extension.

Extension Author

Compusult Limited

Extension Name or Template

cslt_3dtiles

Extension Type

New requirement dependent on the release of 3D Tiles as an OGC standard.

Applicability

A 3D tileset is registered in a GeoPackage by adding a record to gpkg_contents with the new data_type of cslt_3dtiles.

Scope

read-write

Requirements

The cslt_3dtiles extension will allow for the ingestion and representation of a 3D Tile set inside a SQLite database.

3D Tiles Spec :  https://github.com/AnalyticalGraphicsInc/3d-tiles

GeoPackage

3D Tiles Core

gpkg_contents

Table Values

Requirement 1: A GeoPackage that complies with this extension SHALL contain a row in the gpkg_content table with a data_type of cslt_3dtiles for each 3D tileset.

Table 1. GeoPackage Contents Table Values

table_name

data_type

identifier

description

last_change

min_x

min_y

max_x

max_y

srs_id

Name of table defined in gpkg_extensions

cslt_3dtiles

*

*

*

*

*

*

*

4326

min_x,max_x,min_y,max_y can be derived from the bounding_volume  of the root tile

 

gpkg_extensions

Table Values

Requirement 2: For every gpkg_content row with a data_type of cslt_3dtiles, the GeoPackage SHALL have extension tables as seen below. The gpkg_extensions table SHALL contain rows as described in https://www.geopackage.org/spec120/index.html#_extensions

Table 2 . GeoPackage Extensions Table Values

table_name

column_name

extension_name

definition

scope

{table_name}

Null

cslt_3dtiles

TBD

read-write

{table_name}_tiles

Null

cslt_3dtiles

TBD

read-write

{table_name}_properties

Null

cslt_3dtiles

TBD

read-write

{table_name}_tile_resources

Null

cslt_3dtiles

TBD

read-write

 

 

Tilesets: {table_name}

Table Definition

Requirement 3: A GeoPackage that complies with this extension SHALL contain a tileset table as per the definition below. The table name can be any valid SQLite table name.

Table 3. Tilesets Table Definition

Column Name

Column Type

Column Description

Null

Default

Key

id

INTEGER

Auto incrementing primary key.

no

 

PK

version

TEXT

The 3D Tiles version. The version defines the base set of tile formats supported.

no

 

 

tileset_version

TEXT

Application-specific version of this tileset.

yes

 

 

gltf_up_axis

TEXT

Specifies the up-axis of glTF models contained in the tiles.

no

Y

 

geometric_error

REAL

The error, in meters, introduced if this tileset is not rendered. At runtime, the geometric error is used to compute Screen Space Error (SSE), i.e., the error measured in pixels.

no

0

 

is_root

TEXT

Indication of the root tileset defined by 'Y' or 'N'.

no

 

 

root_tile_id

INTEGER

 id from <table_name>_tiles.

no

 

FK

 

Tileset Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tileset.schema.json

 

Tilesets: {table_name}

Table Values

Requirement 4: A GeoPackage that complies with this extension SHALL have exactly one row in a tilesets table that has is_root = "Y" and a root_tile_id found in the id column of a tilesets_tiles table.

Tiles: {table_name}_tiles

Table Definition

Requirement 5: A GeoPackage that complies with this extension SHALL for each tilesets table have a corresponding table of {table_name}_tiles.

Table 4. Tiles Table Definition

Column Name

Column Type

Column Description

Null

Default

Key

Id

INTEGER

Auto incrementing primary key.

no

 

PK

parent_tile_id

INTEGER

Value from the id column of this table representing the parent tile of the current tile.

no

 

FK

bounding_volume

TEXT

The bounding volume that encloses the tile.

no

 

 

viewer_request_volume

TEXT

Optional bounding volume that the viewer must be contained within before the tile's content will be requested, and before the tile will be refined based on geometricError.

yes

 

 

geometric_error

REAL

The geometric error, in meters, introduced if this tile is rendered and its children are not. At runtime, the geometric error is used to compute Screen Space Error (SSE), i.e., the error measured in pixels.

no

 

 

refine

TEXT

Specifies if the additive or replacement refinement is used when traversing the tileset for rendering. This property is required for the root tile of a tileset. It is optional for all other tiles. The default is to inherit from the parent tile.

yes

 

 

transform

TEXT

Represented in JSON array notation.

A floating-point 4x4 affine transformation matrix, stored in column-major order, that transforms the tile's content (i.e., its features as well as content_bounding_volume, bounding_volume, and viewer_request_volume) from the tile's local coordinate system to the parent tile's coordinate system, or, in the case of a root tile, from the tile's local coordinate system to the tileset's coordinate system. This transform does not apply to geometric_error, nor does it apply any volume property when the volume is a region, defined in WGS84 / EPSG:4326 coordinates.

yes

 

 

content_bounding_volume

TEXT

An optional bounding volume that tightly encloses just the tile's contents. Used for replacement refinement,   content_bounding_volume enables tight view frustum culling. When omitted, the bounding_volume is used.

yes

 

 

content_tileset_id

INTEGER

The id referencing a record in this table with content_data.

yes

 

FK

content_data

BLOB

The tile content supported by the core 3D Tiles spec. This can be any of the following formats;

Batched 3D Model (*.b3dm), Instanced 3D Model (*.i3dm), Composite (*.cmpt)

yes

 

 

 

Tile Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tile.schema.json

Tile Content Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tile.content.schema.json

Bounding Volume Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/boundingVolume.schema.json

 

Tileset Properties: {table_name}_properties

Table Definition

Requirement 6: A GeoPackage that complies with this extension SHALL, for each tileset table, have a corresponding table of {table_name}_properties.

Table 5. Tiles Properties Definition

Column Name

Column Type

Column Description

Null

Default

Key

tileset_id

INTEGER

The id found in associated tilesets table.

no

 

FK

name

TEXT

A unique name per tileset_id of the property being specified.

no

 

 

minimum

REAL

The minimum value of this property of all the features in the tileset.

no

 

 

maximum

REAL

The maximum value of this property of all the features in the tileset.

no

 

 

 

Tileset Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tileset.schema.json

Properties Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/properties.schema.json

 

Tilesets Tile Resources : {table_name}_tile_resources

Table Definition

Requirement 7: A GeoPackage that complies with this extension SHALL, for each tileset table, have a corresponding table of {table_name}_tile_resources. This table shall store the content referenced by the glTF to reduce duplication and to eliminate external dependencies. URI references need to be updated inside of a glTF to reference the tile_resources table describing the id and table_name to retrieve the content.

Table 6. Tile Resources Definition

Column Name

Column Type

Column Description

Null

Default

Key

id

INTEGER

Auto increment primary key.

no

 

PK

data

BLOB

Binary data representing a URI referenced resource.

no

 

 

 

glTF URIs: https://github.com/KhronosGroup/glTF/tree/master/specification/2.0#uris

 

GeoPackage SQLite Configuration

None

GeoPackage SQLite Extension

None

Abstract Test Suite

A.1. Common Requirements

A.1.1. Requirement 1

Test Id:

 

Requirement:

Requirement 1

Test purpose:

Verify that the gpkg_contents table has at least one cslt_3dtiles entry.

Test method:

SELECT COUNT(*) FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

- Extension not in use if count is empty.

 

A.1.2. Requirement 2

Test Id:

 

Requirement:

Requirement 2

Test purpose:

Verify whether the Related Tables Extension is in use within a GeoPackage.

Test method:

SELECT COUNT(*) FROM gpkg_extensions WHERE extension_name = 'cslt_3dtiles'

 

- Extension not in use if count is empty.

 

Test Id:

 

Requirement:

Requirement 2

Test purpose:

Verify whether the GeoPackage contains at least one cslt_3dtiles content layer set.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

 

- Fail if number of rows returned is 0.

 

2. For each table_name row returned in Step 1:

- SELECT column_name, scope, table_name FROM gpkg_extensions WHERE table_name IN ('{table_name}', '{table_name}_tiles', '{table_name}_properties', '{table_name}_tile_resources');

 

- Fail if number of rows returned is not equal to 4.

 

- For each row returned in Step 2, fail if column_name is not null or scope is not "read-write".

 

3. For each table_name row returned in Step 3:

- SELECT tbl_name FROM sqlite_master WHERE tbl_name = '{table_name}';

- Fail if no rows returned.

- Pass if no fails.

A.1.3 Requirement 3

Test Id:

 

Requirement:

Requirement 3

Test purpose:

Verify that the tilesets tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

 

2. For each table_name row returned in Step 1:

- PRAGMA table_info(table_name)

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tilesets Table Definition in Requirement 3. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

-Fail otherwise.

 

Test Id:

 

Requirement:

Requirement 3

Test purpose:

Verify that the tilesets tables have valid values.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

 

2. For each table_name row returned in Step 1:

- SELECT geometric_error, root_tile_id FROM table_name;

 

3. For each row in Step 2:

- Fail if geometric_error is null or < 0.

- Fail if root_tile_id is null.

- Fail if version is null.

- Fail if glTFUpAxis is null or not in ["X", "Y", "Z"].

 

Test Id:

 

Requirement:

Requirement 3

Test purpose:

Verify that the tilesets tables have valid tile relationships.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

 

2. For each table_name row returned in Step 1:

- SELECT root_tile_id FROM table_name;

 

3. For each root_tile_id in Step 2:

- SELECT COUNT(*) FROM {table_name}_tiles WHERE id = root_tile_id;

- Pass if rows returned equal to 1.

- Fail otherwise.

A.1.4. Requirement 4

Test Id:

 

Requirement:

Requirement 4

Test purpose:

Verify that the tilesets tables have exactly one root tileset.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

2. For each table_name row returned:

- SELECT COUNT(*) FROM table_name WHERE is_root = 'Y';

- Pass if rows returned equal to 1.

- Fail otherwise.

A.1.5. Requirement 5

Test Id:

 

Requirement:

Requirement 5

Test purpose:

Verify that the tileset_tiles tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

 

2. For each table_name row returned in Step 1:

- PRAGMA table_info('<table_name>_tiles')

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tileset Tiles Table Definition in Requirement 5. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

- Fail otherwise.


Test Id:

 

Requirement:

Requirement 5

Test purpose:

Verify that the tileset_tiles tables have valid table values.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

 

2. For each table_name row returned in Step 1:

- SELECT * FROM {table_name}_tiles;

 

3. For each row in Step 2:

- Fail if bounding_volume is null.

- Fail if bounding_volume, viewer_request_volume, content_bounding_volume are not null and do not match the schema.

https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/boundingVolume.schema.json

- Fail if refine is not null and does not match ["ADD", "REPLACE"].

- Fail if geometric_error < 0.

- Fail if transform is not a JSON array with 16 items.

- Fail if content_data is not null and does not represent a valid 3dtile format.

https://github.com/AnalyticalGraphicsInc/3d-tiles/tree/master/TileFormats

 

 

Test Id:

 

Requirement:

Requirement 5

Test purpose:

Verify that the tileset_tiles tables have valid parent and tileset relationships.

Test method:

1.  SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

 

2. For each table_name row returned in Step 1:

- SELECT parent_tile_id, content_tileset_id FROM {table_name}_tiles;

 

3. For each row in Step 2 with parent_tile_id != null:

- SELECT COUNT(*) FROM {table_name}_tiles WHERE id =  parent_tile_id;

- Fail if rows returned not equal to 1.

 

4. For each row in Step 2 with content_tileset_id != null:

- SELECT COUNT(*) FROM {table_name} WHERE id =   content_tileset_id;

- Fail if rows returned not equal to 1.

A.1.6. Requirement 6

Test Id:

 

Requirement:

Requirement 6

Test purpose:

Verify that the tileset_properties tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- PRAGMA table_info('<table_name>_properties')

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tileset Properties Table Definition in Requirement 6. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

- Fail otherwise.

A.1.7. Requirement 7

Test Id:

 

Requirement:

Requirement 7

Test purpose:

Verify that the tileset_tile_resources tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- PRAGMA table_info('<table_name>_tile_resources')

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tileset Tile Resources Table Definition in Requirement 7. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

- Fail otherwise.

Table Definition SQL

Tilesets Table Definition SQL (Normative)

CREATE TABLE tilesets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
version TEXT NOT NULL,
tileset_version TEXT,
glTF_up_axis TEXT DEFAULT 'Y',
geometric_error REAL DEFAULT 0 NOT NULL,
is_root TEXT NOT NULL,
root_tile_id INTEGER NOT NULL,
CONSTRAINT fk_tilesets_root_tile_id FOREIGN KEY (root_tile_id)
REFERENCES tilesets_tiles (id)
);

 

Tileset Tiles Table Definition SQL (Normative)

CREATE TABLE tilesets_tiles (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 parent_tile_id INTEGER,
 bounding_volume TEXT NOT NULL,
 viewer_request_volume TEXT,
 geometric_error REAL DEFAULT 0 NOT NULL,
 refine TEXT,
 transform DEFAULT '[ 1.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 0.0, 1.0 ]' TEXT,
 content_bounding_volume TEXT,
 content_tileset_id INTEGER,
 content_data BLOB,
 CONSTRAINT fk_tilesets_tiles_parent_id FOREIGN KEY (parent_tile_id)
 REFERENCES tilesets_tiles (id),
 CONSTRAINT fk_tilesets_tiles_ts_id FOREIGN KEY (content_tileset_id)
 REFERENCES tilesets (id)
 );

Tileset Tile Properties Table Definition SQL (Normative)

CREATE TABLE tilesets_properties (
tileset_id INTEGER,
name TEXT NOT NULL,
minimum REAL NOT NULL,
maximum REAL NOT NULL,
CONSTRAINT fk_tilesets_properties_ts_id FOREIGN KEY (tileset_id)
REFERENCES tilesets (id), UNIQUE (tileset_id, name)
);

Tileset Tile Resources Table Definition SQL (Normative)

CREATE TABLE tilesets_tile_resources (
id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB NOT NULL
);