1 | | == WKT Raster User contributed Functions == |
2 | | * This section contains additional functions not currently in WKT Raster. Some of these may become obsolete as WKT Raster becomes more mature |
| 1 | = WKT Raster User contributed Functions = |
| 2 | * ''' This section contains additional functions not currently in WKT Raster. Some of these may become obsolete as WKT Raster becomes more mature ''' |
| 3 | |
| 4 | {{{ |
| 5 | #!sql |
| 6 | CREATE OR REPLACE FUNCTION upgis_ptval(param_rast raster, param_bnum integer, param_pt geometry) |
| 7 | RETURNS float AS |
| 8 | $$ |
| 9 | DECLARE |
| 10 | var_result float = 0; var_rid integer; |
| 11 | var_cols integer; var_rows integer;var_c integer; var_r integer; |
| 12 | var_upx float; var_upy float; var_sizex float; var_sizey float; |
| 13 | BEGIN |
| 14 | -- 1 does point intersects chosen tile |
| 15 | SELECT ST_Width(param_rast), ST_Height(param_rast), ST_UpperLeftX(param_rast), ST_UpperLeftY(param_rast), ST_PixelSizeX(param_rast), ST_PixelSizeY(param_rast) |
| 16 | INTO var_cols, var_rows, var_upx, var_upy, var_sizex, var_sizey |
| 17 | WHERE ST_Intersects(ST_Envelope(param_rast), param_pt) |
| 18 | LIMIT 1; |
| 19 | |
| 20 | -- 2 if no return null |
| 21 | IF var_cols IS NULL THEN -- doesn't intersect |
| 22 | var_result := NULL; |
| 23 | ELSE |
| 24 | -- 3 find row and column |
| 25 | SELECT 1 + floor( ( (ST_X(param_pt) - var_upx) / (var_sizex*var_cols) )*var_cols )::integer, 1 + floor( ( (ST_Y(param_pt) - var_upy)/(var_sizey*var_rows) )*var_rows )::integer |
| 26 | INTO var_c, var_r; |
| 27 | |
| 28 | -- 4 edge case |
| 29 | IF var_c = 0 THEN var_c := 1; END IF; |
| 30 | IF var_r = 0 THEN var_r := 1; END IF; |
| 31 | IF var_c = var_cols + 1 THEN var_c := var_cols; END IF; |
| 32 | IF var_r = var_rows + 1 THEN var_r := var_rows; END IF; |
| 33 | -- 5 get cell value |
| 34 | SELECT ST_Value(param_rast,param_bnum, var_c , var_r ) |
| 35 | INTO var_result; |
| 36 | END IF; |
| 37 | --RAISE NOTICE 'col: %, row: %, val: %, pt: %', var_c, var_r, var_result, ST_AsText(param_pt); |
| 38 | RETURN var_result; |
| 39 | END |
| 40 | $$ |
| 41 | LANGUAGE 'plpgsql' IMMUTABLE; |
| 42 | COMMENT ON FUNCTION upgis_ptval(raster, integer, geometry) IS 'This function takes as input raster, band_num, point geometry and returns the band pixel value of the cell the point falls in. It assumes the spatial reference system of the geometry and raster are the same.'; |
| 43 | }}} |