Skip to main content
100%

Gist 1353427

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

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.

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
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
76% 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
73% 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
73% 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…

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

Gist 1e418bc5c048fed5ef2604a1902eb735

This example demonstrates a spatial correction of a U.S. District Court jurisdiction boundary using PostGIS. It shows how an erroneous polygon (fid 57) is split into two parts, with one part merged into a neighboring jurisdiction (fid 58) and the other retained in place. The SQL uses `st_dump` to extract the polygon components, then `st_union` and `st_multi` to recompute the merged and retained geometries. The `UPDATE` statement with a `CASE` expression applies the corrected shapes back to the table, relying on PostGIS geometry functions for the operation.

CCaged
65% match
Loading thumbnail…

ST_ClipEx a working variation of ST_Clip for raster

ST_ClipEx demonstrates a custom SQL function for clipping raster data in PostGIS, showing how to extract a rectangular subset of pixels from a larger raster grid. The function takes input coordinates and dimensions, then constructs a new empty raster with matching georeferencing properties before copying pixel values band by band. It iterates through each band and cell position using nested loops, applying ST_Value to read source pixels and ST_SetValue to write them into the clipped result. The code explicitly manages edge cases where the requested clip extends beyond the original raster bounds.

Aandrewxhill
65% match