Schema
public
Owner
albenard
Descriptions
args: tab, col, ext, sfx, sfy, tw, th, algo='NearestNeighbor' - Return a set of configured tiles from an arbitrarily tiled raster coverage.
Options
Option | Value |
---|---|
Returns |
Set of public.raster |
Language |
plpgsql |
Parameters |
tab pg_catalog.regclass col name ext public.geometry sfx double precision sfy double precision tw integer th integer algo text = 'NearestNeighbour'::text |
Definition
CREATE OR REPLACE FUNCTION public.st_retile (
tab pg_catalog.regclass,
col name,
ext public.geometry,
sfx double precision,
sfy double precision,
tw integer,
th integer,
algo text = 'NearestNeighbour'::text
)
RETURNS SETOF public.raster AS
$span$
DECLARE
rec RECORD;
ipx FLOAT8;
ipy FLOAT8;
tx int;
ty int;
te GEOMETRY; -- tile extent
ncols int;
nlins int;
srid int;
sql TEXT;
BEGIN
RAISE DEBUG 'Target coverage will have sfx=%, sfy=%', sfx, sfy;
-- 2. Loop over each target tile and build it from source tiles
ipx := st_xmin(ext);
ncols := ceil((st_xmax(ext)-ipx)/sfx/tw);
IF sfy < 0 THEN
ipy := st_ymax(ext);
nlins := ceil((st_ymin(ext)-ipy)/sfy/th);
ELSE
ipy := st_ymin(ext);
nlins := ceil((st_ymax(ext)-ipy)/sfy/th);
END IF;
srid := ST_Srid(ext);
RAISE DEBUG 'Target coverage will have % x % tiles, each of approx size % x %', ncols, nlins, tw, th;
RAISE DEBUG 'Target coverage will cover extent %', ext::box2d;
FOR tx IN 0..ncols-1 LOOP
FOR ty IN 0..nlins-1 LOOP
te := ST_MakeEnvelope(ipx + tx * tw * sfx,
ipy + ty * th * sfy,
ipx + (tx+1) * tw * sfx,
ipy + (ty+1) * th * sfy,
srid);
--RAISE DEBUG 'sfx/sfy: %, %', sfx, sfy;
--RAISE DEBUG 'tile extent %', te;
sql := 'SELECT count(*), public.ST_Clip( public.ST_Union( public.ST_SnapToGrid( public.ST_Rescale( public.ST_Clip(' || quote_ident(col)
|| ', public.ST_Expand($3, greatest($1,$2))),$1, $2, $6), $4, $5, $1, $2)), $3) g FROM ' || tab::text
|| ' WHERE public.ST_Intersects(' || quote_ident(col) || ', $3)';
--RAISE DEBUG 'SQL: %', sql;
FOR rec IN EXECUTE sql USING sfx, sfy, te, ipx, ipy, algo LOOP
--RAISE DEBUG '% source tiles intersect target tile %,% with extent %', rec.count, tx, ty, te::box2d;
IF rec.g IS NULL THEN
RAISE WARNING 'No source tiles cover target tile %,% with extent %',
tx, ty, te::box2d;
ELSE
--RAISE DEBUG 'Tile for extent % has size % x %', te::box2d, st_width(rec.g), st_height(rec.g);
RETURN NEXT rec.g;
END IF;
END LOOP;
END LOOP;
END LOOP;
RETURN;
END;
$span$
LANGUAGE 'plpgsql'
STABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;
COMMENT ON FUNCTION public.st_retile(tab pg_catalog.regclass, col name, ext public.geometry, sfx double precision, sfy double precision, tw integer, th integer, algo text)
IS 'args: tab, col, ext, sfx, sfy, tw, th, algo=''NearestNeighbor'' - Return a set of configured tiles from an arbitrarily tiled raster coverage.';
This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51 |