create table nyc_2021_census_block_groups_morans_i as
select
*
from
nys_2021_census_block_groups
where
left(geoid, 5) in ('36061', '36005', '36047', '36081', '36085')
and population > 0
with a as (
select
*
from
nyc_2021_census_block_groups
where
geoid = '360470201002'
)
select
bgs.geoid,
-- Finds the number of points of the
-- portion of the border that intersects
st_npoints(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
)
) as intersected_points,
-- Finds the length of the
-- portion of the border that intersects
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
) as length,
geom
from
nyc_2021_census_block_groups bgs
where
st_intersects(
(
select
geom
from
a
),
bgs.geom
)
and bgs.geoid != (
select
geoid
from
a
)
with a as (
select
*
from
nyc_2021_census_block_groups
where
geoid = '360470201002'
)
select
bgs.*,
st_npoints(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
)
) as intersected_points,
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
) as length,
geom
from
nyc_2021_census_block_groups bgs
where
-- Only select polygons that have a border overlap
-- of 2 points or more
st_npoints(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
)
) > 1
and bgs.geoid != (
select
geoid
from
a
)
with a as (
select
*
from
nyc_2021_census_block_groups
where
geoid = '360470201002'
)
select
bgs.*,
st_npoints(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
)
) as intersected_points,
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
) as length,
geom
from
nyc_2021_census_block_groups bgs
where
-- Only select polygons that have a border overlap
-- of 100 meters or more
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
) > 100
and bgs.geoid != (
select
geoid
from
a
)
with a as (
select
*
from
nyc_2021_census_block_groups
where
geoid = '360470201002'
)
select
bgs.geoid,
geom,
st_npoints(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
)
) as intersected_points,
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
) as length,
(
-- Finding the length of the shared border
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
-- Dividing that by the perimiter of the source polygon
) / st_perimeter(
(
select
geom :: geography
from
a
)
)
) as percent_of_source
from
nyc_2021_census_block_groups bgs
where
-- Finding touching polygons that share more than 25% of
-- the source polygon's border
(
st_length(
st_intersection(
bgs.geom,
(
select
geom
from
a
)
) :: geography
) / st_perimeter(
(
select
geom :: geography
from
a
)
)
) >.25
and bgs.geoid != (
select
geoid
from
a
)