PostGIS script to determine network distance matrix between all centreline segments

[cczhu]

TEPs-I unpacks the shortest_path.zip file and transforms it into distance_short, a lookup table of arterial and expressway centreline IDs and the network distances to all neighbouring arterial and expressway IDs within a 2km Euclidean radius. Distances between centreline segments is most needed for roadkrige, but can also be used for nearest neighbour lookup in countmatch. Creating a network distance matrix is something that should be done either in Pandana or in PostGIS; since we'll be storing raw input data on PostGIS, the latter is the most reasonable choice.

• Double-check how the Matlab binaries Arman created work. Does he simply calculate network distances between centreline segment midpoints? Does he take into account the exact road location (maybe intersection) counts are being conducted on?
• Determine a method for calculating network distances on the centreline, likely with pgRouting.
• Create a gis.centreline_distances table on PostGIS that stores network distances between arterial centreline segment midpoints and all neighbouring arterial & expressway midpoints, as well as the midpoints of any local roads with AADTs above 2000 (very unlikely).
• Validate values in gis.centreline_distances.
• Determine a nearest-neighbour lookup method for permanent count stations for CountMatch.
• Determine the fastest way to query needed data for kriging.

Ideally we would also determine the network distances to all local roads so we can explore additional methods for modelling, but we need to be selective about road class since the number of segments would triple from 16,000 to 43,000 if we included all local roads, adding a factor of 10 to the size of the distance matrix.

[cczhu]

Checked shortest_distance.zip processing scripts, and lon-lats of centrelines aren't ever explicitly stated. If the start/end points used are identical to the ones in mid_f_point.csv (used for determining Euclidean distances between permanent counts), then they're all within ~5 metres of st_x(st_lineinterpolatepoint(st_linemerge(centreline.geom), 0.5::double precision)) acting on our centreline.