Skip to main content
100%

Straight SQL Kmean

✓ Published0🌍 Public
Aandrewxhill
Last edited Oct 11, 2012
Created on Oct 11, 2012

Straight SQL Kmean demonstrates k-means clustering directly in PostgreSQL, showing how spatial points group into nine clusters based on proximity. The visualization maps each input point to its nearest cluster centroid, with cluster membership determined by recursive SQL queries and PostGIS geometric functions like `ST_Distance`, `ST_Centroid`, and `ST_Collect`. It uses `CDB_RectangleGrid` to generate initial centroids and `UNNEST` to iterate through cluster assignments over 20 refinement steps. The final output renders points colored by cluster ID via CartoDB's web mercator geometry.

AI-generated description

Similar vizzes

Loading thumbnail…

kmeans function and examples

The example shows how to cluster geographic points into groups using a custom k-means algorithm implemented in PostgreSQL. It defines a PL/pgSQL function that takes an array of geometries, iteratively refines cluster centroids, and returns the final centroid locations. The code uses PostGIS functions like `ST_Accum`, `ST_Centroid`, `ST_Collect`, and `ST_Distance` to perform spatial calculations, with queries that assign each point to its nearest cluster via a lateral join and the `<->` operator. The examples also demonstrate optional preprocessing with `ST_SnapToGrid` to simplify input geometries.

Aandrewxhill
81% match
Loading thumbnail…

Better Kmeans loop for any number of clusters

A PostgreSQL recursive query iteratively refines k-means clustering on natural earth point data, partitioning features into an arbitrary number of groups. The process seeds centroids from a rectangular grid generated with CartoDB’s `CDB_RectangleGrid`, then repeatedly reassigns points to the nearest centroid and recomputes cluster centers using `ST_Centroid` and `ST_Collect`. After ten iterations, each point is labeled with its final cluster ID via a spatial join on `ST_Distance`. The output shows the clustered point assignments, not the centroid geometries.

Aandrewxhill
80% match
Loading thumbnail…

Kmeans better still, ~5 seconds for 50,000 points

This example demonstrates k-means clustering of 50,000 geographic points into up to nine clusters in about five seconds. The visualization shows the resulting cluster assignments as a new column in the source data, with each point labeled by its nearest cluster centroid. The implementation uses a recursive SQL query with PostGIS functions including `ST_SnapToGrid`, `ST_Envelope`, `CDB_RectangleGrid`, and `ST_Centroid` to generate initial centroids and iteratively refine them. It leverages spatial indexing and the `<->` operator for efficient nearest-centroid lookups, updating the `occurrence_search_coords` table directly with the computed cluster IDs.

Aandrewxhill
77% match
Loading thumbnail…

Super naive but fast kmeans

This example shows a fast, approximate k-means clustering of 500,000 geographic points, grouping them into up to 16 clusters by nearest centroid. It first reduces the data by aggregating points into a coarse grid, then uses a recursive PostGIS query with `ST_Centroid`, `ST_Collect`, and `ST_Distance` to iteratively refine cluster centers over 20 passes. The final query assigns each original point to its closest cluster using the `<->` spatial index operator, outputting a `kmean_id` for every record. The approach prioritizes speed over precision, completing in about 25 seconds.

Aandrewxhill
74% match
Loading thumbnail…

First pass at a simple point clustering algo for plpgsql

This example demonstrates a PostgreSQL/PostGIS clustering algorithm for point data, grouping nearby features into cluster IDs with sizes based on geographic distance at a given zoom level. The function `axh_cdb_row_cluster` iterates over rows, expands a search radius using `CDB_XYZ_Resolution`, and aggregates IDs via `array_agg`. It relies on `ST_Distance` with geography casts and plpgsql loops, returning a table of cluster identifiers. The code shows an iterative, resolution-adaptive approach rather than a visualization library, focusing on spatial SQL logic.

Aandrewxhill
68% match
Loading thumbnail…

KMeans clustering

This example demonstrates KMeans clustering on a small dataset of six points loaded from a CSV file. It shows how points are iteratively assigned to one of three clusters, with colored hulls drawn around each group and centroids recalculated until a local minimum cost is reached. The visualization uses D3.js v4 with a React framework, rendering SVG circles and polygon hulls with animated transitions. The code employs `d3.csv`, `d3.polygonHull`, `d3.scaleLinear`, and `d3.interval` to animate the clustering process, displaying the iteration count in the final state.

NNaveenaS12
67% match
Loading thumbnail…

K means clustering

This example shows the k-means clustering algorithm in action, animating the iterative process of grouping a small set of points from `data.csv` into three clusters. Initially, three randomly placed centroids are drawn, and each iteration assigns points to the nearest centroid using Euclidean distance, then moves the centroids to the cluster mean. The visualization uses D3 v6’s selection and `transition` methods to animate the points and centroids, with the centroid styled distinctly via a black stroke. The iteration count is displayed as text, and the animation stops after ten iterations.

Kkaviya-ds
66% match
Loading thumbnail…

kmean cartocss style

This example uses CartoCSS to style a map of points that have been classified by a k-means clustering algorithm. It assigns a distinct marker fill color to each cluster based on an `id` property, with default orange for unclassified points. The rendering approach applies marker properties like width, opacity, and line styling through the CartoCSS styling language, which is used by mapping libraries such as CartoDB. The code demonstrates how conditional rules can visually separate spatial clusters.

Aandrewxhill
65% match