GeoPackage¶
This page describes the schema for publishing, storing or exchanging data that conforms to the OFDS data model as a GeoPackage. A GeoPackage is a SQLite database. This page provides an overview of the structure of an OFDS GeoPackage, and detailed definitions for each of the tables in the database.
The OFDS GeoPackage format is based on GeoPackage 1.4.0, including the GeoPackage Schema Extension and GeoPackage Related Tables Extension.
Template¶
The OFDS GeoPackage template implements the structure described on this page.
Tip
You can explore the structure of the OFDS GeoPackage template in common GIS tools such as QGIS, or you can connect directly to the SQLite database using your preferred SQL client.
Overview¶
The following diagram illustrates how the main entities and relationships in the OFDS data model are represented in an OFDS GeoPackage. Features (spatial entities) are coloured yellow, non-spatial entities are coloured blue, and associative tables (M:N relationships) are coloured grey. Only primary and foreign keys are shown in the diagram. For a complete description of the columns in each table, refer to the table definitions.
---
config:
layout: elk
---
erDiagram
contracts {
INTEGER id PK
INTEGER network_id FK
}
networks {
INTEGER id PK
}
nodes {
INTEGER id PK
INTEGER network_id FK
INTEGER phase FK
INTEGER supportingInfrastructure__owner FK
INTEGER transmissionMediumOwner FK
}
organisations {
INTEGER id PK
INTEGER network_id FK
}
phases {
INTEGER id PK
INTEGER network_id FK
}
relation_contracts_relatedPhases {
INTEGER base_id PK,FK
INTEGER related_id PK,FK
}
relation_nodes_networkProviders {
INTEGER base_id PK,FK
INTEGER related_id PK,FK
}
relation_spans_networkProviders {
INTEGER base_id PK,FK
INTEGER related_id PK,FK
}
relation_spans_wayleaves {
INTEGER base_id PK,FK
INTEGER related_id PK,FK
}
spans {
INTEGER cableType FK
INTEGER codeployment FK
INTEGER end FK
INTEGER id PK
INTEGER network_id FK
INTEGER phase FK
INTEGER start FK
INTEGER supplier FK
INTEGER supportingInfrastructure__owner FK
INTEGER transmissionMediumOwner FK
}
wayleaves {
INTEGER grantor FK
INTEGER id PK
INTEGER network_id FK
}
contracts }o--|| networks : "network_id"
relation_contracts_relatedPhases }o--|| contracts : "base_id"
nodes }o--|| networks : "network_id"
organisations }o--|| networks : "network_id"
phases }o--|| networks : "network_id"
spans }o--|| networks : "network_id"
wayleaves }o--|| networks : "network_id"
nodes }o--|| organisations : "supportingInfrastructure__owner"
nodes }o--|| organisations : "transmissionMediumOwner"
nodes }o--|| phases : "phase"
relation_nodes_networkProviders }o--|| nodes : "base_id"
spans }o--|| nodes : "end"
spans }o--|| nodes : "start"
relation_nodes_networkProviders }o--|| organisations : "related_id"
relation_spans_networkProviders }o--|| organisations : "related_id"
spans }o--|| organisations : "supplier"
spans }o--|| organisations : "supportingInfrastructure__owner"
spans }o--|| organisations : "transmissionMediumOwner"
wayleaves }o--|| organisations : "grantor"
relation_contracts_relatedPhases }o--|| phases : "related_id"
spans }o--|| phases : "phase"
relation_spans_networkProviders }o--|| spans : "base_id"
relation_spans_wayleaves }o--|| spans : "base_id"
relation_spans_wayleaves }o--|| wayleaves : "related_id"
classDef feature fill:#f3ffa6ff,stroke:#bbd034
classDef attribute fill:#cec7ffff,stroke:#110e27
classDef mapping fill:#efefefff,stroke:#434343ff
class nodes,spans feature
class networks,organisations,phases,contracts,wayleaves attribute
class relation_contracts_relatedPhases,relation_spans_networkProviders,relation_spans_wayleaves,relation_nodes_networkProviders mapping
direction BT
Features (spatial entities)¶
Nodes and spans are represented as features in Vector Feature User Data Tables, which contain both geometries and attributes.
Example: Nodes
Nodes are spatial entities with a Point geometry so they are represented as spatial features in the nodes vector feature user data table.
Node geometries are stored in the geom column of the nodes table in GeoPackage SQL Geometry Binary Format. Node attributes are stored in the other columns of the nodes table.
id |
name |
status |
|---|---|---|
1 |
Accra |
operational |
Non-spatial entities¶
Non-spatial entities, such as organisations, are represented as non-spatial attribute sets in Attributes User Data Tables, which contain only attributes and no geometries.
One-to-many relationships¶
One-to-many (1:N) relationships between entities in the OFDS data model, such as a network with many nodes, are represented as foreign key relationships. Attributes of type array in the OFDS data model, such as a span’s transmission medium are also represented as foreign key relationships.
Many-to-many relationships¶
Many-to-many (M:N) relationships, such as a node with many network providers, are represented as User-Defined Mapping Tables.
Codelists¶
Some attributes in the OFDS data model refer to codelists to limit and standardise the possible values of the attribute.
The representation of attributes that reference a codelist depends on whether the attribute takes a single value or an array of values from the codelist, and on whether the codelist is closed (i.e. the attributes value must belong to the codelist) or open (i.e. the attribute can take values that do not belong to the codelist):
Attribute data type |
Codelist type |
Representation |
|---|---|---|
Text |
Closed |
A column whose value is constrained to the codelist by an enum defined using the GeoPackage Schema Extension. |
Text |
Open |
A column with a foreign key relationship to a table containing the values in the codelist. |
Array |
Open or Closed |
An M:N relationship between the Vector Feature or Attributes User Data Table that represents the entity to which the attribute belongs, and a table containing the values in the codelist. |
Table definitions¶
Vector feature user data tables¶
Vector Feature User Data Tables represent spatial entities in the OFDS data model.
Attributes user data tables¶
Attributes User Data Tables represent non-spatial entities in the OFDS data model.
Codelist tables¶
Each codelist table has the following columns:
Name |
Type |
Not Null |
Auto Increment |
|---|---|---|---|
id |
INTEGER |
FALSE |
TRUE |
code |
TEXT |
FALSE |
FALSE |
description |
TEXT |
FALSE |
FALSE |
An OFDS GeoPackage includes the following codelist tables:
Table |
Codelist |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
User-defined mapping tables¶
Each user defined mapping table has the following columns:
Name |
Type |
Not Null |
Auto Increment |
|---|---|---|---|
base_id |
INTEGER |
TRUE |
TRUE |
related_id |
INTEGER |
TRUE |
TRUE |
An OFDS GeoPackage includes the following user-defined mapping tables:
Table |
base_id FK |
related_id FK |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|