From 80e03d02950000d2d23cf712026d7cb9b306334a Mon Sep 17 00:00:00 2001
From: Pierre Racine <Pierre.Racine@sbf.ulaval.ca>
Date: Tue, 29 Nov 2011 16:20:06 +0000
Subject: [PATCH] Some more useful functions
git-svn-id: http://svn.osgeo.org/postgis/trunk@8254 b70326c6-7e19-0410-871a-916f4a2858ee
---
raster/scripts/plpgsql/st_pixelaspoints.sql | 46 +++++++++++++++++++++
raster/scripts/plpgsql/st_querytables.sql | 26 ++++++++++++
raster/scripts/plpgsql/st_splittable.sql | 8 ++--
3 files changed, 76 insertions(+), 4 deletions(-)
create mode 100644 raster/scripts/plpgsql/st_pixelaspoints.sql
create mode 100644 raster/scripts/plpgsql/st_querytables.sql
diff --git a/raster/scripts/plpgsql/st_pixelaspoints.sql b/raster/scripts/plpgsql/st_pixelaspoints.sql
new file mode 100644
index 000000000..a34addb44
--- /dev/null
+++ b/raster/scripts/plpgsql/st_pixelaspoints.sql
@@ -0,0 +1,46 @@
+-----------------------------------------------------------------------
+-- Complex type geomvalxy for returning the geometry, the value, the x coordinate and the y coordinate of a pixel
+-----------------------------------------------------------------------
+CREATE TYPE geomvalxy AS (
+ geom geometry,
+ val double precision,
+ x int,
+ y int
+);
+-----------------------------------------------------------------------
+-- ST_PixelAsPoints
+-- Return all the pixels of a raster as a record composed of a point geometry, a value, a x and a y raster coordinate.
+-- Should be called like this:
+-- SELECT (gv).geom, (gv).val, (gv).x, (gv).y FROM (SELECT ST_PixelAsPoints(rast) gv FROM mytable) foo
+-----------------------------------------------------------------------
+DROP FUNCTION IF EXISTS ST_PixelAsPoints(rast raster, band integer);
+CREATE OR REPLACE FUNCTION ST_PixelAsPoints(rast raster, band integer)
+ RETURNS SETOF geomvalxy AS
+ $$
+ DECLARE
+ rast alias for $1;
+ w integer;
+ h integer;
+ x integer;
+ y integer;
+ result geomvalxy;
+ BEGIN
+ SELECT st_width(rast), st_height(rast)
+ INTO w, h;
+ FOR x IN 1..w LOOP
+ FOR y IN 1..h LOOP
+ SELECT ST_Centroid(ST_PixelAsPolygon(rast, x, y)), ST_Value(rast, band, x, y), x, y INTO result;
+ RETURN NEXT result;
+ END LOOP;
+ END LOOP;
+ RETURN;
+ END;
+ $$
+ LANGUAGE 'plpgsql';
+
+DROP FUNCTION IF EXISTS ST_PixelAsPoints(rast raster);
+CREATE FUNCTION ST_PixelAsPoints(raster) RETURNS SETOF geomvalxy AS
+$$
+ SELECT ST_PixelAsPoints($1, 1);
+$$
+LANGUAGE SQL;
\ No newline at end of file
diff --git a/raster/scripts/plpgsql/st_querytables.sql b/raster/scripts/plpgsql/st_querytables.sql
new file mode 100644
index 000000000..4754d56ef
--- /dev/null
+++ b/raster/scripts/plpgsql/st_querytables.sql
@@ -0,0 +1,26 @@
+----------------------------------------------------------------------------------------------------------------------
+-- ST_QueryTables
+-- Execute a query on a series of table based on a prefix.
+-- The string "tablename" will be replaced by the name of the table.
+-- schemaname - The schema where to execute the queries.
+-- prefix - Prefix to restraint the query to tables names starting with this string.
+-- inquery - Query to execute. Can contain the 'tablename' string which will be replaced buy the name of the current table.
+--
+-- Example to drop a set of table
+--
+-- SELECT ST_QueryTables('public', 'aa', 'DROP TABLE IF EXISTS tablename');
+----------------------------------------------------------------------------------------------------------------------
+CREATE OR REPLACE FUNCTION ST_QueryTables(schemaname text, prefix text, inquery text)
+RETURNS int AS
+$BODY$
+DECLARE
+ tabletoquery RECORD;
+BEGIN
+ FOR tabletoquery IN EXECUTE 'SELECT tablename FROM pg_tables WHERE schemaname = ' || quote_literal(schemaname) || ' AND tablename LIKE ' || quote_literal(prefix || '%') LOOP
+ RAISE NOTICE 'Querying %', schemaname || '.' || tabletoquery.tablename;
+ EXECUTE replace(inquery, 'tablename', '"' || schemaname || '"."' || tabletoquery.tablename || '"');
+ END LOOP;
+ RETURN 1;
+END;
+$BODY$
+LANGUAGE plpgsql VOLATILE STRICT;
\ No newline at end of file
diff --git a/raster/scripts/plpgsql/st_splittable.sql b/raster/scripts/plpgsql/st_splittable.sql
index 3c8cb7b35..e72c416b5 100644
--- a/raster/scripts/plpgsql/st_splittable.sql
+++ b/raster/scripts/plpgsql/st_splittable.sql
@@ -1,5 +1,5 @@
----------------------------------------------------------------------------------------------------------------------
--- SplitTable
+-- ST_SplitTable
-- Split a table into a series of table which names are composed of the concatenation of a prefix
-- and the value of a column. This function is usefull when loading many raster in one operation but
-- still wanting to split them in different tables afterward. They must have been loaded with the -F
@@ -13,9 +13,9 @@
-- Example to split the table 'test' into a set of table starting with 't_' and
-- ending with the value of the column 'rid' to be created in the 'public' schema.
--
--- SELECT SplitTable('test', 'public', 't_', 'rid');;
+-- SELECT ST_SplitTable('test', 'public', 't_', 'rid');;
----------------------------------------------------------------------------------------------------------------------
-CREATE OR REPLACE FUNCTION SplitTable(sourcetablename text, targettableschema text, targettableprefix text, suffixcolumnname text)
+CREATE OR REPLACE FUNCTION ST_SplitTable(sourcetablename text, targettableschema text, targettableprefix text, suffixcolumnname text)
RETURNS int AS
$BODY$
DECLARE
@@ -44,4 +44,4 @@ SELECT 2 AS rid, ST_MakeEmptyRaster(2,2,0,0,1,1,1,1,4326) AS rast
SELECT * FROM test;
-SELECT SplitTable('test', 'public', 't_', 'rid');
+SELECT ST_SplitTable('test', 'public', 't_', 'rid');
--
2.40.0