Skip to content

Latest commit

 

History

History
82 lines (51 loc) · 4.89 KB

README.md

File metadata and controls

82 lines (51 loc) · 4.89 KB
Raw

cjdb data model

cjdb data model is designed to store CityJSONL files in a Postgres database.

Table of Contents

1. Overview

2. Table Structure

3. Indexing

1. Overview

After reading the readme, the user will:

  1. Understand the table structure of cjdb model.

  2. Understand the indexing possiblities within the database, and their effects on operations.

2. Table Structure

The conceptual data model contains two main tables. The import_meta table for storing imported files' information, e.g. name or metadata of the source file. The cj_object table for storing city objects.

UML drawio

The physical data model adds one more table on the conceptual data model: the family table to store relations between city objects, e.g the parent-children relationship. This table is added to achieve higher querying speed when selecting objects by their parent/child relationship. Example of this would be: "give me all the objects which are children of X".

Physical Model drawio (3)

1. import_meta

The import_meta table stores information from imported files, e.g. name or metadata of the source file. Belowing section describes its attributes.

<id: import_meta record's index within the database.
source_file: name of the source file.
version: cityJSON version used.
metadata: cityJSON metadata object, a JSON object describing the creator, dataset extent or coordinate reference system used, etc.
transform:cityJSON transform object, a JSON object describing how to decompress the integer coordinates of the geometries to obtain real-world coordinates.
geometry_templates: cityJSON geometry-templates object, a JSON object containing the templates that can be reused by different City Objects (usually for trees).
srid: Coordinate reference system (CRS) of the imported city objects in the database. If not specified when importing, the CRS will be the same with the source file's CRS. If specified when importing, the CRS will be the specified CRS.
extensions: cityJSON Extensions, a JSON file that documents how the core data model of CityJSON is extended.
extra_properties: extraRootProperties, a JSON object with added new properties at the root of the imported document.
started_at: importing start time.
finished_at: importing finish time. null if not finished.
Bounding box: bounding box is taken from the geographicExtent object from the metadata section

2. cj_object

The cj_object model stores individual city objects, for instance buildings, roads, or bridges. Its attributes are described below. The attributes and geometry are seperated into two jsonb column for query optimization purpose.

id: city object's index within the database.
import_meta_id: the source file id of the city object, foriegn key to the id column of import_meta table.
object_id: the identification string of the city object (e.g. NL.IMBAG.Pand.0503100000000033-0).
type: type of the city object (e.g. building, buildingparts, etc.).
attributes:cityJSON attributes, a JSON object that describes attributes of the city object (e.g. roof type, area, etc.).
geometry: cityJSON geometry, a JSON object that describes the geometry of the city object.
ground_geometry: ground geometry of the city object, in geometry type.

3. Family

The family model stores the relations between city objects.

id: family object's index within the database.
parent_id: the identification id of the parent object.
child_id: the identification id of the child object.

3. Indexing

Indexing certain columns or expressions speeds up querying on them.

The users can use GIN index on lod, to speed up queries on geometries:

CREATE INDEX lod ON cjdb.cj_object USING GIN (geometry jsonb_path_ops);

The users can use B-tree partial index on attributes. It will slightly increase the query speed.