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

Function: _add_raster_constraint_coverage_tile

Schema

public

Owner

postgres

Descriptions

There is no description for function _add_raster_constraint_coverage_tile

Options

Definition

CREATE OR REPLACE FUNCTION public._add_raster_constraint_coverage_tile (
 rastschema name,
 rasttable name,
 rastcolumn name
)
RETURNS boolean AS
$span$
DECLARE
fqtn text;
cn name;
sql text;

_scalex double precision;
_scaley double precision;
_skewx double precision;
_skewy double precision;
_tilewidth integer;
_tileheight integer;
_alignment boolean;

_covextent geometry;
_covrast raster;
BEGIN
fqtn := '';
IF length($1) > 0 THEN
fqtn := quote_ident($1) || '.';
END IF;
fqtn := fqtn || quote_ident($2);

cn := 'enforce_coverage_tile_' || $3;

-- metadata
BEGIN
sql := 'WITH foo AS (SELECT ST_Metadata(' || quote_ident($3) || ') AS meta, ST_ConvexHull(' || quote_ident($3) || ') AS hull FROM ' || fqtn || ') SELECT max((meta).scalex), max((meta).scaley), max((meta).skewx), max((meta).skewy), max((meta).width), max((meta).height), ST_Union(hull) FROM foo';
EXECUTE sql INTO _scalex, _scaley, _skewx, _skewy, _tilewidth, _tileheight, _covextent;
EXCEPTION WHEN OTHERS THEN
RAISE DEBUG 'Unable to get coverage metadata for %.%: % (%)',
       fqtn, quote_ident($3), SQLERRM, SQLSTATE;
     -- TODO: Why not return false here ?
END;

-- rasterize extent
BEGIN
_covrast := ST_AsRaster(_covextent, _scalex, _scaley, '8BUI', 1, 0, NULL, NULL, _skewx, _skewy);
IF _covrast IS NULL THEN
RAISE NOTICE 'Unable to create coverage raster. Cannot add coverage tile constraint: % (%)',
         SQLERRM, SQLSTATE;
RETURN FALSE;
END IF;

-- remove band
_covrast := ST_MakeEmptyRaster(_covrast);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Unable to create coverage raster. Cannot add coverage tile constraint: % (%)',
       SQLERRM, SQLSTATE;
RETURN FALSE;
END;

sql := 'ALTER TABLE ' || fqtn ||
' ADD CONSTRAINT ' || quote_ident(cn) ||
' CHECK (st_iscoveragetile(' || quote_ident($3) || ', ''' || _covrast || '''::raster, ' || _tilewidth || ', ' || _tileheight || '))';
RETURN _add_raster_constraint(cn, sql);
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 13/03/2014 13:23