Extract volumes for 2022

[radumas]

• [ ] Miovision ATR data to the centreline
• [ ] ATR counts from MOVE to the centreline
• [ ] Vehicle Detector Stations (to the centreline)

We can create a new folder volume_project/extract/2022 and put the .sql files in there.

[radumas]

Someone should drop what the input to TEPS should look like for future reference

[scann0n]

RE: TEPS input looked like this for Miovision:

But it looked like this for RESCU:

Some observations and questions:

• Last time we sent them the Miovision anomalous ranges table; if we're going to do that we should include the intersection_uid in the output so they can join easily - do you concur?
• Should the RESCU + ATR data be formatted to match the TEPS input?

[radumas]

Last time we sent them the Miovision anomalous ranges table; if we're going to do that we should include the intersection_uid in the output so they can join easily - do you concur?

I think we should include the AR in the output, so that they can filter things that are questionable or whatever out without having to join. @gabrielwol might have some ideas/existing views that would help.

Should the RESCU + ATR data be formatted to match the TEPS input?

• ATR: Yes. I'm assuming all the speed-volume ATRs will have a speed_class. I think you should watch out for pitfalls with the other count types because they also use speed_class to code data, I think that's just classification counts? And those could use vehicle_class instead?
• RESCU: @gabrielwol we are not producing a 15-min speed-vol aggregate at the moment correct?

[scann0n]

There are 23 distinct arterycode + centreline_id combos with no geo_id in the gis.centreline_20220705 table, but since arterycodes are directional, this only works out to about 13 locations (as in, I count like 13 dots on this map):

Should I just find them manually?

[gabrielwol]

I think we should include the AR in the output, so that they can filter things that are questionable or whatever out without having to join. @gabrielwol might have some ideas/existing views that would help.

For VIEW miovision_api.volumes_daily I took the approach of excluding anomalous_ranges labelled as do-not-use/questionable, but including the notes for other anomalous_ranges such as "valid-caveat" in an extra column. You can see that approach in the view's sql!

RESCU: @gabrielwol we are not producing a 15-min speed-vol aggregate at the moment correct?

Check out vds.veh_speeds_15min!

[radumas]

There are 23 distinct arterycode + centreline_id combos with no geo_id in the gis.centreline_20220705 table, but since arterycodes are directional, this only works out to about 13 locations (as in, I count like 13 dots on this map):

Should I just find them manually?

That's less than 2% of the locations so.... would be inclined to say it's not worth it.

[scann0n]

Check out vds.veh_speeds_15min!

I did!!! Super cool to have all of this extra data!!! I noticed there are three count tables on vds:

• veh_speeds_15min - has data for RESCU Detectors and Smartmicro Sensors
• veh_length_15min - has data for RESCU Detectors and Smartmicro Sensors
• counts_15min - has data for RESCU Detectors, Blue City AI and Smartmicro Sensors

Should we send data from all three tables or will veh_speeds_15min suffice? I remember hearing that "lengths" might not be particularly reliable, but maybe that's changed.

Should we send data from non-RESCU detectors?

For VIEW miovision_api.volumes_daily I took the approach of excluding anomalous_ranges labelled as do-not-use/questionable, but including the notes for other anomalous_ranges such as "valid-caveat" in an extra column. You can see that approach in the view's sql!

Should we take the same approach? I don't have permission to see the create script on the view but I'll look for it on bdit_data-sources.

[scann0n]

Oh also, any quick ideas on how to match vds to the centreline?

[radumas]

Should we send data from all three tables or will veh_speeds_15min suffice? Should we send data from non-RESCU detectors?

I think this veh_speeds_15min suffices. If the Smartmicro are on highways then I would say yes, otherwise maybe not.

Should we take the same approach? I don't have permission to see the create script on the view but I'll look for it on bdit_data-sources. It doesn't show in the SQL tab?

Oh also, any quick ideas on how to match vds to the centreline?

Spatial join on vds_identity_locations, you could maybe run the heading_degrees through the logic to create dir_bin but I haven't investigated. Otherwise have to identify direction of travel some other way.