create extension pgrouting;

alter table public.sweden_2po_4pgr add column bike_cost double precision; 
alter table public.sweden_2po_4pgr add column walk_cost double precision; 
update public.sweden_2po_4pgr set 
bike_cost = CASE WHEN clazz IN (11,12,13,14)  THEN 100000 when kmh < 17 then km/kmh ELSE (km/17)::double precision END , 
walk_cost= CASE WHEN clazz IN (11,12,13,14)  THEN 100000 when kmh < 6 then km/kmh ELSE (km/6)::double precision END;

CREATE TABLE public.sweden_osm2po_nodes (id integer primary key);
SELECT AddGeometryColumn('public','sweden_osm2po_nodes','geom',4326,'point',2);

INSERT INTO public.sweden_osm2po_nodes(id, geom) (
select id, st_centroid(st_collect(pt)) as geom
from (
    (select source as id, st_startpoint(geom_way) as pt
    from public.sweden_2po_4pgr
    ) 
union
    (select target as id, st_endpoint(geom_way) as pt
    from public.sweden_2po_4pgr
    ) 
) as foo
group by id
);

CREATE INDEX idx_sweden_osm2po_nodes_id ON public.sweden_osm2po_nodes(id);
CREATE INDEX osm2po_nodes_gix ON public.sweden_osm2po_nodes USING GIST (geom);


--drop table public.distance_nodes
CREATE TABLE public.distance_nodes (id serial, "name" text, node integer, edge integer, cost double precision );
SELECT AddGeometryColumn('public','distance_nodes','geom',4326,'point',2);

-- isocrones 
CREATE TABLE distance_polygons (id serial, name text, max_cost double precision);
SELECT AddGeometryColumn('distance_polygons','geom',4326,'POLYGON',2);