8.8 Special Operators

8.46

select
    zipcode,
    st_transform(geom, 4326)
from
    nyc_zips
where
    -- Finds all of the zip codes that intersect the bounding box
    -- of the East Village
    st_transform(geom, 4326) && (
        select
            geom
        from
            nyc_2010_neighborhoods
        where
            ntaname = 'East Village'
    )
UNION

-- Query to show the East Village bounding box on the map using ST_Envelope
select
    'None' as zipcode,
    st_envelope(
        (
            select
                geom
            from
                nyc_2010_neighborhoods
            where
                ntaname = 'East Village'
        )
    )

8.47

select
    geom
from
    nyc_neighborhoods
where
    geom &< (
        select
            geom
        from
            nyc_neighborhoods
        where
            neighborhood = 'East Village'
    )

8.48

with ev as (
    select
        geom
    from
        nyc_neighborhoods
    where
        neighborhood = 'East Village'
),
ues as (
    select
        geom
    from
        nyc_neighborhoods
    where
        neighborhood = 'Upper East Side'
)
select
    ev.geom <-> ues.geom,
    st_distance(ev.geom, use.geom)
from
    ev,
    ues

8.49

with ev as (
    select
        geom :: geography
    from
        nyc_neighborhoods
    where
        neighborhood = 'East Village'
),
ues as (
    select
        geom :: geography
    from
        nyc_neighborhoods
    where
        neighborhood = 'Upper East Side'
)
select
    ev.geom <-> ues.geom as new_operator,
    st_distance(ev.geom, ues.geom)
from
    ev,
    ues