vw_tww_damage_channel

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

comments

text

2147483647

√

null

connection

int4

√

null

fk_dataowner

varchar

√

null

fk_examination

varchar

√

null

fk_provider

varchar

√

null

last_modification

timestamp

29,6

√

null

line_damage

text

2147483647

√

null

single_damage_class

int4

√

null

video_counter

text

2147483647

√

null

view_parameters

text

2147483647

√

null

obj_id

varchar

√

tww_app.generate_oid('tww_od'::text, 'damage_channel'::text)

channel_damage_begin

int2

√

null

channel_damage_code

int4

√

null

channel_damage_end

int2

√

null

channel_distance

numeric

7,2

√

null

channel_quantification1

int4

√

null

channel_quantification2

int4

√

null

ws_identifier

text

2147483647

√

null

situation2d_geometry

geometry

2147483647

√

null

direction

text

2147483647

√

null

tww_is_primary

bool

√

null

picture_1

text

2147483647

√

null

picture_2

text

2147483647

√

null

video

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH base AS (
         SELECT dg.obj_id,
            dg.comments,
            dg.connection,
            dg.fk_dataowner,
            dg.fk_examination,
            dg.fk_provider,
            dg.last_modification,
            dg.line_damage,
            dg.single_damage_class,
            dg.video_counter,
            dg.view_parameters,
            dc.channel_damage_begin,
            dc.channel_damage_code,
            dc.channel_damage_end,
            dc.channel_distance,
            dc.channel_quantification1,
            dc.channel_quantification2,
            ws.identifier AS ws_identifier,
            ch.progression2d_geometry AS ch_progression2d_geometry,
                CASE
                    WHEN ((ch.fk_reach_point_to)::text = (ex.fk_reach_point)::text) THEN 'upstream'::text
                    WHEN ((ch.fk_reach_point_from)::text = (ex.fk_reach_point)::text) THEN 'downstream'::text
                    ELSE 'unknown'::text
                END AS direction,
            ch.tww_is_primary
           FROM (((((tww_od.damage_channel dc
             LEFT JOIN tww_od.damage dg ON (((dg.obj_id)::text = (dc.obj_id)::text)))
             LEFT JOIN tww_od.examination ex ON (((ex.obj_id)::text = (dg.fk_examination)::text)))
             LEFT JOIN tww_od.re_maintenance_event_wastewater_structure mews ON (((mews.fk_maintenance_event)::text = (ex.obj_id)::text)))
             LEFT JOIN tww_od.wastewater_structure ws ON (((mews.fk_wastewater_structure)::text = (ws.obj_id)::text)))
             LEFT JOIN tww_app.mvw_tww_channel ch ON (((ch.obj_id)::text = (ws.obj_id)::text)))
          WHERE (ex.recording_type = 3686)
        ), damage_pictures AS (
         SELECT dc.obj_id,
            array_agg(COALESCE((dm.path || fi.path_relative), fi.path_relative)) OVER w AS pics
           FROM (((((tww_od.file fi
             JOIN tww_od.damage_channel dc ON ((fi.object = (dc.obj_id)::text)))
             JOIN tww_od.damage dg ON (((dg.obj_id)::text = (dc.obj_id)::text)))
             LEFT JOIN tww_od.examination ex ON (((dg.fk_examination)::text = (ex.obj_id)::text)))
             LEFT JOIN tww_od.maintenance_event me ON (((me.obj_id)::text = (ex.obj_id)::text)))
             LEFT JOIN tww_od.data_media dm ON (((dm.obj_id)::text = (fi.fk_data_media)::text)))
          WHERE ((fi.tww_outdated IS NOT TRUE) AND (fi.kind = 3772))
          WINDOW w AS (ORDER BY fi.obj_id DESC)
        ), examination_videos AS (
         SELECT dc.obj_id,
            first_value(COALESCE((dm.path || fi.path_relative), fi.path_relative)) OVER w AS video
           FROM (((((tww_od.file fi
             JOIN tww_od.examination ex ON ((fi.object = (ex.obj_id)::text)))
             JOIN tww_od.maintenance_event me ON (((me.obj_id)::text = (ex.obj_id)::text)))
             RIGHT JOIN tww_od.damage dg ON (((dg.fk_examination)::text = (ex.obj_id)::text)))
             JOIN tww_od.damage_channel dc ON (((dg.obj_id)::text = (dc.obj_id)::text)))
             LEFT JOIN tww_od.data_media dm ON (((dm.obj_id)::text = (fi.fk_data_media)::text)))
          WHERE ((fi.tww_outdated IS NOT TRUE) AND (ex.tww_outdated IS NOT TRUE) AND (fi.kind = ANY (ARRAY[3775, 9146])))
          WINDOW w AS (PARTITION BY ex.fk_reach_point ORDER BY me.time_point DESC)
        )
 SELECT base.comments,
    base.connection,
    base.fk_dataowner,
    base.fk_examination,
    base.fk_provider,
    base.last_modification,
    base.line_damage,
    base.single_damage_class,
    base.video_counter,
    base.view_parameters,
    base.obj_id,
    base.channel_damage_begin,
    base.channel_damage_code,
    base.channel_damage_end,
    base.channel_distance,
    base.channel_quantification1,
    base.channel_quantification2,
    base.ws_identifier,
    st_lineinterpolatepoint(base.ch_progression2d_geometry,
        CASE
            WHEN (base.direction = 'downstream'::text) THEN LEAST(((base.channel_distance)::double precision / st_length(base.ch_progression2d_geometry)), (1)::double precision)
            ELSE ((1)::double precision - LEAST(((base.channel_distance)::double precision / st_length(base.ch_progression2d_geometry)), (1)::double precision))
        END) AS situation2d_geometry,
    base.direction,
    base.tww_is_primary,
    damage_pictures.pics[1] AS picture_1,
    damage_pictures.pics[2] AS picture_2,
    examination_videos.video
   FROM ((base
     LEFT JOIN damage_pictures ON (((damage_pictures.obj_id)::text = (base.obj_id)::text)))
     LEFT JOIN examination_videos ON (((examination_videos.obj_id)::text = (base.obj_id)::text)));

vw_tww_damage_channel

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views