process.sql

ALTER TABLE ways RENAME COLUMN gid TO id,
ALTER TABLE ways ADD COLUMN country text;
ALTER TABLE ways RENAME COLUMN maxspeed_forward TO maxspeed;
ALTER TABLE ways DROP COLUMN maxspeed_backward;
ALTER TABLE ways DROP COLUMN cost_s;
ALTER TABLE ways DROP COLUMN reverse_cost_s;
ALTER TABLE ways DROP COLUMN one_way;
ALTER TABLE ways DROP COLUMN oneway;
ALTER TABLE ways DROP COLUMN priority;
ALTER TABLE ways DROP COLUMN tag_id;
ALTER TABLE ways DROP COLUMN rule;
ALTER TABLE ways DROP COLUMN reverse_cost;

WITH ids_to_delete AS (
    SELECT osm_id
        FROM planet_osm_line
        WHERE electrified='no'
            OR railway<>'rail'
            OR usage in ('industrial', 'military', 'test', 'tourism')
)
DELETE FROM ways
    WHERE id IN (SELECT * FROM ids_to_delete);

-- removes isolated edges
WITH ids_to_delete AS (
    SELECT ways.id FROM ways, ways_vertices_pgr as a, ways_vertices_pgr as b
        WHERE a.cnt = 1 AND b.cnt = 1 AND ways.source = a.id AND ways.target = b.id
)
DELETE FROM ways
    WHERE id IN (SELECT * FROM ids_to_delete);

-- remove isolated chunks of edge

WITH components AS (
    SELECT * FROM pgr_connectedComponents('select * from ways')
),
main_component AS (
    SELECT count(*), component
        FROM components
        GROUP BY component
        ORDER BY count DESC
        LIMIT 1
),
edges_to_delete AS (
    SELECT id
        FROM ways, components, main_component
        WHERE ways.source = components.node
            AND components.component <> main_component.component
)
DELETE FROM ways
    WHERE id IN (SELECT * from edges_to_delete);


-- shp2pgsql -D -I -d -s 4326 ne_10m_admin_0_countries.shp | psql furlong
-- attention, long. Environ 1h
update ways set country=iso_a2 from ne_10m_admin_0_countries where st_within(ways.the_geom, ne_10m_admin_0_countries.geom) and iso_a2 <> '-99';

UPDATE ways
    SET maxspeed=planet_osm_line.maxspeed::int
    FROM planet_osm_line
    WHERE ways.id = planet_osm_line.osm_id
        AND planet_osm_line.maxspeed ~ '^[0-9]+$';

UPDATE ways
    SET  maxspeed=160
    FROM planet_osm_line
    WHERE ways.id = planet_osm_line.osm_id
        AND usage='main'
        AND ways.maxspeed IS NULL;

UPDATE ways
    SET maxspeed=80
    FROM planet_osm_line
    WHERE ways.id = planet_osm_line.osm_id
        AND usage='branch'
        AND ways.maxspeed IS NULL;

UPDATE ways
    SET maxspeed=30
    FROM planet_osm_line
    WHERE ways.id = planet_osm_line.osm_id
        AND service IS NOT NULL
        AND ways.maxspeed is NULL;

UPDATE ways
    SET maxspeed=30
    WHERE ways.maxspeed is NULL;

UPDATE ways
    SET cost = 3.6 * length_m / LEAST(maxspeed, 200);

SELECT * INTO nodes
    FROM (
        SELECT source as id, st_point(x1, y1)::geography as geog FROM ways
        UNION (
            SELECT target as id, st_point(x2, y2)::geography as geog from ways
        )
    ) as united
    GROUP BY id, geog;