Road and land use data for RoadKrige and LSVR

[cczhu]

Aside from the output of PRTCS, KCOUNT reads in a number of road and land use predictors. In TEPs-I, these are stored as Excel spreadsheets and preprocessed by PRTCS functions like data_prep_kriging.m. We need to reproduce these processes in PostGIS and the roadkrige ETL pipeline.

• [ ] Take an inventory of all road and land use features TEPs-I uses, and figure out where these data are located on our Postgres (or where we can acquire these data).

• [x] Determine preprocessing methodology of data_prep_kriging.m and reproduce it in Postgres.

• [ ] roadkrige ETL pipeline (break this up into smaller jobs!)

[cczhu]

Created a prototype ETL pipeline in the sandbox branch that reads in shortest_path.zip, Landuse_pop_lane_speed.xlsx and poprate300.xlsx to produce:

• AADT_landuse_2_2010 - AADT and land use data for LSVR.
• distance_short - a lookup table for the shortest path matrix between (mostly) arterials. (TEPs doesn't use fcode_desc descriptions of road class, but the centreline segments included are around 90% arterials.)
• data_for_pred - road, land use and AADT data (if available) for KCOUNT.
• data_for_fit - as data_for_pred but only for arterials with observed AADTs > 2000.

Data inventory in the next comment.

[cczhu]

Data inventory for road properties, land use, etc. for KCOUNT and LSVR to be adapted for RoadKrige and LocalSVR:

AADT_landuse_2_2010 for LSVR:

• Sum_pop - sum of population within a 300m buffer from the centreline.
• number of lanes - number of road lanes
• speed limit - road speed limit
• employment - ???
• commercial - ???
• industrial - ???
• government - ???
• road type - ???

Waiting for Arman to describe what the outstanding columns are.

distance_short for KCOUNT:

• network_distance - network distance between an origin (mostly arterial) centreline segment and all destination (mostly arterial) centreline segments within 2 km. We'll also need to include expressways and any other roads overwhich we have data.

I've noticed a small number of road IDs not found in the current centreline. I'm not sure whether these are from an outdated version of the centreline or are roads not included within the centreline.

data_for_fit/pred for KCOUNT:

• Contains the same columns as aadt_landuse_2_2010, but processed for only centreline IDs in distance_short in the positive or negative direction.