locations_list_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

location_id

varchar

2147483647

√

null

atd_txdot_locations.location_id

Implied Constraint

R

description

text

2147483647

√

null

council_district

int4

10

√

null

location_group

int2

5

√

null

total_est_comp_cost

int8

19

√

null

cr3_crash_count

int8

19

√

null

non_cr3_crash_count

int8

19

√

null

crash_count

int8

19

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH cr3_comp_costs AS (
         SELECT crashes_list_view.location_id,
            sum(crashes_list_view.est_comp_cost_crash_based) AS cr3_comp_costs_total
           FROM crashes_list_view
          WHERE (crashes_list_view.crash_timestamp > (now() - '5 years'::interval))
          GROUP BY crashes_list_view.location_id
        ), cr3_crash_counts AS (
         SELECT crashes.location_id,
            count(crashes.location_id) AS crash_count
           FROM crashes
          WHERE ((crashes.private_dr_fl = false) AND (crashes.location_id IS NOT NULL) AND (crashes.crash_timestamp > (now() - '5 years'::interval)))
          GROUP BY crashes.location_id
        ), non_cr3_crash_counts AS (
         SELECT atd_apd_blueform.location_id,
            count(atd_apd_blueform.location_id) AS crash_count,
            (count(atd_apd_blueform.location_id) * 10000) AS noncr3_comp_costs_total
           FROM atd_apd_blueform
          WHERE ((atd_apd_blueform.location_id IS NOT NULL) AND (atd_apd_blueform.date > (now() - '5 years'::interval)))
          GROUP BY atd_apd_blueform.location_id
        )
 SELECT locations.location_id,
    locations.description,
    locations.council_district,
    locations.location_group,
    COALESCE((cr3_comp_costs.cr3_comp_costs_total + non_cr3_crash_counts.noncr3_comp_costs_total), (0)::bigint) AS total_est_comp_cost,
    COALESCE(cr3_crash_counts.crash_count, (0)::bigint) AS cr3_crash_count,
    COALESCE(non_cr3_crash_counts.crash_count, (0)::bigint) AS non_cr3_crash_count,
    (COALESCE(cr3_crash_counts.crash_count, (0)::bigint) + COALESCE(non_cr3_crash_counts.crash_count, (0)::bigint)) AS crash_count
   FROM (((atd_txdot_locations locations
     LEFT JOIN cr3_crash_counts ON (((locations.location_id)::text = cr3_crash_counts.location_id)))
     LEFT JOIN non_cr3_crash_counts ON (((locations.location_id)::text = (non_cr3_crash_counts.location_id)::text)))
     LEFT JOIN cr3_comp_costs ON (((locations.location_id)::text = cr3_comp_costs.location_id)));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views