postgis_raster

pig repo add pgdg -u          # Add PGDG repo and update cache

CREATE EXTENSION postgis_raster CASCADE;  -- requires: postgis

CREATE EXTENSION postgis_raster;

# Load a GeoTIFF as tiled 100x100 rasters, create spatial index, use COPY
raster2pgsql -s 4326 -t 100x100 -I -C -M elevation.tif public.dem | psql mydb

# Append to existing table
raster2pgsql -s 4326 -t 100x100 -a more_data.tif public.dem | psql mydb

-- Get raster dimensions and pixel size
SELECT rid,
    ST_Width(rast) AS width,
    ST_Height(rast) AS height,
    ST_ScaleX(rast) AS pixel_size_x,
    ST_ScaleY(rast) AS pixel_size_y,
    ST_NumBands(rast) AS bands,
    ST_SRID(rast) AS srid
FROM dem LIMIT 5;

-- Get the value at a specific point
SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)) AS elevation
FROM dem
WHERE ST_Intersects(rast, ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326));

-- Get value at column/row position (band 1)
SELECT ST_Value(rast, 1, 10, 20) FROM dem WHERE rid = 1;

SELECT (ST_SummaryStats(rast)).*
FROM dem WHERE rid = 1;
-- Returns: count, sum, mean, stddev, min, max

-- Clip raster to a polygon boundary
SELECT ST_Clip(rast, geom) AS clipped_rast
FROM dem, boundaries
WHERE ST_Intersects(rast, geom);

-- Single-raster map algebra: classify elevation
SELECT ST_MapAlgebra(rast, 1, NULL,
    'CASE WHEN [rast] > 100 THEN 1 WHEN [rast] > 50 THEN 2 ELSE 3 END') AS classified
FROM dem;

-- Two-raster map algebra: difference between two DEMs
SELECT ST_MapAlgebra(a.rast, 1, b.rast, 1, '[rast1] - [rast2]') AS diff
FROM dem_old a, dem_new b
WHERE ST_Intersects(a.rast, b.rast);

-- Convert raster pixels to vector points
SELECT (ST_PixelAsPoints(rast)).*
FROM dem WHERE rid = 1;

-- Convert raster to polygons (one per unique value)
SELECT (ST_DumpAsPolygons(rast)).*
FROM dem WHERE rid = 1;

-- Intersect raster with vector and get values
SELECT p.name, ST_Value(d.rast, p.geom) AS elevation
FROM dem d, points p
WHERE ST_Intersects(d.rast, p.geom);

-- Resample to a different pixel size
SELECT ST_Rescale(rast, 0.001, -0.001) FROM dem;

-- Reproject to a different SRID
SELECT ST_Transform(rast, 3857) FROM dem;

-- Export as GeoTIFF (binary)
SELECT ST_AsTIFF(rast) FROM dem WHERE rid = 1;

-- Export as PNG
SELECT ST_AsPNG(rast) FROM dem WHERE rid = 1;

-- Export as JPEG
SELECT ST_AsJPEG(rast) FROM dem WHERE rid = 1;