alter table planet_osm_roads add column "category" text;


--select distinct tags->'gauge', count(*) from planet_osm_roads where railway='rail' group by tags->'gauge' ;
--select distinct railway, count(*) from planet_osm_roads group by railway;
--select distinct tags->'usage', count(*) from planet_osm_roads group by tags->'usage' ;
-- osmosis --read-pbf file="france-latest.osm.pbf" --tf accept-ways railway=* --used-node --write-pbf rail.osm.pbf
-- osm2pgsql -d railways rail.osm.pbf  -c -k


-- Fix speeds
update planet_osm_roads set
  tags = tags || hstore('maxspeed', (rtrim(tags->'maxspeed', 'mph')::float * 1.6)::text)
  where tags->'maxspeed' like '%mph';

update planet_osm_roads set
  tags = tags || hstore('maxspeed', rtrim(tags->'maxspeed', 'km/h'))
  where tags->'maxspeed' like '%km/h';

update planet_osm_roads set
  tags = delete(tags, 'maxspeed')
  where tags->'maxspeed' !~ '^[0-9. ]+$';


-- Highspeed lines
update planet_osm_roads set category='primary'
  where (tags->'maxspeed')::float > 250
  and category is null and railway = 'rail';

update planet_osm_roads set category='primary'
  where name LIKE '%LGV%'
  or tags->'route_name' LIKE '%LGV%'
  and category is null and railway = 'rail';

update planet_osm_roads set category='primary'
  where tags->'highspeed' = 'yes'
  and category is null and railway = 'rail';


-- Long distance lines
--update planet_osm_roads set category='secondary'
--  where tags->'voltage' IN ('25000', '15000', '3000', '1500')
--  and category is null and railway = 'rail';

update planet_osm_roads set category='secondary'
  where tags->'usage' = 'main'
  and category is null and railway = 'rail';

update planet_osm_roads set category='secondary'
  where (tags->'maxspeed')::float <= 250 and (tags->'maxspeed')::float > 120
  and category is null and railway = 'rail';


-- Smaller lines
update planet_osm_roads set category='tertiary'
  where railway='narrow_gauge'
  and category is null;

update planet_osm_roads set category='tertiary'
  where tags->'gauge' in ('1000', '600')
  and category is null and railway = 'rail';

update planet_osm_roads set category='tertiary'
  where tags->'electrified' in('no', 'rail')
  and category is null and railway = 'rail';

update planet_osm_roads set category='tertiary'
  where tags->'usage' in ('branch', 'freight', 'tourism')
  and category is null and railway = 'rail';

update planet_osm_roads set category='tertiary'
  where (tags->'maxspeed')::float <= 120
  and category is null and railway = 'rail';


-- Service tracks
update planet_osm_roads set category='lowest'
  where service in ('yard', 'spur', 'siding')
  and category is null;


-- Urban rails
update planet_osm_roads set category='urban'
  where railway in ('subway', 'funicular', 'light_rail', 'tram', 'monorail')
  and category is null;


-- Tracks where no train runs
update planet_osm_roads set category='discard'
  where tags->'usage' in ('industrial', 'military', 'disused')
  and category is null;

update planet_osm_roads set category='discard'
  where railway in ('abandoned', 'disused', 'preserved')
  and category is null;

update planet_osm_roads set category='construction'
  where railway = 'construction'
  and category is null;
