Skip to content

Instantly share code, notes, and snippets.

Last active Feb 23, 2019
What would you like to do?
-- Drop our table just in case so we can rerun
DROP TABLE IF EXISTS clusterwithin;

-- Create table
CREATE TABLE clusterwithin AS (

-- We'll use the endlessly flexible CTE "WITH" to make our query readable
-- This has the effect to creating a sequence of temporary tables
WITH unnestt AS (
-- First we cluster out points within 1000 meters
	SELECT unnest(ST_ClusterWithin(geom, 1000)) geomcollect
		FROM (SELECT * FROM vibhav_rand) vibhav
-- Then we dump out the clusters and assign them cluster ids in the process
dumpnest AS (
	SELECT row_number() over() AS clusterid, (ST_Dump(geomcollect)).geom AS geom FROM unnestt
-- It will be useful to know how many points are in each cluster later,
-- so let's calculate that now
countcluster AS (
	SELECT clusterid, count(clusterid) FROM dumpnest
		GROUP BY clusterid
-- We end with a left join to bring the count together with our clusters
SELECT dn.clusterid, count, geom FROM
	dumpnest dn LEFT JOIN countcluster cc ON dn.clusterid = cc.clusterid 


-- As it might be useful to draw an outline around each of our groups,
-- we do this with a convex hull:
DROP TABLE IF EXISTS clusterwithinn;

CREATE TABLE clusterwithinn AS (
SELECT clusterid, ST_ConvexHull(ST_Union(geom)) AS geom FROM
	clusterwithin WHERE count > 2
	GROUP BY clusterid
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment