Minimum spanning tree in SQL... just because

SQL for building a dynamic topo

This example builds a dynamic topographic map from traffic data by buffering and dissolving point geometries into nested contour layers. It first generates a series of buffer rings around each data point, scales the car counts across nine distance levels, and then unions those buffers into isolines representing traffic intensity. The SQL query uses PostGIS functions like `ST_Transform`, `ST_Buffer`, and `ST_Union`, while a second pass outputs the original points. The result combines colored topo bands and overlaid points, with the visualization likely rendered using a web-mapping library that consumes the `the_geom_webmercator` geometries.

59% match

CartoDB SQL tricks: Various things that as a SQL-newbie I had to search for >10 minutes to find answers for.

This example demonstrates a collection of SQL recipes for solving common spatial data problems in CartoDB, ranging from merging neighboring county polygons with ST_Union to creating great circle lines via ST_MakeLine and ST_Segmentize. It shows practical techniques like updating geometries with conditional CASE statements, extracting coordinates with ST_X and ST_Y, stripping HTML tags using regexp_replace, and formatting zip codes with to_char. The code also covers performance strategies for large updates and querying spatial relationships with st_contains. Each snippet targets PostGIS functions within the CartoDB platform, emphasizing data preparation and geometric transformations.

Aandrewbt

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.

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.

Longer single line text

This example uses PostGIS’s ST_GeometryFromText function to load a single LINESTRING of GPS track points and render it as a path. The line is drawn from over two hundred coordinate pairs tracing a route through southern Idaho, with the SQL query creating the geometric line feature. The visualization highlights how raw coordinate data can be transformed into a continuous geographic path, emphasizing the shape and direction of the route. The author, andrewxhill, sourced this from a gist, demonstrating a minimal SQL approach to spatial data handling.

Postgres is Enough

The visualization maps PostgreSQL’s ecosystem onto a network graph, showing how the database extends into background jobs, search, APIs, and data visualization. It uses a force-directed layout with `d3.forceSimulation` to position nodes representing tools like pg_cron, PostGIS, pgvector, and Metabase, while links connect related categories. The graph is rendered as an SVG with D3’s `d3.select` and `d3.line`, with node sizes encoding the number of associated libraries. Data comes from the static markdown file listing each tool’s URL and category.

Aapburnes

57% match

Test of SunCalc in SQL

This example demonstrates how the SunCalc library’s solar position and event calculations can be ported to SQL, using PostgreSQL functions to compute azimuth, altitude, declination, and right ascension for a given time and location. It defines custom types and functions such as `SunCalc_GetPosition`, `SunCalc_SunCoords`, and `SunCalc_solarTransitJ`, mirroring the original JavaScript API. The code relies on plpgsql, PostGIS geometry for coordinates, and standard trigonometric functions, showing how to bring astronomical calculations into a relational database environment.