Versions Compared

Version Old Version 4 New Version 5
Changes made by

Sven-Ola Svensson

Sven-Ola Svensson

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
titleosm2po tables
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);

Find a start and end node for making examples of routing and distances

Longitude (west) and Latitude (north) , place

...

Code Block
languagesql
titlefind node
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;

Example how to create isocrone polygons based on traveltime

Code Block
languagesql
titleIsocrones
--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 )