-- RENAME SPEEDLIMIT
ALTER TABLE network RENAME maxspeed TO speedlimit;

-- SEE DISTINCT VALUES OF SPEEDLIMIT
--SELECT DISTINCT speedlimit from network;

-- UPDATE SPEEDLIMIT
UPDATE network SET speedlimit = 50 WHERE speedlimit LIKE '50%';
UPDATE network SET speedlimit = osm_set_speedlimit(highway) WHERE speedlimit IS NULL;
ALTER TABLE network ALTER COLUMN speedlimit TYPE smallint USING speedlimit::smallint;

-- CALCULATE THE TIME IN MINUTES IT TAKES TO CROSS THE SEGMENT
ALTER TABLE network ADD COLUMN time_min double precision;
UPDATE network SET time_min =  (ST_Length(geom::geography) / (speedlimit * 1000 / 60)) where speedlimit <> 0 AND speedlimit IS NOT NULL;
UPDATE network SET time_min = 99999 where time_min IS NULL;

-- UPDATE ONEWAY FIELD
--SELECT oneway, COUNT(oneway) FROM network GROUP BY oneway;
UPDATE network SET oneway = 'y' where oneway='yes';
UPDATE network SET oneway = 'y' where oneway='-1';
UPDATE network SET oneway = 'n' where oneway='no';
UPDATE network SET oneway = 'y' where highway in ('motorway', 'motorway_link', 'trunk', 'trunk_link') and oneway is null;
UPDATE network SET oneway = 'n' where oneway IS NULL;
UPDATE network SET oneway = 'y' where junction = 'roundabout';
ALTER TABLE network ALTER COLUMN oneway TYPE varchar(1) USING oneway::varchar(1);

-- UPDATE MAXHEIGHT DATA
UPDATE network SET maxheight = 2.8 WHERE maxheight = '2.8m';
UPDATE network SET maxheight = NULL WHERE maxheight in ('??', 'FIXME', 'fixme');
ALTER TABLE network ALTER COLUMN maxheight TYPE FLOAT USING maxheight::double precision;

-- UPDATE LANES FIELD
UPDATE network SET lanes = 1 WHERE lanes IS NULL;
ALTER TABLE network ALTER COLUMN lanes TYPE smallint USING lanes::smallint;

-- UPDATE BRIDGE FIELD
UPDATE network SET bridge = 'No' WHERE bridge IS NULL;
ALTER TABLE network ALTER COLUMN bridge TYPE varchar(10) USING bridge::varchar(10);

-- UPDATE HORSE FIELD
UPDATE network SET horse = 'yes' WHERE horse IS NULL;
ALTER TABLE network ALTER COLUMN horse TYPE varchar(10) USING horse::varchar(10);


-- CHANGE COLUMN level FROM VARCHAR TO SMALLINT
ALTER TABLE network ALTER COLUMN level TYPE smallint USING lanes::smallint;

-- CREATE NEW ID
ALTER TABLE network RENAME COLUMN id TO osmid;
ALTER TABLE network ADD COLUMN id serial NOT NULL;

-- Reverse cost
ALTER TABLE network ADD COLUMN reverse_time_min double precision;
UPDATE network SET reverse_time_min = time_min where oneway='n';
UPDATE network SET reverse_time_min = 99999 where oneway='y';

-- 
