Spatial SQL: Code Reference

13.3 Building an origin-destination matrix

13.20

osmfilter mnt/mydata/planet_-74.459,40.488_-73.385,41.055.osm \
--keep="highway= route= cycleway= bicycle= segregated=" \
-o=mnt/mydata/bike_ways.osm

13.21

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

13.22

ogr2ogr \
-f PostgreSQL PG:"host=localhost user=docker password=docker dbname=bike port=25432" \
nyc_pharmacies.geojson \
-nln nyc_pharmacies -lco GEOMETRY_NAME=geom 

ogr2ogr \
-f PostgreSQL PG:"host=localhost user=docker password=docker dbname=bike port=25432" \
Building_Footprints.geojson \
-nln nyc_building_footprints -lco GEOMETRY_NAME=geom

13.23

alter table
    configuration
add
    column penalty float;

update
    configuration
set
    penalty = 1.0;

13.24

update
    configuration
set
    penalty = 10.0
where
    tag_value in ('steps', 'footway', 'pedestrian');

update
    configuration
set
    penalty = 0.3
where
    tag_key in ('cycleway');

update
    configuration
set
    penalty = 0.3
where
    tag_value in ('cycleway', 'bicycle');

update
    configuration
set
    penalty = 0.7
where
    tag_value in ('tertiary', 'residential');

update
    configuration
set
    penalty = 1
where
    tag_value in ('secondary');

update
    configuration
set
    penalty = 1.2
where
    tag_value in ('primary', 'primary_link');

update
    configuration
set
    penalty = 2
where
    tag_value in (
        'trunk',
        'trunk_link',
        'motorway',
        'motorway_junction',
        'motorway_link'
    );

13.26

with pharm as (
    select
        name,
        st_centroid(geom) as geom,
        id
    from
        nyc_pharmacies
    where
        st_dwithin(
            st_centroid(geom) :: geography,
            st_setsrid(st_makepoint(-73.9700743, 40.6738928), 4326) :: geography,
            3000
        )
    order by
        random()
    limit
        3
), bldgs as (
    select
        st_centroid(geom) as bldg_geom,
        bin
    from
        nyc_building_footprints
    where
        st_dwithin(
            st_centroid(geom) :: geography,
            st_setsrid(st_makepoint(-73.9700743, 40.6738928), 4326) :: geography,
            3000
        )
    order by
        random()
    limit
        10
), 

c as (

    -- First we select all the columns from the pharm, bldgs, and wid CTEs and subqueries
    select
        pharm.*,
        bldgs.*,
        wid.*
    from

        -- We perform a cross join to find all possible matches between
        -- the 5 pharmacies and the 20 buildings
        pharm,
        bldgs
        cross join lateral (

            -- For each row find the start and end way IDs
            with start as (
                select
                    source
                from
                    ways
                order by
                    the_geom <-> pharm.geom
                limit
                    1
            ), destination as (
                select
                    source
                from
                    ways
                order by
                    ways.the_geom <-> st_centroid(bldgs.bldg_geom)
                limit
                    1
            )
            select
                start.source as start_id,
                destination.source as end_id
            from
                start,
                destination
        ) wid
)
select
    -- For each combination we get the sum of the cost in distance, seconds, and route length
    -- and we repeat this for every row, or possible combinatino
    sum(di.cost) as cost,
    sum(length) as length,
    sum(pt.cost_s) as seconds,
    st_union(st_transform(the_geom, 4326)) 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 configuration using (tag_id)',
        array(
            select
                distinct start_id
            from
                c
        ),
        array(
            select
                distinct end_id
            from
                c
        ),
        true
    ) as di
    join ways as pt on di.edge = pt.gid
group by
    start_vid,
    end_vid