Skip to main content
100%

Rasters to polygons for CartoDB

✓ Published0🌍 Public
Aandrewxhill
Last edited Sep 11, 2012
Created on Sep 11, 2012

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.

AI-generated description

Similar vizzes

Loading thumbnail…

On the fly size sort of polygons in CartoDB

A CartoDB map displays building polygons from OpenStreetMap, sorted and repositioned by size. The polygons are ordered by height and laid out in rows using a recursive SQL query that translates each geometry into a packed grid. The visualization uses CartoDB.js with Leaflet to render the map and tiles, while the query employs `ST_Area`, `ST_Translate`, and `ST_Transform` to compute offsets and project the geometries. Red polygons with white outlines appear over a Mapbox basemap.

Aandrewxhill
68% 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
67% match
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
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…

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…

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

A temporary fix to raster2pgsql.py to work with current PostGIS HEAD

A Python script loads GDAL raster datasets and converts them into hex-encoded WKB streams for insertion into a PostGIS raster table. It uses the `osgeo.gdal` and `osgeo.osr` bindings to read geospatial data, with `optparse` handling command-line flags for SRID assignment, band extraction, block-size tiling, and overview table creation. The output is a SQL-ready binary representation suitable for PostGIS’s current raster format.

Aandrewxhill
62% match