The QGEP Datamodel

This repository contains a bare QGEP datamodel. This is an SQL implementation of the VSA-DSS datamodel (including SIA405). It ships with SQL scripts required to setup an empty PostgreSQL/PostGIS database to use as basis for the QGEP project.

The latest release can be downloaded here: https://github.com/QGEP/datamodel/releases/

Ordinary data tables (od_)

These tables contain the business data. In these tables the information which is maintained by organizations can be found.

Value Lists (vl_)

These tables contain value lists which are referenced by od_ tables. The value lists contain additional information in different languages about the values.

Information Schema (is_)

These tables contain meta information about the schema.

Views (vw_)

The VSA-DSS model is built in an object relational way. Its PostgreSQL implementation does not make use of object inheritance and instead uses a pure relational approach. For base classes (like od_wastewater_structure) there are multiple child classes (like od_manhole or od_special_structure) which are linked with the same obj_id to the parent object.

For easier usage views are provided which give access to the merged attributes of child and parent classes. These views are prefixed with vw_ and all come with INSERT, UPDATE and DELETE rules which allow changing data directly on the view.

E.g. The view vw_manhole merges all the attributes of the tables od_manhole and od_wastewater_structure.

QGEP Views (vw_qgep_*)

These Views are handcrafted specifically for QGEP data entry. They normally join data from various tables. They also come with INSERT, UPDATE and DELETE rules but some attributes may be read-only (aggregated from multiple tables, calculated otherwise).

Functions

The functions are mainly used to create cached data required for symbology. They are often triggered for changes on specific tables and then executed only to update information on specific roles.

Installation instructions

Detailed instructions can be found in the QGEP documentation. This is only a short summary for reference.

Preparation:

Create new database (e.g. qgeptest)
Create a service in a pg_service definition (e.g. pg_qgep)

Installation:

export PG_SERVICE=pg_qgep
Run scripts/db_setup.sh

ponceta/datamodel