Spatial SQL: Code Reference

10.6 Finding the most isolated feature

10.23

alter table
    nyc_building_footprints
add
    column h3 text

10.24

update
    nyc_building_footprints
set
    h3 = h3_lat_lng_to_cell(
        st_centroid(
            st_transform(geom, 4326)
        ), 10)

10.25

select
    bin,
    st_transform(geom, 4326) as geom
from
    nyc_building_footprints b
where

    -- Returns all the H3 cells that meet the condition in the subquery
    h3 in (
        select
            h3
        from
            nyc_building_footprints

        -- First we group the H3 cells to use the aggregate function
        group by
            h3

        -- This finds all the H3 cells that have an aggregate
        -- count greater than 1
        having
            count(*) = 1
    )

10.26

select
    bin,
    closest.distance,
    st_transform(geom, 4326) as geom
from
    nyc_building_footprints b
    cross join lateral (
        select

            -- Finding the distance to the nearest building in meters
            st_distance(
                st_transform(geom, 3857),
                st_transform(b.geom, 3857)
            ) as distance
        from
            nyc_building_footprints

        -- This removes the ID of the building we want to analyze    
        where
            bin != b.bin
        order by
            geom <-> b.geom
        limit
            1
    ) closest
where
    h3 in (
        select
            h3
        from
            nyc_building_footprints
        group by
            h3
        having
            count(*) = 1
    )
order by
    closest.distance desc

10.27

select
    *
from
    nyc_building_footprints
where
    bin = '2127308'