pgrouting setup

pgrouting.sql

/*
highway types by count
11466531 residential 
10299767 null 

5460331 service 
1148498 footway 
1137593 track 

767727 tertiary 
670525 unclassified 
653799 secondary 
407851 primary 
339889 path 
228048 motorway_link 
205754 motorway 
130889 trunk 

98274 cycleway 
54592 secondary_link 
53967 trunk_link 
48425 primary_link 
46706 living_street 
39411 steps 
23765 tertiary_link 
21217 road 
14700 pedestrian 
14560 construction 
11716 proposed 

5400 bridleway 
2677 raceway 
2371 abandoned 
1400 corridor 

912 crossing 
449 unsurfaced 
260 platform 
218 rest_area 
135 planned 
112 escape 

90 yes 
61 disused 
53 driveway 
42 landing 
39 bus_guideway 
30 elevator 
29 bus_stop 
25 services 
24 turning_circle 
24 trail 
23 access 
23 razed 
23 turning_loop 
22 dismantled 
20 residential_link 
18 service road 
16 SER 
14 private 
11 parking_aisle 

9 no 
8 highway 
6 byway 
6 unclassified_link 
6 traffic_signals 
5 access_ramp 
5 demolished 
5 truck 
5 unknown 
4 closed 
4 crossing_ref 
3 mini_roundabout 
3 traffic_island 
3 Horse to Horse 
3 sr 
3 stairs 
3 ramp 
3 closed:cycleway 
3 escalator 
3 unm 
3 destroyed 
2 er 
2 unclassified link 
2 unclassified; tertiary 
2 stop 
2 unclassifiedser 
2 service_link 
2 piste 
2 bridge 
2 asphalt 
2 stream 
2 emergency_access_point 
2 tertiary; unclassified 
1 industrial 
1 fof 
1 footway; track; track 
1 U 
1 footpath 
1 uns 
1 foot 
1 winter_road 
1 primary;tertiary 
1 Mule Deer Lane 
1 Phyliss Lane 
1 conveyor 
1 residential; abandoned 
1 residential;footway 
1 path;service 
1 passing_place 
1 commerial 
1 runway 
1 commercial 
1 seondary 
1 parking_aile 
1 Service Lane 
1 Oak Avenue 
1 service; residential 
1 none 
1 California Avenue 
1 TD Lane 
1 ppath 
1 tertiary; unclunassified 
1 tertiary_ 
1 minor 
1 tertiaryun 
1 TR
TOTAL 33359206 (33.3M)
*/

CREATE TABLE "north-america".routing
(
    fid integer NOT NULL,
    osm_id character varying COLLATE pg_catalog."default"
    name character varying COLLATE pg_catalog."default",
    highway character varying COLLATE pg_catalog."default",
    geom geometry(1107460),
    length double precision,
    source integer,
    target integer,    
    CONSTRAINT routing_pkey PRIMARY KEY (fid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE "north-america".routing
    OWNER to postgres;

CREATE INDEX routing_geom_geom_idx
    ON "north-america".routing USING gist
    (geom)
    TABLESPACE pg_default;

ALTER TABLE "north-america".routing
    CLUSTER ON routing_geom_geom_idx;

CREATE INDEX routing_source_idx
    ON "north-america".routing USING btree
    (source)
    TABLESPACE pg_default;

CREATE INDEX routing_target_idx
    ON "north-america".routing USING btree
    (target)
    TABLESPACE pg_default; 
    
/* populate with a subset of lines 
11466531 residential 
5460331 service 
767727 tertiary 
653799 secondary 
407851 primary 
228048 motorway_link 
205754 motorway 
54592 secondary_link 
48425 primary_link 
46706 living_street 
23765 tertiary_link 
21217 road 
TOTAL 19384746 (19.3M)
*/
INSERT INTO
	"north-america".routing
SELECT
	fid, 
    name, 
    highway, 
    geom, 
    NULL source, 
    NULL target, 
    ST_Length(ST_Transform(geom,3857)) as length
FROM
	"north-america".lines
WHERE
	highway IN (
        'motorway', 'motorway_link', 
        'primary', 'primary_link',
        'secondary', 'secondary_link',
        'tertiary', 'tertiary_link',
	'residential',
        'living_street',
        'road',
        'service'
    )
;

SELECT pgr_createTopology(
    'north-america.routing',
    0.00001,
    'geom',
    'fid',
    'source',
    'target');
    
CLUSTER "north-america".routing USING routing_geom_geom_idx;

ANALYZE "north-america".routing;

/* cannot do any routing without a cost */
UPDATE "north-america".routing SET length = ST_Length(ST_Transform(geom,3857));

/* 
resulting data does not make sense
SELECT source, target, length FROM "north-america".routing where source=473242
target = 473243
length = 3915.7
But pgr_bdDijkstra costs are all zero (and the path arbitrary).
 */
SELECT seq, id1 AS node, id2 AS edge, cost
    FROM pgr_bdDijkstra(
        'SELECT fid as id, source, target, length as cost FROM "north-america".routing',
        4, 10, false, false
    );