Delete `miovision_cursed`

[radumas]

The schema was renamed to miovision_cursed a while back to ensure that no one would use it.

Way back in 2021 Charles said we should delete it https://github.com/CityofToronto/bdit_data-sources/pull/407#issuecomment-857073314

[radumas]

Open Data views

Unfortunately the open_data views depend on it (not that they are being actively updated), but this is evidence that elements within it were used for reporting.

https://github.com/CityofToronto/bdit_data-sources/blob/b77ae5a93da27f291afe08899d7fbff3acdbe7dd/volumes/miovision/sql/open_data_views.sql

The main things being used there are the report_dates_view and the miovision_new.exceptions table. report_dates_view is a mat view and is committed.

Unfortunately miovision_new.exceptions isn't documented anywhere. Appears to list out time ranges for specific intersections and the reason to not use the data for that time. I am going to move it to miovision_csv and commit the code for it?

[radumas]

miovision_new.int_time_bin_avg is also being used by open_data.ksp_miovision_summary so am going to migrate it to miovision_csv

https://github.com/CityofToronto/bdit_data-sources/blob/6d01d093d1696770d87fb6ac584babdf89f1fc90/volumes/miovision/sql/csv_data/materialized-view_intersection_time_bin_avg.sql#L2

[radumas]

Kind of super weird that a bunch of stuff in the miovision_csv schema seems to be the old, uncommitted versions, but the king_pilot objects depend on miovision_new. I kind of want to replace them with their cursed versions

[radumas]

Report_daily & report_summary

-- View: miovision_csv.report_summary

DROP VIEW miovision_csv.report_summary;

DROP VIEW miovision_csv.report_daily;

ALTER VIEW miovision_cursed.report_daily SET SCHEMA miovision_csv;

CREATE OR REPLACE VIEW miovision_csv.report_summary
 AS
 SELECT report_daily.class_type,
    report_daily.street_main,
    report_daily.street_cross,
    report_daily.dir,
    report_daily.period_name,
    avg(
        CASE
            WHEN report_daily.period_type = 'Baseline'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS baseline,
    avg(
        CASE
            WHEN report_daily.period_type = 'Dec 2017'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS dec_17,
    avg(
        CASE
            WHEN report_daily.period_type = 'Jan 2018'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS jan_18,
    avg(
        CASE
            WHEN report_daily.period_type = 'Feb 2018'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS feb_18,
    avg(
        CASE
            WHEN report_daily.period_type = 'Mar 2018'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS mar_18,
    avg(
        CASE
            WHEN report_daily.period_type = 'Apr 2018'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS apr_18,
    avg(
        CASE
            WHEN report_daily.period_type = 'May 2018'::text THEN report_daily.total_volume
            ELSE NULL::numeric
        END) AS may_18
   FROM miovision_csv.report_daily
  GROUP BY report_daily.class_type, report_daily.intersection_uid, report_daily.street_main, report_daily.street_cross, report_daily.dir, report_daily.period_name;

ALTER TABLE miovision_csv.report_summary
    OWNER TO miovision_admins;

GRANT SELECT ON TABLE miovision_csv.report_summary TO bdit_humans WITH GRANT OPTION;
GRANT ALL ON TABLE miovision_csv.report_summary TO rds_superuser WITH GRANT OPTION;

ERROR:  view miovision_csv.report_daily depends on materialized view miovision_csv.report_volumes_15min
view miovision_csv.report_summary depends on view miovision_csv.report_daily
view miovision_csv.veh_tod depends on materialized view miovision_csv.report_volumes_15mincannot drop materialized view miovision_csv.report_volumes_15min because other objects depend on it 

[radumas]

WIP

ROLLBACK; BEGIN;

DROP VIEW miovision_csv.veh_tod;

DROP MATERIALIZED VIEW IF EXISTS miovision_csv.report_volumes_15min_all;

DROP MATERIALIZED VIEW miovision_csv.report_volumes_15min;

DROP MATERIALIZED VIEW miovision_csv.volumes_15min_by_class;

DROP MATERIALIZED VIEW miovision_csv.report_dates_view;

ALTER MATERIALIZED VIEW miovision_cursed.report_dates_view SET SCHEMA miovision_csv;

ALTER MATERIALIZED VIEW miovision_cursed.volumes_15min_by_class SET SCHEMA miovision_csv; -- View: miovision_csv.report_volumes_15min_all

-- View: miovision_csv.report_volumes_15min_all

-- DROP MATERIALIZED VIEW IF EXISTS miovision_csv.report_volumes_15min_all;

CREATE MATERIALIZED VIEW IF NOT EXISTS miovision_csv.report_volumes_15min_all TABLESPACE pg_default AS WITH valid_bins AS ( SELECT a_1.intersection_uid, a_1.class_type_id, a_1.dt + b_1.b::time without time zone AS datetime_bin, a_1.period_type FROM miovision_csv.report_dates a_1 CROSS JOIN generate_series('2017-01-01 00:00:00'::timestamp without time zone, '2017-01-01 23:45:00'::timestamp without time zone, '00:15:00'::interval) b_1(b) ORDER BY a_1.intersection_uid, a_1.class_type_id, (a_1.dt + b_1.b::time without time zone) ), int_avg AS ( SELECT volumes_15min_by_class.intersection_uid, volumes_15min_by_class.class_type_id, volumes_15min_by_class.dir, volumes_15min_by_class.leg, volumes_15min_by_class.period_type, volumes_15min_by_class.datetime_bin::time without time zone AS time_bin, avg(volumes_15min_by_class.total_volume) AS avg_volume FROM miovision_csv.volumes_15min_by_class GROUP BY volumes_15min_by_class.intersection_uid, volumes_15min_by_class.class_type_id, volumes_15min_by_class.period_type, volumes_15min_by_class.dir, volumes_15min_by_class.leg, (volumes_15min_by_class.datetime_bin::time without time zone) ) SELECT a.intersection_uid, a.period_type, a.datetime_bin, a.class_type_id, b.dir, b.leg, COALESCE(c.total_volume, b.avg_volume) AS volume FROM valid_bins a JOIN int_avg b USING (intersection_uid, class_type_id, period_type) LEFT JOIN miovision_csv.volumes_15min_by_class c USING (datetime_bin, intersection_uid, class_type_id, dir, leg, period_type) WHERE b.time_bin = a.datetime_bin::time without time zone ORDER BY a.intersection_uid, a.period_type, a.datetime_bin, a.class_type_id, b.dir, b.leg WITH DATA;

ALTER TABLE IF EXISTS miovision_csv.report_volumes_15min_all OWNER TO miovision_admins;

GRANT SELECT, REFERENCES, TRIGGER ON TABLE miovision_csv.report_volumes_15min_all TO bdit_humans WITH GRANT OPTION; GRANT ALL ON TABLE miovision_csv.report_volumes_15min_all TO rds_superuser WITH GRANT OPTION;

ALTER MATERIALIZED VIEW miovision_cursed.report_volumes_15min SET SCHEMA miovision_csv;

CREATE OR REPLACE VIEW miovision_csv.veh_tod AS SELECT b.street_main, b.street_cross AS street_agg, a.dir, a.datetime_bin::time without time zone AS time_bin, avg( CASE WHEN a.period_type = 'Baseline'::text THEN a.volume ELSE NULL::numeric END) AS baseline_vol, avg( CASE WHEN a.period_type = 'Baseline'::text THEN NULL::numeric ELSE a.volume END) AS pilot_vol FROM miovision_csv.report_volumes_15min a JOIN miovision_csv.intersections b USING (intersection_uid) WHERE a.class_type = 'Vehicles'::text AND (a.dir = 'EB'::text AND a.leg = 'W'::text AND b.street_cross <> 'Bathurst'::text OR a.dir = 'EB'::text AND a.leg = 'E'::text AND b.street_cross = 'Bathurst'::text OR a.dir = 'WB'::text AND a.leg = 'E'::text AND b.street_cross <> 'Jarvis'::text OR a.dir = 'WB'::text AND a.leg = 'W'::text AND b.street_cross = 'Jarvis'::text) AND (b.street_cross = ANY (ARRAY['Bathurst'::text, 'Spadina'::text, 'Bay'::text, 'Jarvis'::text])) GROUP BY b.street_main, b.street_cross, a.dir, (a.datetime_bin::time without time zone);

ALTER TABLE miovision_csv.veh_tod OWNER TO miovision_admins;

GRANT SELECT ON TABLE miovision_csv.veh_tod TO bdit_humans WITH GRANT OPTION; GRANT ALL ON TABLE miovision_csv.veh_tod TO rds_superuser WITH GRANT OPTION;

[radumas]

Thinking I should try to do a SQL backup of the two schemas and compare with what's committed to GitHub