Schema
Owner
postgres
Descriptions
args: tab, col, factor, algo='NearestNeighbor' - Create an reduced resolution version of a given raster coverage.
Options
Option | Value |
---|---|
Returns |
pg_catalog.regclass |
Language |
|
Parameters |
tab pg_catalog.regclass col name factor integer algo text = 'NearestNeighbour'::text |
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 07/12/2018 13:23 |