Skip to main content
100%

Create a density grid from geometries. For use in CartoDB

✓ Published0🌍 Public
Aandrewxhill
Last edited Mar 19, 2012
Created on Mar 19, 2012

This example creates a density grid from point geometries using PostGIS and CartoDB, aggregating points into square cells via `ST_SnapToGrid` and returning cell envelopes with an associated value. It shows both a simple count of points per cell and a version that applies aggregation methods like MAX, MIN, SUM, or AVG to a specified numeric column, such as restaurant ratings or view counts. The functions generate a `CDB_DensityGridPair` type with geometry and float value, enabling thematic mapping of spatial density. An example query uses the grid to visualize cuisine ratings across New York restaurants.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 4063106

This example visualizes the density distribution of bicycle data from the `table_2007_2009_bicy` dataset, dividing points into five quantile-based buckets. It shows how the maximum point count and density vary across these buckets, revealing spatial clustering patterns. The SQL code uses CartoDB functions such as `st_snaptogrid` and `CDB_XYZ_Resolution` to aggregate points into grid cells at a given zoom level, then calculates density per cell and assigns quartiles via `ntile(5)`. The final output summarizes each bucket’s maximum values, providing a statistical overview of the underlying geographic data.

Aandrewxhill
72% match
Loading thumbnail…

Create an equal area grid over a buffer of points

This example visualizes a rectangular grid of points covering a 50-kilometer buffer around input locations, with each point representing the centroid of a grid cell in an equal-area projection. The SQL query first buffers the original NAD83 geometries, then unions and reprojects them into the Lambert equal-area projection (SRID 97314) to create an envelope, and finally generates a 1000-by-1000-meter grid using CartoDB’s `CDB_RectangleGrid` function. Each cell’s centroid is reprojected back to WGS84 (SRID 4326) for display, ensuring spatially uniform sampling across the buffered area.

Aandrewxhill
70% match
Loading thumbnail…

Client side hexgrid | CartoDB.js

This example visualizes point data from a table named `za` as a hexbin density grid over a Leaflet map, using the CartoDB.js library. It initially renders hexagonal cells colored by point density, then recomputes the grid at a finer resolution and updates the map’s SQL and CartoCSS style to reflect the new bin size. The client-side SQL queries use PostGIS functions like `CDB_HexagonGrid` and `ST_Expand`, while `cartodb.SQL` and sublayer methods handle dynamic style and data updates. The base map uses Stamen toner tiles.

Aandrewxhill
69% match
Loading thumbnail…

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.

Aandrewxhill
69% match
Loading thumbnail…

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.

Aandrewxhill
67% match
Loading thumbnail…

Fun SQL statements for CartoDB

This example shows how to explore and analyze tornado and county data in CartoDB using SQL statements. It demonstrates basic queries for filtering, sorting, and aggregating records, followed by spatial functions that inspect geometry types, compute areas, distances, and intersections. The code uses PostGIS functions such as `ST_Area`, `ST_Distance`, `ST_Intersects`, and `ST_DWithin`, alongside CartoDB-specific helpers like `CDB_LatLng`. It also includes updates to add tornado counts and density per county, highlighting how SQL can drive interactive map visualizations.

Aandrewxhill
65% match
Loading thumbnail…

Course notes

The visualization maps New York City’s census tracts, coloring each by the density of public bicycle racks. It first simplifies tract geometries using PostGIS’s `ST_SimplifyPreserveTopology`, then counts rack intersections with `ST_Intersects` to populate a numeric column, normalizing the count by `shape_area` via SQL. The final map, rendered with CartoDB’s default styling, displays the normalized rack density as a choropleth, revealing spatial patterns in bike infrastructure across neighborhoods.

Aandrewxhill
64% 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
64% match