8.6 Overlay Functions

8.36

select
    st_difference(a.geom, st_transform(b.geom, 4326)) as geom
from
    nyc_neighborhoods a,
    nyc_zips b
where
    b.zipcode = '10014'
    and a.neighborhood = 'West Village'

8.37

select
    st_intersection(a.geom, st_transform(b.geom, 4326)) as geom
from
    nyc_neighborhoods a,
    nyc_zips b
where
    b.zipcode = '10003'
    and a.neighborhood = 'Gramercy'

8.38

select
    st_intersection(a.geom, st_transform(b.geom, 4326)) as geom
from
    nyc_neighborhoods a,
    nyc_zips b
where
    b.zipcode = '10003'
    and a.neighborhood = 'Gramercy'
union
select
    geom
from
    nyc_neighborhoods
where
    neighborhood = 'Gramercy'
union
select
    st_transform(geom, 4326)
from
    nyc_zips
where
    zipcode = '10003'

8.39

-- Query all the street segments between W 39 ST and BANJ ST
with a as (
    select
        st_union(geom) as geom
    from
        nyc_bike_routes
    where
        fromstreet IN ('W HOUSTON ST', 'BANK ST')
        or tostreet IN ('BANK ST', 'W 39 ST', 'W HOUSTON ST')
)
select
    st_split(
        -- Select the geometgry for the West Village
        (
            select
                st_transform(geom, 4326)
            from
                nyc_neighborhoods
            where
                neighborhood = 'West Village'
        ),
        
        -- Split it with our geometry in our CTE above
        (
            select
                geom
            from
                a
        )
    )

8.40

select
    st_subdivide(st_transform(geom, 4326), 50) as geom
from
    nyc_neighborhoods
where
    neighborhood = 'West Village'

8.41

select
    st_union(geom) as geom
from
    nyc_neighborhoods
where
    st_intersects(
        geom,
        (
            select
                st_transform(geom, 4326)
            from
                nyc_zips
            where
                zipcode = '10009'
        )
    )