pggeodb.nancy.inra.fr/db_cefs - db_cefs on pggeodb.nancy.inra.fr

Function: st_retile

Schema

public

Owner

postgres

Descriptions

args: tab, col, ext, sfx, sfy, tw, th, algo='NearestNeighbor' - Return a set of configured tiles from an arbitrarily tiled raster coverage.

Options

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(*), ST_Clip(ST_Union(ST_SnapToGrid(ST_Rescale(ST_Clip(' || quote_ident(col)
         || ', st_expand($3, greatest($1,$2))),$1, $2, $6), $4, $5, $1, $2)), $3) g FROM ' || tab::text
         || ' WHERE 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 13/03/2014 13:23