Vai al contenuto

Table: transport_system

Description (EN): This table represents transport network systems. In the database's inheritance architecture, it acts as a direct sub-type of the network_system table. It does not store redundant descriptive columns; instead, it uses a shared primary key to inherit all high-level attributes (such as name, location, management type, and geometry) from its parent network_system, which on turn is a child of a enterprise record.

Column Structure

Column Data type Definition Example value Constraint? Geometry? Comments
id BIGINT Unique identifier for the transport network system 8 PK, FK No Primary key that also acts as a Foreign Key referencing network_system.id and enterprise.id

Relationships

  • Inherits from (Sub-type of): network_system. The electrical_system table is a specialized extension of the network_system table.
  • Contains: While not linked directly via a transport_system_id, a transport system owns infrastructural assets through the enterprise_id column in the component table. These components often include specialized sub-types such as:
    • transport_network_component

!!! tip "Where is the rest of the data?" To find the name, geographical location (geometry, town, country), or ownership details of a network system, you must join this table with the enterprise record that shares the exact same id.

Example Query

Retrieve a list of all electrical systems, including their names, towns, and spatial coordinates, by joining the table with its parent network_system - enterprise entity:

```sql SELECT tr.id, e.name, e.town, ST_AsText(e.geometry) as coordinates FROM transport_system tr JOIN enterprise e ON tr.id = e.id WHERE e.type = 'TRANSPORT';