Skip to content

Instantly share code, notes, and snippets.

@smathermather

smathermather/dumb_cluster1.md Secret

Created Feb 23, 2019
Embed
What would you like to do?
DROP TABLE IF EXISTS cluster;

CREATE TABLE cluster AS 

WITH 
	buffer AS (
		SELECT ST_Buffer(geom, 750) AS geom FROM vibhav
	),
	unionn AS (
		SELECT ST_Union(geom) AS geom FROM buffer
	),
	dump AS (
		SELECT (ST_Dump(geom)).geom FROM unionn
	),
	hull AS (
		SELECT ST_ConcaveHull(geom, 1) AS geom FROM dump
	),
	unionagain AS (
		SELECT ST_Union(geom) AS geom FROM hull
	),
	dumpagain AS (
		SELECT (ST_Dump(geom)).geom FROM unionagain
	),
	rownum AS (
		SELECT row_number() over() AS clusterid, geom FROM dumpagain
	),
	leftjoin AS (
		SELECT v.gid, v.geom, user_name, name, address, longitude, latitude, clusterid
			FROM vibhav v LEFT JOIN rownum rn ON ST_Intersects(v.geom, rn.geom)
	)

	SELECT * FROM leftjoin;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.