azmag / fms Goto Github PK

CSS updates design review

• This should allow comparisons between years of data and between specific dates
• Compare detectors or corridors
• This should generate a dashboard like output that can be printed?

Clean up and look at design issues

This will take several hours

Create bin and color (Gray)

Rename to FMS_DATA

provides the ability to delete generated report

Add segment data
Flip coordinate data

Improve import process to include several new tables to allow the detector pages to load faster when querying using a detector and a year.

Right now these are the names that I am using for these tables:

So essentially, anytime the import process runs, each view would need to be selected into the corresponding table. This would "Cache" the results and allow for much faster load times at least for annual queries.

This query would do the trick:
(Unfortunately, this will probably take ~1hr to run)

use Jacobs_PlayPen

delete from dbo.detector_AvgHourlySpeed
select * into dbo.detector_AvgHourlySpeed from vw_detector_AvgHourlySpeed

delete from dbo.detector_AvgHourlyThroughput
select * into dbo.detector_AvgHourlyThroughput from vw_detector_AvgHourlyThroughput

delete from dbo.detector_AvgByLane
select * into dbo.detector_AvgByLane from vw_detector_AvgByLane

delete from dbo.detector_MiscData
select * into dbo.detector_MiscData from vw_detector_MiscData

The issue with this process, is that these queries rely on a few views that I created that combine the yearly data tables that are produced by the import script currently:

For example, here is the RawData combined view's underlying query:

select 2015 as year, * from Jacobs_PlayPen.dbo.RawData2015
union all
select 2016 as year, * from Jacobs_PlayPen.dbo.RawData2016
union all
select 2017 as year, * from Jacobs_PlayPen.dbo.RawData2017
union all
select 2018 as year, * from Jacobs_PlayPen.dbo.RawData2018

A similar view exists to combine the yearly tables that start with these prefixes:
RawData
LaneErrors
ExistingLanes
Errors

Here is one solution that we could try in SQL (As long as these prefixes stay the same):

This would need to be run every time a new set of tables is create:
https://stackoverflow.com/questions/13635295/i-need-to-create-a-view-that-unions-all-tables-based-on-their-prefix-new-tables

Also, because I haven't gotten started on the Corridor dashboard section of the site yet, I think there may be a similar number of tables needed to cache those reports.

Route Names

Button to clear main map settings

The corridor creation / editing process needs to be greatly improved.

• Allow creation based on a different year's corridor
• Allow editing for existing corridors

Should show most of the charts / analysis from the detectors page.

Including these analyses:

• Annual Hourly Average Speeds – Weekday
• Annual Hourly Average Throughput Per Lane – Weekday
• Annual Hourly Average Occupancy Percent – Weekday
• Annual Average by Lane
• Distribution of Data Passing Quality Control Criteria by Date
• Distribution of data Passing Quality Control Criteria by Weekday
• Annual Quality Control Flags by Hour of Day – Weekday
• Flow vs. Density – All Data Rows & 5-min Weekday Average
• Speed vs. Density – All Data Rows & 5-min Weekday Average
• Speed vs. Flow – All Data Rows & 5-min Weekday Average

Work through the Corridor logic to create more accurate statistics / reports when a corridor doesn't include all detectors, or the validity of included detectors doesn't meet the QC/QA checks.

Additionally, we might need to add some verbiage / disclaimer to the generated reports that explains the methodology that we end up choosing.

Here are a couple scenarios to think through:

• Single detector excluded in middle of corridor
• Single detector excluded at either end of corridor
• Multiple detectors excluded sequentially in a corridor