create
or replace view nyc_311_dbscan_noise as
-- Create the clustering functon using the Window funciton syntax
select
id,
ST_ClusterDBSCAN(st_transform(geom, 3857), 30, 30) over () AS cid,
geom
from
nyc_311
where
-- Find just the complaints with "noise" in the description
-- and that are in zip code 10009
st_intersects(
geom,
(
select
st_transform(geom, 4326)
from
nyc_zips
where
zipcode = '10009'
)
)
and complaint_type ilike '%noise%'
create
or replace view nyc_311_kmeans_noise as
-- Create the clustering functon using the Window funciton syntax
select
id,
ST_ClusterKMeans(st_transform(geom, 3857), 7, 1609) over () AS cid,
geom
from
nyc_311
where
-- Find just the complaints with "noise" in the description
-- and that are in zip code 10009
st_intersects(
geom,
(
select
st_transform(geom, 4326)
from
nyc_zips
where
zipcode = '10009'
)
)
and complaint_type ilike '%noise%'
create table nyc_311_clusterwithin_noise as
-- Find the 311 calls in the 10009 zip code
with a as (
select
geom
from
nyc_311
where
st_intersects(
geom,
(
select
st_transform(geom, 4326)
from
nyc_zips
where
zipcode = '10009'
)
)
and complaint_type ilike '%noise%'
),
-- In CTE "b", we have to unnest the results since it returns
-- geometries in an array
b as (
select
st_transform(
unnest(ST_ClusterWithin(st_transform(geom, 3857), 25)),
4326
) AS geom
from
a
)
-- row_number() over() creates an id for each row starting at 1
select
row_number() over() as id,
geom
from
b