First pass at a simple point clustering algo for plpgsql

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.

72% match

Straight SQL Kmean

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.

Gist 6114786

This example implements a private torque-style time-series visualization for geospatial data using PostgreSQL and PostGIS. It defines custom SQL functions that aggregate point features into web-Mercator grid cells, grouping counts by time steps and returning arrays of values and dates for rendering. The code uses `ST_SnapToGrid`, `CDB_XYZ_Resolution`, and `CDB_XYZ_Extent` to tile data at a given zoom level, with two variants: a standard response for instantaneous counts and a cumulative response that includes expiration dates for each cell. A metadata function returns the spatial bounds and time range from the source table, supporting both timestamped and numeric columns.

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.

CartoDB Point Clustering

This example demonstrates point clustering on a map using CartoDB, showing how thousands of tornado records can be aggregated into hierarchical groups of different sizes. The visualization uses an SQL query with ST_SnapToGrid to cluster points at multiple scales, dynamically adjusting the cluster radius based on zoom level and a slider control. CartoCSS styles differentiate the cluster sizes, and clicking a point reveals the individual records it contains. The map is embedded with Google Maps and uses CartoDB.js, with an interactive side panel for cluster-size adjustments.

CartoDB Point Clustering

This example demonstrates point clustering on a map using CartoDB.js, Google Maps, and a custom SQL template. It visualizes tornado data, grouping nearby points into hexagonal clusters of varying sizes based on a user-adjustable "cluster size" slider. The clustering algorithm runs through a SQL query that creates three levels of hexagon grids (large, medium, small) to aggregate points, with each cluster's size and opacity reflecting the number of points it contains. Clicking a cluster reveals details about the original points it represents. The side panel includes a range input to dynamically adjust the cluster radius, which updates the visualization via CartoDB's SQL and CartoCSS templating.

cdb_latlng

This example defines a set of PostgreSQL functions for converting latitude and longitude values into spatial geometries. It shows how to construct valid 4326-point geometries from numeric, floating-point, or text inputs, including support for comma-style decimal separators. The code uses PostGIS functions such as `ST_SetSRID` and `ST_MakePoint` to build the geometry, along with custom helpers like `CDB_DecimalDegreeLiteral` for parsing text coordinates. It handles both individual lat/lng arguments and a single space-separated coordinate string, making it a flexible utility for geospatial data preprocessing in SQL.

66% match

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.