Polygon insert Named Function

upsert the_geom with security definer

This example shows a PostgreSQL function that upserts geometry data for a Leaflet-based mapping application. It accepts parallel arrays of CartoDB IDs and GeoJSON strings, then returns operation codes (insert, update, delete) with corresponding cartodb_ids. The function uses PL/pgSQL with SECURITY DEFINER privileges to build a dynamic SQL query that performs updates, deletes, and inserts against the `leaflet_data` table. It leverages PostGIS functions `ST_SetSRID` and `ST_GeomFromGeoJSON` to convert GeoJSON into spatial geometries, while handling null values to trigger deletion.

70% match

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.

59% match

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.

58% match

parenthesis enclosed NYC open data lat/lon to the_geom

A SQL snippet converts NYC Open Data latitude/longitude coordinates stored as parenthesized text in a column into the `the_geom` geometry field for PostGIS. The example shows how to parse a string like `(lat, lon)` by stripping parentheses, splitting on commas, and reordering the values into `ST_MakePoint` before applying `ST_SetSRID` with EPSG:4326. This enables spatial queries and map rendering from raw tabular coordinate data. The command targets a placeholder table and column, making it adaptable to any dataset with similar text-encoded points.

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.

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

PlpgSQL to cross dateline when drawing two points

This example demonstrates a PostGIS/PLpgSQL function that corrects lines crossing the 180-degree antimeridian when drawn on a web Mercator map. It takes two point geometries and constructs a MultiLineString that breaks the crossing segment at the map’s edge, inserting a vertical line at ±20037508.34 to create a continuous visual path. The code uses ST_Transform, ST_MakeLine, and ST_Collect to handle the geometry, and defines a custom aggregate (axh_crossdateline) to process ordered point sequences. The function expects SRID 3857 inputs and returns the adjusted line for seamless rendering.

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.