-- Current Bike Network VIEW

SELECT
a.SHAPE, b.OBJECTID, a.STREETPREFIX, a.STREETNAME, a.STREETTYPE, a.STREETSUFFIX, a.EX_LTS, a.BMP_LTS, b.MASTERID, b.SEGTYPE, b.FACILITY, b.FACILITY_CLASS, b.BIKEWAY, b.BREEZEWAY, b.BMP_STATUS, b.BIKEWAY_TYPE, b.BIKEWAY_STREET, b.BIKEWAY_FROM, b.BIKEWAY_TO, b.MASTERPLAN, b.ADTDATE, b.ADT, b.ADT_SOURCE, b.ADT_EYEBAL, b.SPEEDLIMIT, b.ACTUALLANES, b.APPARENTLANES, b.PKLN, b.PARKBIKELNWIDTH, b.PARKTRNOVER, b.INDUSTST, b.RESIDENTIAL, b.DRIVEWAY, b.BLOCKAGE, b.MEDIANREFUGE, b.XINGLANES, b.XINGSPEED, b.BUFFERTYPE, b.BUFFERWIDTH, b.BIKELANEWIDTH, b.BKLN_DROP, b.IMPLEMENT, b.NOTES, b.LTS, b.LTS_TEXT, b.LTS_FURTH, b.LTS_RATION, b.ERROR, b.POLICY_AREA, b.BIPPA, b.SUBPAR, b.FACILITY_YEAR, b.TIER, b.CENTERLINE, b.INTCONTROL, b.BIKEABLESIDEWALK, b.EFFECTIVEDATE, b.RETIREDATE, c.PROJECT_NAME as BIKE_PROJECT_NAME, c.PROJECT, c.PROJECT_FROM, c.PROJECT_TO, c.PROJECT_BIKEWAY_TYPE, d.INFRASTRUCTURE_NAME, d.INFRASTRUCTURE_TYPE, e.PROJECTTYPE, e.STATUS, e.PROJECT_NAME, e.BIKEWAY AS IMPLEBIKEWAY, e.PRELIM_PLAN, e.SITE_PLAN, e.PLAN_LINK, e.RESOLUTION_LINK, e.CIP_LINK, e.SITE_PLAN_APPR_DATE, e.PRELIM_PLAN_APPR_DATE, a.STREETVIEW    
FROM BIKEPEDHYBRID a
INNER JOIN BICYCLEBASETABLE b
ON (a.MASTERID = b.MASTERID AND b.FACILITY IN ('Existing', 'Planned', 'Capital Proj Under Constr', 'Dvlpmt Proj Under Constr','Programmed Capital Proj','Development Approvals','Development Approvals - Frontage','Development Approvals - LATR')) AND (b.EFFECTIVEDATE <= CURRENT_DATE AND ((b.RETIREDATE IS NOT NULL AND b.RETIREDATE >= CURRENT_DATE) OR b.RETIREDATE IS NULL))
LEFT JOIN BMPPROJECTTABLE c
ON b.PROJECT_ID = c.PROJECT_ID
LEFT JOIN IMPLEMENTATIONTABLE e
ON b.IMPLEMENTATION_ID = e.IMPLEMENTATION_ID
LEFT JOIN BIKEWAYSTABLE d
ON c.INFRASTRUCTURE_ID = d.INFRASTRUCTURE_ID

--Bike network at the end of 2020 VIEW

SELECT
a.SHAPE, b.OBJECTID, a.STREETPREFIX, a.STREETNAME, a.STREETTYPE, a.STREETSUFFIX, a.EX_LTS, a.BMP_LTS, b.MASTERID, b.SEGTYPE, b.FACILITY, b.FACILITY_CLASS, b.BIKEWAY, b.BREEZEWAY, b.BMP_STATUS, b.BIKEWAY_TYPE, b.BIKEWAY_STREET, b.BIKEWAY_FROM, b.BIKEWAY_TO, b.MASTERPLAN, b.ADTDATE, b.ADT, b.ADT_SOURCE, b.ADT_EYEBAL, b.SPEEDLIMIT, b.ACTUALLANES, b.APPARENTLANES, b.PKLN, b.PARKBIKELNWIDTH, b.PARKTRNOVER, b.INDUSTST, b.RESIDENTIAL, b.DRIVEWAY, b.BLOCKAGE, b.MEDIANREFUGE, b.XINGLANES, b.XINGSPEED, b.BUFFERTYPE, b.BUFFERWIDTH, b.BIKELANEWIDTH, b.BKLN_DROP, b.IMPLEMENT, b.NOTES, b.LTS, b.LTS_TEXT, b.LTS_FURTH, b.LTS_RATION, b.ERROR, b.POLICY_AREA, b.BIPPA, b.SUBPAR, b.FACILITY_YEAR, b.TIER, b.CENTERLINE, b.INTCONTROL, b.BIKEABLESIDEWALK, b.EFFECTIVEDATE, b.RETIREDATE, c.PROJECT_NAME as BIKE_PROJECT_NAME, c.PROJECT, c.PROJECT_FROM, c.PROJECT_TO, c.PROJECT_BIKEWAY_TYPE, d.INFRASTRUCTURE_NAME, d.INFRASTRUCTURE_TYPE, e.PROJECTTYPE, e.STATUS, e.PROJECT_NAME, e.BIKEWAY AS IMPLEBIKEWAY, e.PRELIM_PLAN, e.SITE_PLAN, e.PLAN_LINK, e.RESOLUTION_LINK  
FROM BIKEPEDHYBRID a
INNER JOIN BICYCLEBASETABLE b
ON (a.MASTERID = b.MASTERID AND b.FACILITY IN ('Existing', 'Planned', 'Capital Proj Under Constr', 'Dvlpmt Proj Under Constr','Programmed Capital Proj','Development Approvals','Development Approvals - Frontage','Development Approvals - LATR')) AND (b.EFFECTIVEDATE <= date  '2020-12-31' AND (( b.RETIREDATE IS NOT NULL AND b.RETIREDATE >= date  '2020-12-31') OR b.RETIREDATE IS NULL))
LEFT JOIN BMPPROJECTTABLE c
ON b.PROJECT_ID = c.PROJECT_ID
LEFT JOIN IMPLEMENTATIONTABLE e
ON b.IMPLEMENTATION_ID = e.IMPLEMENTATION_ID
LEFT JOIN BIKEWAYSTABLE d
ON c.INFRASTRUCTURE_ID = d.INFRASTRUCTURE_ID

--Current Ped Network VIEW

SELECT
a.SHAPE, a.STREETPREFIX, a.STREETNAME, a.STREETTYPE, a.STREETSUFFIX, b."OBJECTID",b."LINKTYPE",b."ROADCODE",b."PATHWIDTH",b."FREQUENTLYOBS",b."SPEED",b."CROSSINGTYPE",b."RTORPERMITTED",b."NUMBEROFLANES",b."HOTRIGHT",b."INTERSTATERAMP",b."CONNECTOR",b."FACILITY",b."MASTERID",b."CENTERLINE",b."BUFFERTYPE",b."MPOHTCLASS",b."CREATEDBY",b."PARKING",b."BUFFERWIDTH",b."CROSSINGRAMP",b."ONSTREETSEP",b."CLASSIFYMEDIAN",b."CROSSWALKTYPE",b."PARALLELXINGSPEED",b."CONDITION",b."LOWVOLUME",b."TEST",b."JURISDICTION",b."EFFECTIVEDATE",b."RETIREDATE",b."PROJECTNAME",b."PLOC",b."PLOC_DESCRIPTION",b."PLOC_SCOREID",b."TEMP",b."RRFB",b."IMPLEMENTATION_ID", c.PROJECTTYPE, c.STATUS, c.PROJECT_NAME, c.PRELIM_PLAN, c.SITE_PLAN, c.PLAN_LINK, c.RESOLUTION_LINK, c.CIP_LINK, c.SITE_PLAN_APPR_DATE, c.PRELIM_PLAN_APPR_DATE, a.STREETVIEW    
FROM BIKEPEDHYBRID a
INNER JOIN PedBaseTable b
ON (a.MASTERID = b.MASTERID) AND (b.EFFECTIVEDATE <= CURRENT_DATE AND (( b.RETIREDATE IS NOT NULL AND b.RETIREDATE >= CURRENT_DATE) OR b.RETIREDATE IS NULL))
LEFT JOIN IMPLEMENTATIONTABLE c
ON b.IMPLEMENTATION_ID = c.IMPLEMENTATION_ID

-- View of ped network along thrive corridors with Master Plan of Highways info

SELECT
a.SHAPE, a.STREETPREFIX, a.STREETNAME, a.STREETTYPE, a.STREETSUFFIX, a.MPOHTID, b.OBJECTID, b.LINKTYPE, b.ROADCODE, b.PATHWIDTH, b.FREQUENTLYOBS, b.SPEED, b.CROSSINGTYPE, b.RTORPERMITTED, b.NUMBEROFLANES, b.HOTRIGHT, b.INTERSTATERAMP, b.CONNECTOR, b.FACILITY, b.MASTERID, b.CENTERLINE, b.BUFFERTYPE, b.MPOHTCLASS, b.CREATEDBY, b.PARKING, b.BUFFERWIDTH, b.CROSSINGRAMP, b.ONSTREETSEP, b.CLASSIFYMEDIAN, b.CROSSWALKTYPE, b.PARALLELXINGSPEED, b.CONDITION, b.LOWVOLUME, b.TEST, b.JURISDICTION, b.EFFECTIVEDATE, b.RETIREDATE, b.PROJECTNAME, b.PLOC, b.PLOC_DESCRIPTION, b.PLOC_SCOREID, b.TEMP, b.RRFB, b.IMPLEMENTATION_ID, c.CLASSLABEL2, c.THRIVECORRIDOR, a.STREETVIEW    
FROM BIKEPEDHYBRID a
INNER JOIN PedBaseTable b
ON (a.MASTERID = b.MASTERID AND (b.EFFECTIVEDATE <= date  '2019-12-31' AND (( b.RETIREDATE IS NOT NULL AND b.RETIREDATE >= date  '2019-12-31') OR b.RETIREDATE IS NULL)))
INNER JOIN GIS1.MPOHT_CSDG c
ON c.MasterID = a.MPOHTID