#download osm2po (check version)
wget  http://osm2po.de/releases/osm2po-5.1.35.zip

#unzip
mkdir osm2po
unzip osm2po-5.1.35.zip -d  ./osm2po
rm osm2po-5.1.35.zip

# only convert ways with one of these flags

#.default.wtr.finalMask = car
.default.wtr.finalMask = car|bike
#.default.wtr.finalMask = bike|foot&goodgrade (since v5.0.20) 

# osm2po needs to know the anchor-tag and some default values if not tagged.
# To avoid ambiguities configure a well chosen priority.
# Main-Tag definitions. Params 1-4:
# 1) priority
# 2) clazz identifier (1-127)
# 3) default speed in kmh
# 4) allowed transportation type (optional)

.default.wtr.tag.highway.motorway =       1,  11, 100, car
.default.wtr.tag.highway.motorway_link =  1,  12, 30,  car
.default.wtr.tag.highway.trunk =          1,  13, 90,  car
.default.wtr.tag.highway.trunk_link =     1,  14, 30,  car
.default.wtr.tag.highway.primary =        1,  15, 70,  car
.default.wtr.tag.highway.primary_link =   1,  16, 30,  car
.default.wtr.tag.highway.secondary =      1,  21, 60,  car
.default.wtr.tag.highway.secondary_link = 1,  22, 30,  car
.default.wtr.tag.highway.tertiary =       1,  31, 40,  car|bike
.default.wtr.tag.highway.tertiary =       1,  33, 40,  car|bike
.default.wtr.tag.highway.tertiary_link =  1,  32, 20,  car|bike
.default.wtr.tag.highway.residential =    1,  41, 40,  car|bike
.default.wtr.tag.highway.road =           1,  42, 50,  car|bike
.default.wtr.tag.highway.unclassified =   1,  43, 50,  car|bike
.default.wtr.tag.highway.service =        1,  51, 5,   car|bike
.default.wtr.tag.highway.living_street =  1,  63, 7,   car|bike|foot   
.default.wtr.tag.highway.pedestrian =     1,  62, 5,   bike|foot
.default.wtr.tag.highway.track =          1,  71, 10,  bike|foot
.default.wtr.tag.highway.path =           1,  72, 10,  bike|foot
.default.wtr.tag.highway.cycleway =       1,  81, 15,  bike
#.default.wtr.tag.highway.footway =        2,  91, 5,   foot
#.default.wtr.tag.highway.steps =          2,  92, 5,   foot
.default.wtr.tag.route.ferry =            2,   1, 10,  ferry
#.default.wtr.tag.route.shuttle_train =    2,   2, 50,  rail|car
#.default.wtr.tag.railway.rail =           3,   3, 50,  rail

# Other tags may also overwrite the default transportation type definition.
# Tags without explicit values like wtr.deny.motorcar act like
# an else-part and will be used if no other tag=value matches.

.default.wtr.allow.motor[car|_vehicle].[yes|destination] = car
.default.wtr.allow.[bicycle|cycleway] = bike
.default.wtr.allow.[footway|sidewalk] = foot

#.default.wtr.deny.tracktype.grade[4|5] = car|bike
#.default.wtr.deny.access.no = car|bike|foot|rail|ferry
#.default.wtr.deny.vehicle.no = car|bike
#.default.wtr.deny.motor[_vehicle|car] = car
#.default.wtr.deny.bicycle.no = bike
#.default.wtr.deny.foot.no = foot
#.default.wtr.deny.footway.none = foot

cd osm2po
java -Xmx1g -jar osm2po-core-5.1.35-signed.jar prefix=sweden tileSize=x http://download.geofabrik.de/europe/sweden-latest.osm.pbf postp.0.class=de.cm.osm2po.plugins.postp.PgRoutingWriter cmd=c

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

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

CREATE TABLE public.osm_roadnames (id serial primary key, name_0 text, name_1 text, name_2 text, name text, km double precision);
SELECT AddGeometryColumn('public','osm_roadnames','geom',4326,'MULTILINESTRING',2);

insert into public.osm_roadnames (name_0, name_1, name_2, name,km,geom)
SELECT    name_0, name_1, name_2, osm_name, sum(km), ST_Multi(st_union(geom_way))
 FROM sweden_2po_4pgr 
    JOIN gadm.gadm ON gadm.objectid = sweden_2po_4pgr.gadm_link
 where osm_name is not null
  group by 1,2;

CREATE INDEX osm_roadnames_gix ON public.osm_roadnames USING GIST (geom);
vacuum analyze public.osm_roadnames; 

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 )