Journey planner help

-- STEP 1. 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 COMMENT 'planetExtractOSM.ways.id',
     `originalTags` text COLLATE utf8_unicode_ci not null COMMENT 'Tab separated original tags',
--     `highway`  varchar(255) COLLATE utf8_unicode_ci NULL DEFAULT NULL,
     `highway`  enum('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','tertiary','unclassified','road','residential','living_street','service','track','pedestrian','bus_guideway','path','cycleway','footway','bridleway','byway','steps','construction','ferry','secondary_link','towpath','unsurfaced','FIXME','abandoned','tertiary_link','suggested','walkway','raceway','racetrack','public_bridleway','traffic_signals','proposed','private','crossing','layby','access','platform','line_of_cairns','racetracksmall','broken bridge','trail','gallop','unpaved road','closed:service','services','grass','pier','disused','demolished road','unknown','back_passage','elevator','psv','no','gallops','ramp','lift','zebra','undesignated','bus_stop','access_ramp','arcade','alley','escape_lane','plaza','stepping_stones','footway broken','dam','conveyor','byway_open_to_all_traffic','closed_footway','closed_steps','barrier','emergency_access_point','weir','closed:bridleway','ford','course_of_highway','s','slipway','stream','roman_road','drive','planned','bus_lane','historic','Stookes Way','pathway','suspect','projected','emergency','possible','unfinished','turning_circle','Lane','foot','bus','subway','rest_area','depot','paved','yes','wall','farm','track/path thro park','not on the ground at all','none','service=parking_aisle','m','stepping stones','marshy track','surface','incline_steep','escalator','closed','unclassified_link','kerb','give_way','route  - there is no path','krb','incline','island','re#','res','parking_aisle','right of way','industrial','buslane','farm track','manor','former_footway','CO11 2NE','tracke','traffic_calming','disused_footway','name_only','pedestran','tar','parking','trck','bridge','waterway','minor road','ser')  COLLATE utf8_unicode_ci NULL DEFAULT NULL,
     `cycleway` enum('lane','track','opposite_lane','opposite_track','opposite','shared','segregated','no','share_busway','shared_lane','road','oneway','shared_use','yes','restaurant route','crawler_lane','cut_through','pavement_left','proposed','opposite_share_busway','left','pavement_right','mtb','right','path','Np','share_footway','proposed_lane','cycle','redway','only_sunday','crossing','highway','primary','bmx_track','shared use','lane_opposite','=yes','construction') COLLATE utf8_unicode_ci NULL DEFAULT NULL,
     `access`   enum('designated','yes','permissive','destination','private','no','delivery','controlled','official','emergency','public','4wd_only','unknown','foot','restricted','psv','16:30 - 10:30','residents','agricultural','FIXME','cars:private','taxi','perhaps','pubilc','direction','escorted','maybe private','MOD','forestry','probably private','unknown but frequently walked','taxis','bus','A&E Only','university','works traffic only','no motor traffic','closed','forced','customers','unknown - frequently walked','danger','fisherman','fee','defacto','staff','disabled','loading_only','authorised','psv=opposite_lane','timed','property','closed in the morning','emergency_only','so','unnknown','private?','Mad Dog Lane','Fir Tree Close','set_down','no_through_road','driveway','access_only','hgv','gated','++','military','access','limited','uncertain','inpassable','tidal','The Close','unknwon','Public Park','Goostry Road','Children','no_throughfair','GPS','dedicated','psv only','permit','customer','permit holders','public_footpath','wheelchair','residentail','prohibited','Dilston Close','Coquet Close','members','public_service_vehicle','construction','open_access','member','school','yea','none','buses')   COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'http://wiki.openstreetmap.org/wiki/Key:access',
     `foot`     enum('yes','permissive','designated','no','private','destination','unknown','unofficial','public','fixme','probably','official','difficult','path','tide_dependant','undefined','steps','crossing','Footway','Bishops Walk','presumably','defra','access','*','viewing platform','unnofficial','lane','definitive path','footpath','inside','ye','yea','Bing')     COLLATE utf8_unicode_ci NULL DEFAULT NULL,
     `bicycle`  enum('yes','no','designated','permissive','dismount','private','destination','pedestrianShared','fixme','Mo-Sa 09:30-17:30','discouraged','tolerated','unknown','probably not','shared','mtb','only with written permission','not_advisable','unsuitable','permissive or not at all!','official','loony_only','no idea','ramp','delivery','undefined','desginated','gutter_ramp','c','wheeled','opposite')  COLLATE utf8_unicode_ci NULL DEFAULT NULL,
     `oneway`   enum('yes','no','-1','true','yesno','undefined','1','reversible','other','unknown','false','GPS survey','0','fixme','-1 bicycle = no','(new value)no','A255','OS_OpenData_StreetView')   COLLATE utf8_unicode_ci NULL DEFAULT NULL,
     `routeId`  int unsigned not null DEFAULT '0' COMMENT 'Relation id',
     `routingFactorId` smallint unsigned not null default 0 comment 'map_routingFactor.id',
     `note`     text         COLLATE utf8_unicode_ci not null          COMMENT 'Inferences applied',
     PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Compilation of OSM tags related to the map_path.';

-- Create the ways, and set the starting note (text columns cannot have a default in mysql).;
INSERT map_way_tags(id, note)
SELECT SQL_NO_CACHE id, 'start'
  FROM map_path ORDER BY id ASC;

-- Note the original tags.;
	update map_way_tags y
	  join (select SQL_NO_CACHE mwt.id, group_concat(concat(wt.k,'=',wt.v) 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;

-- Copy OSM wayTag to the map_way_tags table fields.;
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.note = concat(mwt.note,'|','Planet: ', wt.k,'=',wt.v);
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.note = concat(mwt.note,'|','Planet: ', wt.k,'=',wt.v);
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.note = concat(mwt.note,'|','Planet: ', wt.k,'=',wt.v);
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.note = concat(mwt.note,'|','Planet: ', wt.k,'=',wt.v);
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.note = concat(mwt.note,'|','Planet: ', wt.k,'=',wt.v);
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.note = concat(mwt.note,'|','Planet: ', wt.k,'=',wt.v);

-- STEP 2 REPAIR;

-- REPAIR PHASE 0;
-- Note: This section might be better named as wrong-tag (rather than cross-tag) repairs.;
-- 	     Some of the changes moving to the repair table.;

-- These are some special cross-tag repairs that can't be made by the simple repair table mechanism of later phases.;

-- Cross tag repair highway=null,cycleway=not null to highway=cycleway.;
	update map_way_tags
	   set note = concat(note,'|','Cross tag repair highway=null,cycleway=not null to highway=cycleway.'),
		   highway = 'cycleway'
	 where highway is null and cycleway is not null;

-- Cross tag repair highway=access to highway=service,access=destination;
    update map_way_tags
	   set note = concat(note,'|','Cross tag repair highway=access to highway=service,access=destination'),
		   access  = 'destination',
		   highway = 'service'
	 where highway = 'access' and access is null;

-- Cross tag repair highway=private or private_driveway to highway=service,access=private;
    update map_way_tags
	   set note = concat(note,'|','Cross tag repair highway=', highway, ' to highway=service,access=private'),
		   access  = 'private',
		   highway = 'service'
	 where highway in ('private','private_driveway') and access is null;

-- Cross tag repair cycleway=yes|no to bicycle=yes|no;
    update map_way_tags
	   set note = concat(note,'|','Cross tag repair cycleway=', cycleway, ' to bicycle=', cycleway),
		   bicycle  = cycleway,
		   cycleway = null
	 where cycleway in ('yes','no') and bicycle is null;

-- REPAIR PHASE 1;
-- The intention of this section is to populate the 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 the repairs that are needed for each tag;

-- Compile Highway repairs;
	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 ('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','tertiary','unclassified','road','residential','living_street','service','track','pedestrian','bus_guideway','path','cycleway','footway','bridleway','byway','steps','construction','ferry')
  group by highway order by count desc;

-- Compile Cycleway repairs;
	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','track','opposite_lane','opposite_track','opposite','shared','segregated')
  group by cycleway order by count desc;

-- Compile Access repairs;
	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','yes','permissive','destination','private','no')
  group by access order by count desc;

-- Compile Bicycle repairs;
	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 ('yes','no','designated','permissive','dismount','private','destination')
  group by bicycle order by count desc;

-- Compile Foot repairs;
	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 ('yes','permissive','designated','no','private','destination')
  group by foot order by count desc;

-- Compile Oneway repairs;
	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 ('yes','no','-1')
  group by oneway order by count desc;

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

-- Highway count;
	update map_osm_repair_tags a
	  join (select SQL_NO_CACHE 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;
	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.note = concat(mwt.note,'|','Repaired ', rt.tag, '=', mwt.highway),
           mwt.highway  = rt.repair;

-- Cycleway count;
	update map_osm_repair_tags a
	  join (select SQL_NO_CACHE 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;
	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.note = concat(mwt.note,'|','Repaired ', rt.tag, '=', mwt.cycleway),
		   mwt.cycleway  = rt.repair;

-- Access count;
	update map_osm_repair_tags a
	  join (select SQL_NO_CACHE 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;
	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.note = concat(mwt.note,'|','Repaired ', rt.tag, '=', mwt.access),
		   mwt.access  = rt.repair;

-- Bicycle count;
	update map_osm_repair_tags a
	  join (select SQL_NO_CACHE 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;
	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.note = concat(mwt.note,'|','Repaired ', rt.tag, '=', mwt.bicycle),
		   mwt.bicycle  = rt.repair;

-- Foot count;
	update map_osm_repair_tags a
	  join (select SQL_NO_CACHE 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;
	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.note = concat(mwt.note,'|','Repaired ', rt.tag, '=', mwt.foot),
		   mwt.foot  = rt.repair;

-- Oneway count;
	update map_osm_repair_tags a
	  join (select SQL_NO_CACHE 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;
	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.note = concat(mwt.note,'|','Repaired ', rt.tag, '=', mwt.oneway),
		   mwt.oneway  = rt.repair;

-- REPAIR PHASE 3;
-- 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;
	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.note = concat(mwt.note,'|','Assume roundabouts are oneway=yes'),
		   mwt.oneway = 'yes'
	 where mwt.oneway is null;

-- Assume roundabouts are not for walking: junction=roundabout => foot=no;
	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.note = concat(mwt.note,'|','Assume roundabouts are 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.).;
	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.note = concat(mwt.note,'|','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.;
-- 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.note = concat(mwt.note,'|','Jetties => highway=footway'),
		   mwt.highway = 'footway'
	 where mwt.highway <> 'footway' or highway is null;

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


-- STEP 3 CLEAN HIGHWAY TAG.;

-- Canonize by changing the type to enum. Unrecognized values become the empty string, or index 0 in the enum list.;
ALTER TABLE `map_way_tags`
 CHANGE `highway`   `highway` ENUM('motorway','motorway_link','trunk','trunk_link','primary','primary_link','secondary','tertiary','unclassified','road','residential','living_street','service','track','pedestrian','bus_guideway','path','cycleway','footway','bridleway','byway','steps','construction','ferry')  CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
 CHANGE `cycleway` `cycleway` ENUM('lane','track','opposite_lane','opposite_track','opposite','shared','segregated') CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
 CHANGE `access`     `access` ENUM('designated','yes','permissive','destination','private','no')   CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
 CHANGE `bicycle`   `bicycle` ENUM('yes','no','designated','permissive','dismount','private','destination')  CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
 CHANGE `foot`         `foot` ENUM('yes','permissive','designated','no','private','destination')     CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
 CHANGE `oneway`     `oneway` ENUM('yes','no','-1')   CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no';

-- Delete unrecognized highways from map_path.;
	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.;
	delete from map_way_tags
	 where highway = 0 or highway is null;

-- STEP 4 HAS MOVED TO STEP 6.5;

-- STEP 5 SIMPLIFY OTHER SECONDARY TAGS.;

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

-- Handling 2 way cycling in one-way streets: oneway=no where cycleway=opposite*;
-- 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 XXX INHERIT FROM ACCESS;
-- This is far too complicated.;
-- A better thing to do, is 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.;
	update map_way_tags
	   set note = concat(note,'|','Inherit: bicycle=access'),
	       bicycle = access       
	 where bicycle is null and access is not null and access != 'designated';

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



-- STEP 6. Cycle Routes;
-- http://wiki.openstreetmap.org/index.php/Cycle_routes ;
-- http://wiki.openstreetmap.org/wiki/Relations/Routes#Cycle_Routes ;
-- This is more complex because the tags are some distance from the ways.;

DROP TABLE IF EXISTS map_osmBicycleRoute;
CREATE TABLE `map_osmBicycleRoute` (
     `id` int unsigned not null AUTO_INCREMENT,
     `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Name of the bicycle route',
     `operator` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Who is in charge of the route',
     `network`  ENUM('mtb', 'lcn', 'rcn', 'ncn' , 'icn') DEFAULT NULL COMMENT 'Which network',
     `ref`      varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Reference number',
     `state`    varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'State of the route',
     `distance`    varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The length of the route',
     `distanceKm` smallint unsigned default 0 COMMENT 'The length of the route 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.;
	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 = 'bicycle';

-- Pick out items from the route relation that are of interest to cycle routing.;
-- Route name;
	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;
	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;
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.network = tags.v
	 where tags.k = 'network';

-- Route ref;
	update map_osmBicycleRoute route
	  join planetDB.osm_relationTag tags on tags.id = route.id
	   set route.ref = tags.v
	 where tags.k = 'ref';

-- Route state;
	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;
	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;
	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';

-- National cycle network name;
	update map_osmBicycleRoute
	   set name = concat('National cycle network ', ref)
	 where name is null and network = 'ncn' and ref is not null;

-- Regional cycle network name;
	update map_osmBicycleRoute
	   set name = concat('Regional cycle network ', ref)
	 where name is null and network = 'rcn' and ref is not null;

-- Local cycle network name;
	update map_osmBicycleRoute
	   set name = concat('Local cycle network ', ref)
	 where name is null and network = 'lcn' and ref is not null;

-- Repair the network, when it has been really used to mean operator=National Byway.;
	update map_osmBicycleRoute
	   set operator = 'National Byway',
		   network = 'rcn'
	 where network in ('National Byway', 'national_byway');

-- Repair deprecated network (this specific fix must be applied before the next fix).;
	update map_osmBicycleRoute
	   set state = 'temporary'
	 where network in ('cambscc_deprecated11');

-- Repair the network, when it has been really used to mean operator=Cambridgeshire County Council.;
	update map_osmBicycleRoute
	   set operator = 'Cambridgeshire County Council',
		   network = 'rcn'
	 where network in ('cambscc_link51', 'cambscc_deprecated11');

-- Repair the network with obvious corrections/simplifications.;
	update map_osmBicycleRoute
	   set network = 'lcn'
	 where network in ('local', 'lcn2');

-- This does leave some relations with no network.;
-- They could probably be categorized by counting their lengths - but alas that information has not been calculated at this stage.;


-- Scrape together a name for un-named routes.;
	update map_osmBicycleRoute
	   set name = concat('Route', if(operator is null, '', concat(' ', operator)), if(network is null, '', concat(' ', network)), if(ref is null, '', concat(' ', ref)))
	 where name is null and (operator is not null or network is not null or ref is not null);

-- Anonymous bicycle routes;
	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 ncn routes;
	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.note = concat(way.note,'|','Way on ncn route'),
		   way.routeId = rm.id
	 where route.network = 'ncn' and way.routeId = 0;

-- Ways on rcn routes;
	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.note = concat(way.note,'|','Way on rcn route'),
		   way.routeId = rm.id
	 where route.network = 'rcn' and way.routeId = 0;

-- Ways on lcn routes;
	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.note = concat(way.note,'|','Way on lcn route'),
		   way.routeId = rm.id
	 where route.network = 'lcn' 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.;
ALTER TABLE `map_way_tags` ADD `network` ENUM('mtb', 'lcn', 'rcn', 'ncn' , 'icn') DEFAULT NULL COMMENT 'The type of cycle route' AFTER `routeId`;

-- In terms of general cycle routing the order of importance from highest to lowest is icn,ncn,rcn,lcn,mtb.;
-- Find the ref, or just the *cn=yes tag, avoiding *cn=proposed.;
-- International;
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set note  = concat(way.note, '|', 'Directly tagged icn'),
	       network = 'icn'
	 where routeId = 0 and network is null and (k = 'icn_ref' or (k = 'icn' and v = 'yes'));
-- National;
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set note  = concat(way.note, '|', 'Directly tagged ncn'),
	       network = 'ncn'
	 where routeId = 0 and network is null and (k = 'ncn_ref' or (k = 'ncn' and v = 'yes'));
-- Regional;
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set note  = concat(way.note, '|', 'Directly tagged rcn'),
	       network = 'rcn'
	 where routeId = 0 and network is null and (k = 'rcn_ref' or (k = 'rcn' and v = 'yes'));
-- Local;
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set note  = concat(way.note, '|', 'Directly tagged lcn'),
	       network = 'lcn'
	 where routeId = 0 and network is null and (k = 'lcn_ref' or (k = 'lcn' and v = 'yes'));
-- Mountain bike;
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set note  = concat(way.note, '|', 'Directly tagged mtb'),
	       network = 'mtb'
	 where routeId = 0 and network is null and (k = 'mtb_ref' or (k = 'mtb' and v = 'yes'));

-- Add a ref field for directly tagged routes.;
ALTER TABLE `map_way_tags` ADD `ref` varchar(255) COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Any route reference found.' AFTER `network`;

-- Now find any refs amongst these.; 
	update map_way_tags way
	  join planetDB.osm_wayTag tags on way.id = tags.id
	   set note = concat(way.note, '|', 'Directly tagged *cn_ref'),
	       ref  = 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 SQL_NO_CACHE max(id) from map_osmBicycleRoute);

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

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

-- This covers the cases that have ref=NULL that are missed in the above query.;
	update map_way_tags way
	  join map_osmBicycleRoute route on way.network = route.network and route.id > @maxRelationRouteId
	   set way.note = concat(way.note,'|','Way on directly tagged route without ref'),
		   way.routeId = route.id
	 where way.routeId = 0;

-- Method of rating the route.;

-- Named long distance ncn routes;
	update map_osmBicycleRoute route
	   set rating  = 'continuous'
	 where network = 'ncn' and ref is not null;

-- Regional routes;
	update map_osmBicycleRoute route
	   set rating  = 'touring'
	 where network = 'rcn';

-- Local routes;
	update map_osmBicycleRoute route
	   set rating  = 'suggested'
	 where network = 'lcn';

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




-- STEP XXX FACILITIES / 'FARCILITIES'.;
-- In an ideal world we'd split based on directionality at this point, rather than at the end.;
-- Use of cycleway:(left|right) here is relevant to #wayDirectionality (hashtag for related issues);
-- 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.;
	update map_way_tags mwt
	  join planetDB.osm_wayTag wt on mwt.id = wt.id
	   set mwt.note = concat(mwt.note,'|','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.;
-- 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 note = concat(note,'|','Cycle route requiring a dismount'),
		   bicycle = 'dismount'
	 where bicycle = 'no' and routeId > 0 and foot != 'no';

-- STEP 6.5 IMPLICATIONS && INFERENCES;
-- STEP 6.5.1 HIGHWAY IMPLICATIONS.;
-- 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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','OSM: highway=tertiary implies access=yes') where highway = 'tertiary' and access is null;

-- STEP 6.5.2 MAKE INFERENCES.;

-- Apply the CycleStreets inferences of the highway tag;
-- During a future revsion, we shall remove the many of the inferences (particularly on the access tag), and treat them as assumed anyway.;
-- CS: highway=byway infers access=yes;
update map_way_tags set access = 'yes', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','CS: highway=cycleway infers access=yes') where highway = 'cycleway' and access is null;
-- CS: highway=cycleway infers foot=no;
update map_way_tags set foot = 'no', note = concat(note,'|','CS: highway=cycleway infers foot=no') where highway = 'cycleway' and foot is null;
-- CS: highway=ferry infers access=yes;
update map_way_tags set access = 'yes', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','CS: highway=living_street infers access=yes') where highway = 'living_street' and access is null;
-- CS: highway=path infers access=no;
update map_way_tags set access = 'no', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','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', note = concat(note,'|','CS: highway=unclassified infers access=yes') where highway = 'unclassified' and access is null;

-- STEP 7. Initialize the translation.;

-- 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.;
ALTER TABLE `map_way_tags`
ADD `cyclable` ENUM( 'yes', 'no' ) NOT NULL DEFAULT 'yes' COMMENT 'Whether the way can be ridden.' AFTER `note` ,
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;
	update map_way_tags mwt
	  join map_osmWayTag oWT on oWT.k = 'highway' and oWT.v = mwt.highway
	   set mwt.note = concat(mwt.note,'|','Initialize translation from highway tag ', oWT.quietness, '% ', oWT.speed, 'km/h ', oWT.pause, 's'),
		   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;
	update map_way_tags
	   set note     = concat(note,'|','cyclable=no from bicycle tag'),
		   cyclable = 'no'
	 where bicycle in ('no','dismount');

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

-- Fix walkable=no from foot tag;
	update map_way_tags
	   set note     = concat(note,'|','walkable=no from foot tag'),
		   walkable = 'no'
	 where foot = 'no';

-- Fix walkable=yes from foot tag;
	update map_way_tags
	   set note     = concat(note,'|','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.;
	update map_way_tags
	   set note = concat(note,'|','Bicycles cannot even be pushed'),
		   walkable = 'no'
	 where bicycle = 'no';

-- STEP 8 Apply the Cycling Rules.;
-- 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.;
	update map_way_tags
	   set note      = concat(note,'|','Avoid private =1=1=60'),
		   quietness = 1,
		   speed     = 1,
		   pause     = 60
	 where access = 'private' or bicycle = 'private' or foot = 'private';

-- Avoid destination/permissive;
-- The need to avoid these areas is not as strong as for private areas.;
	update map_way_tags
	   set note      = concat(note,'|','Avoid destination/permissive =42=12=10'),
		   quietness = 42,
		   speed     = 12,
		   pause     = 10
	 where bicycle   in ('destination','permissive')
		or foot      in ('destination','permissive');

-- Appreciate continuous cycle routes.;
	update map_way_tags tags
	  join map_osmBicycleRoute route on tags.routeId = route.id
	   set note      = concat(note,'|','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.;
	update map_way_tags tags
	  join map_osmBicycleRoute route on tags.routeId = route.id
	   set note      = concat(note,'|','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.;
	update map_way_tags tags
	  join map_osmBicycleRoute route on tags.routeId = route.id
	   set note      = concat(note,'|','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.;
-- Use of opposite here is relevant to #wayDirectionality (hashtag for related issues);
	update map_way_tags
	   set note      = concat(note,'|','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).;
	update map_way_tags
	   set note      = concat(note,'|','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.;
	update map_way_tags
	   set note      = concat(note,'|','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.;
	update map_way_tags
	   set note      = concat(note,'|','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.;
-- 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.note  = concat(mwt.note,'|','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;
	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.note  = concat(mwt.note,'|','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;
	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.note  = concat(mwt.note,'|','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;
	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.note  = concat(mwt.note,'|','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;
	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.note = concat(mwt.note,'|','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.;
	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.note = concat(mwt.note,'|', 'Detect steps with ramp:bicycle=yes +10+1,0'),
		   quietness = if(quietness > 90, 100, quietness + 10),
	       speed = speed + 1;

-- Too many steps, not on a bike route;
	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.note = concat(mwt.note,'|', '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.;
	update map_way_tags
	   set note = concat(note,'|','Normalize quietness'),
		   quietness = if(quietness > 100, 100, 1)
	 where quietness > 100 or quietness < 1;

-- Normalize speed.;
	update map_way_tags
	   set note = concat(note,'|','Normalize speed'),
		   speed = 1
	 where speed < 1;

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

-- break;