Journey planner help

-- Script created: 2012 05 16 11:57:13;

-- Step 1 createMapWayTags();

-- Create a table to scrutinize the tags on ways.;
drop table if exists `map_way_tags`;
create table `map_way_tags` (
     `id`		int unsigned not null default 0 comment '__planetDb__.osm_wayNode.id',
     `originalTags` text not null comment 'Tab separated original tags',
     `originalTagsId` int unsigned not null comment '__routingDb__.map_wayOriginalTags.id',
     `highway`  enum('	footway','CO11 2NE','FIXME','Lane','Lindhagen','abandoned','access','access_ramp','alley','barrier','bridleway','broken bridge','bus','bus_guideway','bus_lane','bus_platform','bus_stop','buslane','byway','closed','closed:bridleway','closed_footway','closed_steps','construction','constuction','contruction','conveyor','course_of_highway','crossing','curbs','cycleway','dam','demolished road','depot','disappeared','disused','disused_footway','drive','driveway','elevator','emergency','emergency_access_point','emergency_bay','escalator','escape_lane','ex-footway','farm track','farm','fence','ferry','field','foot','footway broken','footway','ford','former_footway','gallop','gallops','generic','give_way','grass','gritting_route','hallway','historic','industrial','island','junction','kerb','krb','layby','living_street','m','manor','mini_roundabout','minor','motorway','motorway_link','name_only','no','none','not on the ground at all','old','overgrown','paper','passing_place','path','path-x','paths','pathway','paved','pedestran','pedestrian','pier','planned','platdorm','platfom','platform','platforn','plaza','possible','preserved','primary','primary_link','private','projected','proposal','proposed','racetrack','racetracksmall','raceway','ramp','re#','residential','residential_link','rest_area','restarea','retreat','right of way','road','roman_road','route  - there is no path','route','s','sec','secondary','secondary_link','ser','servce','service','services','sidewalk','ski_jump','slipway','stairs','stepping stones','stepping_stones','steps','stream','street_Lamp','subway','suggested','surface','survery','survey','suspect','tar','teritary','tertiary','tertiary_link','towpath','track','track/path thro park','tracke','traffic_calming','traffic_signals','trail','truck','trunk','trunk_link','turning_circle','unclassified','unclassified_link','undesignated','unfinished','unknown','unpaved road','unsurfaced','uyes','walkway','wall','was_path','wassertaxi','waterway','weir','x-cycleway','yes','yra')  null default null,
     `cycleway` enum('*','=yes','Loafallet','Np','bmx_track','construction','crawler_lane','crossing','curbed_lane','cut_through','cycle','d','designated','highway','lane','lane:right','lane_opposite','left','left=lane','mtb','no','none','oneway','only_sunday','opposite','opposite_lane','opposite_share_busway','opposite_track','path','pavement_left','pavement_right','primary','proposed','proposed_lane','redway','restaurant route','right','road','sahred','segragated','segregated','separate','separated','seperate','seperated','share','share_busway','share_footway','shared use','shared','shared_lane','shared_use','sidewalk','track','track:left','undefined','yes') null default null,
     `access`   enum('*','++','16:30 - 10:30','4wd_only','A&E Only','Blue Pacific','Bodie Drive','Children','Coquet Close','Dilston Close','Esslin Road','FIXME','Fir Tree Close','Fu','GPS','Georges Road','Goostry Road','Grusväg','Harapaki Road','Hercegovčaka ulica','Kennedy Point Road','Kotuku Street','Ladd Road','MOD','Mad Dog Lane','Maramaramau Road','McLeod Road','Ngaruru Road','Pahautea Road','Palm Parade','Public Park','Puriri Street','Rata Street','Ražnjanska','Scannell Street.','Syvhøjvej','Tamarisk Drive','The Close','Vetelsvegen','Winifred Street','access','access_only','agricultural','all','authorised','authorised_traffic','bdouble','bus','buses','cars:private','closed in the morning','closed','construction','controlled','customer','customers','danger','dedicated','defacto','delivery','designated','destination','destinatiuon','direction','disabled','driveway','dunno?','emergency','emergency_only','emergency_vehicles_only','escorted','false','fee','fisherman','foot','forced','forestry','gated','genomfart förbjuden','goods','grade2','hgv','horse','inpassable','limited','loading_only','local','maybe private','member','members','military','motor_vehicle','motorcycle=no','need to go thourgh unlocked gates','no motor traffic','no','no_through_road','no_throughfair','none','official','open_access','perhaps','permisive','permissive','permit holders','permit','permitted','privage','private','private?','prohibited','property','prviate','psv only','psv','psv=opposite_lane','pubilc','public right of way','public','public_footpath','public_service_vehicle','raceday','residentail','residential','residents','restricted','s','school','set_down','ski:nordicc','so','staff','taxi','taxis','tidal','timed','uncertain','university','unknown - frequently walked','unknown but frequently walked','unknown','unknwon','unnknown','visitor','wheelchair','works traffic only','yea','yes')   null default null comment 'http://wiki.openstreetmap.org/wiki/Key:access',
     `foot`     enum('*','+','0','Bing','Bishops Walk','Footway','Ottestadstien','Revsvatn-Rundwanderweg','Silla tee','access','allowed','b','crossing','definitive path','defra','designated','destination','difficult','fixme','footpath','inside','lane','no','off_season','official','paper road','path','pemissive','permissive','presumably','private','probably','public','sidewalk','stairs','steps','tide_dependant','undefined','unknown','unnofficial','unofficial','viewing platform','ye','yea','yes','yse')     null default null,
     `bicycle`  enum('y','Mo-Sa 09:30-17:30','allowed','almost','c','carefully','carry','critical','crossbike','delivery','desgnated','designate','designated','destination','discouraged','dismount','fixme','foot','gutter_ramp','limiting','loony_only','mountainbike','mtb','no idea','no','not recommended','not_advisable','off_season','official','ok','only with written permission','opposite','opposite_lane','opposite_track','passable','pedestrianShared','perhaps','permissive or not at all!','permissive','private','probably not','ramp','ridable','shared','tolerated','track','undefined','unknown','unsuitable','wheeled','yes MTB','yes')  null default null,
     `oneway`   enum('!destination','(new value)no','-1 bicycle = no','-1','0','1','A255','GPS survey','OS_OpenData_StreetView','b','bing','fixme','mo','no','other','reversed','reversible','shared','supermarket','true','unclear','undefined','unknown','yes','yes]','yesno')   null default null,
     `routeId`  int unsigned not null default 0 comment '__routingDb__.map_osmBicycleRoute.id',
     `network`	enum('mtb','lcn','rcn','ncn','icn') default null comment 'The type of cycle route',
     `cnRef`	varchar(255) null default null comment 'Cycle network reference name or number.',
     `routingFactorId` smallint unsigned not null default 0 comment 'map_routingFactor.id',
     `sieveNote`     text         not null          comment 'Inferences applied',
     primary key (`id`)
) engine=myisam comment='Compilation of OSM tags related to the map_path.';

-- Create the ways. (3272469 15s);
insert map_way_tags (id)
select sql_no_cache id
  from map_path order by id asc;

-- Note the original tags using an ordering for consistency. (3272469 720s);
	update map_way_tags y
	  join (select mwt.id, group_concat(concat(wt.k,'=',wt.v) order by wt.k asc, wt.v asc separator "	")tags
			  from map_way_tags mwt
			  join planetDB.osm_wayTag wt ON mwt.id = wt.id
		  group by mwt.id)x on y.id = x.id
	   set y.originalTags = x.tags;

-- Create a table to manage the originalTags;
drop table if exists `map_wayOriginalTags`;
create table `map_wayOriginalTags` (
     `id`		int unsigned not null auto_increment primary key,
     `tags` text not null comment 'Tab separated original tags',
 unique index (`tags`(255))
) engine=myisam comment='Used to optimize original tags.';

-- Add the unique original tags combinations.;
insert map_wayOriginalTags (tags) select sql_no_cache originalTags from map_way_tags group by originalTags;

-- Link the map_way_tags to the map_wayOriginalTags.;
	update map_way_tags wt
	  join map_wayOriginalTags oT on wt.originalTags = oT.tags
	   set wt.originalTagsId = oT.id;

-- Copy OSM wayTag to the map_way_tags table fields. (3262263 152s);
update map_way_tags mwt join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'highway'
   set mwt.highway = wt.v, mwt.sieveNote = concat(mwt.sieveNote,'|','Planet: ', wt.k,'=',wt.v);

-- Copy OSM wayTag to the map_way_tags table fields. (20237 6s);
update map_way_tags mwt join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'cycleway'
   set mwt.cycleway = wt.v, mwt.sieveNote = concat(mwt.sieveNote,'|','Planet: ', wt.k,'=',wt.v);

-- Copy OSM wayTag to the map_way_tags table fields. (66046 11s);
update map_way_tags mwt join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'access'
   set mwt.access = wt.v, mwt.sieveNote = concat(mwt.sieveNote,'|','Planet: ', wt.k,'=',wt.v);

-- Copy OSM wayTag to the map_way_tags table fields. (250458 21s);
update map_way_tags mwt join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'foot'
   set mwt.foot = wt.v, mwt.sieveNote = concat(mwt.sieveNote,'|','Planet: ', wt.k,'=',wt.v);

-- Copy OSM wayTag to the map_way_tags table fields. (96213 11s);
update map_way_tags mwt join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'bicycle'
   set mwt.bicycle = wt.v, mwt.sieveNote = concat(mwt.sieveNote,'|','Planet: ', wt.k,'=',wt.v);

-- Copy OSM wayTag to the map_way_tags table fields. (216880 18s);
update map_way_tags mwt join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'oneway'
   set mwt.oneway = wt.v, mwt.sieveNote = concat(mwt.sieveNote,'|','Planet: ', wt.k,'=',wt.v);


-- Step 2 repairTags();

-- REPAIR PHASE 1;
-- These are some special wrong value repairs that can't be made by the simple repair table mechanism of later phases.;
-- Figures in brackets indicate affected numbers on the 2012 March 16 import run.;

-- Wrong value repair highway=access to highway=service,access=destination (139 18s);
    update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Wrong value repair highway=access to highway=service,access=destination'),
		   access  = 'destination',
		   highway = 'service'
	 where highway = 'access' and access is null;

-- Wrong value repair highway=private or private_driveway to highway=service,access=private (18 16s);
    update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Wrong value repair highway=', highway, ' to highway=service,access=private'),
		   access  = 'private',
		   highway = 'service'
	 where highway in ('private','private_driveway') and access is null;

-- Wrong value repair cycleway=yes|no to bicycle=yes|no (362 15s);
    update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Wrong value repair cycleway=', cycleway, ' to bicycle=', cycleway),
		   bicycle  = cycleway,
		   cycleway = null
	 where cycleway in ('yes','no') and bicycle is null;

-- REPAIR PHASE 2;
-- Populate a repair table with newly-discovered unrecognized values.;
-- Clear out from the repair table any previously identifed tags with broken values where there is no repair value or commentary.;
-- This keeps the repair table clean and tidy.;
delete from map_osm_repair_tags where repair is null and note is null;

-- Reset the count of the number of matches to zero;
update map_osm_repair_tags set matches = 0;


-- Compile highway repairs (118 11s);
	insert ignore map_osm_repair_tags (tag, broken, matches)
	select sql_no_cache 'highway' as tag, highway, count(*) count
	  from map_way_tags where highway is not null and highway not in ('bridleway','bus_guideway','byway','construction','cycleway','ferry','footway','living_street','motorway','motorway_link','path','pedestrian','primary','primary_link','residential','road','secondary','service','steps','tertiary','track','trunk','trunk_link','unclassified')
  group by highway order by count desc;

-- Compile cycleway repairs (35 8s);
	insert ignore map_osm_repair_tags (tag, broken, matches)
	select sql_no_cache 'cycleway' as tag, cycleway, count(*) count
	  from map_way_tags where cycleway is not null and cycleway not in ('lane','opposite','opposite_lane','opposite_track','segregated','shared','track')
  group by cycleway order by count desc;

-- Compile access repairs (100 8s);
	insert ignore map_osm_repair_tags (tag, broken, matches)
	select sql_no_cache 'access' as tag, access, count(*) count
	  from map_way_tags where access is not null and access not in ('designated','destination','no','permissive','private','yes')
  group by access order by count desc;

-- Compile foot repairs (30 8s);
	insert ignore map_osm_repair_tags (tag, broken, matches)
	select sql_no_cache 'foot' as tag, foot, count(*) count
	  from map_way_tags where foot is not null and foot not in ('designated','destination','no','permissive','private','yes')
  group by foot order by count desc;

-- Compile bicycle repairs (30 8s);
	insert ignore map_osm_repair_tags (tag, broken, matches)
	select sql_no_cache 'bicycle' as tag, bicycle, count(*) count
	  from map_way_tags where bicycle is not null and bicycle not in ('designated','destination','dismount','no','permissive','private','yes')
  group by bicycle order by count desc;

-- Compile oneway repairs (13 8s);
	insert ignore map_osm_repair_tags (tag, broken, matches)
	select sql_no_cache 'oneway' as tag, oneway, count(*) count
	  from map_way_tags where oneway is not null and oneway not in ('-1','no','yes')
  group by oneway order by count desc;


-- REPAIR PHASE 3;
-- For each tag type, count, then apply the repairs where a correction has already been manually defined.;

-- highway count (25 36s);
	update map_osm_repair_tags a
	  join (select count(*) repaired, rt.id from map_way_tags mwt
			  join map_osm_repair_tags rt on rt.tag='highway' and mwt.highway  = rt.broken and rt.repair is not null
		  group by rt.broken)b on a.id = b.id
	   set a.matches = b.repaired;
-- highway repair (3849 36s);
	update map_way_tags mwt
	  join map_osm_repair_tags rt on rt.tag='highway' and mwt.highway  = rt.broken and rt.repair is not null
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Repaired ', rt.tag, '=', mwt.highway),
           mwt.highway  = rt.repair;

-- cycleway count (2 9s);
	update map_osm_repair_tags a
	  join (select count(*) repaired, rt.id from map_way_tags mwt
			  join map_osm_repair_tags rt on rt.tag='cycleway' and mwt.cycleway  = rt.broken and rt.repair is not null
		  group by rt.broken)b on a.id = b.id
	   set a.matches = b.repaired;
-- cycleway repair (19 8s);
	update map_way_tags mwt
	  join map_osm_repair_tags rt on rt.tag='cycleway' and mwt.cycleway  = rt.broken and rt.repair is not null
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Repaired ', rt.tag, '=', mwt.cycleway),
           mwt.cycleway  = rt.repair;

-- access count (10 8s);
	update map_osm_repair_tags a
	  join (select count(*) repaired, rt.id from map_way_tags mwt
			  join map_osm_repair_tags rt on rt.tag='access' and mwt.access  = rt.broken and rt.repair is not null
		  group by rt.broken)b on a.id = b.id
	   set a.matches = b.repaired;
-- access repair (3176 9s);
	update map_way_tags mwt
	  join map_osm_repair_tags rt on rt.tag='access' and mwt.access  = rt.broken and rt.repair is not null
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Repaired ', rt.tag, '=', mwt.access),
           mwt.access  = rt.repair;

-- foot count (5 10s);
	update map_osm_repair_tags a
	  join (select count(*) repaired, rt.id from map_way_tags mwt
			  join map_osm_repair_tags rt on rt.tag='foot' and mwt.foot  = rt.broken and rt.repair is not null
		  group by rt.broken)b on a.id = b.id
	   set a.matches = b.repaired;
-- foot repair (3687 9s);
	update map_way_tags mwt
	  join map_osm_repair_tags rt on rt.tag='foot' and mwt.foot  = rt.broken and rt.repair is not null
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Repaired ', rt.tag, '=', mwt.foot),
           mwt.foot  = rt.repair;

-- bicycle count (4 9s);
	update map_osm_repair_tags a
	  join (select count(*) repaired, rt.id from map_way_tags mwt
			  join map_osm_repair_tags rt on rt.tag='bicycle' and mwt.bicycle  = rt.broken and rt.repair is not null
		  group by rt.broken)b on a.id = b.id
	   set a.matches = b.repaired;
-- bicycle repair (72 8s);
	update map_way_tags mwt
	  join map_osm_repair_tags rt on rt.tag='bicycle' and mwt.bicycle  = rt.broken and rt.repair is not null
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Repaired ', rt.tag, '=', mwt.bicycle),
           mwt.bicycle  = rt.repair;

-- oneway count (7 9s);
	update map_osm_repair_tags a
	  join (select count(*) repaired, rt.id from map_way_tags mwt
			  join map_osm_repair_tags rt on rt.tag='oneway' and mwt.oneway  = rt.broken and rt.repair is not null
		  group by rt.broken)b on a.id = b.id
	   set a.matches = b.repaired;
-- oneway repair (6784 9s);
	update map_way_tags mwt
	  join map_osm_repair_tags rt on rt.tag='oneway' and mwt.oneway  = rt.broken and rt.repair is not null
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Repaired ', rt.tag, '=', mwt.oneway),
           mwt.oneway  = rt.repair;


-- REPAIR PHASE 4;
-- This section contains some useful constraints, observations and inferences that can be made to improve the data for routing.;

-- Assume roundabouts are one way: junction=roundabout => oneway=yes (18664 112s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'junction' and wt.v = 'roundabout'
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Roundabout => oneway=yes'),
		   mwt.oneway = 'yes'
	 where mwt.oneway is null;

-- Assume roundabouts are not for walking: junction=roundabout => foot=no (27011 111s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt ON mwt.id = wt.id AND wt.k = 'junction' and wt.v = 'roundabout'
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Roundabout => foot=no'),
		   mwt.foot = 'no'
	 where mwt.foot is null;

-- Ferries: route=ferry => highway=ferry (note this is not a valid OSM value for the highway tag, but is used by CycleStreets for simplicity.). (689 118s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt ON mwt.id = wt.id AND wt.k = 'route' and wt.v='ferry'
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','route=ferry => highway=ferry'),
		   mwt.highway = 'ferry';

-- This inference is necessary to get access from the highway network to the ferry across man_made structures. (9611 5s);
-- Jetties: man_made=pier|jetty => highway=footway.;
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt ON mwt.id = wt.id AND wt.k = 'man_made' and (wt.v='pier' or wt.v='jetty')
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Jetties => highway=footway'),
		   mwt.highway = 'footway'
	 where mwt.highway <> 'footway' or highway is null;

-- Avoid ways that have construction=yes. (49 115s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Avoid construction'),
		   mwt.access = 'no'
	 where k = 'construction' and v = 'yes' and mwt.access is null;


-- Step 3 canonizeMainTags();

-- Canonize by changing the type to enum. Unrecognized values become the empty string, or index 0 in the enum list. (3272469 32s);
alter table `map_way_tags`
 change `highway`   `highway` enum('bridleway','bus_guideway','byway','construction','cycleway','ferry','footway','living_street','motorway','motorway_link','path','pedestrian','primary','primary_link','residential','road','secondary','service','steps','tertiary','track','trunk','trunk_link','unclassified')  null,
 change `cycleway` `cycleway` enum('lane','opposite','opposite_lane','opposite_track','segregated','shared','track') null,
 change `access`     `access` enum('designated','destination','no','permissive','private','yes')   null,
 change `bicycle`   `bicycle` enum('designated','destination','dismount','no','permissive','private','yes')  null,
 change `foot`         `foot` enum('designated','destination','no','permissive','private','yes')     null,
 change `oneway`     `oneway` enum('-1','no','yes')   not null default 'no';

-- Delete unrecognized highways from map_path. (4914 23s);
	delete p
	  from map_path p
	  join map_way_tags t on p.id = t.id
	 where t.highway = 0 or t.highway is null;

-- Delete unrecognized highways from map_way_tags. (4914 2s);
	delete from map_way_tags
	 where highway = 0 or highway is null;

-- Fix all the unrecognized oneway values to oneway=no. (3032341 39s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Assume oneway=no'),
 		   oneway = 'no'
	 where oneway = 0;

-- Handling 2 way cycling in one-way streets: oneway=no where cycleway=opposite* (1154 9s);
-- I.e. as far as CycleStreets is concerned these are two way streets.;
	update map_way_tags
	   set oneway = 'no'
	 where cycleway in ('opposite_lane','opposite_track','opposite');


-- Step 4 bicycleRoutes();
drop table if exists map_osmBicycleRoute;
create table `map_osmBicycleRoute` (
     `id`			int unsigned not null auto_increment comment '__planetDb__.osm_relationTag.id',
     `originalTags` text not null comment 'Tab separated original tags',
     `name`			varchar(255) default null comment 'Name of the bicycle route',
     `operator`		varchar(255) default null comment 'Who is in charge of the route',
     `network`		varchar(255) default null comment 'Which network',
     `cnRef`		varchar(255) default null comment 'Cycle network reference name or number',
     `state`    	varchar(255) default null comment 'State of the route',
     `distance`		varchar(255) default null comment 'The length of the route',
     `distanceKm` 	smallint unsigned default 0 comment 'The length of the route in kilometres',
     `sumLengthsKm` smallint unsigned default 0 comment 'Sum length of the ways, in kilometres',
	 `rating`		enum('neutral', 'touring', 'suggested' , 'continuous') default 'neutral' comment 'The impact on cyclability',
     primary key (`id`)
) engine=myisam comment='Relations from the planet: type=route,route=bicycle';

-- Identify all bicycle routes from the relations. (1666 1s);
	insert map_osmBicycleRoute (id)
	select sql_no_cache rt1.id
	  from planetDB.osm_relationTag rt1
	  join planetDB.osm_relationTag rt2 on rt1.id = rt2.id
	 where rt1.k = 'type'  and rt1.v = 'route'
	   and rt2.k = 'route' and rt2.v in ('bicycle','mtb');

-- Note the original tags. (1666 0s);
	update map_osmBicycleRoute y
	  join (select oBR.id, group_concat(concat(oRT.k,'=',oRT.v) separator "	")tags
			  from map_osmBicycleRoute oBR
			  join planetDB.osm_relationTag oRT ON oBR.id = oRT.id
		  group by oBR.id)x on y.id = x.id
	   set y.originalTags = x.tags;

-- Route name (1119 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.name = tags.v
	 where tags.k = 'name';

-- Route operator (532 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.operator = tags.v
	 where tags.k = 'operator';

-- Route network (1526 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.network = tags.v
	 where tags.k = 'network';

-- Route cnRef (1247 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.cnRef = tags.v
	 where tags.k = 'ref';

-- Route state (268 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.state = tags.v
	 where tags.k = 'state';

-- Route distance (125 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.distance = tags.v
	 where tags.k = 'distance';

-- Route distanceKm (124 0s);
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.distanceKm = if(tags.v regexp '^[0-9\.]+[ ]*mile[s]?$', tags.v * 1.6, tags.v)
	 where tags.k = 'distance';

-- For those without, make up a cycle network name from the network and references. (532 0s);
	update map_osmBicycleRoute
	   set name = concat(case network
						 when 'icn' then 'International cycle network'
						 when 'ncn' then 'National cycle network'
						 when 'rcn' then 'Regional cycle network'
						 when 'lcn' then 'Local cycle network'
						 when 'mtb' then 'Mountain bike network'
						 else ifnull(network, 'Bicycle route')
						 end,
						 if(operator, concat(' (', operator, ')'), ''),
						 if(cnRef, concat(' ', cnRef), ''))
	 where name is null and (operator is not null or network is not null or cnRef is not null);

-- Repair the network field with obvious corrections/simplifications. (20 0s);
	update map_osmBicycleRoute
	   set network = 'lcn'
	 where network in ('local', 'lcn2', 'huntsleisure', 'cambscc_link51', 'lnc');


-- Reset any unrecognised network values. (4 0s);
	update map_osmBicycleRoute
	   set network = null
	 where network not in ('mtb','lcn','rcn','ncn','icn');

-- Canonize (1666 0s);
alter table `map_osmBicycleRoute` change `network` `network` enum('mtb','lcn','rcn','ncn','icn') default null comment 'The type of cycle route' after `operator`;

-- Anonymous bicycle routes (15 0s);
	update map_osmBicycleRoute
	   set name = 'Anonymous bicycle route'
	 where name is null;

-- The OSM table planetDB.osm_relationMember defines a many-to-many link between routes and ways.;
-- I.e. a route has many ways, and a way can be part of many routes.;
-- Here the link is simplified into many-to-one between each way and its most significant route.;
-- This is enough information for cycle routing.;

-- Ways on icn routes (2676 1s);
	update map_way_tags way
	  join planetDB.osm_relationMember rm on way.id = rm.member_id and member_type = 'way'
	  join map_osmBicycleRoute route on route.id = rm.id
	   set way.sieveNote = concat(way.sieveNote,'|','Way on icn route'),
		   way.routeId = rm.id
	 where route.network = 'icn' and way.routeId = 0;

-- Ways on ncn routes (36651 2s);
	update map_way_tags way
	  join planetDB.osm_relationMember rm on way.id = rm.member_id and member_type = 'way'
	  join map_osmBicycleRoute route on route.id = rm.id
	   set way.sieveNote = concat(way.sieveNote,'|','Way on ncn route'),
		   way.routeId = rm.id
	 where route.network = 'ncn' and way.routeId = 0;

-- Ways on rcn routes (18457 1s);
	update map_way_tags way
	  join planetDB.osm_relationMember rm on way.id = rm.member_id and member_type = 'way'
	  join map_osmBicycleRoute route on route.id = rm.id
	   set way.sieveNote = concat(way.sieveNote,'|','Way on rcn route'),
		   way.routeId = rm.id
	 where route.network = 'rcn' and way.routeId = 0;

-- Ways on lcn routes (14936 36s);
	update map_way_tags way
	  join planetDB.osm_relationMember rm on way.id = rm.member_id and member_type = 'way'
	  join map_osmBicycleRoute route on route.id = rm.id
	   set way.sieveNote = concat(way.sieveNote,'|','Way on lcn route'),
		   way.routeId = rm.id
	 where route.network = 'lcn' and way.routeId = 0;

-- Ways on mtb routes (118 2s);
	update map_way_tags way
	  join planetDB.osm_relationMember rm on way.id = rm.member_id and member_type = 'way'
	  join map_osmBicycleRoute route on route.id = rm.id
	   set way.sieveNote = concat(way.sieveNote,'|','Way on mtb route'),
		   way.routeId = rm.id
	 where route.network = 'mtb' and way.routeId = 0;

-- Directly tagged route names that are not already part of a relation. (Routes defined by relations are considered dominant.);
-- Collate the different status of routes as lcn, rcn or national.;
-- In terms of general cycle routing the order of importance from highest to lowest is icn,ncn,rcn,lcn,mtb.;
-- Find the cnRef, or just the *cn=yes tag, avoiding *cn=proposed.;

-- icn (0 6s);
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set sieveNote  = concat(way.sieveNote, '|', 'Directly tagged icn'),
	       network = 'icn'
	 where routeId = 0 and network is null and (k = 'icn_ref' or (k = 'icn' and v = 'yes'));

-- ncn (2675 6s);
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set sieveNote  = concat(way.sieveNote, '|', 'Directly tagged ncn'),
	       network = 'ncn'
	 where routeId = 0 and network is null and (k = 'ncn_ref' or (k = 'ncn' and v = 'yes'));

-- rcn (853 6s);
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set sieveNote  = concat(way.sieveNote, '|', 'Directly tagged rcn'),
	       network = 'rcn'
	 where routeId = 0 and network is null and (k = 'rcn_ref' or (k = 'rcn' and v = 'yes'));

-- lcn (8727 6s);
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set sieveNote  = concat(way.sieveNote, '|', 'Directly tagged lcn'),
	       network = 'lcn'
	 where routeId = 0 and network is null and (k = 'lcn_ref' or (k = 'lcn' and v = 'yes'));

-- mtb (181 7s);
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set sieveNote  = concat(way.sieveNote, '|', 'Directly tagged mtb'),
	       network = 'mtb'
	 where routeId = 0 and network is null and (k = 'mtb_ref' or (k = 'mtb' and v = 'yes'));

-- Now find any cnRefs amongst these. (5130 8s);
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set sieveNote = concat(way.sieveNote, '|', 'Directly tagged *cn_ref'),
	       cnRef  = v
	 where routeId = 0 and k in ('icn_ref','ncn_ref','rcn_ref','lcn_ref','mtb_ref');

-- Use a temporary variable to distinguish the relation tagged routes from the directly tagged routes.;
set @maxRelationRouteId = (select max(id) from map_osmBicycleRoute);

-- Create entries in the bicycle route table for the directly tagged routes. (263 4s);
	insert ignore map_osmBicycleRoute (name, operator, network, cnRef)
	select sql_no_cache concat(network, ifnull(cnRef,'?')), 'Directly tagged', network, cnRef
	  from map_way_tags
	 where network is not null
  group by network, cnRef;


-- Covering index to help the next two updates. (2540 2s);
alter table map_osmBicycleRoute add unique (`network`,`cnRef`,`id`);

-- Link the ways to these newly formed routes.;
-- This should, in principle render the network and cnRef fields of the map_way_tags table redundant.;
-- Link directly tagged ways to route via network and cnRef. (5640 6s);
	update map_way_tags way
	  join map_osmBicycleRoute route on way.network = route.network and way.cnRef = route.cnRef and route.id > @maxRelationRouteId
	   set way.sieveNote = concat(way.sieveNote,'|','Way on directly tagged route with cnRef'),
		   way.routeId = route.id
	 where way.routeId = 0;

-- Link other directly tagged ways without cnRef via network. This covers the cases that have cnRef=NULL that are missed in the above query. (7529 26s);
	update map_way_tags way
	  join map_osmBicycleRoute route on way.network = route.network and route.id > @maxRelationRouteId
	   set way.sieveNote = concat(way.sieveNote,'|','Way on directly tagged route without cnRef'),
		   way.routeId = route.id
	 where way.routeId = 0;

-- Method of rating the route.;

-- Named long distance ncn routes (224 0s);
	update map_osmBicycleRoute route
	   set rating  = 'continuous'
	 where network = 'ncn' and cnRef is not null;

-- Regional routes (448 0s);
	update map_osmBicycleRoute route
	   set rating  = 'touring'
	 where network = 'rcn';

-- Local routes (747 0s);
	update map_osmBicycleRoute route
	   set rating  = 'suggested'
	 where network = 'lcn';

-- Unfinished routes are treated as neutral. (171 0s);
	update map_osmBicycleRoute route
	   set rating = 'neutral'
	 where state in ('proposed','temporary');


-- Step 5 facilities();
-- Detect cycle lane on one side of the road;
-- Apart from one-way streets, the CycleStreets routing engine cannot currently treat roads better for cycling one way than the other.;
-- The inference here is therefore a bit broad - asserting that even if there is a cycle lane on one side of the road then that street is basically one where cyclists are a common sight. (509 5s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Detect cycle lane on one side of the road'),
		   mwt.cycleway = 'lane'
	 where k in ('cycleway:left', 'cycleway:right') and v = 'lane' and mwt.cycleway is null;

-- Cycle route requiring a dismount. (16 10s);
-- Strictly bicycle=no is supposed to mean bicycles cannot even be pushed.;
-- But if this is on a cycle route it is safe to infer that the intention is bicycle=dismount.;
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Cycle route requiring a dismount'),
		   bicycle = 'dismount'
	 where bicycle = 'no' and routeId > 0 and foot != 'no';


-- Step 6 highwayImplications();
-- Apply the implications arising from the highway tag;
-- This does not overwrite things that have been already explicitly defined in the original tag data (perhaps cleaned).;
-- OSM: highway=bridleway implies foot=yes;
update map_way_tags set foot = 'yes', sieveNote = concat(sieveNote,'|','OSM: highway=bridleway implies foot=yes') where highway = 'bridleway' and foot is null;
-- OSM: highway=bridleway implies bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','OSM: highway=bridleway implies bicycle=yes') where highway = 'bridleway' and bicycle is null;
-- OSM: highway=bus_guideway implies access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','OSM: highway=bus_guideway implies access=no') where highway = 'bus_guideway' and access is null;
-- OSM: highway=cycleway implies bicycle=designated;
update map_way_tags set bicycle = 'designated', sieveNote = concat(sieveNote,'|','OSM: highway=cycleway implies bicycle=designated') where highway = 'cycleway' and bicycle is null;
-- OSM: highway=cycleway implies cycleway=track;
update map_way_tags set cycleway = 'track', sieveNote = concat(sieveNote,'|','OSM: highway=cycleway implies cycleway=track') where highway = 'cycleway' and cycleway is null;
-- OSM: highway=footway implies foot=designated;
update map_way_tags set foot = 'designated', sieveNote = concat(sieveNote,'|','OSM: highway=footway implies foot=designated') where highway = 'footway' and foot is null;
-- OSM: highway=motorway implies access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','OSM: highway=motorway implies access=no') where highway = 'motorway' and access is null;
-- OSM: highway=motorway implies oneway=yes;
update map_way_tags set oneway = 'yes', sieveNote = concat(sieveNote,'|','OSM: highway=motorway implies oneway=yes') where highway = 'motorway' and oneway is null;
-- OSM: highway=motorway_link implies access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','OSM: highway=motorway_link implies access=no') where highway = 'motorway_link' and access is null;
-- OSM: highway=motorway_link implies oneway=yes;
update map_way_tags set oneway = 'yes', sieveNote = concat(sieveNote,'|','OSM: highway=motorway_link implies oneway=yes') where highway = 'motorway_link' and oneway is null;
-- OSM: highway=steps implies access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','OSM: highway=steps implies access=no') where highway = 'steps' and access is null;
-- OSM: highway=steps implies foot=yes;
update map_way_tags set foot = 'yes', sieveNote = concat(sieveNote,'|','OSM: highway=steps implies foot=yes') where highway = 'steps' and foot is null;
-- OSM: highway=tertiary implies access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','OSM: highway=tertiary implies access=yes') where highway = 'tertiary' and access is null;

-- Step 7 csInferences();
-- Apply the CycleStreets inferences of the highway tag;
-- CS: highway=byway infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=byway infers access=yes') where highway = 'byway' and access is null;
-- CS: highway=construction infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=construction infers access=no') where highway = 'construction' and access is null;
-- CS: highway=cycleway infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=cycleway infers access=yes') where highway = 'cycleway' and access is null;
-- CS: highway=ferry infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=ferry infers access=yes') where highway = 'ferry' and access is null;
-- CS: highway=footway infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=footway infers access=no') where highway = 'footway' and access is null;
-- CS: highway=footway infers bicycle=dismount;
update map_way_tags set bicycle = 'dismount', sieveNote = concat(sieveNote,'|','CS: highway=footway infers bicycle=dismount') where highway = 'footway' and bicycle is null;
-- CS: highway=living_street infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=living_street infers access=yes') where highway = 'living_street' and access is null;
-- CS: highway=motorway_link infers bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=motorway_link infers bicycle=yes') where highway = 'motorway_link' and bicycle is null;
-- CS: highway=path infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=path infers access=no') where highway = 'path' and access is null;
-- CS: highway=path infers foot=yes;
update map_way_tags set foot = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=path infers foot=yes') where highway = 'path' and foot is null;
-- CS: highway=path infers bicycle=dismount;
update map_way_tags set bicycle = 'dismount', sieveNote = concat(sieveNote,'|','CS: highway=path infers bicycle=dismount') where highway = 'path' and bicycle is null;
-- CS: highway=pedestrian infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=pedestrian infers access=no') where highway = 'pedestrian' and access is null;
-- CS: highway=pedestrian infers foot=yes;
update map_way_tags set foot = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=pedestrian infers foot=yes') where highway = 'pedestrian' and foot is null;
-- CS: highway=pedestrian infers bicycle=dismount;
update map_way_tags set bicycle = 'dismount', sieveNote = concat(sieveNote,'|','CS: highway=pedestrian infers bicycle=dismount') where highway = 'pedestrian' and bicycle is null;
-- CS: highway=primary infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=primary infers access=no') where highway = 'primary' and access is null;
-- CS: highway=primary infers bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=primary infers bicycle=yes') where highway = 'primary' and bicycle is null;
-- CS: highway=primary_link infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=primary_link infers access=no') where highway = 'primary_link' and access is null;
-- CS: highway=primary_link infers bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=primary_link infers bicycle=yes') where highway = 'primary_link' and bicycle is null;
-- CS: highway=residential infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=residential infers access=yes') where highway = 'residential' and access is null;
-- CS: highway=road infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=road infers access=yes') where highway = 'road' and access is null;
-- CS: highway=secondary infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=secondary infers access=no') where highway = 'secondary' and access is null;
-- CS: highway=secondary infers bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=secondary infers bicycle=yes') where highway = 'secondary' and bicycle is null;
-- CS: highway=service infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=service infers access=yes') where highway = 'service' and access is null;
-- CS: highway=track infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=track infers access=yes') where highway = 'track' and access is null;
-- CS: highway=trunk infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=trunk infers access=no') where highway = 'trunk' and access is null;
-- CS: highway=trunk infers bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=trunk infers bicycle=yes') where highway = 'trunk' and bicycle is null;
-- CS: highway=trunk_link infers access=no;
update map_way_tags set access = 'no', sieveNote = concat(sieveNote,'|','CS: highway=trunk_link infers access=no') where highway = 'trunk_link' and access is null;
-- CS: highway=trunk_link infers bicycle=yes;
update map_way_tags set bicycle = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=trunk_link infers bicycle=yes') where highway = 'trunk_link' and bicycle is null;
-- CS: highway=unclassified infers access=yes;
update map_way_tags set access = 'yes', sieveNote = concat(sieveNote,'|','CS: highway=unclassified infers access=yes') where highway = 'unclassified' and access is null;

-- Step 8 preAccess();

-- Oneway cycleways are not walkable.;
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Oneway cycleways are not walkable'),
	       foot = 'no'
	 where highway = 'cycleway' and oneway = 'yes';


-- Step 9 inheritFromAccess();
-- Perhaps this method is rather too complicated.;
-- A better thing may be to get rid of all the access=no, bicycle=yes inferences.;
-- Assume that bicycles are allowed unless bicycle is marked as no or dismount or private etc.;
-- Ditto foot;

-- Routeability requires (access AND bicycle).;
-- Bicycling is allowed unless blocked either by access tag or bicycle tag.;

-- The bicycle field inherits from access. (58252 13s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Inherit: bicycle=access'),
	       bicycle = access       
	 where bicycle is null and access is not null and access != 'designated';

-- The foot field inherits from access. (55278 14s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Inherit: foot=access'),
	       foot = access
	 where foot is null and access is not null and access != 'designated';


-- Step 10 initializeTranslation();
-- At this point the map_way_tags have all been cleaned and normalised as much as possible.;
-- From here the translation table is built up where a routingFactor is selected for a combination of way tags.;

-- Add the routing parameter fields to the map_way_tags table. (3267555 27s);
ALTER TABLE `map_way_tags`
ADD `cyclable` ENUM( 'yes', 'no' ) NOT NULL DEFAULT 'yes' COMMENT 'Whether the way can be ridden.' AFTER `sieveNote` ,
ADD `walkable` ENUM( 'yes', 'no' ) NOT NULL DEFAULT 'yes' COMMENT 'Whether the way can be walked.' AFTER `cyclable`, 
ADD `quietness`   TINYINT UNSIGNED NOT NULL DEFAULT '100' COMMENT 'A percentage, 100 for the quietest routes.' AFTER `walkable`,
ADD `speed`       TINYINT UNSIGNED NOT NULL DEFAULT '6'   COMMENT 'Maximum speed in km/h.' AFTER `quietness`,
ADD `pause`      SMALLINT UNSIGNED NOT NULL DEFAULT '0'   COMMENT 'Delay in seconds for using this way.' AFTER `speed`;


-- Initialize the map_way_tags parameters by matching the highway tag in the map_osmWayTag table (3267555 144s);
	update map_way_tags mwt
	  join map_osmWayTag oWT on oWT.k = 'highway' and oWT.element = 'way' and oWT.v = mwt.highway
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Translate: ', if(oWT.cyclable='yes','c','_'), if(oWT.walkable='yes','w','_'), oWT.quietness, '-', oWT.speed, '-', oWT.pause),
		   mwt.cyclable  = oWT.cyclable,
		   mwt.walkable  = oWT.walkable,
		   mwt.quietness = oWT.quietness,
		   mwt.speed     = oWT.speed,
		   mwt.pause     = oWT.pause;

-- Implementing UK legal cycling rules. i.e. the Highway Code.;
-- Infer cyclable and walkable from the bicycle and foot tags.;

-- Fix cyclable=no from bicycle tag (524858 23s);
	update map_way_tags
	   set sieveNote     = concat(sieveNote,'|','cyclable=no from bicycle tag'),
		   cyclable = 'no'
	 where bicycle in ('no','dismount');

-- Fix cyclable=yes from bicycle tag (427718 23s);
	update map_way_tags
	   set sieveNote     = concat(sieveNote,'|','cyclable=yes from bicycle tag'),
		   cyclable = 'yes'
	 where bicycle in ('yes','designated','permissive','private','destination');

-- Fix walkable=no from foot tag (69883 19s);
	update map_way_tags
	   set sieveNote     = concat(sieveNote,'|','walkable=no from foot tag'),
		   walkable = 'no'
	 where foot = 'no';

-- Fix walkable=yes from foot tag (719730 29s);
	update map_way_tags
	   set sieveNote     = concat(sieveNote,'|','walkable=yes from foot tag'),
		   walkable = 'yes'
	 where foot in ('yes','permissive','designated','private');

-- Bicycles cannot even be pushed e.g when there are barriers designed to prevent bikes getting through, or shopping malls. (18728 16s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Bicycles cannot even be pushed'),
		   walkable = 'no'
	 where bicycle = 'no';


-- Step 11 cyclingRules();
-- Before this step the changes were objective, we now move into the subjective phase i.e. applying scorings.;

-- Be careful in all these rules not to let the numbers go outside their numeric range. It can lead to overflows, and unusual routing.;

-- Avoid private, which is likely if private is on any of the access, foot or bicycle tags.;
-- These values should be enough to make the router find alternatives to transitting private areas. (38695 19s);
	update map_way_tags
	   set sieveNote      = concat(sieveNote,'|','Avoid private =1=1=60'),
		   quietness = 1,
		   speed     = 1,
		   pause     = 60
	 where access = 'private' or bicycle = 'private' or foot = 'private';

-- Avoid destination;
-- The need to avoid these areas is not as strong as for private areas. (When included permissive: 34404 18s);
	update map_way_tags
	   set sieveNote      = concat(sieveNote,'|','Avoid destination =42=12=10'),
		   quietness = 42,
		   speed     = 12,
		   pause     = 10
	 where bicycle   = 'destination'
		or foot      = 'destination';

-- Appreciate continuous cycle routes. (35138 10s);
	update map_way_tags tags
	  join map_osmBicycleRoute route on tags.routeId = route.id
	   set sieveNote      = concat(sieveNote,'|','Appreciate continuous cycle routes +50+3-3'),
		   quietness = if(quietness >= 50, 100, quietness + 50),
		   speed     = speed + 3,
		   pause     = if(pause > 3, pause - 3, 0)
	 where route.rating = 'continuous';

-- Appreciate touring cycle routes. (16673 14s);
	update map_way_tags tags
	  join map_osmBicycleRoute route on tags.routeId = route.id
	   set sieveNote      = concat(sieveNote,'|','Appreciate suggested cycle routes +25+6-3'),
		   quietness = if(quietness >= 75, 100, quietness + 25),
		   speed     = speed + 6,
		   pause     = if(pause > 3, pause - 3, 0)
	 where route.rating = 'touring';

-- Appreciate suggested cycle routes. (12960 17s);
	update map_way_tags tags
	  join map_osmBicycleRoute route on tags.routeId = route.id
	   set sieveNote      = concat(sieveNote,'|','Appreciate suggested cycle routes +30+3-3'),
		   quietness = if(quietness >= 70, 100, quietness + 30),
		   speed     = speed + 3,
		   pause     = if(pause > 3, pause - 3, 0)
	 where route.rating = 'suggested';

-- Appreciate cycling facilities. (68234 26s);
	update map_way_tags
	   set sieveNote      = concat(sieveNote,'|','Appreciate cycling facilities +30+3-3'),
		   quietness = if(quietness >= 70, 100, quietness + 30),
		   speed     = speed + 3,
		   pause     = if(pause > 3, pause - 3, 0)
	 where cycleway  in ('lane','track','opposite_lane','opposite_track','opposite');

-- Appreciate segregated fooway, (but not as much as facilities). (66 14s);
	update map_way_tags
	   set sieveNote      = concat(sieveNote,'|','Segregated fooway +15+2-2'),
		   quietness = if(quietness >= 85, 100, quietness + 15),
		   speed     = speed + 2,
		   pause     = if(pause > 2,pause - 2, 1)
	 where bicycle   = 'yes'
	   and cycleway  = 'segregated';

-- Acknowledge shared footway. (284 14s);
	update map_way_tags
	   set sieveNote      = concat(sieveNote,'|','Acknowledge shared footway +12-2+2'),
		   quietness = if(quietness >= 88, 100, quietness + 12),
		   speed     = if(speed > 3, speed - 2, 1),
		   pause     = pause + 2
	 where bicycle   = 'yes'
	   and cycleway  = 'shared';

-- Where bikes allowed on footway. (30335 18s);
	update map_way_tags
	   set sieveNote      = concat(sieveNote,'|','Where bikes allowed on footway +40-2+7'),
		   quietness = if(quietness > 60, 100, quietness + 40),
		   speed     = if(speed > 3,speed - 2,1),
		   pause     = pause + 7
	 where bicycle   = 'yes'
	   and (foot	 = 'designated'
			or highway   = 'footway');

-- highway=track|footway|bridleway|service, tracktype=grade 1-5. (8226 47s);
-- Grade 1 track - paved;
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'tracktype' and wt.v = 'grade1'
	   set mwt.sieveNote  = concat(mwt.sieveNote,'|','Grade 1 track - paved +10+4,0'),
		   quietness = if(quietness > 90, 100, quietness + 10),
		   speed     = speed + 4
	 where highway  in ('track','bridleway','footway','service');

-- Grade 2 track - unpaved densely packed (13105 49s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'tracktype' and wt.v = 'grade2'
	   set mwt.sieveNote  = concat(mwt.sieveNote,'|','Grade 2 track - unpaved densely packed +4+1,0'),
		   quietness = if(quietness > 95, 100, quietness + 4),
		   speed     = speed + 1
	 where highway  in ('track','bridleway','footway','service');

-- Grade 3 track - unpaved mixture (12997 49s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'tracktype' and wt.v = 'grade3'
	   set mwt.sieveNote  = concat(mwt.sieveNote,'|','Grade 3 track - unpaved mixture -5-2,0'),
		   quietness = if(quietness > 6, quietness - 5, 1),
		   speed     = if(speed > 3, speed - 2, 1)
	 where highway  in ('track','bridleway','footway','service');

-- Grade 4 track - some hardcore (6600 49s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'tracktype' and wt.v = 'grade4'
	   set mwt.sieveNote  = concat(mwt.sieveNote,'|','Grade 4 track - some hardcore -15-6,0'),
		   quietness = if(quietness > 16, quietness - 15, 1),
		   speed     = if(speed > 7, speed - 6, 1)
	 where highway  in ('track','bridleway','footway','service');

-- Grade 5 track - no hardcore (5018 48s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id and wt.k = 'tracktype' and wt.v = 'grade5'
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Grade 5 track - no hardcore -25-6,0'),
		   quietness = if(quietness > 26, quietness - 25, 1),
		   speed     = if(speed > 7, speed - 6, 1)
	 where highway  in ('track','bridleway','footway','service');


-- Steps with channels. (47 9s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.highway = 'steps' and mwt.id = wt.id and wt.k='ramp:bicycle' and wt.v='yes'
	   set mwt.sieveNote = concat(mwt.sieveNote,'|', 'Detect steps with ramp:bicycle=yes +10+1,0'),
		   quietness = if(quietness > 90, 100, quietness + 10),
	       speed = speed + 1;

-- Surfaced bridleway. (375 8s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Surfaced ', highway ,' +20,20,0'),
		   quietness = quietness + 20,
		   speed     = 20,
		   pause     = 0
	 where highway  in ('track','bridleway','footway','service')
	   and wt.k		 = 'surface'
	   and wt.v     in ('paved','asphalt');

-- Traffic calming that also slows bikes (523 7s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Calming slows bikes ', highway ,' +20,-4,+1'),
		   quietness = quietness + 20,
		   speed     = speed - 4,
		   pause     = pause + 1
	 where highway  in ('residential', 'tertiary', 'unclassified', 'road', 'service')
	   and wt.k		 = 'traffic_calming'
	   and wt.v     in ('bump', 'yes', 'hump');

-- Traffic calming that helps bikes (302 7s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Calming helps bikes ', highway ,' +20,+0,0'),
		   quietness = quietness + 20,
		   speed     = speed,
		   pause     = 0
	 where highway  in ('residential', 'tertiary', 'unclassified', 'road', 'service')
	   and wt.k		 = 'traffic_calming'
	   and wt.v     in ('cushion', 'chicane', 'table');


-- Routes that are lit (35503 6s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.sieveNote = concat(mwt.sieveNote,'|','Lighting helps bikes ', highway ,' +10,+2,0'),
		   quietness = quietness + 10,
		   speed     = speed + 2,
		   pause     = 0
	 where highway  in ('residential', 'tertiary', 'unclassified', 'road', 'service', 'bridleway', 'steps', 'track',  'living_street', 'cycleway', 'path', 'secondary', 'footway')
	   and wt.k		 = 'lit'
	   and wt.v     in ('yes', 'automatic');

-- Too many steps, not on a bike route (3 4s);
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.highway = 'steps' and mwt.id = wt.id and wt.k='step_count' and wt.v > 50
	   set mwt.sieveNote = concat(mwt.sieveNote,'|', 'Too many steps, and not on a bike route'),
	       bicycle = 'no'
	 where not (bicycle  in ('yes','designated')
				or routeId > 0
				or cycleway is not null);

-- The rules may have changed the quietness / speed / pause outside their limits. Fix now.;
-- Normalize quietness. (0 13s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Normalize quietness'),
		   quietness = if(quietness > 100, 100, 1)
	 where quietness > 100 or quietness < 1;

-- Normalize speed. (0 13s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Normalize speed'),
		   speed = 1
	 where speed < 1;

-- Normalize pause. (0 12s);
	update map_way_tags
	   set sieveNote = concat(sieveNote,'|','Normalize pause'),
		   pause = 0
	 where pause < 0;

-- break;