Spatial SQL: Code Reference

8.3 Spatial relationship functions

8.12

select
    name,
    geom
from
    nyc_building_footprints
where
    st_contains(
        (
            select
                st_buffer(
                    buildpoint(-73.993584, 40.750580, 4326) :: geography,
                    200
                ) :: geometry
        ),
        geom
    )

8.13

select
    a.ogc_fid,
    a.name,
    st_centroid(st_transform(a.geom, 4326)),
    b.ogc_fid as b_id,
    st_transform(b.geom, 4326)
from
    nyc_building_footprints a
    join nyc_bike_routes b on st_crosses(a.geom, b.geom)

8.14

select
    name,
    geom
from
    nyc_building_footprints
where
    st_disjoint(
        (
            select
                st_buffer(
                    buildpoint(-74.002222, 40.733889, 4326) :: geography,
                    200
                ) :: geometry
        ),
        geom
    )
order by
    st_distance(
        (
            select
                st_buffer(
                    buildpoint(-74.002222, 40.733889, 4326) :: geography,
                    200
                ) :: geometry
        ),
        geom
    ) asc
limit
    200

8.15

select
    st_geomfromtext(
        'GEOMETRYCOLLECTION(LINESTRING(0 0, 3 3), POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)))'
    ) as geom,
    st_overlaps(
        st_geomfromtext('LINESTRING(0 0, 0 3)'),
        st_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')
    )

8.16

select
    st_geomfromtext(
        'GEOMETRYCOLLECTION(POLYGON((1 0, 1 1, 1 2, 0 2, 1 0)), POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)))'
    ) as geom,
    st_overlaps(
        st_geomfromtext('POLYGON((1 0, 1 1, 1 2, 0 2, 1 0))'),
        st_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')
    )

8.17

select
    st_geomfromtext(
        'GEOMETRYCOLLECTION(LINESTRING(1 0, 1 1, 0 1, 0 0), LINESTRING(2 1, 1 1, 0 1, 1 2))'
    ) as geom,
    st_overlaps(
        st_geomfromtext('LINESTRING(1 0, 1 1, 0 1, 0 0)'),
        st_geomfromtext('LINESTRING(2 1, 1 1, 0 1, 1 2)')
    )

8.18

select
    *
from
    nyc_zips
where
    st_touches(
        geom,
        (
            select
                geom
            from
                nyc_zips
            where
                zipcode = '10009'
        )
    )

8.19

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