Versions Compared

Version Old Version 7 New Version 8
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
titleCreate osm2po database
#be postgres
sudo su postgres
#run psql
psql globalmap
create extension postgis; 
\i ./sweden/sweden_2po_4pgr.sql;
\q
exit

Create tables and views

Update sweden_2po_4pgr with cost for bike and walk. Create index

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

...

Code Block
languagesql
titleGadm
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 tables 'sweden_osm2po_nodes' with all nodes, 'distance_nodes' with calculated ditances from a location and 'distance_polygons' with calculated iso-crone polygons

Code Block
languagesql
titleosm2po tables
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 a table with all road names

Code Block
languagesql
titleGadm

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

insert into public.osm_roadnames (area, name,km,geom)
SELECT    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;

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

...