Skip to main content
100%

Gist 1353003

✓ Published0🌍 Public
Aandrewxhill
Last edited Nov 9, 2011
Created on Nov 9, 2011

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.

AI-generated description

Similar vizzes

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
87% match
Loading thumbnail…

carbon poly tool sum SQL

This example computes the total carbon stored in a user-defined polygon by summing raster pixel values from a carbon dataset. It shows a SQL query using PostGIS raster functions, where `ST_Intersects` filters relevant pixels, `ST_PixelAsPolygon` converts pixels to polygons, and `ST_Transform` projects them to calculate area. The query also uses `ST_Value` to extract pixel carbon values, dividing by 100 and scaling by area to produce a total, alongside the polygon’s geographic area. The code relies on the PostGIS spatial extension and a `generate_series` cross join to iterate over a 10-by-10 grid of pixels.

Aandrewxhill
83% 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
76% 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
75% 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
67% match
Loading thumbnail…

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
67% match
Loading thumbnail…

Rasters to polygons for CartoDB

A PostGIS workflow converts nightly satellite raster data into vector polygons for display in CartoDB. The SQL first uses `ST_MapAlgebraExpr` to reclassify raster band values into discrete categories, then `ST_DumpAsPolygons` extracts each distinct value region as a geometry, wrapping results with `ST_MakeValid` and `ST_Multi`. The resulting polygons, along with their original raster values, are inserted into a new `light_polygons` table with a `band` column, enabling efficient cartographic rendering of classified light intensity zones.

Aandrewxhill
67% match
Loading thumbnail…

just a bookmark of random psql queries.

This example visualizes spatial relationships between neighborhoods, city blocks, and demographic data in Portland and Laurelhurst through a series of SQL queries. The code shows how geographic joins and intersections are performed using PostGIS functions like `ST_Intersects`, combining building footprints, block polygons, and census block-group data. The queries calculate race-based percentages, such as percent white and percent black, from census fields like `h7x002` and `h7x003`, while filtering for specific places and positive population counts. The gist demonstrates iterative query building for choropleth-style analysis, revealing how raw geographic and tabular data are merged to support subsequent mapping or charting in a visualization tool.

CCaged
66% match