Skip to main content
100%

Gist 6114786

✓ Published0🌍 Public
Aandrewxhill
Last edited Jul 30, 2013
Created on Jul 30, 2013

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.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 1353003

This example demonstrates a spatial SQL query that computes raster statistics for a custom polygon region. It uses PostGIS functions to sum pixel values, count pixels, and calculate the average value across a 10x10 grid of sub-pixels within each raster tile. The query filters raster tiles intersecting the target polygon and then intersects translated grid cells against the same polygon. The code relies on `ST_Value`, `ST_Intersects`, `ST_Translate`, `ST_MakeEnvelope`, and `generate_series` to perform the analysis, extracting data from the `carbon_query_test_s` table.

Aandrewxhill
76% match
Loading thumbnail…

Gist 89811213f98645c51fa8

Using CartoDB’s SQL API and PostGIS functions, this notebook explores Gilt purchase data aggregated by zip code. It shows a series of interactive maps, including a bubble map of average session time and choropleths of purchase rates, built from geocoded postal points and polygon tables. The workflow demonstrates nested SQL subqueries to join transaction aggregates with geometries, spatial filtering with `ST_Intersects` and `ST_Buffer` around New York City, and on-the-fly centroid generation via `st_centroid` for a target audience map. Geocoding and tile rendering rely on CartoDB’s default web-mercator projection.

Aandrewxhill
75% match
Loading thumbnail…

Gist 1353427

This example shows how to sum raster pixel values from a PostGIS database that fall within a specified multi-polygon region, using SQL queries to perform spatial intersection tests. The code defines a multi-polygon geometry and iterates over a 10×10 grid of pixel offsets, translating each pixel’s envelope and checking for intersection with the polygon. It uses PostGIS functions like `ST_Value`, `ST_Intersects`, `ST_Translate`, and `ST_MakeEnvelope` to calculate the total sum and count of intersecting raster cells, demonstrating a spatial aggregation workflow.

Aandrewxhill
73% match
Loading thumbnail…

torque query and output

This example demonstrates a CartoDB SQL query that aggregates and grids a dataset of points over both space and time. The query snaps geographic coordinates to a grid, groups counts by spatial cell and time bin, and returns an array of counts and corresponding date indices for each cell. The accompanying JSON shows the result—rows with x/y coordinates, value arrays, and date indices, which can be used to build a temporal heatmap or animated map. The visualization highlights how spatial and temporal aggregation can reveal patterns in point data, such as event frequency over time across a gridded map.

Aandrewxhill
73% match
Loading thumbnail…

Gist 7c1efd9bdead734010b0

This tutorial demonstrates how to build interactive maps and animated visualizations of a cat’s GPS tracking data using CartoDB’s web interface, SQL, and CartoCSS. It shows point, categorical, and temporal bubble maps, including a Torque animation of Spencer the Cat’s movements over time. The code uses CartoDB’s SQL API with functions like `ST_MakeLine` and `date_part`, along with CartoCSS styling for markers. It also covers importing polygon building datasets from MassGIS and querying them with spatial operations like `ST_Distance` and `CDB_LatLng`.

Aandrewxhill
72% match
Loading thumbnail…

Gist 1447684

This visualization shows a clipped portion of a Mars elevation raster dataset, rendering it as a base64-encoded PNG image for a defined geographic region. The SQL code uses PostGIS functions including `ST_AsPng`, `ST_Resample`, and `ST_ClipEx` to extract and resample the raster, while `ST_World2RasterCoordX` and `ST_World2RasterCoordY` convert real-world coordinates to raster grid positions. A multipolygon defines the area of interest, which is also converted to a raster grid via `ST_AsRaster` for alignment. The query filters rows with `St_Intersects` to return only relevant raster tiles, demonstrating spatial clipping and coordinate transformation for remote-sensing data delivery.

Aandrewxhill
72% match
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
71% 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
70% match