Spatial SQL: Code Reference

9.6 Tapered buffers

9.65

-- Select all bike routes on Hudson Street
with lines as (
    select
        1 as id,
        st_linemerge(st_union(geom)) as geom
    from
        nyc_bike_routes
    where
        street = 'HUDSON ST'
)
select
    *
from
    lines

9.66

with lines as (
    select
        1 as id,
        ST_LineMerge(st_union(geom)) as geom
    from
        nyc_bike_routes
    where
        street = 'HUDSON ST'
),

-- Dump all of the points and find the length of the geometry
first as (
    select
        id,
        st_dumppoints(geom) as dump,
        st_length(geom) as len,
        geom
    from
        lines
)
select
    *
from
    first

9.67

with lines as (
    select
        1 as id,
        ST_LineMerge(st_union(geom)) as geom
    from
        nyc_bike_routes
    where
        street = 'HUDSON ST'
),
first as (
    select
        id,
        st_dumppoints(geom) as dump,
        st_length(geom) as len,
        geom
    from
        lines
),

-- For each path, select the id, path, and a buffer
-- around the path point. Using ST_LineLocatePoint
-- we use the line geometry and the point to find 
-- the distance along the line, then make it smaller
-- using the log of the length 
second as (
    select
        id,
        (dump).path [1],
        st_buffer(
            (dump).geom,
            ST_LineLocatePoint(geom, (dump).geom) * log(len)
        ) as geom
    from
        first
)
select
    *
from
    second

9.68

with lines as (
    select
        1 as id,
        st_linemerge(st_union(geom)) as geom
    from
        nyc_bike_routes
    where
        street = 'HUDSON ST'
),
first as (
    select
        id,
        st_dumppoints(geom) as dump,
        st_length(geom) as len,
        geom
    from
        lines
),
second as (
    select
        id,
        (dump).path [1],
        st_buffer(
            (dump).geom,
            ST_LineLocatePoint(geom, (dump).geom) * len / 10
        ) as geom
    from
        first
),

-- Create a convex hull around the buffers by union-ing
-- all the buffers together. These are ordered using the 
-- LEAD window function and partition
third as (
    select
        id,
        st_convexhull(
            st_union(
                geom,
                lead(geom) over(
                    partition by id
                    order by
                        id,
                        path
                )
            )
        ) as geom
    from
        second
)
select
    id,
    st_union(geom) as geom
from
    third
group by
    id

9.69

with lines as (
    select
        1 as id,
        st_linemerge(st_union(geom)) as geom
    from
        nyc_bike_routes
    where
        street = 'HUDSON ST'
),
first as (
    select
        id,
        st_dumppoints(geom) as dump,
        st_length(geom) as len,
        geom
    from
        lines
),
second as (
    select
        id,
        (dump).path [1],
        st_transform(
            st_buffer(st_transform((dump).geom, 3857), random() * 100),
            4326
        ) as geom
    from
        first
),
third as (
    select
        id,
        st_convexhull(
            st_union(
                geom,
                lead(geom) over(
                    partition by id
                    order by
                        id,
                        path
                )
            )
        ) as geom
    from
        second
)
select
    id,
    st_union(geom) as geom
from
    third
group by
    id

9.70

select
    st_symdifference(
        (
            select
                geom
            from
                nyc_neighborhoods
            where
                neighborhood = 'NoHo'
        ),
        (
            select
                st_transform(geom, 4326)
            from
                nyc_zips
            where
                zipcode = '10012'
        )
    )