Spatial SQL: Code Reference

8.5 Spatial Joins

8.29

select
    a.ogc_fid,
    a.health,
    a.spc_common,
    b.neighborhood
from
    nyc_2015_tree_census a,
    nyc_neighborhoods b
where
    st_intersects(a.geom, b.geom)
    and a.spc_common ilike '%maple%'

8.30

select
    a.ogc_fid,
    a.health,
    a.spc_common,
    b.neighborhood
from
    nyc_2015_tree_census a
    join nyc_neighborhoods b on st_intersects(a.geom, b.geom)
    and a.spc_common ilike '%maple%'

8.31

create table nyc_2010_neighborhoods_subdivide as
select
    st_subdivide(geom) as geom,
    neighborhood
from
    nyc_neighborhoods

8.32

with trees as (
        select
            ogc_fid,
            health,
            spc_common,
            geom
        from
            nyc_2015_tree_census
        where
            spc_common ilike '%maple%'
    )
select
    trees.ogc_fid,
    trees.health,
    trees.spc_common,
    b.neighborhood
from
    trees
    join nyc_neighborhoods_subdivide b on st_intersects(trees.geom, b.geom)

8.33

create index nyc_neighborhoods_subdivide_geom_idx 
n nyc_neighborhoods_subdivide using gist(geom)

8.34

cluster nyc_neighborhoods_subdivide using nyc_neighborhoods_subdivide_geom_idx;

cluster nyc_2015_tree_census using nyc_2015_tree_census_geom_geom_idx;

8.35

select
    count(a.ogc_fid) filter (
        where
            a.spc_common ilike '%maple%'
    ) :: numeric / count(a.ogc_fid) :: numeric as percent_trees,
    count(a.ogc_fid) filter (
        where
            a.spc_common ilike '%maple%'
    ) as count_maples,
    b.neighborhood
from
    nyc_2015_tree_census a
    join nyc_neighborhoods_subdivide b on st_intersects(a.geom, b.geom)
group by
    b.neighborhood