Draw routes for newly installed bluetooth detectors

[chmnata]

Create routes for the following analysis_id

select * 
from bluetooth.all_analyses where route_name like '%DT2%'

Steps:

• get start and end geom for each analysis_id
• create table with detector_id, detector_geom, centreline_int_id
• join the detector's geometry to the closest centreline intersection
• route the segments using centreline's intersection and gis.centreline_both_dir

Also re-draw these #183

[chmnata]

table with analysis_id, street, direction, from_street, to_street, from_geom, to_geom, from_id, to_id in natalie.new_detectors temporarily

view with int_id and blutooth_id in natalie.bluetooth_nodes

[chmnata]

Route new bluetooth routes with gis.centreline_routing_undirected

with lookup as (
select analysis_id, from_id, origin.int_id as source, to_id, dest.int_id as target
from new_detectors 
inner join bluetooth_nodes origin on from_id = origin.bluetooth_id 
inner join bluetooth_nodes dest on to_id = dest.bluetooth_id)
, results as (select * from 
			 lookup
			 cross join lateral pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_undirected', source::int, target::int, FALSE)
			 )
select analysis_id, street, direction, from_street, to_street, edge as geo_id, geom from results			 
inner join gis.centreline on edge=geo_id
inner join new_detectors using (analysis_id)
order by analysis_id

[chmnata]

Draw new routes with the below sql, currently in natalie.new_bluetooth_segments

with lookup as (
select analysis_id, from_id, origin.int_id as source, to_id, dest.int_id as target
from new_detectors 
inner join bluetooth_nodes origin on from_id = origin.bluetooth_id 
inner join bluetooth_nodes dest on to_id = dest.bluetooth_id)
, results as (select * from 
			 lookup
			 cross join lateral pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_undirected', source::int, target::int, FALSE)
			 )
, lines as (select analysis_id, street, direction, from_street, to_street, edge as geo_id, geom from results			 
inner join gis.centreline on edge=geo_id
inner join new_detectors using (analysis_id)
order by analysis_id)
select analysis_id, street, direction, from_street, to_street, case when geom_dir != direction then ST_reverse(geom) else geom end as geom
from (
select analysis_id, street, direction, from_street, to_street,
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) as geom_dir,
		ST_linemerge(ST_union(geom)) as geom
from lines
group by analysis_id, street, direction, from_street, to_street)a

[chmnata]

Validating with direction, everything is fine.

select * from new_bluetooth_segments where gis.twochar_direction(gis.direction_from_line(geom)) != direction

Validating by 👀 and everything looks like its been routed from intersection to intersection

Validating by centreline's lf_name, straight-forward segments looks fine except some Lakeshore segments are using Gardiner's centreline geom. That is because Gardiner's int_id got matched to Lakeshore's bluetooth_id in natalie.bluetooth_nodes

[chmnata]

Need to redraw analysis_id in (1495610,1495638,195370,1496238,1496264,1496289) by updating natalie.new_detectors_updated with the correct int_id

Changed int_id to the correct intersection_id but still routed using Gardiner instead of Lakeshore. Going to filter out Gardiner segments before routing.

[chmnata]

Decided just to re-route the Lakeshore segments, created materialized view that only has lakeshore and cherry st segments in natalie.lakeshore_routing

--re-route lakeshore segments
with lookup as (
select analysis_id, from_id, from_intid as source, to_id, to_intid as target
from new_detectors_updated 
)
, results as (select * from 
			 lookup
			 cross join lateral pgr_dijkstra('SELECT id, source, target, cost FROM lakeshore_routing', source::int, target::int, FALSE)
			 )
, lines as (select analysis_id, street, lf_name, direction, from_street, to_street, edge as geo_id, geom from results			 
inner join gis.centreline on edge=geo_id
inner join new_detectors_updated using (analysis_id)
order by analysis_id)
select analysis_id, street, direction, from_street, to_street, case when geom_dir != direction then ST_reverse(geom) else geom end as geom
from (
select analysis_id, street, direction, from_street, to_street,
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) as geom_dir,
		ST_linemerge(ST_union(geom)) as geom
from lines
group by analysis_id, street, direction, from_street, to_street)a

[chmnata]

Decided just to re-route the Lakeshore segments, created materialized view that only has lakeshore and cherry st segments in natalie.lakeshore_routing

--re-route lakeshore segments
with lookup as (
select analysis_id, from_id, from_intid as source, to_id, to_intid as target
from new_detectors_updated 
)
, results as (select * from 
			 lookup
			 cross join lateral pgr_dijkstra('SELECT id, source, target, cost FROM lakeshore_routing', source::int, target::int, FALSE)
			 )
select analysis_id, street, lf_name, direction, from_street, to_street, edge as geo_id, geom from results			 
inner join gis.centreline on edge=geo_id
inner join new_detectors_updated using (analysis_id)
order by analysis_id

Should've just created a materialized view with all centreline without Gardiner....created a table natalie.new_bluetooth_routes and inserted 6 new lakeshore segments

[chmnata]

update natalie.new_bluetooth_routes with trimmed segment

update natalie.new_bluetooth_routes
set geom = a.geom
from 
(with closest as (select analysis_id, direction, ST_linelocatepoint(geom, ST_closestpoint(pt, geom)), geom from natalie.new_bluetooth_routes,
(select ST_startpoint(geom) as pt from bluetooth.segments where analysis_id = 1396227)a
where analysis_id in (1496289,1495610))
select analysis_id, case when direction = 'WB' then ST_linesubstring(geom, 0, st_linelocatepoint) else ST_linesubstring(geom, st_linelocatepoint, 1) end as geom
from closest)a
where new_bluetooth_routes.analysis_id = a.analysis_id  

[chmnata]

Because we are using undirected routing, the function does not take into account of street direction. Resulting in an Adelaide segment routed to Richmond.

[chmnata]

Should've just created a materialized view with all centreline without Gardiner....created a table natalie.new_bluetooth_routes and inserted 6 new lakeshore segments

Did that and created a materialized view natalie.centreline_routing_undirected_bluetooth without Gardiner, richmond and some segments of eastern. Re-routed it again with the following:

with lookup as (
select analysis_id, from_id, from_intid as source, to_id, to_intid as target
from new_detectors_updated 
)
, results as (select * from 
			 lookup
			 cross join lateral pgr_dijkstra('SELECT id, source, target, cost FROM natalie.centreline_routing_undirected_bluetooth', source::int, target::int, FALSE)
			 )
, lines as (select analysis_id, street, direction, from_street, to_street, edge as geo_id, geom from results			 
inner join gis.centreline on edge=geo_id
inner join new_detectors using (analysis_id)
order by analysis_id)
select analysis_id, street, direction, from_street, to_street, case when geom_dir != direction then ST_reverse(geom) else geom end as geom
from (
select analysis_id, street, direction, from_street, to_street,
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) as geom_dir,
		ST_linemerge(ST_union(geom)) as geom
from lines
group by analysis_id, street, direction, from_street, to_street)a

[chmnata]

Because we are using undirected routing, the function does not take into account of street direction. Resulting in an Adelaide segment routed to Richmond.

another example at bloor and parliament!

Seems like we could've used gis.centreline_one_way to route instead of undirected routing

[chmnata]

updated bloor and parliament's int id and re-routed again with the following, final table in natalie.bluetooth_routed

create table bluetooth_routed as 
with lookup as (
select analysis_id, from_id, from_intid as source, to_id, to_intid as target
from new_detectors_updated 
)
, results as (select * from 
			 lookup
			 cross join lateral pgr_dijkstra('SELECT id, source, target, cost FROM natalie.centreline_routing_undirected_bluetooth', source::int, target::int, FALSE)
			 )
, lines as (select analysis_id, street, direction, from_street, to_street, edge as geo_id, geom from results			 
inner join gis.centreline on edge=geo_id
inner join new_detectors using (analysis_id)
order by analysis_id)
select analysis_id, street, direction, from_street, to_street, case when geom_dir != direction then ST_reverse(geom) else geom end as geom
from (
select analysis_id, street, direction, from_street, to_street,
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) as geom_dir,
		ST_linemerge(ST_union(geom)) as geom
from lines
group by analysis_id, street, direction, from_street, to_street)a;

update natalie.bluetooth_routed
set geom = a.geom
from 
(with closest as (select analysis_id, direction, ST_linelocatepoint(geom, ST_closestpoint(pt, geom)), geom from natalie.new_bluetooth_routes,
(select ST_startpoint(geom) as pt from bluetooth.segments where analysis_id = 1396227)a
where analysis_id in (1496289,1495610))
select analysis_id, case when direction = 'WB' then ST_linesubstring(geom, 0, st_linelocatepoint) else ST_linesubstring(geom, st_linelocatepoint, 1) end as geom
from closest)a
where bluetooth_routed.analysis_id = a.analysis_id  	 		

[radumas]

Is this complete? Can we close #202 as well? And #183?

Which tables have to get merged in to bluetooth.segments?

[KatiRG]

I think @aharpalaniTO had to review the work we did for these issues.

[chmnata]

Steps to route bluetooth segments:

1. Prepare table with :

• analysis_id (or any sets of unique identifier)
• start centreline intersection
• end centreline intersection
• direction of the segment

2. Route with the directional parameter using gis.centreline_routing_directional e.g.

pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_directional', source::int, target::int, TRUE)

3. Validate with length ST_length(geom) and direction gis.direction_from_line(geom)

Notes:

• Double check if any bluetooth detectors are not located at the start or end point of a centreline. If they do, we will need to cut the centreline using ST_linelocatepoint()
• Sometimes routing does not return the segment you want (if it is not the shortest path from point a to point b), if this happens you could filter the routing network to not include local or smaller streets, forcing the function to route using only arterials or the fcode_desc of your choice

[webgisgeek]

mohan.bt_segments_new is created

Steps followed

1. Tabulate all bluetooth nodes that are missing segments.
2. Used the same sql created earlier except that pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_undirected', source::int, target::int, FALSE) is replaced by this (routed using directional parameter) pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_directional', source::int, target::int, TRUE)

The final SQL is :

CREATE table mohan.bt_segments_new AS (
WITH lookup AS (
SELECT analysis_id, from_id, origin.int_id AS source, to_id, dest.int_id AS target
FROM mohan.new_added_detectors 
INNER JOIN mohan.bluetooth_nodes origin ON from_id = origin.bluetooth_id 
INNER JOIN mohan.bluetooth_nodes dest ON to_id = dest.bluetooth_id
),
results AS (
	SELECT * 
	FROM lookup
			 CROSS JOIN LATERAL pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_directional', source::int, target::int, TRUE)
			 
), 
lines as (
	SELECT analysis_id, street, direction, from_street, to_street, edge AS geo_id, geom 
	FROM results			 
INNER JOIN gis.centreline ON edge=geo_id
INNER JOIN mohan.new_added_detectors USING (analysis_id)
ORDER BY analysis_id
)
SELECT analysis_id, street, direction, from_street, to_street, 
	CASE WHEN geom_dir != direction THEN ST_reverse(geom) 
	ELSE geom 
	END 
	AS geom
FROM (
SELECT analysis_id, street, direction, from_street, to_street,
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) AS geom_dir,
		ST_linemerge(ST_union(geom)) AS geom
FROM lines
GROUP BY analysis_id, street, direction, from_street, to_street) a)

The resulting segments (green dots online and red dots offline detectors)

3. Check and Validation Validated the length of the segments with length ST_length(geom) and direction using gis.direction_from_line(geom) function. Both length and direction are correct. One route (Roncesvalles from Dundas to Bloor is very short only 275m may be ignored) Detectors close to intersections, did not require centreline cutting.

At this location, the route passed directly through Spadina Crescent instead of going around.

Everything else looks correct. Ready to merge with bluetooth.segments and natalie.bluetooth_routed

[chmnata]

mohan.bt_segments_new is created

Steps followed

1. Tabulate all bluetooth nodes that are missing segments.

2. Used the same sql created earlier except that
   ` pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_undirected', source::int, target::int, FALSE)`
   is replaced by this (routed using directional parameter)
   ` pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_directional', source::int, target::int, TRUE)`

The final SQL is :

CREATE table mohan.bt_segments_new AS (
WITH lookup AS (
SELECT analysis_id, from_id, origin.int_id AS source, to_id, dest.int_id AS target
FROM mohan.new_added_detectors 
INNER JOIN mohan.bluetooth_nodes origin ON from_id = origin.bluetooth_id 
INNER JOIN mohan.bluetooth_nodes dest ON to_id = dest.bluetooth_id
),
results AS (
	SELECT * 
	FROM lookup
			 CROSS JOIN LATERAL pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_directional', source::int, target::int, TRUE)
			 
), 
lines as (
	SELECT analysis_id, street, direction, from_street, to_street, edge AS geo_id, geom 
	FROM results			 
INNER JOIN gis.centreline ON edge=geo_id
INNER JOIN mohan.new_added_detectors USING (analysis_id)
ORDER BY analysis_id
)
SELECT analysis_id, street, direction, from_street, to_street, 
	CASE WHEN geom_dir != direction THEN ST_reverse(geom) 
	ELSE geom 
	END 
	AS geom
FROM (
SELECT analysis_id, street, direction, from_street, to_street,
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) AS geom_dir,
		ST_linemerge(ST_union(geom)) AS geom
FROM lines
GROUP BY analysis_id, street, direction, from_street, to_street) a)

The resulting segments (green dots online and red dots offline detectors)

1. Check and Validation
   Validated the length of the segments with length ST_length(geom) and direction using gis.direction_from_line(geom) function.
   Both length and direction are correct. One route (Roncesvalles from Dundas to Bloor is very short only 275m may be ignored) Detectors close to intersections, did not require centreline cutting.

At this location, the route passed directly through Spadina Crescent instead of going around.

Everything else looks correct. Ready to merge with bluetooth.segments and natalie.bluetooth_routed

I just went and check the segment routed through Spadina Crescent, apparently the middle line is a planning boundary. You can exclude planing boundary from the routing step by modifying the sql section in pgr_dijkstra into something like this

select * from gis.centreline_routing_directional
inner join gis.centreline on geo_id = id
where fcode != 207001 -- fcode 207001 is the functional code for geostatistical line 

The segments looks good otherwise! :D Next time you could exclude the ST_reverse step and use the geom directly resulted from routing with gis.centreline_routing_directional since the geom from that table is directional to begin with.

[chmnata]

The segments in mohan.bt_segments_newest looks good! Can you post the sql in the comment?

[webgisgeek]

The problem of routing through geostatistical line is corrected.

The segments in mohan.bt_segments_newest looks good! Can you post the sql in the comment?

CREATE table mohan.bt_segments_new AS (
WITH lookup AS (
SELECT analysis_id, from_id, origin.int_id AS source, to_id, dest.int_id AS target
FROM mohan.new_added_detectors 
INNER JOIN mohan.bluetooth_nodes origin ON from_id = origin.bluetooth_id 
INNER JOIN mohan.bluetooth_nodes dest ON to_id = dest.bluetooth_id
),
results AS (
	SELECT * 
	FROM lookup
			 CROSS JOIN LATERAL pgr_dijkstra('SELECT id, source, target, cost FROM gis.centreline_routing_directional inner join gis.centreline on geo_id = id
where fcode != 207001', source::int, target::int, TRUE)
			 
), 
lines as (
	SELECT analysis_id, street, direction, from_street, to_street, edge AS geo_id, geom 
	FROM results			 
INNER JOIN gis.centreline ON edge=geo_id
INNER JOIN mohan.new_added_detectors USING (analysis_id)
ORDER BY analysis_id
)
SELECT analysis_id, street, direction, from_street, to_street,
	CASE WHEN geom_dir != direction THEN ST_reverse(geom) 
	ELSE geom 
	END AS 
	geom
FROM (
SELECT analysis_id, street, direction, from_street, to_street, 
		gis.twochar_direction(gis.direction_from_line(ST_linemerge(ST_union(geom)))) AS geom_dir,
		ST_linemerge(ST_union(geom)) AS geom
	
FROM lines
GROUP BY analysis_id, street, direction, from_street, to_street) a)

[chmnata]

They are all drawn