Spatial SQL: Code Reference

13.2 Create a simple route in pgRouting

13.6

osm2pgrouting \
    -f "/mnt/mydata/good_ways.osm" \
    -d routing \
    -p 25432 \
    -U docker \
    -W docker \
    --clean

13.11

-- Find the source ID closest to the starting point
with start as (
    select
        source
    from
        ways
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('point (-74.244391 40.498995)'),
            4326
        )
    limit
        1
), 

-- Find the source ID closest to the end point
destination as (
    select
        source
    from
        ways
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('point (-73.902630 40.912329)'),
            4326
        )
    limit
        1
)

-- Run our pgRouting query
select
    st_union(the_geom) as route
from
    pgr_dijkstra(
        'select gid as id, source, target, cost,
        reverse_cost, st_length(st_transform(the_geom, 3857)) 
        as cost from ways',
        (
            select
                source
            from
                start
        ),
        (
            select
                source
            from
                destination
        ),
        true
    ) as di
    join ways as pt on di.edge = pt.gid;

13.12

select
    tag_id,
    tag_key,
    tag_value
from
    configuration
order by
    tag_id;

13.13

create table car_config as
select
    *
from
    configuration

13.14

alter table
    car_config
add
    column penalty float;

13.15

update car_config set penalty=1

13.16

update
    car_config
set
    penalty = -1.0
where
    tag_value in ('steps', 'footway', 'pedestrian');

update
    car_config
set
    penalty = 5
where
    tag_value in ('unclassified');

13.17

update
    car_config
set
    penalty = 0.5
where
    tag_value in ('tertiary');

update
    car_config
set
    penalty = 0.3
where
    tag_value in (
        'primary',
        'primary_link',
        'trunk',
        'trunk_link',
        'motorway',
        'motorway_junction',
        'motorway_link',
        'secondary'
    );

13.19

with start as (
    select
        source
    from
        ways
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('point (-74.244391 40.498995)'),
            4326
        )
    limit
        1
), destination as (
    select
        source
    from
        ways
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('point (-73.902630 40.912329)'),
            4326
        )
    limit
        1
)
select
    st_union(the_geom) as route
from
    pgr_dijkstra(
        'select
            gid as id,
            source,
            target,
            cost_s * penalty as cost,
            reverse_cost_s * penalty as reverse_cost,
            st_length(st_transform(the_geom, 3857)) as length
        from
            ways
            join car_config using (tag_id)',
        (
            select
                source
            from
                start
        ),
        (
            select
                source
            from
                destination
        ),
        true
    ) as di
    join ways as pt on di.edge = pt.gid;