[postgis-devel] [PostGIS] #1319: [raster] Make raster_columns a view and AddRasterColumn enforce more

[PostGIS]

#1319: [raster] Make raster_columns a view and AddRasterColumn enforce more
----------------------------+-----------------------------------------------
 Reporter:  robe            |       Owner:  robe         
     Type:  task            |      Status:  new          
 Priority:  high            |   Milestone:  PostGIS 2.0.0
Component:  postgis raster  |     Version:  trunk        
 Keywords:                  |  
----------------------------+-----------------------------------------------

Comment(by dustymugs):

 Added scalex and scaley.  Will need to write a lower-level function to get
 the band pixtypes of a raster quickly.

 {{{
 CREATE OR REPLACE VIEW v_raster_columns AS
         SELECT
                 current_database()::character varying(256) AS
 r_table_catalog,
                 n.nspname::character varying(256) AS r_table_schema,
                 c.relname::character varying(256) AS r_table_name,
                 a.attname::character varying(256) AS r_raster_column,
                 COALESCE(raster_constraint_srid(n.nspname::text,
 c.relname::text, a.attname::text), (SELECT ST_SRID('POINT(0
 0)'::geometry))) AS srid,
                 COALESCE(raster_constraint_scalex(n.nspname::text,
 c.relname::text, a.attname::text), 0) AS scalex,
                 COALESCE(raster_constraint_scaley(n.nspname::text,
 c.relname::text, a.attname::text), 0) AS scaley
                 -- bandtype[] text[]
         FROM
                 pg_class c,
                 pg_attribute a,
                 pg_type t,
                 pg_namespace n
         WHERE t.typname = 'raster'::name
                 AND a.attisdropped = false
                 AND a.atttypid = t.oid
                 AND a.attrelid = c.oid
                 AND c.relnamespace = n.oid
                 AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char")
                 AND NOT pg_is_other_temp_schema(c.relnamespace);

 CREATE OR REPLACE FUNCTION raster_constraint_srid(rastschema text,
 rasttable text, rastcolumn text)
   RETURNS integer AS
 $BODY$
 SELECT replace(replace(split_part(s.consrc, ' = ', 2), ')', ''), '(',
 '')::integer
                  FROM pg_class c, pg_namespace n, pg_attribute a,
 pg_constraint s
                  WHERE n.nspname = $1
                  AND c.relname = $2
                  AND a.attname = $3
                  AND a.attrelid = c.oid
                  AND s.connamespace = n.oid
                  AND s.conrelid = c.oid
                  AND a.attnum = ANY (s.conkey)
                  AND s.consrc LIKE '%srid(% = %';
 $BODY$
   LANGUAGE sql STABLE STRICT
   COST 100;

 CREATE OR REPLACE FUNCTION raster_constraint_scalex(rastschema text,
 rasttable text, rastcolumn text)
   RETURNS double precision AS
 $BODY$
         SELECT
                 replace(replace(split_part(split_part(s.consrc, ' = ', 2),
 '::', 1), ')', ''), '(', '')::double precision
         FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
         WHERE n.nspname = $1
                 AND c.relname = $2
                 AND a.attname = $3
                 AND a.attrelid = c.oid
                 AND s.connamespace = n.oid
                 AND s.conrelid = c.oid
                 AND a.attnum = ANY (s.conkey)
                 AND s.consrc LIKE '%scalex(% = %';
 $BODY$
   LANGUAGE sql STABLE STRICT
   COST 100;

 CREATE OR REPLACE FUNCTION raster_constraint_scaley(rastschema text,
 rasttable text, rastcolumn text)
   RETURNS double precision AS
 $BODY$
         SELECT
                 replace(replace(split_part(split_part(s.consrc, ' = ', 2),
 '::', 1), ')', ''), '(', '')::double precision
         FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
         WHERE n.nspname = $1
                 AND c.relname = $2
                 AND a.attname = $3
                 AND a.attrelid = c.oid
                 AND s.connamespace = n.oid
                 AND s.conrelid = c.oid
                 AND a.attnum = ANY (s.conkey)
                 AND s.consrc LIKE '%scaley(% = %';
 $BODY$
   LANGUAGE sql STABLE STRICT
   COST 100;
 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1319#comment:8>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.