root/spike/wktraster/rt_pg/rtpostgis.sql.in.c

Revision 5823, 68.6 KB (checked in by pracine, 22 months ago)

-Ticket 569. Added a serie of ST_AddBand function
  • Property svn:keywords set to Id
Line 
1-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2--
3-- $Id$
4--
5-- WKTRaster - Raster Type for PostGIS
6-- http://trac.osgeo.org/postgis/wiki/WKTRaster
7--
8-- Copyright (c) 2009 Sandro Santilli <strk@keybit.net>
9-- Copyright (c) 2009-2010 Pierre Racine <pierre.racine@sbf.ulaval.ca>
10-- Copyright (c) 2009-2010 Jorge Arevalo <jorge.arevalo@deimos-space.com>
11-- Copyright (c) 2009-2010 Mateusz Loskot <mateusz@loskot.net>
12-- Copyright (c) 2010 David Zwarg <dzwarg@avencia.com>
13--
14-- This is free software; you can redistribute and/or modify it under
15-- the terms of the GNU General Public Licence. See the COPYING file.
16--
17-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
18--
19-- WARNING: Any change in this file must be evaluated for compatibility.
20--
21-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
22
23-- BEGIN;
24
25------------------------------------------------------------------------------
26-- RASTER Type
27------------------------------------------------------------------------------
28
29CREATE OR REPLACE FUNCTION raster_in(cstring)
30    RETURNS raster
31    AS 'MODULE_PATHNAME','RASTER_in'
32    LANGUAGE 'C' IMMUTABLE STRICT;
33
34CREATE OR REPLACE FUNCTION raster_out(raster)
35    RETURNS cstring
36    AS 'MODULE_PATHNAME','RASTER_out'
37    LANGUAGE 'C' IMMUTABLE STRICT;
38
39CREATE TYPE raster (
40    alignment = double,
41    internallength = variable,
42    input = raster_in,
43    output = raster_out,
44    storage = extended
45);
46
47------------------------------------------------------------------------------
48-- FUNCTIONS
49------------------------------------------------------------------------------
50
51-----------------------------------------------------------------------
52-- RasterLib Version
53-----------------------------------------------------------------------
54
55CREATE OR REPLACE FUNCTION postgis_raster_lib_version()
56    RETURNS text
57    AS 'MODULE_PATHNAME', 'RASTER_lib_version'
58    LANGUAGE 'C' IMMUTABLE; -- a new lib will require a new session
59
60CREATE OR REPLACE FUNCTION postgis_raster_lib_build_date()
61    RETURNS text
62    AS 'MODULE_PATHNAME', 'RASTER_lib_build_date'
63    LANGUAGE 'C' IMMUTABLE; -- a new lib will require a new session
64
65-----------------------------------------------------------------------
66-- Raster Accessors
67-----------------------------------------------------------------------
68
69CREATE OR REPLACE FUNCTION st_convexhull(raster)
70    RETURNS geometry
71    AS 'MODULE_PATHNAME','RASTER_convex_hull'
72    LANGUAGE 'C' IMMUTABLE STRICT;
73
74CREATE OR REPLACE FUNCTION st_box2d(raster)
75    RETURNS box2d
76    AS 'select st_box2d(st_convexhull($1))'
77    LANGUAGE 'SQL' IMMUTABLE STRICT;
78
79CREATE OR REPLACE FUNCTION st_envelope(raster)
80    RETURNS geometry
81    AS 'select st_envelope(st_convexhull($1))'
82    LANGUAGE 'SQL' IMMUTABLE STRICT;
83
84CREATE OR REPLACE FUNCTION st_height(raster)
85    RETURNS integer
86    AS 'MODULE_PATHNAME','RASTER_getHeight'
87    LANGUAGE 'C' IMMUTABLE STRICT;
88
89CREATE OR REPLACE FUNCTION st_numbands(raster)
90    RETURNS integer
91    AS 'MODULE_PATHNAME','RASTER_getNumBands'
92    LANGUAGE 'C' IMMUTABLE STRICT;
93
94CREATE OR REPLACE FUNCTION st_pixelsizex(raster)
95    RETURNS float8
96    AS 'MODULE_PATHNAME','RASTER_getXPixelSize'
97    LANGUAGE 'C' IMMUTABLE STRICT;
98
99CREATE OR REPLACE FUNCTION st_pixelsizey(raster)
100    RETURNS float8
101    AS 'MODULE_PATHNAME','RASTER_getYPixelSize'
102    LANGUAGE 'C' IMMUTABLE STRICT;
103
104CREATE OR REPLACE FUNCTION st_skewx(raster)
105    RETURNS float8
106    AS 'MODULE_PATHNAME','RASTER_getXSkew'
107    LANGUAGE 'C' IMMUTABLE STRICT;
108
109CREATE OR REPLACE FUNCTION st_skewy(raster)
110    RETURNS float8
111    AS 'MODULE_PATHNAME','RASTER_getYSkew'
112    LANGUAGE 'C' IMMUTABLE STRICT;
113
114CREATE OR REPLACE FUNCTION st_srid(raster)
115    RETURNS integer
116    AS 'MODULE_PATHNAME','RASTER_getSRID'
117    LANGUAGE 'C' IMMUTABLE STRICT;
118
119CREATE OR REPLACE FUNCTION st_upperleftx(raster)
120    RETURNS float8
121    AS 'MODULE_PATHNAME','RASTER_getXUpperLeft'
122    LANGUAGE 'C' IMMUTABLE STRICT;
123
124CREATE OR REPLACE FUNCTION st_upperlefty(raster)
125    RETURNS float8
126    AS 'MODULE_PATHNAME','RASTER_getYUpperLeft'
127    LANGUAGE 'C' IMMUTABLE STRICT;
128
129CREATE OR REPLACE FUNCTION st_width(raster)
130    RETURNS integer
131    AS 'MODULE_PATHNAME','RASTER_getWidth'
132    LANGUAGE 'C' IMMUTABLE STRICT;
133
134CREATE OR REPLACE FUNCTION st_metadata(rast raster,
135                                       OUT upperleftx float8,
136                                       OUT upperlefty float8,
137                                       OUT width int,
138                                       OUT height int,
139                                       OUT pixelsizex float8,
140                                       OUT pixelsizey float8,
141                                       OUT skewx float8,
142                                       OUT skewy float8,
143                                       OUT srid int,
144                                       OUT numbands int
145                                      )
146    AS $$
147    SELECT st_upperleftx($1),
148           st_upperlefty($1),
149           st_width($1),
150           st_height($1),
151           st_pixelsizex($1),
152           st_pixelsizey($1),
153           st_skewx($1),
154           st_skewy($1),
155           st_srid($1),
156           st_numbands($1)
157    $$
158    LANGUAGE SQL;
159
160-----------------------------------------------------------------------
161-- Constructors ST_MakeEmptyRaster and ST_AddBand
162-----------------------------------------------------------------------
163CREATE OR REPLACE FUNCTION st_makeemptyraster(width int, height int, upperleftx float8, upperlefty float8, pixelsizex float8, pixelsizey float8, skewx float8, skewy float8, srid int4)
164    RETURNS RASTER
165    AS 'MODULE_PATHNAME', 'RASTER_makeEmpty'
166    LANGUAGE 'C' IMMUTABLE;
167   
168CREATE OR REPLACE FUNCTION st_makeemptyraster(width int, height int, upperleftx float8, upperlefty float8, pixelsize float8) 
169    RETURNS raster
170    AS 'select st_makeemptyraster($1, $2, $3, $4, $5, $5, 0, 0, -1)'
171    LANGUAGE 'SQL' IMMUTABLE;   
172   
173CREATE OR REPLACE FUNCTION st_makeemptyraster(width int, height int, upperleftx float8, upperlefty float8, pixelsizex float8, pixelsizey float8, skewx float8, skewy float8) 
174    RETURNS raster
175    AS 'select st_makeemptyraster($1, $2, $3, $4, $5, $6, $7, $8, -1)'
176    LANGUAGE 'SQL' IMMUTABLE;   
177
178CREATE OR REPLACE FUNCTION st_makeemptyraster(rast raster) 
179    RETURNS raster
180    AS 'select st_makeemptyraster(st_width($1), st_height($1), st_upperleftx($1), st_upperlefty($1), st_pixelsizex($1), st_pixelsizey($1), st_skewx($1), st_skewy($1), st_srid($1))'
181    LANGUAGE 'SQL' IMMUTABLE STRICT;   
182
183CREATE OR REPLACE FUNCTION st_addband(rast raster, index int, pixeltype text, initialvalue float8, nodataval float8)
184    RETURNS RASTER
185    AS 'MODULE_PATHNAME', 'RASTER_addband'
186    LANGUAGE 'C' IMMUTABLE;
187
188CREATE OR REPLACE FUNCTION st_addband(rast raster, pixeltype text) 
189    RETURNS raster
190    AS 'select st_addband($1, NULL, $2, NULL, NULL)'
191    LANGUAGE 'SQL' IMMUTABLE;
192
193CREATE OR REPLACE FUNCTION st_addband(rast raster, pixeltype text, initialvalue float8) 
194    RETURNS raster
195    AS 'select st_addband($1, NULL, $2, $3, NULL)'
196    LANGUAGE 'SQL' IMMUTABLE;
197
198CREATE OR REPLACE FUNCTION st_addband(rast raster, pixeltype text, initialvalue float8, nodataval float8) 
199    RETURNS raster
200    AS 'select st_addband($1, NULL, $2, $3, $4)'
201    LANGUAGE 'SQL' IMMUTABLE;
202
203CREATE OR REPLACE FUNCTION st_addband(rast raster, index int, pixeltype text) 
204    RETURNS raster
205    AS 'select st_addband($1, $2, $3, NULL, NULL)'
206    LANGUAGE 'SQL' IMMUTABLE;
207
208CREATE OR REPLACE FUNCTION st_addband(rast raster, index int, pixeltype text, initialvalue float8) 
209    RETURNS raster
210    AS 'select st_addband($1, $2, $3, $4, NULL)'
211    LANGUAGE 'SQL' IMMUTABLE;
212
213-----------------------------------------------------------------------
214-- Raster Band Accessors
215-----------------------------------------------------------------------
216
217CREATE OR REPLACE FUNCTION st_bandhasnodatavalue(rast raster, band integer)
218    RETURNS boolean
219    AS 'MODULE_PATHNAME','RASTER_getBandHasNoDataValue'
220    LANGUAGE 'C' IMMUTABLE STRICT;
221
222CREATE OR REPLACE FUNCTION st_bandhasnodatavalue(raster)
223    RETURNS boolean
224    AS $$ SELECT st_bandhasnodatavalue($1, 1) $$
225    LANGUAGE SQL;
226
227CREATE OR REPLACE FUNCTION st_bandnodatavalue(rast raster, band integer)
228    RETURNS float4
229    AS 'MODULE_PATHNAME','RASTER_getBandNoDataValue'
230    LANGUAGE 'C' IMMUTABLE STRICT;
231
232CREATE OR REPLACE FUNCTION st_bandnodatavalue(raster)
233    RETURNS float4
234    AS $$ SELECT st_bandnodatavalue($1, 1) $$
235    LANGUAGE SQL;
236
237CREATE OR REPLACE FUNCTION st_bandpath(rast raster, band integer)
238    RETURNS text
239    AS 'MODULE_PATHNAME','RASTER_getBandPath'
240    LANGUAGE 'C' IMMUTABLE STRICT;
241
242CREATE OR REPLACE FUNCTION st_bandpath(raster)
243    RETURNS text
244    AS $$ SELECT st_bandpath($1, 1) $$
245    LANGUAGE SQL;
246
247CREATE OR REPLACE FUNCTION st_bandpixeltype(rast raster, band integer)
248    RETURNS text
249    AS 'MODULE_PATHNAME','RASTER_getBandPixelTypeName'
250    LANGUAGE 'C' IMMUTABLE STRICT;
251
252CREATE OR REPLACE FUNCTION st_bandpixeltype(raster)
253    RETURNS text
254    AS $$ SELECT st_bandpixeltype($1, 1) $$
255    LANGUAGE SQL;
256
257CREATE OR REPLACE FUNCTION st_bandmetadata(rast raster, 
258                                           band int, 
259                                           OUT pixeltype text, 
260                                           OUT hasnodatavalue boolean, 
261                                           OUT nodatavalue float4, 
262                                           OUT isoutdb boolean, 
263                                           OUT path text)
264    AS $$
265    SELECT st_bandpixeltype($1, $2), 
266       st_bandhasnodatavalue($1, $2), 
267       st_bandnodatavalue($1, $2), 
268       st_bandpath($1, $2) IS NOT NULL, 
269       st_bandpath($1, $2)
270    $$
271    LANGUAGE SQL;
272
273CREATE OR REPLACE FUNCTION st_bandmetadata(rast raster, 
274                                           OUT pixeltype text, 
275                                           OUT hasnodatavalue boolean, 
276                                           OUT nodatavalue float4, 
277                                           OUT isoutdb boolean, 
278                                           OUT path text)
279    AS $$
280    SELECT st_bandpixeltype($1, 1), 
281       st_bandhasnodatavalue($1, 1), 
282       st_bandnodatavalue($1, 1), 
283       st_bandpath($1, 1) IS NOT NULL, 
284       st_bandpath($1, 1)
285    $$
286    LANGUAGE SQL;
287
288-----------------------------------------------------------------------
289-- Raster Pixel Accessors
290-----------------------------------------------------------------------
291
292CREATE OR REPLACE FUNCTION st_value(rast raster, band integer, x integer, y integer)
293    RETURNS float8
294    AS 'MODULE_PATHNAME','RASTER_getPixelValue'
295    LANGUAGE 'C' IMMUTABLE STRICT;
296
297CREATE OR REPLACE FUNCTION st_value(rast raster, x integer, y integer)
298    RETURNS float8
299    AS $$ SELECT st_value($1, 1, $2, $3) $$
300    LANGUAGE SQL;
301   
302CREATE OR REPLACE FUNCTION st_value(rast raster, band integer, pt geometry) 
303    RETURNS float8 AS
304    $$
305    DECLARE
306        x float8;
307        y float8;
308        gtype text;
309    BEGIN
310        gtype := st_geometrytype(pt);
311        IF ( gtype != 'ST_Point' ) THEN
312            RAISE EXCEPTION 'Attempting to get the value of a pixel with a non-point geometry';
313        END IF;
314        x := st_x(pt);
315        y := st_y(pt);
316        RETURN st_value(rast, 
317                        band, 
318                        st_world2rastercoordx(rast, x, y), 
319                        st_world2rastercoordy(rast, x, y));
320    END;
321    $$
322    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
323
324CREATE OR REPLACE FUNCTION st_value(rast raster, pt geometry)
325    RETURNS float8
326    AS $$ SELECT st_value($1, 1, $2) $$
327    LANGUAGE SQL;
328
329-----------------------------------------------------------------------
330-- Raster Accessors ST_Georeference()
331-----------------------------------------------------------------------
332
333CREATE OR REPLACE FUNCTION st_georeference(rast raster, format text) 
334    RETURNS text AS
335    $$
336    DECLARE
337        x numeric;
338        result text;
339    BEGIN
340            x := st_pixelsizex(rast)::numeric;
341            result := trunc(x, 10) || E'\n';
342
343            x := st_skewy(rast)::numeric;
344            result := result || trunc(x, 10) || E'\n';
345
346            x := st_skewx(rast)::numeric;
347            result := result || trunc(x, 10) || E'\n';
348
349            x := st_pixelsizey(rast)::numeric;
350            result := result || trunc(x, 10) || E'\n';
351
352        IF format = 'ESRI' THEN
353            x := (st_upperleftx(rast) + st_pixelsizex(rast)*0.5)::numeric;
354            result := result || trunc(x, 10) || E'\n';
355
356            x := (st_upperlefty(rast) + st_pixelsizey(rast)*0.5)::numeric;
357            result = result || trunc(x, 10) || E'\n';
358        ELSE -- IF format = 'GDAL' THEN
359            x := st_upperleftx(rast)::numeric;
360            result := result || trunc(x, 10) || E'\n';
361
362            x := st_upperlefty(rast)::numeric;
363            result := result || trunc(x, 10) || E'\n';
364        END IF;
365
366        RETURN result;
367    END;
368    $$
369    LANGUAGE 'plpgsql' IMMUTABLE STRICT; -- WITH (isstrict);
370
371CREATE OR REPLACE FUNCTION st_georeference(raster)
372    RETURNS text
373    AS $$ select st_georeference($1,'GDAL') $$
374    LANGUAGE 'SQL' IMMUTABLE STRICT;
375   
376-----------------------------------------------------------------------
377-- Raster Editors
378-----------------------------------------------------------------------
379
380CREATE OR REPLACE FUNCTION st_setpixelsize(rast raster, pixelsize float8)
381    RETURNS raster
382    AS 'MODULE_PATHNAME','RASTER_setPixelSize'
383    LANGUAGE 'C' IMMUTABLE STRICT;
384
385CREATE OR REPLACE FUNCTION st_setpixelsize(rast raster, pixelsizex float8, pixelsizey float8)
386    RETURNS raster
387    AS 'MODULE_PATHNAME','RASTER_setPixelSizeXY'
388    LANGUAGE 'C' IMMUTABLE STRICT;
389
390CREATE OR REPLACE FUNCTION st_setskew(rast raster, skew float8)
391    RETURNS raster
392    AS 'MODULE_PATHNAME','RASTER_setSkew'
393    LANGUAGE 'C' IMMUTABLE STRICT;
394
395CREATE OR REPLACE FUNCTION st_setskew(rast raster, skewx float8, skewy float8)
396    RETURNS raster
397    AS 'MODULE_PATHNAME','RASTER_setSkewXY'
398    LANGUAGE 'C' IMMUTABLE STRICT;
399
400CREATE OR REPLACE FUNCTION st_setsrid(rast raster, srid integer)
401    RETURNS raster
402    AS 'MODULE_PATHNAME','RASTER_setSRID'
403    LANGUAGE 'C' IMMUTABLE STRICT;
404
405CREATE OR REPLACE FUNCTION st_setupperleft(rast raster, upperleftx float8, upperlefty float8)
406    RETURNS raster
407    AS 'MODULE_PATHNAME','RASTER_setUpperLeftXY'
408    LANGUAGE 'C' IMMUTABLE STRICT;
409
410-----------------------------------------------------------------------
411-- Raster Editors ST_SetGeoreference()
412-----------------------------------------------------------------------
413CREATE OR REPLACE FUNCTION st_setgeoreference(rast raster, georef text, format text) 
414    RETURNS raster AS
415    $$
416    DECLARE
417        params text[];
418        rastout raster;
419    BEGIN
420        IF rast IS NULL THEN
421            RAISE WARNING 'Cannot set georeferencing on a null raster in st_setgeoreference.';
422            RETURN rastout;
423        END IF;
424
425        SELECT regexp_matches(georef,
426            E'(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s' || 
427            E'(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)') INTO params;
428
429        IF NOT FOUND THEN
430            RAISE EXCEPTION 'st_setgeoreference requires a string with 6 floating point values.';
431        END IF;
432
433        IF format = 'ESRI' THEN
434            -- params array is now:
435            -- {pixelsizex, skewy, skewx, pixelsizey, upperleftx, upperlefty}
436            rastout := st_setpixelsize(rast, params[1]::float8, params[4]::float8);
437            rastout := st_setskew(rastout, params[3]::float8, params[2]::float8);
438            rastout := st_setupperleft(rastout, 
439                                   params[5]::float8 - (params[1]::float8 * 0.5),
440                                   params[6]::float8 - (params[4]::float8 * 0.5));
441        ELSE
442            IF format != 'GDAL' THEN
443                RAISE WARNING E'Format \'%\' is not recognized, defaulting to GDAL format.', format;
444            END IF;
445            -- params array is now:
446            -- {pixelsizex, skewy, skewx, pixelsizey, upperleftx, upperlefty}
447
448            rastout := st_setpixelsize(rast, params[1]::float8, params[4]::float8);
449            rastout := st_setskew( rastout, params[3]::float8, params[2]::float8);
450            rastout := st_setupperleft(rastout, params[5]::float8, params[6]::float8);
451        END IF;
452        RETURN rastout;
453    END;
454    $$
455    LANGUAGE 'plpgsql' IMMUTABLE STRICT; -- WITH (isstrict);
456
457CREATE OR REPLACE FUNCTION st_setgeoreference(rast raster, georef text)
458    RETURNS raster AS
459    $$
460        SELECT st_setgeoreference($1, $2, 'GDAL');
461    $$
462    LANGUAGE 'SQL' IMMUTABLE STRICT; -- WITH (isstrict);
463
464-----------------------------------------------------------------------
465-- Raster Band Editors
466-----------------------------------------------------------------------
467
468CREATE OR REPLACE FUNCTION st_setbandhasnodatavalue(rast raster, band integer, hasnodatavalue boolean)
469    RETURNS raster
470    AS 'MODULE_PATHNAME','RASTER_setBandHasNoDataValue'
471    LANGUAGE 'C' IMMUTABLE STRICT;
472
473CREATE OR REPLACE FUNCTION st_setbandhasnodatavalue(rast raster, hasnodatavalue boolean)
474    RETURNS raster
475    AS $$ SELECT st_setbandhasnodatavalue($1, 1, $2) $$
476    LANGUAGE SQL;
477
478CREATE OR REPLACE FUNCTION st_setbandnodatavalue(rast raster, band integer, nodatavalue float8)
479    RETURNS raster
480    AS 'MODULE_PATHNAME','RASTER_setBandNoDataValue'
481    LANGUAGE 'C' IMMUTABLE STRICT;
482
483CREATE OR REPLACE FUNCTION st_setbandnodatavalue(rast raster, nodatavalue float8)
484    RETURNS raster
485    AS $$ SELECT st_setbandnodatavalue($1, 1, $2) $$
486    LANGUAGE SQL;
487   
488-----------------------------------------------------------------------
489-- Raster Pixel Editors
490-----------------------------------------------------------------------
491
492CREATE OR REPLACE FUNCTION st_setvalue(rast raster, band integer, x integer, y integer, newvalue float8)
493    RETURNS raster
494    AS 'MODULE_PATHNAME','RASTER_setPixelValue'
495    LANGUAGE 'C' IMMUTABLE STRICT;
496
497CREATE OR REPLACE FUNCTION st_setvalue(rast raster, x integer, y integer, newvalue float8)
498    RETURNS raster
499    AS $$ SELECT st_setvalue($1, 1, $2, $3, $4) $$
500    LANGUAGE SQL;
501   
502CREATE OR REPLACE FUNCTION st_setvalue(rast raster, band integer, pt geometry, newvalue float8) 
503    RETURNS raster AS
504    $$
505    DECLARE
506        x float8;
507        y float8;
508        gtype text;
509    BEGIN
510        gtype := st_geometrytype(pt);
511        IF ( gtype != 'ST_Point' ) THEN
512            RAISE EXCEPTION 'Attempting to get the value of a pixel with a non-point geometry';
513        END IF;
514        x := st_x(pt);
515        y := st_y(pt);
516        RETURN st_setvalue(rast, 
517                           band, 
518                           st_world2rastercoordx(rast, x, y), 
519                           st_world2rastercoordy(rast, x, y),
520                           newvalue);
521    END;
522    $$
523    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
524
525CREATE OR REPLACE FUNCTION st_setvalue(rast raster, pt geometry, newvalue float8)
526    RETURNS raster
527    AS $$ SELECT st_setvalue($1, 1, $2, $3) $$
528    LANGUAGE SQL;
529   
530
531-----------------------------------------------------------------------
532-- Raster Processing Functions
533-----------------------------------------------------------------------
534
535CREATE TYPE geomval AS (
536    geom geometry,
537    val double precision
538);
539
540CREATE TYPE wktgeomval AS (
541    wktgeom text,
542    val double precision,
543    srid int 
544);
545
546CREATE OR REPLACE FUNCTION dumpaswktpolygons(rast raster, band integer)
547    RETURNS SETOF wktgeomval
548    AS 'MODULE_PATHNAME','RASTER_dumpAsWKTPolygons'
549    LANGUAGE 'C' IMMUTABLE STRICT;
550
551CREATE OR REPLACE FUNCTION st_dumpaspolygons(rast raster, band integer) 
552    RETURNS SETOF geomval AS
553    $$
554    SELECT st_geomfromtext(wktgeomval.wktgeom, wktgeomval.srid), wktgeomval.val
555    FROM dumpaswktpolygons($1, $2) AS wktgeomval;
556    $$ 
557    LANGUAGE 'SQL' IMMUTABLE STRICT;
558
559CREATE OR REPLACE FUNCTION st_dumpaspolygons(raster) 
560    RETURNS SETOF geomval AS
561    $$
562    SELECT st_geomfromtext(wktgeomval.wktgeom, wktgeomval.srid), wktgeomval.val
563    FROM dumpaswktpolygons($1, 1) AS wktgeomval;
564    $$ 
565    LANGUAGE 'SQL' IMMUTABLE STRICT;
566
567CREATE OR REPLACE FUNCTION st_polygon(rast raster, band integer) 
568    RETURNS geometry AS
569    $$
570    SELECT st_union(f.geom) AS singlegeom
571    FROM (SELECT (st_dumpaspolygons($1, $2)).geom AS geom) AS f;
572    $$ 
573    LANGUAGE 'SQL' IMMUTABLE STRICT;
574
575CREATE OR REPLACE FUNCTION st_polygon(raster) 
576    RETURNS geometry AS
577    $$       
578    SELECT st_union(f.geom) AS singlegeom
579    FROM (SELECT (st_dumpaspolygons($1, 1)).geom AS geom) AS f;
580    $$ 
581    LANGUAGE 'SQL' IMMUTABLE STRICT;
582   
583CREATE OR REPLACE FUNCTION st_pixelaspolygon(rast raster, band integer, x integer, y integer) 
584    RETURNS geometry AS
585    $$
586    DECLARE
587        w integer;
588        h integer;
589        pxsizex float8;
590        pxsizey float8;
591        skewx float8;
592        skewy float8;
593        x1 float8;
594        y1 float8;
595        x2 float8;
596        y2 float8;
597        x3 float8;
598        y3 float8;
599        x4 float8;
600        y4 float8;
601    BEGIN
602        pxsizex := st_pixelsizex(rast);
603        skewx := st_skewy(rast);
604        skewy := st_skewx(rast);
605        pxsizey := st_pixelsizey(rast);
606        x1 := pxsizex * (x - 1) + skewx * (y - 1) + st_upperleftx(rast);
607        y1 := pxsizey * (y - 1) + skewy * (x - 1) + st_upperlefty(rast);
608        x2 := x1 + pxsizex;
609        y2 := y1 + skewy;
610        x3 := x1 + pxsizex + skewx;
611        y3 := y1 + pxsizey + skewy;
612        x4 := x1 + skewx;
613        y4 := y1 + pxsizey;
614        RETURN st_setsrid(st_makepolygon(st_makeline(ARRAY[st_makepoint(x1, y1), 
615                                                           st_makepoint(x2, y2), 
616                                                           st_makepoint(x3, y3), 
617                                                           st_makepoint(x4, y4), 
618                                                           st_makepoint(x1, y1)]
619                                                    )
620                                        ), 
621                          st_srid(rast)
622                         );
623    END;
624    $$
625    LANGUAGE 'plpgsql';
626
627CREATE FUNCTION st_pixelaspolygon(rast raster, x integer, y integer) 
628    RETURNS geometry AS
629    $$
630        SELECT st_pixelaspolygon($1, 1, $2, $3)
631    $$
632    LANGUAGE SQL;
633
634
635-----------------------------------------------------------------------
636-- Raster Utility Functions
637-----------------------------------------------------------------------
638
639---------------------------------------------------------------------------------
640-- ST_World2RasterCoordX(rast raster, xw float8, yw float8)
641-- Returns the column number of the pixel covering the provided X and Y world
642-- coordinates. 
643-- This function works even if the world coordinates are outside the raster extent. 
644---------------------------------------------------------------------------------
645CREATE OR REPLACE FUNCTION st_world2rastercoordx(rast raster, xw float8, yw float8) 
646    RETURNS int AS
647    $$
648    DECLARE
649        a float8 := 0.0;
650        b float8 := 0.0;
651        c float8 := 0.0;
652        d float8 := 0.0;
653        e float8 := 0.0;
654        f float8 := 0.0;
655        xr numeric := 0.0;
656    BEGIN
657        a := st_pixelsizex(rast); 
658        d := st_skewy(rast); 
659        b := st_skewx(rast); 
660        e := st_pixelsizey(rast); 
661        c := st_upperleftx(rast); 
662        f := st_upperlefty(rast);
663        IF ( b * d - a * e = 0 ) THEN
664            RAISE EXCEPTION 'Attempting to compute raster coordinate on a raster with pixel size equal to 0';
665        END IF;
666        xr := (b * (yw - f) - e * (xw - c)) / (b * d - a * e);
667        RETURN floor(xr) + 1;
668    END;
669    $$
670    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
671
672---------------------------------------------------------------------------------
673-- ST_World2RasteCoordX(rast raster, xw float8)
674-- Returns the column number of the pixels covering the provided world X coordinate
675-- for a non-rotated raster. 
676-- This function works even if the world coordinate is outside the raster extent. 
677-- This function returns an error if the raster is rotated. In this case you must
678-- also provide a Y.
679---------------------------------------------------------------------------------
680CREATE OR REPLACE FUNCTION st_world2rastercoordx(rast raster, xw float8) 
681    RETURNS int AS
682    $$
683    DECLARE
684        a float8 := 0.0;
685        b float8 := 0.0;
686        c float8 := 0.0;
687        d float8 := 0.0;
688        e float8 := 0.0;
689        f float8 := 0.0;
690        xr numeric := 0.0;
691    BEGIN
692        a := st_pixelsizex(rast); 
693        d := st_skewy(rast); 
694        b := st_skewx(rast); 
695        e := st_pixelsizey(rast); 
696        c := st_upperleftx(rast); 
697        f := st_upperlefty(rast);
698        IF ( b * d - a * e = 0 ) THEN
699            RAISE EXCEPTION 'Attempting to compute raster coordinate on a raster with pixel size equal to 0';
700        END IF;
701        IF ( b != 0 OR d != 0) THEN
702            RAISE EXCEPTION 'Attempting to compute raster coordinate on a raster with rotation providing x only. A y must also be provided';
703        END IF;
704        xr := (e * (xw - c)) / (a * e);
705        RETURN floor(xr) + 1;
706    END;
707    $$
708    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
709
710---------------------------------------------------------------------------------
711-- ST_World2RasterCoordX(rast raster, pt geometry)
712-- Returns the column number of the pixel covering the provided point geometry.
713-- This function works even if the point is outside the raster extent.
714---------------------------------------------------------------------------------
715CREATE OR REPLACE FUNCTION st_world2rastercoordx(rast raster, pt geometry) 
716    RETURNS float8 AS
717    $$
718    DECLARE
719    BEGIN
720        IF ( st_geometrytype(pt) != 'ST_Point' ) THEN
721            RAISE EXCEPTION 'Attempting to compute raster coordinate with a non-point geometry';
722        END IF;
723        RETURN st_world2rastercoordx($1, st_x(pt), st_y(pt));
724    END;
725    $$
726    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
727   
728---------------------------------------------------------------------------------
729-- ST_World2RasterCoordY(rast raster, xw float8, yw float8)
730-- Returns the row number of the pixel covering the provided X and Y world
731-- coordinates. 
732-- This function works even if the world coordinates are outside the raster extent. 
733---------------------------------------------------------------------------------
734CREATE OR REPLACE FUNCTION st_world2rastercoordy(rast raster, xw float8, yw float8) 
735    RETURNS int AS
736    $$
737    DECLARE
738        a float8 := 0.0;
739        b float8 := 0.0;
740        c float8 := 0.0;
741        d float8 := 0.0;
742        e float8 := 0.0;
743        f float8 := 0.0;
744        yr numeric := 0.0;
745    BEGIN
746        a := st_pixelsizex(rast); 
747        d := st_skewy(rast); 
748        b := st_skewx(rast); 
749        e := st_pixelsizey(rast); 
750        c := st_upperleftx(rast); 
751        f := st_upperlefty(rast);
752        IF ( b * d - a * e = 0 ) THEN
753            RAISE EXCEPTION 'Attempting to compute raster coordinate on a raster with pixel size equal to 0';
754        END IF;
755        yr := (a * (yw - f) + d * (c - xw)) / (a * e - b * d);
756        RETURN floor(yr) + 1;
757    END;
758    $$
759    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
760
761---------------------------------------------------------------------------------
762-- ST_World2RasterCoordY(rast raster, yw float8)
763-- Returns the row number of the pixels covering the provided world Y coordinate
764-- for a non-rotated raster. 
765-- This function works even if the world coordinate is outside the raster extent. 
766-- This function returns an error if the raster is rotated. In this case you must
767-- also provide an X.
768---------------------------------------------------------------------------------
769CREATE OR REPLACE FUNCTION st_world2rastercoordy(rast raster, yw float8) 
770    RETURNS int AS
771    $$
772    DECLARE
773        a float8 := 0.0;
774        b float8 := 0.0;
775        c float8 := 0.0;
776        d float8 := 0.0;
777        e float8 := 0.0;
778        f float8 := 0.0;
779        yr numeric := 0.0;
780    BEGIN
781        a := st_pixelsizex(rast); 
782        d := st_skewy(rast); 
783        b := st_skewx(rast); 
784        e := st_pixelsizey(rast); 
785        c := st_upperleftx(rast); 
786        f := st_upperlefty(rast);
787        IF ( b * d - a * e = 0 ) THEN
788            RAISE EXCEPTION 'Attempting to compute raster coordinate on a raster with pixel size equal to 0';
789        END IF;
790        IF ( b != 0 OR d != 0) THEN
791            RAISE EXCEPTION 'Attempting to compute raster coordinate on a raster with rotation providing y only. A x must also be provided';
792        END IF;
793        yr := (a * (yw - f)) / (a * e);
794        RETURN floor(yr) + 1;
795    END;
796    $$
797    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
798
799---------------------------------------------------------------------------------
800-- ST_World2RasterCoordY(rast raster, pt geometry)
801-- Returns the row number of the pixel covering the provided point geometry.
802-- This function works even if the point is outside the raster extent.
803---------------------------------------------------------------------------------
804CREATE OR REPLACE FUNCTION st_world2rastercoordy(rast raster, pt geometry) 
805    RETURNS float8 AS
806    $$
807    DECLARE
808    BEGIN
809        IF ( st_geometrytype(pt) != 'ST_Point' ) THEN
810            RAISE EXCEPTION 'Attempting to compute raster coordinate with a non-point geometry';
811        END IF;
812        RETURN st_world2rastercoordy($1, st_x(pt), st_y(pt));
813    END;
814    $$
815    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
816
817---------------------------------------------------------------------------------
818-- ST_Raster2WorldCoordX(rast raster, xr int, yr int)
819-- Returns the X world coordinate of the upper left corner of the pixel located at
820-- the provided column and row numbers.
821-- This function works even if the provided raster column and row are beyond or
822-- below the raster width and height.
823---------------------------------------------------------------------------------
824CREATE OR REPLACE FUNCTION st_raster2worldcoordx(rast raster, xr int, yr int)
825    RETURNS float8 AS
826    $$
827    DECLARE
828        a float8 := 0.0;
829        b float8 := 0.0;
830        c float8 := 0.0;
831        xw numeric := 0.0;
832    BEGIN
833        a := st_pixelsizex(rast); 
834        b := st_skewx(rast);
835        c := st_upperleftx(rast);
836        xw := (a::numeric * (xr::numeric - 1.0) + b::numeric * (yr::numeric - 1.0) + c::numeric)::numeric;
837        RETURN xw;
838    END;
839    $$
840    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
841
842---------------------------------------------------------------------------------
843-- ST_Raster2WorldCoordX(rast raster, xr int)
844-- Returns the X world coordinate of the upper left corner of the pixel located at
845-- the provided column number for a non-rotated raster.
846-- This function works even if the provided raster column is beyond or below the
847-- raster width.
848-- This function returns an error if the raster is rotated. In this case you must
849-- also provide a Y.
850---------------------------------------------------------------------------------
851CREATE OR REPLACE FUNCTION st_raster2worldcoordx(rast raster, xr int) 
852    RETURNS float8 AS
853    $$
854    DECLARE
855        a float8 := 0.0;
856        b float8 := 0.0;
857        c float8 := 0.0;
858        xw numeric := 0.0;
859    BEGIN
860        a := st_pixelsizex(rast); 
861        b := st_skewx(rast);
862        c := st_upperleftx(rast);
863        IF ( b != 0 ) THEN
864            RAISE EXCEPTION 'Attempting to compute raster coordinates on a raster with rotation providing X only. A Y coordinate must also be provided';
865        END IF;
866        xw := (a::numeric * (xr::numeric - 1.0) + c::numeric)::numeric;
867        RETURN xw;
868    END;
869    $$
870    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
871
872---------------------------------------------------------------------------------
873-- ST_Raster2WorldCoordY(rast raster, xr int, yr int)
874-- Returns the Y world coordinate of the upper left corner of the pixel located at
875-- the provided column and row numbers.
876-- This function works even if the provided raster column and row are beyond or
877-- below the raster width and height.
878---------------------------------------------------------------------------------
879CREATE OR REPLACE FUNCTION st_raster2worldcoordy(rast raster, xr int, yr int) 
880    RETURNS float8 AS
881    $$
882    DECLARE
883        d float8 := 0.0;
884        e float8 := 0.0;
885        f float8 := 0.0;
886        yw numeric := 0.0;
887    BEGIN
888        d := st_skewy(rast);
889        e := st_pixelsizey(rast); 
890        f := st_upperlefty(rast);
891        yw := (d::numeric * (xr::numeric - 1.0) + e::numeric * (yr::numeric - 1.0) + f::numeric)::numeric;
892        RETURN yw;
893    END;
894    $$
895    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
896
897---------------------------------------------------------------------------------
898-- ST_Raster2WorldCoordY(rast raster, yr int)
899-- Returns the Y world coordinate of the upper left corner of the pixel located at
900-- the provided row number for a non-rotated raster.
901-- This function works even if the provided raster row is beyond or below the
902-- raster height.
903-- This function returns an error if the raster is rotated. In this case you must
904-- also provide an X.
905---------------------------------------------------------------------------------
906CREATE OR REPLACE FUNCTION st_raster2worldcoordy(rast raster, yr int) 
907    RETURNS float8 AS
908    $$
909    DECLARE
910        d float8 := 0.0;
911        e float8 := 0.0;
912        f float8 := 0.0;
913        yw numeric := 0.0;
914    BEGIN
915        d := st_skewy(rast);
916        e := st_pixelsizey(rast); 
917        f := st_upperlefty(rast);
918        IF ( d != 0 ) THEN
919            RAISE EXCEPTION 'Attempting to compute raster coordinates on a raster with rotation providing Y only. An X coordinate must also be provided';
920        END IF;
921        yw := (e::numeric * (yr::numeric - 1.0) + f::numeric)::numeric;
922        RETURN yw;
923    END;
924    $$
925    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
926
927
928-----------------------------------------------------------------------
929-- Raster Outputs
930-----------------------------------------------------------------------
931
932CREATE OR REPLACE FUNCTION st_asbinary(raster)
933    RETURNS bytea
934    AS 'MODULE_PATHNAME', 'RASTER_to_binary'
935    LANGUAGE 'C' IMMUTABLE;
936
937CREATE OR REPLACE FUNCTION st_bytea(raster)
938    RETURNS bytea
939    AS 'MODULE_PATHNAME', 'RASTER_to_bytea'
940    LANGUAGE 'C' IMMUTABLE;
941
942------------------------------------------------------------------------------
943--  Casts
944------------------------------------------------------------------------------
945
946CREATE CAST (raster AS box2d)
947    WITH FUNCTION st_box2d(raster) AS IMPLICIT;
948
949CREATE CAST (raster AS geometry)
950    WITH FUNCTION st_convexhull(raster) AS IMPLICIT;
951
952CREATE CAST (raster AS bytea)
953    WITH FUNCTION st_bytea(raster) AS IMPLICIT;
954
955------------------------------------------------------------------------------
956--  GiST index OPERATOR support functions
957------------------------------------------------------------------------------
958
959CREATE OR REPLACE FUNCTION st_overleft(raster, raster)
960    RETURNS bool
961    AS 'select geometry_overleft($1::geometry, $2::geometry)'
962    LANGUAGE 'SQL' IMMUTABLE STRICT;
963
964CREATE OR REPLACE FUNCTION st_overright(raster, raster)
965    RETURNS bool
966    AS 'select geometry_overright($1::geometry, $2::geometry)'
967    LANGUAGE 'SQL' IMMUTABLE STRICT;
968
969CREATE OR REPLACE FUNCTION st_left(raster, raster)
970    RETURNS bool
971    AS 'select geometry_left($1::geometry, $2::geometry)'
972    LANGUAGE 'SQL' IMMUTABLE STRICT;
973
974CREATE OR REPLACE FUNCTION st_right(raster, raster)
975    RETURNS bool
976    AS 'select geometry_right($1::geometry, $2::geometry)'
977    LANGUAGE 'SQL' IMMUTABLE STRICT;
978
979CREATE OR REPLACE FUNCTION st_overabove(raster, raster)
980    RETURNS bool
981    AS 'select geometry_overabove($1::geometry, $2::geometry)'
982    LANGUAGE 'SQL' IMMUTABLE STRICT;
983
984CREATE OR REPLACE FUNCTION st_overbelow(raster, raster)
985    RETURNS bool
986    AS 'select geometry_overbelow($1::geometry, $2::geometry)'
987    LANGUAGE 'SQL' IMMUTABLE STRICT;
988
989CREATE OR REPLACE FUNCTION st_above(raster, raster)
990    RETURNS bool
991    AS 'select geometry_above($1::geometry, $2::geometry)'
992    LANGUAGE 'SQL' IMMUTABLE STRICT;
993
994CREATE OR REPLACE FUNCTION st_below(raster, raster)
995    RETURNS bool
996    AS 'select geometry_below($1::geometry, $2::geometry)'
997    LANGUAGE 'SQL' IMMUTABLE STRICT;
998
999CREATE OR REPLACE FUNCTION st_same(raster, raster)
1000    RETURNS bool
1001    AS 'select geometry_same($1::geometry, $2::geometry)'
1002    LANGUAGE 'SQL' IMMUTABLE STRICT;
1003
1004CREATE OR REPLACE FUNCTION st_contained(raster, raster)
1005    RETURNS bool
1006    AS 'select geometry_contained($1::geometry, $2::geometry)'
1007    LANGUAGE 'SQL' IMMUTABLE STRICT;
1008
1009CREATE OR REPLACE FUNCTION st_contain(raster, raster)
1010    RETURNS bool
1011    AS 'select geometry_contain($1::geometry, $2::geometry)'
1012    LANGUAGE 'SQL' IMMUTABLE STRICT;
1013
1014CREATE OR REPLACE FUNCTION st_overlap(raster, raster)
1015    RETURNS bool
1016    AS 'select geometry_overlap($1::geometry, $2::geometry)'
1017    LANGUAGE 'SQL' IMMUTABLE STRICT;
1018
1019------------------------------------------------------------------------------
1020--  GiST index OPERATORs
1021------------------------------------------------------------------------------
1022
1023CREATE OPERATOR << (
1024    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_left,
1025    COMMUTATOR = '>>',
1026    RESTRICT = positionsel, JOIN = positionjoinsel
1027    );
1028
1029CREATE OPERATOR &< (
1030    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_overleft,
1031    COMMUTATOR = '&>',
1032    RESTRICT = positionsel, JOIN = positionjoinsel
1033    );
1034
1035CREATE OPERATOR <<| (
1036    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_below,
1037    COMMUTATOR = '|>>',
1038    RESTRICT = positionsel, JOIN = positionjoinsel
1039    );
1040
1041CREATE OPERATOR &<| (
1042    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_overbelow,
1043    COMMUTATOR = '|&>',
1044    RESTRICT = positionsel, JOIN = positionjoinsel
1045    );
1046
1047CREATE OPERATOR && (
1048    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_overlap,
1049    COMMUTATOR = '&&',
1050    RESTRICT = postgis_gist_sel, JOIN = postgis_gist_joinsel
1051    );
1052
1053CREATE OPERATOR &> (
1054    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_overright,
1055    COMMUTATOR = '&<',
1056    RESTRICT = positionsel, JOIN = positionjoinsel
1057    );
1058
1059CREATE OPERATOR >> (
1060    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_right,
1061    COMMUTATOR = '<<',
1062    RESTRICT = positionsel, JOIN = positionjoinsel
1063    );
1064
1065CREATE OPERATOR |&> (
1066    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_overabove,
1067    COMMUTATOR = '&<|',
1068    RESTRICT = positionsel, JOIN = positionjoinsel
1069    );
1070
1071CREATE OPERATOR |>> (
1072    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_above,
1073    COMMUTATOR = '<<|',
1074    RESTRICT = positionsel, JOIN = positionjoinsel
1075    );
1076
1077CREATE OPERATOR ~= (
1078    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_same,
1079    COMMUTATOR = '~=',
1080    RESTRICT = eqsel, JOIN = eqjoinsel
1081    );
1082
1083CREATE OPERATOR @ (
1084    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_contained,
1085    COMMUTATOR = '~',
1086    RESTRICT = contsel, JOIN = contjoinsel
1087    );
1088
1089CREATE OPERATOR ~ (
1090    LEFTARG = raster, RIGHTARG = raster, PROCEDURE = st_contain,
1091    COMMUTATOR = '@',
1092    RESTRICT = contsel, JOIN = contjoinsel
1093    );
1094
1095-----------------------------------------------------------------------
1096-- Raster/Geometry Spatial Relationship
1097-----------------------------------------------------------------------
1098
1099-----------------------------------------------------------------------
1100-- _st_intersects(geomin geometry, rast raster, band integer)
1101-- Check for the presence of withvalue pixels in the area shared by the
1102-- raster and the geometry. If only nodatavalue pixel are found, the
1103-- geometry does not intersect with the raster.
1104-----------------------------------------------------------------------
1105CREATE OR REPLACE FUNCTION _st_intersects(geomin geometry, rast raster, band integer) 
1106    RETURNS boolean AS
1107    $$
1108    DECLARE
1109        nodata float8 := 0.0;
1110        geomintersect geometry;
1111        x1w double precision := 0.0;
1112        x2w double precision := 0.0;
1113        y1w double precision := 0.0;
1114        y2w double precision := 0.0;
1115        x1 integer := 0;
1116        x2 integer := 0;
1117        x3 integer := 0;
1118        x4 integer := 0;
1119        y1 integer := 0;
1120        y2 integer := 0;
1121        y3 integer := 0;
1122        y4 integer := 0;
1123        x integer := 0;
1124        y integer := 0;
1125        xinc integer := 0;
1126        yinc integer := 0;
1127        pixelval double precision;
1128        bintersect boolean := FALSE;
1129        gtype text;
1130        psize float8;
1131    BEGIN
1132   
1133        -- Get the intersection between with the geometry. 
1134        -- We will search for withvalue pixel only in this area.
1135        geomintersect := st_intersection(geomin, st_convexhull(rast));
1136       
1137--RAISE NOTICE 'geomintersect1=%', astext(geomintersect);
1138
1139        -- If the intersection is empty, return false
1140        IF st_isempty(geomintersect) THEN
1141            RETURN FALSE;
1142        END IF;
1143
1144        -- If the band does not have a nodatavalue, there is no need to search for with value pixels
1145        IF NOT st_bandhasnodatavalue(rast, band) THEN
1146            RETURN TRUE;
1147        END IF;
1148
1149        -- We create a minimalistic buffer around the intersection in order to scan every pixels
1150        -- that would touch the edge or intersect with the geometry
1151        psize := st_pixelsizex(rast) + st_skewy(rast);
1152        geomintersect := st_buffer(geomintersect, psize / 1000000);
1153
1154--RAISE NOTICE 'geomintersect2=%', astext(geomintersect);
1155       
1156        -- Find the world coordinates of the bounding box of the intersecting area
1157        x1w := st_xmin(geomintersect);
1158        y1w := st_ymin(geomintersect);
1159        x2w := st_xmax(geomintersect);
1160        y2w := st_ymax(geomintersect); 
1161        nodata := st_bandnodatavalue(rast, band);
1162
1163--RAISE NOTICE 'x1w=%, y1w=%, x2w=%, y2w=%', x1w, y1w, x2w, y2w;
1164
1165        -- Convert world coordinates to raster coordinates
1166        x1 := st_world2rastercoordx(rast, x1w, y1w);
1167        y1 := st_world2rastercoordy(rast, x1w, y1w);
1168        x2 := st_world2rastercoordx(rast, x2w, y1w);
1169        y2 := st_world2rastercoordy(rast, x2w, y1w);
1170        x3 := st_world2rastercoordx(rast, x1w, y2w);
1171        y3 := st_world2rastercoordy(rast, x1w, y2w);
1172        x4 := st_world2rastercoordx(rast, x2w, y2w);
1173        y4 := st_world2rastercoordy(rast, x2w, y2w);
1174
1175--RAISE NOTICE 'x1=%, y1=%, x2=%, y2=%, x3=%, y3=%, x4=%, y4=%', x1, y1, x2, y2, x3, y3, x4, y4;
1176
1177        -- Order the raster coordinates for the upcoming FOR loop.
1178        x1 := int4smaller(int4smaller(int4smaller(x1, x2), x3), x4);
1179        y1 := int4smaller(int4smaller(int4smaller(y1, y2), y3), y4);
1180        x2 := int4larger(int4larger(int4larger(x1, x2), x3), x4);
1181        y2 := int4larger(int4larger(int4larger(y1, y2), y3), y4);
1182
1183        -- Make sure the range is not lower than 1. 
1184        -- This can happen when world coordinate are exactly on the left border
1185        -- of the raster and that they do not span on more than one pixel.
1186        x1 := int4smaller(int4larger(x1, 1), st_width(rast));
1187        y1 := int4smaller(int4larger(y1, 1), st_height(rast));
1188
1189        -- Also make sure the range does not exceed the width and height of the raster. 
1190        -- This can happen when world coordinate are exactly on the lower right border
1191        -- of the raster.
1192        x2 := int4smaller(x2, st_width(rast));
1193        y2 := int4smaller(y2, st_height(rast));
1194
1195--RAISE NOTICE 'x1=%, y1=%, x2=%, y2=%', x1, y1, x2, y2;
1196
1197        -- Search exhaustively for withvalue pixel on a moving 3x3 grid
1198        -- (very often more efficient than searching on a mere 1x1 grid)
1199        FOR xinc in 0..2 LOOP
1200            FOR yinc in 0..2 LOOP
1201                FOR x IN x1+xinc..x2 BY 3 LOOP
1202                    FOR y IN y1+yinc..y2 BY 3 LOOP
1203                        -- Check first if the pixel intersects with the geometry. Often many won't.
1204                        bintersect := NOT st_isempty(st_intersection(st_pixelaspolygon(rast, band, x, y), geomin));
1205
1206                        IF bintersect THEN
1207                            -- If the pixel really intersects, check its value. Return TRUE if with value.
1208                            pixelval := st_value(rast, band, x, y);
1209                            IF pixelval != nodata THEN
1210                                RETURN TRUE;
1211                            END IF;
1212                        END IF;
1213                    END LOOP;
1214                END LOOP;
1215            END LOOP;
1216        END LOOP;
1217        RETURN FALSE;
1218    END;
1219    $$
1220    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
1221
1222
1223CREATE OR REPLACE FUNCTION st_intersects(geometry, raster, integer) 
1224    RETURNS boolean AS
1225    $$ SELECT $1 && $2 AND _st_intersects($1, $2, $3);
1226    $$ LANGUAGE 'SQL' IMMUTABLE STRICT;
1227
1228CREATE OR REPLACE FUNCTION st_intersects(raster, integer, geometry) 
1229    RETURNS boolean AS
1230    $$ SELECT st_intersects($3, $1, $2);
1231    $$ LANGUAGE 'SQL' IMMUTABLE STRICT;
1232
1233CREATE OR REPLACE FUNCTION st_intersects(geometry, raster) 
1234    RETURNS boolean AS
1235    $$ SELECT st_intersects($1, $2, 1);
1236    $$ LANGUAGE 'SQL' IMMUTABLE STRICT;
1237
1238CREATE OR REPLACE FUNCTION st_intersects(raster, geometry) 
1239    RETURNS boolean AS
1240    $$ SELECT st_intersects($2, $1, 1);
1241    $$ LANGUAGE 'SQL' IMMUTABLE STRICT;
1242
1243-----------------------------------------------------------------------
1244-- _st_intersection(geom geometry, rast raster, band integer)
1245-- Returns a geometry set that represents the shared portion of the
1246-- provided geometry and the geometries produced by the vectorization of rast. 
1247-- Return an empty geometry if the geometry does not intersect with the
1248-- raster.
1249-- Raster nodata value areas are not vectorized and hence do not intersect
1250-- with any geometries.
1251-----------------------------------------------------------------------
1252CREATE OR REPLACE FUNCTION ST_Intersection(geomin geometry, rast raster, band integer)
1253  RETURNS SETOF geomval AS
1254$BODY$
1255    DECLARE
1256        intersects boolean := FALSE;
1257    BEGIN
1258        intersects := ST_Intersects(geomin, rast, band);
1259        IF intersects THEN
1260            -- Return the intersections of the geometry with the vectorized parts of
1261            -- the raster and the values associated with those parts, if really their
1262            -- intersection is not empty.
1263            RETURN QUERY
1264                SELECT intgeom, 
1265                       val
1266                FROM (SELECT ST_Intersection((gv).geom, geomin) AS intgeom, 
1267                             (gv).val
1268                      FROM ST_DumpAsPolygons(rast, band) gv
1269                      WHERE ST_Intersects((gv).geom, geomin)
1270                     ) foo
1271                WHERE NOT ST_IsEmpty(intgeom);
1272        ELSE
1273            -- If the geometry does not intersect with the raster, return an empty
1274            -- geometry and a null value
1275            RETURN QUERY
1276                SELECT emptygeom, 
1277                       NULL::float8
1278                FROM ST_GeomCollFromText('GEOMETRYCOLLECTION EMPTY', ST_SRID($1)) emptygeom;
1279        END IF;
1280    END;
1281    $BODY$
1282  LANGUAGE 'plpgsql' IMMUTABLE STRICT;
1283
1284CREATE OR REPLACE FUNCTION st_intersection(rast raster, geom geometry) 
1285    RETURNS SETOF geomval AS
1286    $$
1287        SELECT (gv).geom, (gv).val FROM st_intersection($2, $1, 1) gv;
1288    $$   
1289    LANGUAGE SQL IMMUTABLE STRICT;
1290
1291CREATE OR REPLACE FUNCTION st_intersection(geom geometry, rast raster) 
1292    RETURNS SETOF geomval AS
1293    $$
1294        SELECT (gv).geom, (gv).val FROM st_intersection($1, $2, 1) gv;
1295    $$   
1296    LANGUAGE SQL IMMUTABLE STRICT;
1297
1298CREATE OR REPLACE FUNCTION st_intersection(rast raster, band integer, geom geometry) 
1299    RETURNS SETOF geomval AS
1300    $$
1301        SELECT (gv).geom, (gv).val FROM st_intersection($3, $1, $2) gv;
1302    $$   
1303    LANGUAGE SQL IMMUTABLE STRICT;
1304
1305------------------------------------------------------------------------------
1306-- RASTER_COLUMNS
1307--
1308-- The metadata is documented in the WKT Raster specification:
1309-- http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationFinal01
1310------------------------------------------------------------------------------
1311CREATE TABLE raster_columns (
1312    r_table_catalog varchar(256) not null,
1313    r_table_schema varchar(256) not null,
1314    r_table_name varchar(256) not null,
1315    r_column varchar(256) not null,
1316    srid integer not null,
1317    pixel_types varchar[] not null,
1318    out_db boolean not null,
1319    regular_blocking boolean not null,
1320    nodata_values double precision[],
1321    pixelsize_x double precision,
1322    pixelsize_y double precision,
1323    blocksize_x integer,
1324    blocksize_y integer,
1325    extent geometry,
1326
1327    CONSTRAINT raster_columns_pk PRIMARY KEY (
1328        r_table_catalog,
1329        r_table_schema,
1330        r_table_name,
1331        r_column )
1332    ) WITH OIDS;
1333
1334
1335------------------------------------------------------------------------------
1336-- RASTER_OVERVIEWS
1337------------------------------------------------------------------------------
1338CREATE TABLE raster_overviews (
1339    o_table_catalog character varying(256) NOT NULL,
1340    o_table_schema character varying(256) NOT NULL,
1341    o_table_name character varying(256) NOT NULL,
1342    o_column character varying(256) NOT NULL,
1343    r_table_catalog character varying(256) NOT NULL,
1344    r_table_schema character varying(256) NOT NULL,
1345    r_table_name character varying(256) NOT NULL,
1346    r_column character varying(256) NOT NULL,
1347    out_db boolean NOT NULL,
1348    overview_factor integer NOT NULL,
1349
1350    CONSTRAINT raster_overviews_pk PRIMARY KEY (
1351      o_table_catalog,
1352      o_table_schema,
1353      o_table_name,
1354      o_column, overview_factor )
1355    ) WITH OIDS;
1356
1357
1358------------------------------------------------------------------------------
1359-- AddRasterColumn
1360-------------------------------------------------------------------------------
1361CREATE OR REPLACE FUNCTION AddRasterColumn(p_catalog_name varchar,
1362                                           p_schema_name varchar,
1363                                           p_table_name varchar,
1364                                           p_column_name varchar,
1365                                           p_srid integer,
1366                                           p_pixel_types varchar[],
1367                                           p_out_db boolean,
1368                                           p_regular_blocking boolean,
1369                                           p_nodata_values double precision[],
1370                                           p_pixelsize_x double precision,
1371                                           p_pixelsize_y double precision,
1372                                           p_blocksize_x integer,
1373                                           p_blocksize_y integer,
1374                                           p_extent geometry)
1375
1376    RETURNS text AS
1377    $$
1378    DECLARE
1379        sql text;
1380        real_schema name;
1381        srid_into varchar;
1382        geometry_op_into varchar;
1383        pixel_types varchar[];
1384        pixel_types_size integer := 0;
1385        pixel_types_found integer := 0;
1386        nodata_values_size integer := 0;
1387
1388    BEGIN
1389
1390        RAISE DEBUG 'Parameters: catalog=%, schema=%, table=%, column=%, srid=%, pixel_types=%, out_db=%, regular_blocking=%, nodata_values=%, pixelsize_x=%, pixelsize_y=%, blocksize_x=%, blocksize_y=%',
1391                     p_catalog_name, p_schema_name, p_table_name, p_column_name, p_srid, p_pixel_types, p_out_db, p_regular_blocking, p_nodata_values, p_pixelsize_x, p_pixelsize_y, p_blocksize_x, p_blocksize_y;
1392
1393        -- Validate required parametersa and combinations
1394        IF ( (p_catalog_name IS NULL) OR (p_schema_name IS NULL)
1395              OR (p_table_name IS NULL) OR (p_column_name IS NULL) ) THEN
1396            RAISE EXCEPTION 'Name of catalog, schema, table or column IS NULL, value expected';
1397            RETURN 'fail';
1398        END IF;
1399
1400        IF ( p_srid IS NULL ) THEN
1401            RAISE EXCEPTION 'SRID IS NULL, value expected';
1402            RETURN 'fail';
1403        END IF;
1404
1405        IF ( p_pixel_types IS NULL ) THEN
1406            RAISE EXCEPTION 'Array of pixel types IS NULL, value expected';
1407            RETURN 'fail';
1408        END IF;
1409
1410        IF ( p_out_db IS NULL ) THEN
1411            RAISE EXCEPTION 'out_db IS NULL, value expected';
1412            RETURN 'fail';
1413        END IF;
1414
1415        IF ( p_regular_blocking IS NULL ) THEN
1416            RAISE EXCEPTION 'regular_blocking IS NULL, value expected';
1417            RETURN 'fail';
1418        END IF;
1419
1420        IF ( p_regular_blocking = true ) THEN
1421           IF ( p_blocksize_x IS NULL or p_blocksize_y IS NULL ) THEN
1422               RAISE EXCEPTION 'blocksize_x/blocksize_y IS NULL, value expected if regular_blocking is TRUE';
1423               RETURN 'fail';
1424           END IF;
1425        ELSE
1426           IF ( p_blocksize_x IS NOT NULL or p_blocksize_y IS NOT NULL ) THEN
1427               RAISE EXCEPTION 'blocksize_x/blocksize_y values given, but regular_blocking is FALSE';
1428               RETURN 'fail';
1429           END IF;
1430        END IF;
1431
1432
1433        -- Verify SRID
1434        IF ( (p_srid != 0) AND (p_srid != -1) ) THEN
1435            SELECT SRID INTO srid_into FROM spatial_ref_sys WHERE SRID = p_srid;
1436            IF NOT FOUND THEN
1437                RAISE EXCEPTION 'Invalid SRID';
1438                RETURN 'fail';
1439            END IF;
1440            RAISE DEBUG 'Verified SRID = %', p_srid;
1441        END IF;
1442
1443
1444        -- Verify PIXEL TYPE
1445        -- TODO: If only PostgreSQL 8.2+ supported, use @> operator instead of brute-force lookup
1446        --       SELECT p_pixel_types <@ pixel_types INTO pixel_types_found_into; -- boolean
1447        pixel_types := ARRAY['1BB','2BUI','4BUI','8BSI','8BUI','16BSI','16BUI','32BSI','32BUI','16BF','32BF','64BF'];
1448
1449        FOR npti IN array_lower(p_pixel_types, 1) .. array_upper(p_pixel_types, 1) LOOP
1450
1451            pixel_types_found := 0;
1452            FOR pti IN array_lower(pixel_types, 1) .. array_upper(pixel_types, 1) LOOP
1453                IF p_pixel_types[npti] = pixel_types[pti] THEN
1454                    pixel_types_found := 1;
1455                    RAISE DEBUG 'Identified pixel type %', p_pixel_types[npti];
1456                END IF;
1457            END LOOP;
1458
1459            IF pixel_types_found = 0 THEN
1460                RAISE EXCEPTION 'Invalid pixel type % - valid ones are %', p_pixel_types[npti], pixel_types;
1461                RETURN 'fail';
1462            END IF;
1463
1464            pixel_types_size := pixel_types_size + 1;
1465        END LOOP;
1466
1467        -- Verify NODATA
1468        -- TODO: Validate if NODATA values matche range of corresponding pixel types
1469        nodata_values_size := 1 + array_upper(p_nodata_values, 1) - array_lower(p_nodata_values, 1);
1470        IF ( pixel_types_size != nodata_values_size ) THEN
1471            RAISE EXCEPTION 'Number of pixel types (%) and NODATA values (%) do not match',
1472                            pixel_types_size, nodata_values_size;
1473            RETURN 'fail';
1474        END IF;
1475
1476
1477        -- Verify extent geometry
1478        IF ( p_extent IS NOT NULL ) THEN
1479
1480            -- Verify POLYGON type
1481            SELECT GeometryType(p_extent) INTO geometry_op_into;
1482            IF ( NOT ( geometry_op_into = 'POLYGON' ) ) THEN
1483                RAISE EXCEPTION 'extent is of invalid type (%), expected simple and non-rotated POLYGON', geometry_op_into;
1484                RETURN 'fail';
1485            END IF;
1486
1487            -- Verify SRID
1488            SELECT ST_SRID(p_extent) INTO srid_into;
1489            IF ( p_srid != srid_into::integer ) THEN
1490                RAISE EXCEPTION 'SRID values for raster (%) and extent (%) do not match', p_srid, srid_into;
1491                RETURN 'fail';
1492            END IF;
1493        END IF;
1494
1495
1496        -- Verify regular_blocking
1497        IF ( p_regular_blocking = true ) THEN
1498
1499            IF ( p_blocksize_x IS NULL or p_blocksize_y IS NULL ) THEN
1500                RAISE EXCEPTION 'unexpected NULL for blocksize_x or blocksize_y';
1501                RETURN 'fail';
1502            END IF;
1503
1504            -- Verify extent is non-rotated rectangle
1505            IF ( p_extent IS NOT NULL ) THEN
1506                -- TODO: Replace with bounding box overlapping test (&&)
1507                SELECT ST_Equals(p_extent, ST_Envelope(p_extent)) INTO geometry_op_into;
1508                IF ( NOT ( geometry_op_into = 't' ) ) THEN
1509                    RAISE EXCEPTION 'extent does not represent non-rotated rectangle';
1510                    RETURN 'fail';
1511                END IF;
1512            END IF;
1513
1514            -- TODO: Set number of constraints on target table:
1515            --       - all tiles have the same size (blocksize_x and blocksize_y)
1516            --       - all tiles do not overlap
1517            --       - all tiles appear on the regular block grid
1518            --       - top left block start at the top left corner of the extent
1519        END IF;
1520
1521
1522
1523        -- Verify SCHEMA
1524        IF ( p_schema_name IS NOT NULL AND p_schema_name != '' ) THEN
1525            sql := 'SELECT nspname FROM pg_namespace '
1526                || 'WHERE text(nspname) = ' || quote_literal(p_schema_name)
1527                || 'LIMIT 1';
1528            RAISE DEBUG '%', sql;
1529            EXECUTE sql INTO real_schema;
1530
1531            IF ( real_schema IS NULL ) THEN
1532                RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(p_schema_name);
1533                RETURN 'fail';
1534            END IF;
1535        END IF;
1536
1537        IF ( real_schema IS NULL ) THEN
1538            RAISE DEBUG 'Detecting schema';
1539            sql := 'SELECT n.nspname AS schemaname '
1540                || 'FROM pg_catalog.pg_class c '
1541                || 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
1542                || 'WHERE c.relkind = ' || quote_literal('r')
1543                || ' AND n.nspname NOT IN ('
1544                || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')'
1545                || ' AND pg_catalog.pg_table_is_visible(c.oid)'
1546                || ' AND c.relname = ' || quote_literal(p_table_name);
1547            RAISE DEBUG '%', sql;
1548            EXECUTE sql INTO real_schema;
1549
1550            IF ( real_schema IS NULL ) THEN
1551                RAISE EXCEPTION 'Table % does not occur in the search_path',
1552                                quote_literal(p_table_name);
1553                RETURN 'fail';
1554            END IF;
1555        END IF;
1556
1557
1558        -- Add raster column to target table
1559        sql := 'ALTER TABLE '
1560            || quote_ident(real_schema) || '.' || quote_ident(p_table_name)
1561            || ' ADD COLUMN ' || quote_ident(p_column_name) ||  ' raster ';
1562        RAISE DEBUG '%', sql;
1563        EXECUTE sql;
1564
1565
1566        -- Delete stale record in RASTER_COLUMNS (if any)
1567        sql := 'DELETE FROM raster_columns '
1568            || ' WHERE r_table_catalog = ' || quote_literal('')
1569            || ' AND r_table_schema = ' || quote_literal(real_schema)
1570            || ' AND r_table_name = ' || quote_literal(p_table_name)
1571            || ' AND r_column = ' || quote_literal(p_column_name);
1572        RAISE DEBUG '%', sql;
1573        EXECUTE sql;
1574
1575
1576        -- Add record in RASTER_COLUMNS
1577        sql := 'INSERT INTO raster_columns '
1578            || '(r_table_catalog, r_table_schema, r_table_name, r_column, srid, '
1579            || 'pixel_types, out_db, regular_blocking, nodata_values, '
1580            || 'pixelsize_x, pixelsize_y, blocksize_x, blocksize_y, extent) '
1581            || 'VALUES ('
1582            || quote_literal('') || ','
1583            || quote_literal(real_schema) || ','
1584            || quote_literal(p_table_name) || ','
1585            || quote_literal(p_column_name) || ','
1586            || p_srid::text || ','
1587            || quote_literal(p_pixel_types::text) || ','
1588            || p_out_db::text || ','
1589            || p_regular_blocking::text || ','
1590            || COALESCE(quote_literal(p_nodata_values::text), 'NULL') || ','
1591            || COALESCE(quote_literal(p_pixelsize_x), 'NULL') || ','
1592            || COALESCE(quote_literal(p_pixelsize_y), 'NULL') || ','
1593            || COALESCE(quote_literal(p_blocksize_x), 'NULL') || ','
1594            || COALESCE(quote_literal(p_blocksize_y), 'NULL') || ','
1595            || COALESCE(quote_literal(p_extent::text), 'NULL') || ')';
1596        RAISE DEBUG '%', sql;
1597        EXECUTE sql;
1598
1599
1600        -- Add CHECK for SRID
1601        sql := 'ALTER TABLE '
1602            || quote_ident(real_schema) || '.' || quote_ident(p_table_name)
1603            || ' ADD CONSTRAINT '
1604            || quote_ident('enforce_srid_' || p_column_name)
1605            || ' CHECK (ST_SRID(' || quote_ident(p_column_name)
1606            || ') = ' || p_srid::text || ')';
1607        RAISE DEBUG '%', sql;
1608        EXECUTE sql;
1609
1610
1611        -- TODO: Add more CHECKs
1612        -- - Add CHECK for pixel types
1613        -- - Add CHECK for pixel size
1614        -- - Do we need CHECK for NODATA values?
1615
1616
1617        RETURN p_schema_name || '.' || p_table_name || '.' || p_column_name
1618            || ' srid:' || p_srid::text
1619            || ' pixel_types:' || p_pixel_types::text
1620            || ' out_db:' || p_out_db::text
1621            || ' regular_blocking:' || p_regular_blocking::text
1622            || ' nodata_values:' || COALESCE(quote_literal(p_nodata_values::text), 'NULL')
1623            || ' pixelsize_x:' || COALESCE(quote_literal(p_pixelsize_x), 'NULL')
1624            || ' pixelsize_y:' || COALESCE(quote_literal(p_pixelsize_y), 'NULL')
1625            || ' blocksize_x:' || COALESCE(quote_literal(p_blocksize_x), 'NULL')
1626            || ' blocksize_y:' || COALESCE(quote_literal(p_blocksize_y), 'NULL')
1627            || ' extent:' || COALESCE(ST_AsText(p_extent), 'NULL');
1628
1629    END;
1630    $$
1631    LANGUAGE 'plpgsql' VOLATILE;
1632
1633
1634------------------------------------------------------------------------------
1635-- AddRasterColumn (with default catalog)
1636-------------------------------------------------------------------------------
1637CREATE OR REPLACE FUNCTION AddRasterColumn(schema varchar, 
1638                                           p_table varchar, 
1639                                           p_column varchar, 
1640                                           p_srid integer, 
1641                                           p_pixel_types varchar[],
1642                                           p_out_db boolean, 
1643                                           p_regular_blocking boolean, 
1644                                           p_nodata_values double precision[],
1645                                           p_pixelsize_x double precision, 
1646                                           p_pixelsize_y double precision,
1647                                           p_blocksize_x integer, 
1648                                           p_blocksize_y integer, 
1649                                           p_extent geometry)
1650    RETURNS text AS
1651    $$
1652    DECLARE
1653        ret text;
1654    BEGIN
1655        SELECT AddRasterColumn('',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) INTO ret;
1656        RETURN ret;
1657    END;
1658    $$
1659    LANGUAGE 'plpgsql' STABLE;
1660
1661
1662------------------------------------------------------------------------------
1663-- AddRasterColumn (with default catalog and schema)
1664-------------------------------------------------------------------------------
1665CREATE OR REPLACE FUNCTION AddRasterColumn(p_table varchar, 
1666                                           p_column varchar, 
1667                                           p_srid integer, 
1668                                           p_pixel_types varchar[],
1669                                           p_out_db boolean, 
1670                                           p_regular_blocking boolean, 
1671                                           p_nodata_values double precision[],
1672                                           p_pixelsize_x double precision, 
1673                                           p_pixelsize_y double precision,
1674                                           p_blocksize_x integer, 
1675                                           p_blocksize_y integer, 
1676                                           p_extent geometry)
1677    RETURNS text AS
1678    $$
1679    DECLARE
1680        ret text;
1681    BEGIN
1682        SELECT AddRasterColumn('','',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) INTO ret;
1683        RETURN ret;
1684    END;
1685    $$
1686    LANGUAGE 'plpgsql' STABLE;
1687
1688
1689-------------------------------------------------------------------------------
1690-- DropRasterColumn
1691-------------------------------------------------------------------------------
1692-- FIXME: Use 'name' type for table,column and other names
1693-------------------------------------------------------------------------------
1694CREATE OR REPLACE FUNCTION DropRasterColumn(catalog_name varchar, 
1695                                            schema_name varchar,
1696                                            table_name varchar, 
1697                                            column_name varchar)
1698    RETURNS text AS
1699    $$
1700    DECLARE
1701        myrec record;
1702        real_schema name;
1703        okay boolean;
1704    BEGIN
1705        -- Find, check or fix schema_name
1706        IF ( schema_name != '' ) THEN
1707            okay = 'f';
1708
1709            FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP
1710                okay := 't';
1711            END LOOP;
1712
1713            IF ( okay <> 't' ) THEN
1714                RAISE NOTICE 'invalid schema name - using current_schema()';
1715                SELECT current_schema() INTO real_schema;
1716            ELSE
1717                real_schema = schema_name;
1718            END IF;
1719        ELSE
1720            SELECT current_schema() INTO real_schema;
1721        END IF;
1722
1723        -- Find out if the column is in the raster_columns table
1724        okay = 'f';
1725        FOR myrec IN SELECT * FROM raster_columns WHERE r_table_schema = text(real_schema)
1726                     AND r_table_name = table_name AND r_column = column_name LOOP
1727            okay := 't';
1728        END LOOP;
1729        IF (okay <> 't') THEN
1730            RAISE EXCEPTION 'column % not found in raster_columns table', column_name;
1731            RETURN 'f';
1732        END IF;
1733
1734        -- Remove ref from raster_columns table
1735        EXECUTE 'DELETE FROM raster_columns WHERE r_table_schema = '
1736                || quote_literal(real_schema) || ' AND r_table_name = '
1737                || quote_literal(table_name)  || ' AND r_column = '
1738                || quote_literal(column_name);
1739
1740        -- Remove table column
1741        EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.'
1742                || quote_ident(table_name) || ' DROP COLUMN '
1743                || quote_ident(column_name);
1744
1745        RETURN schema_name || '.' || table_name || '.' || column_name ||' effectively removed.';
1746    END;
1747    $$
1748    LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);
1749
1750
1751-----------------------------------------------------------------------
1752-- DropRasterColumn (with default catalog name)
1753-----------------------------------------------------------------------
1754CREATE OR REPLACE FUNCTION DropRasterColumn(schema_name varchar, 
1755                                            table_name varchar,
1756                                            column_name varchar)
1757    RETURNS text AS
1758    $$
1759    DECLARE
1760        ret text;
1761    BEGIN
1762        SELECT DropRasterColumn('', schema_name, table_name, column_name) INTO ret;
1763        RETURN ret;
1764    END;
1765    $$
1766    LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);
1767
1768-----------------------------------------------------------------------
1769-- DropRasterColumn (with default catalog and schema name)
1770-----------------------------------------------------------------------
1771CREATE OR REPLACE FUNCTION DropRasterColumn(table_name varchar, 
1772                                            column_name varchar)
1773    RETURNS text AS
1774    $$
1775    DECLARE
1776        ret text;
1777    BEGIN
1778        SELECT DropRasterColumn('', '', table_name, column_name) INTO ret;
1779        RETURN ret;
1780    END;
1781    $$
1782    LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);
1783
1784-----------------------------------------------------------------------
1785-- DropRasterTable
1786-- Drop a table and all its references in raster_columns
1787-----------------------------------------------------------------------
1788CREATE OR REPLACE FUNCTION DropRasterTable(catalog_name varchar, 
1789                                           schema_name varchar,
1790                                           table_name varchar)
1791    RETURNS text AS
1792    $$
1793    DECLARE
1794        real_schema name;
1795    BEGIN
1796        IF ( schema_name = '' ) THEN
1797            SELECT current_schema() into real_schema;
1798        ELSE
1799            real_schema = schema_name;
1800        END IF;
1801
1802        -- Remove refs from raster_columns table
1803        EXECUTE 'DELETE FROM raster_columns WHERE '
1804                || 'r_table_schema = ' || quote_literal(real_schema)
1805                || ' AND ' || ' r_table_name = ' || quote_literal(table_name);
1806
1807        -- Remove table
1808        EXECUTE 'DROP TABLE ' || quote_ident(real_schema) || '.'
1809                || quote_ident(table_name);
1810
1811        RETURN real_schema || '.' || table_name || ' effectively dropped.';
1812    END;
1813    $$
1814    LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);
1815
1816-----------------------------------------------------------------------
1817-- DropRasterTable (with default catalog name)
1818-- Drop a table and all its references in raster_columns
1819-----------------------------------------------------------------------
1820CREATE OR REPLACE FUNCTION DropRasterTable(schema_name varchar, 
1821                                           table_name varchar)
1822    RETURNS text AS
1823    $$
1824    DECLARE
1825        ret text;
1826    BEGIN
1827        SELECT DropRasterTable('', schema_name, table_name) INTO ret;
1828        RETURN ret;
1829    END;
1830    $$
1831    LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);
1832
1833-----------------------------------------------------------------------
1834-- DropRasterTable (with default catalog and schema name)
1835-- Drop a table and all its references in raster_columns
1836-- For PG>=73 use current_schema()
1837-----------------------------------------------------------------------
1838CREATE OR REPLACE FUNCTION DropRasterTable(table_name varchar)
1839    RETURNS text AS
1840    $$
1841    DECLARE
1842        ret text;
1843    BEGIN
1844        SELECT DropRasterTable('', '', table_name) INTO ret;
1845        RETURN ret;
1846    END;
1847    $$
1848    LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);
1849
1850-------------------------------------------------------------------
1851--  END
1852-------------------------------------------------------------------
1853
1854-- COMMIT;
Note: See TracBrowser for help on using the browser.