Spatial SQL: Code Reference

8.4 Distance Relationship Functions

8.20

select
    name,
    geom
from
    nyc_building_footprints
where
    st_intersects(
        geom,
        (
            select
                st_buffer(
                    buildpoint(-74.002222, 40.733889, 4326) :: geography,
                    200
                ) :: geometry
        )
    )

8.21

select
    name,
    geom
from
    nyc_building_footprints
where
    st_intersects(
        geom,
        (
            select
                st_buffer(
                    buildpoint(-74.002222, 40.733889, 4326) :: geography,
                    10000
                ) :: geometry
        )
    )

8.22

select
    name,
    geom
from
    nyc_building_footprints
where
    st_dwithin(
        st_transform(geom, 3857),
        buildpoint(-74.002222, 40.733889, 3857),
        200
    )

8.23

select
    name,
    geom
from
    nyc_building_footprints
where
    st_dwithin(
        st_transform(geom, 3857),
        st_transform(
            st_setsrid(st_makepoint(-74.002222, 40.733889), 4326),
            3857
        ),
        200
    )

8.24

alter table
    nyc_building_footprints
add
    column geom_3857 geometry;

update
    nyc_building_footprints
set
    geom_3857 = st_transform(geom, 3857);

8.25

select
    name,
    geom
from
    nyc_building_footprints
where
    st_dwithin(
        geom_3857,
        st_transform(
            st_setsrid(st_makepoint(-74.002222, 40.733889), 4326),
            3857
        ),
        200
    )

8.26

with a as (
    select
        st_transform(
            st_setsrid(st_makepoint(-74.002222, 40.733889), 4326),
            3857
        ) as geo
)
select
    name,
    geom
from
    nyc_building_footprints,
    a
where
    st_dwithin(geom_3857, a.geo, 200)

8.27

create index geom_3857_idx on 
nyc_building_footprints using gist(geom_3857)

8.28

with a as (
    select
        st_transform(
            st_setsrid(st_makepoint(-74.002222, 40.733889), 4326),
            3857
        ) as geo
)
select
    name,
    geom
from
    nyc_building_footprints,
    a
where
    st_dwithin(
        geom_3857,
        (
            select
                geo
            from
                a
        ),
        10000
    )