#be postgres
sudo su postgres
#run psql
psql globalmap
create extension postgis; 
\i ./sweden/sweden_2po_4pgr.sql;
\q
exit

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 index for the id column and for the geom_way column
CREATE INDEX idx_sweden_2po_4pgr_id ON public.sweden_2po_4pgr(id);
CREATE INDEX sweden_2po_4pgr_gix ON public.sweden_2po_4pgr USING GIST (geom_way);




vacuum analyze  public.sweden_2po_4pgr;





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);

alter table public.sweden_2po_4pgr add column gadm_link integer;

UPDATE public.sweden_2po_4pgr
SET gadm_link=gadm.objectid
from gadm.gadm
WHERE gadm.iso= 'SWE'  and ST_Intersects(sweden_2po_4pgr.geom_way, gadm.the_geom); 

select id 
from  public.sweden_osm2po_nodes 
order by  ST_Distance_Spheroid(ST_Transform(geom,4326), ST_GeomFromText('POINT(59.1214893 18.1033966)',4326), 'SPHEROID["WGS84",6378137,298.25728]' ) limit 1;

--Create a dataset with nodes
insert into distance_nodes (name,node,edge,cost,geom) (
 SELECT '1h_walk_vh',id1 AS node, id2 AS edge, cost, geom
  FROM pgr_drivingdistance(
    'SELECT id, source, target, walk_cost as cost FROM public.sweden_2po_4pgr',
    732011, 1, false, false --from VH an one hour away
  ) as di
  JOIN  sweden_osm2po_nodes pt
  ON di.id1 = pt.id
  );


--Create a isocrone polygon
INSERT INTO distance_polygons (name,max_cost, geom) (
SELECT 'Ola',0.25, ST_ConvexHull(ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),4326)) as geom
from
(
SELECT ST_MakeLine(points ORDER BY id) AS openline FROM 
(   
SELECT ST_MakePoint(x, y) AS points, row_number() over() AS id from
pgr_alphashape($$SELECT node as id, ST_X(geom) AS x, ST_Y(geom) AS y 
FROM public.distance_nodes WHERE cost < 0.25 and name ='test'$$)
) as a

) as foo )