Spatial SQL: Code Reference

13.5 Creating travel time polygons or isochones

13.41

with start as (
    select
        source
    from
        ways
        join car_config using (tag_id)
    where
        car_config.penalty != -1
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('POINT (-73.987374 40.722349)'),
            4326
        )
    limit
        1
), b as (
    select
        *
    from
        pgr_drivingdistance(
            'select 
            gid as id, 
            source, 
            target,
            cost_s * 2.5 as cost, 
            reverse_cost_s * 2.5 as reverse_cost 
            from ways 
            join car_config 
            using (tag_id) 
            where tag_id in (110, 100, 124, 115, 112, 125, 109, 101, 
            103, 102, 106, 107, 108, 104, 105)',
            (
                select
                    source
                from
                    start
            ),
            600
        )
)

-- Union the geometries into a single geometry
select
	st_union(ways.the_geom)
from
    ways
where
	gid in (select edge from b)

13.42

with start as (
    select
        source
    from
        ways
        join car_config using (tag_id)
    where
        car_config.penalty != -1
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('POINT (-73.987374 40.722349)'),
            4326
        )
    limit
        1
), b as (
    select
        *
    from
        pgr_drivingdistance(
            'select 
            gid as id, 
            source, 
            target, 
            cost_s * 2.5 as cost, 
            reverse_cost_s * 2.5 as reverse_cost 
            from ways 
            join car_config 
            using (tag_id) 
            where tag_id in (110, 100, 124, 115, 112, 125, 
            109, 101, 103, 102, 106, 107, 108, 104, 105)',
            (
                select
                    source
                from
                    start
            ),
            600
        )
)
select
    -- Creates a concave hull around the entire routes geometry
    st_concavehull(
        st_transform(
            st_union(the_geom), 4326),
        0.1) as geom
from
    ways
where
    gid in (
        select
            distinct edge
        from
            b
    )

13.43

with start as (
    select
        source
    from
        ways
        join car_config using (tag_id)
    where
        car_config.penalty != -1
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('POINT (-73.987374 40.722349)'),
            4326
        )
    limit
        1
), b as (
    select
        *
    from
        pgr_drivingdistance(
            'select gid as id, 
            source, 
            target, 
            cost_s * 2.5 as cost, 
            reverse_cost_s * 2.5 as reverse_cost 
            from ways 
            join car_config 
            using (tag_id) 
            where tag_id in (110, 100, 124, 115, 112, 
            125, 109, 101, 103, 102, 106, 107, 108, 104, 105)',
            (
                select
                    source
                from
                    start
            ),
            600
        )
)
select

    -- Turn it all into a polygon
    st_makepolygon(

        -- Find the exterior ring which will eliminate islands
        st_exteriorring(

            -- Create a 20 meter buffer
            st_buffer(
                st_transform(
                    st_union(the_geom), 4326) :: geography,
                20
            ) :: geometry
        )
    ) as geom
from
    ways
where
    gid in (
        select
            distinct edge
        from
            b
    )

13.44

with start as (
    select
        source
    from
        ways
        join configuration using (tag_id)
    where
        configuration.penalty <= 1
    order by
        the_geom <-> st_setsrid(
            st_geomfromtext('POINT (-73.987374 40.722349)'),
            4326
        )
    limit
        1
), b as (
    select
        *
    from
        pgr_drivingdistance(
            'select 
            gid as id, 
            source, 
            target, 
            cost_s * 2.5 as cost, 
            reverse_cost_s * 2.5 as reverse_cost 
            from ways 
            join configuration 
            using (tag_id) 
            where penalty <= 1',
            (
                select
                    source
                from
                    start
            ),
            600
        )
)
select
    st_makepolygon(
        st_exteriorring(
            st_buffer(
                st_transform(st_union(the_geom), 4326) :: geography,
                20
            ) :: geometry
        )
    ) as geom
from
    ways
where
    gid in (
        select
            distinct edge
        from
            b
    )