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

Function: st_createoverview

Schema

public

Owner

albenard

Descriptions

args: tab, col, factor, algo='NearestNeighbor' - Create an reduced resolution version of a given raster coverage.

Options

Definition

CREATE OR REPLACE FUNCTION public.st_createoverview (
 tab pg_catalog.regclass,
 col name,
 factor integer,
 algo text = 'NearestNeighbour'::text
)
RETURNS pg_catalog.regclass AS
$span$
DECLARE
 sinfo RECORD; -- source info
 sql TEXT;
 ttab TEXT;
BEGIN

 -- 0. Check arguments, we need to ensure:
 --    a. Source table has a raster column with given name
 --    b. Source table has a fixed scale (or "factor" would have no meaning)
 --    c. Source table has a known extent ? (we could actually compute it)
 --    d. Source table has a fixed tile size (or "factor" would have no meaning?)
 -- # all of the above can be checked with a query to raster_columns
 sql := 'SELECT r.r_table_schema sch, r.r_table_name tab, '
     || 'r.scale_x sfx, r.scale_y sfy, r.blocksize_x tw, '
     || 'r.blocksize_y th, r.extent ext, r.srid FROM public.raster_columns r, '
     || 'pg_class c, pg_namespace n WHERE r.r_table_schema = n.nspname '
     || 'AND r.r_table_name = c.relname AND r_raster_column = $2 AND '
     || ' c.relnamespace = n.oid AND c.oid = $1'
 ;
 EXECUTE sql INTO sinfo USING tab, col;
 IF sinfo IS NULL THEN
     RAISE EXCEPTION '%.% raster column does not exist', tab::text, col;
 END IF;
 IF sinfo.sfx IS NULL or sinfo.sfy IS NULL THEN
   RAISE EXCEPTION 'cannot create overview without scale constraint, try select AddRasterConstraints(''%'', ''%'');', tab::text, col;
 END IF;
 IF sinfo.tw IS NULL or sinfo.tw IS NULL THEN
   RAISE EXCEPTION 'cannot create overview without tilesize constraint, try select AddRasterConstraints(''%'', ''%'');', tab::text, col;
 END IF;
 IF sinfo.ext IS NULL THEN
   RAISE EXCEPTION 'cannot create overview without extent constraint, try select AddRasterConstraints(''%'', ''%'');', tab::text, col;
 END IF;

 -- TODO: lookup in raster_overviews to see if there's any
 --       lower-resolution table to start from

 ttab := 'o_' || factor || '_' || sinfo.tab;
 sql := 'CREATE TABLE ' || quote_ident(sinfo.sch)
     || '.' || quote_ident(ttab)
     || ' AS SELECT ST_Retile($1, $2, $3, $4, $5, $6, $7) '
     || quote_ident(col);
 EXECUTE sql USING tab, col, sinfo.ext,
                   sinfo.sfx * factor, sinfo.sfy * factor,
                   sinfo.tw, sinfo.th, algo;

 -- TODO: optimize this using knowledge we have about
 --       the characteristics of the target column ?
 PERFORM public.AddRasterConstraints(sinfo.sch, ttab, col);

 PERFORM  public.AddOverviewConstraints(sinfo.sch, ttab, col,
                                sinfo.sch, sinfo.tab, col, factor);

   -- return the schema as well as the table
 RETURN sinfo.sch||'.'||ttab;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.st_createoverview(tab pg_catalog.regclass, col name, factor integer, algo text)
IS 'args: tab, col, factor, algo=''NearestNeighbor'' - Create an reduced resolution version of a given raster coverage.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51