[postgis-tickets] r17333 - Schema qualify all geometry casts in raster code, otherwise materialized views do not resture
Regina Obe
lr at pcorp.us
Mon Mar 11 11:21:25 PDT 2019
Author: robe
Date: 2019-03-11 11:21:25 -0700 (Mon, 11 Mar 2019)
New Revision: 17333
Modified:
branches/2.3/NEWS
branches/2.3/raster/rt_pg/rtpostgis.sql.in
Log:
Schema qualify all geometry casts in raster code, otherwise materialized views do not resture
Closes #4291 for PostGIS 2.3.9
Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS 2019-03-11 17:58:07 UTC (rev 17332)
+++ branches/2.3/NEWS 2019-03-11 18:21:25 UTC (rev 17333)
@@ -1,5 +1,5 @@
PostGIS 2.3.9
-2019/xx/xx
+2019/03/11

* Bug Fixes and Enchantments *

@@ -11,6 +11,7 @@
- #4267, Enable Proj 6 deprecated APIs (Darafei Praliaskouski, Raúl Marín)
- #4275, Avoid passing a NULL pointer to GEOSisEmpty (Raúl Marín)
- #4298, Geodetic tolerance issue in 32-bit (Paul Ramsey)
+ - #4290, Schema qualify geometry casts in raster functions (Regina Obe)
PostGIS 2.3.8
2018/11/24
Modified: branches/2.3/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.3/raster/rt_pg/rtpostgis.sql.in 2019-03-11 17:58:07 UTC (rev 17332)
+++ branches/2.3/raster/rt_pg/rtpostgis.sql.in 2019-03-11 18:21:25 UTC (rev 17333)
@@ -121,7 +121,7 @@
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL;
-- Availability: 2.0.0
--- Changed: 2.1.4 raised cost
+-- Changed: 2.1.4 raised cost
CREATE OR REPLACE FUNCTION st_convexhull(raster)
RETURNS geometry
AS 'MODULE_PATHNAME','RASTER_convex_hull'
@@ -242,7 +242,7 @@
msg text;
msgset text[];
BEGIN
- extent := @extschema at .ST_Extent(rast::geometry);
+ extent := @extschema at .ST_Extent(rast::@extschema at .geometry);
metadata := @extschema at .ST_Metadata(rast);
msg := 'Raster of ' || metadata.width || 'x' || metadata.height || ' pixels has ' || metadata.numbands || ' ';
@@ -255,7 +255,7 @@
msg := msg || 'and extent of ' || extent;
IF
- round(metadata.skewx::numeric, 10) <> round(0::numeric, 10) OR
+ round(metadata.skewx::numeric, 10) <> round(0::numeric, 10) OR
round(metadata.skewy::numeric, 10) <> round(0::numeric, 10)
THEN
msg := 'Skewed ' || overlay(msg placing 'r' from 1 for 1);
@@ -301,7 +301,7 @@
CREATE OR REPLACE FUNCTION st_makeemptyraster(width int, height int, upperleftx float8, upperlefty float8, pixelsize float8)
RETURNS raster
- AS $$ SELECT @extschema at .ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, @extschema at .ST_SRID('POINT(0 0)'::geometry)) $$
+ AS $$ SELECT @extschema at .ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, @extschema at .ST_SRID('POINT(0 0)'::@extschema at .geometry)) $$
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION st_makeemptyraster(rast raster)
@@ -372,7 +372,7 @@
)
RETURNS raster
AS 'MODULE_PATHNAME', 'RASTER_copyBand'
- LANGUAGE 'c' IMMUTABLE _PARALLEL;
+ LANGUAGE 'c' IMMUTABLE _PARALLEL;
CREATE OR REPLACE FUNCTION st_addband(
torast raster,
@@ -568,7 +568,7 @@
sample_percent double precision DEFAULT 1
)
RETURNS summarystats
- AS $$
+ AS $$
DECLARE
stats summarystats;
BEGIN
@@ -1793,7 +1793,7 @@
_colormap := '
100% 0
0% 254
- nv 255
+ nv 255
';
WHEN 'pseudocolor' THEN
_colormap := '
@@ -2472,7 +2472,7 @@
LANGUAGE 'sql' STABLE STRICT;
CREATE OR REPLACE FUNCTION st_transform(
- rast raster,
+ rast raster,
alignto raster,
algorithm text DEFAULT 'NearestNeighbour', maxerr double precision DEFAULT 0.125
)
@@ -4370,7 +4370,7 @@
mean := (Z1 + Z2 + Z3 + Z4 + Z6 + Z7 + Z8 + Z9)/8;
tpi := Z5-mean;
-
+
return tpi;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE _PARALLEL;
@@ -4652,8 +4652,8 @@
Z9 := _value[z][3][3];
tri := ( abs(Z1 - Z5 ) + abs( Z2 - Z5 ) + abs( Z3 - Z5 ) + abs( Z4 - Z5 ) + abs( Z6 - Z5 ) + abs( Z7 - Z5 ) + abs( Z8 - Z5 ) + abs ( Z9 - Z5 )) / 8;
-
- return tri;
+
+ return tri;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE _PARALLEL;
@@ -4975,7 +4975,7 @@
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION st_setgeotransform(rast raster,
- imag double precision,
+ imag double precision,
jmag double precision,
theta_i double precision,
theta_ij double precision,
@@ -5395,7 +5395,7 @@
---------------------------------------------------------------------------------
-- ST_WorldToRasterCoordX(rast raster, pt geometry)
--- Returns the pixel column and row covering the provided point geometry.
+-- Returns the pixel column and row covering the provided point geometry.
-- This function works even if the point is outside the raster extent.
---------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION st_worldtorastercoord(
@@ -5683,52 +5683,52 @@
CREATE OR REPLACE FUNCTION raster_left(raster, raster)
RETURNS bool
- AS 'select $1::geometry << $2::geometry'
+ AS 'select $1::@extschema at .geometry << $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_right(raster, raster)
RETURNS bool
- AS 'select $1::geometry >> $2::geometry'
+ AS 'select $1::@extschema at .geometry >> $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_overabove(raster, raster)
RETURNS bool
- AS 'select $1::geometry |&> $2::geometry'
+ AS 'select $1::@extschema at .geometry |&> $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_overbelow(raster, raster)
RETURNS bool
- AS 'select $1::geometry &<| $2::geometry'
+ AS 'select $1::@extschema at .geometry &<| $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_above(raster, raster)
RETURNS bool
- AS 'select $1::geometry |>> $2::geometry'
+ AS 'select $1::@extschema at .geometry |>> $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_below(raster, raster)
RETURNS bool
- AS 'select $1::geometry <<| $2::geometry'
+ AS 'select $1::@extschema at .geometry <<| $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_same(raster, raster)
RETURNS bool
- AS 'select $1::geometry ~= $2::geometry'
+ AS 'select $1::@extschema at .geometry ~= $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_contained(raster, raster)
RETURNS bool
- AS 'select $1::geometry OPERATOR(@extschema at .@) $2::geometry'
+ AS 'select $1::@extschema at .geometry OPERATOR(@extschema at .@) $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_contain(raster, raster)
RETURNS bool
- AS 'select $1::geometry ~ $2::geometry'
+ AS 'select $1::@extschema at .geometry ~ $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
CREATE OR REPLACE FUNCTION raster_overlap(raster, raster)
RETURNS bool
- AS 'select $1::geometry OPERATOR(@extschema at .&&) $2::geometry'
+ AS 'select $1::@extschema at .geometry OPERATOR(@extschema at .&&) $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-- raster/geometry functions
@@ -5736,41 +5736,41 @@
-- Availability: 2.0.0
CREATE OR REPLACE FUNCTION raster_geometry_contain(raster, geometry)
RETURNS bool
- AS 'select $1::geometry ~ $2'
+ AS 'select $1::@extschema at .geometry ~ $2'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-- Availability: 2.0.5
CREATE OR REPLACE FUNCTION raster_contained_by_geometry(raster, geometry)
RETURNS bool
- AS 'select $1::geometry OPERATOR(@extschema at .@) $2'
+ AS 'select $1::@extschema at .geometry OPERATOR(@extschema at .@) $2'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-- Availability: 2.0.0
CREATE OR REPLACE FUNCTION raster_geometry_overlap(raster, geometry)
RETURNS bool
- AS 'select $1::geometry OPERATOR(@extschema at .&&) $2'
+ AS 'select $1::@extschema at .geometry OPERATOR(@extschema at .&&) $2'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-
+
-- geometry/raster functions
-- Availability: 2.0.0
CREATE OR REPLACE FUNCTION geometry_raster_contain(geometry, raster)
RETURNS bool
- AS 'select $1 OPERATOR(@extschema at .~) $2::geometry'
+ AS 'select $1 OPERATOR(@extschema at .~) $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-- Availability: 2.0.5
CREATE OR REPLACE FUNCTION geometry_contained_by_raster(geometry, raster)
RETURNS bool
- AS 'select $1 OPERATOR(@extschema at .@) $2::geometry'
+ AS 'select $1 OPERATOR(@extschema at .@) $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-- Availability: 2.0.0
CREATE OR REPLACE FUNCTION geometry_raster_overlap(geometry, raster)
RETURNS bool
- AS 'select $1 OPERATOR(@extschema at .&&) $2::geometry'
+ AS 'select $1 OPERATOR(@extschema at .&&) $2::@extschema at .geometry'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
-
+
------------------------------------------------------------------------------
-- GiST index OPERATORs
------------------------------------------------------------------------------
@@ -6055,7 +6055,7 @@
-- edge tile
-- edge tile may have same size as inner tile
- IF
+ IF
(_rastmeta.width = tilewidth) AND
(_rastmeta.height = tileheight)
THEN
@@ -6155,7 +6155,7 @@
-- This function can not be STRICT
CREATE OR REPLACE FUNCTION st_intersects(geom geometry, rast raster, nband integer DEFAULT NULL)
RETURNS boolean AS
- $$ SELECT $1 OPERATOR(@extschema at .&&) $2::geometry AND @extschema at ._st_intersects($1, $2, $3); $$
+ $$ SELECT $1 OPERATOR(@extschema at .&&) $2::@extschema at .geometry AND @extschema at ._st_intersects($1, $2, $3); $$
LANGUAGE 'sql' IMMUTABLE _PARALLEL
COST 1000;
@@ -6165,13 +6165,13 @@
CREATE OR REPLACE FUNCTION st_intersects(rast raster, geom geometry, nband integer DEFAULT NULL)
RETURNS boolean
- AS $$ SELECT $1::geometry OPERATOR(@extschema at .&&) $2 AND @extschema at ._st_intersects($2, $1, $3) $$
+ AS $$ SELECT $1::@extschema at .geometry OPERATOR(@extschema at .&&) $2 AND @extschema at ._st_intersects($2, $1, $3) $$
LANGUAGE 'sql' IMMUTABLE _PARALLEL
COST 1000;
CREATE OR REPLACE FUNCTION st_intersects(rast raster, nband integer, geom geometry)
RETURNS boolean
- AS $$ SELECT $1::geometry OPERATOR(@extschema at .&&) $3 AND @extschema at ._st_intersects($3, $1, $2) $$
+ AS $$ SELECT $1::@extschema at .geometry OPERATOR(@extschema at .&&) $3 AND @extschema at ._st_intersects($3, $1, $2) $$
LANGUAGE 'sql' IMMUTABLE _PARALLEL
COST 1000;
@@ -6341,7 +6341,7 @@
CREATE OR REPLACE FUNCTION st_dwithin(rast1 raster, nband1 integer, rast2 raster, nband2 integer, distance double precision)
RETURNS boolean
- AS $$ SELECT $1::geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($3), $5) AND $3::geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($1), $5) AND CASE WHEN $2 IS NULL OR $4 IS NULL THEN @extschema at ._ST_dwithin(st_convexhull($1), st_convexhull($3), $5) ELSE @extschema at ._ST_dwithin($1, $2, $3, $4, $5) END $$
+ AS $$ SELECT $1::@extschema at .geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($3), $5) AND $3::@extschema at .geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($1), $5) AND CASE WHEN $2 IS NULL OR $4 IS NULL THEN @extschema at ._ST_dwithin(st_convexhull($1), st_convexhull($3), $5) ELSE @extschema at ._ST_dwithin($1, $2, $3, $4, $5) END $$
LANGUAGE 'sql' IMMUTABLE _PARALLEL
COST 1000;
@@ -6363,7 +6363,7 @@
CREATE OR REPLACE FUNCTION st_dfullywithin(rast1 raster, nband1 integer, rast2 raster, nband2 integer, distance double precision)
RETURNS boolean
- AS $$ SELECT $1::geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($3), $5) AND $3::geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($1), $5) AND CASE WHEN $2 IS NULL OR $4 IS NULL THEN @extschema at ._ST_dfullywithin(st_convexhull($1), st_convexhull($3), $5) ELSE @extschema at ._ST_dfullywithin($1, $2, $3, $4, $5) END $$
+ AS $$ SELECT $1::@extschema at .geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($3), $5) AND $3::@extschema at .geometry OPERATOR(@extschema at .&&) ST_Expand(ST_ConvexHull($1), $5) AND CASE WHEN $2 IS NULL OR $4 IS NULL THEN @extschema at ._ST_dfullywithin(st_convexhull($1), st_convexhull($3), $5) ELSE @extschema at ._ST_dfullywithin($1, $2, $3, $4, $5) END $$
LANGUAGE 'sql' IMMUTABLE _PARALLEL
COST 1000;
@@ -6461,7 +6461,7 @@
newnodata1 := coalesce(nodataval[1], ST_BandNodataValue(rast1, band1), ST_MinPossibleValue(ST_BandPixelType(rast1, band1)));
newnodata2 := coalesce(nodataval[2], ST_BandNodataValue(rast2, band2), ST_MinPossibleValue(ST_BandPixelType(rast2, band2)));
-
+
_returnband := upper(returnband);
rtn := NULL;
@@ -6966,10 +6966,10 @@
AND s.connamespace = n.oid
AND s.conrelid = c.oid
AND a.attnum = ANY (s.conkey)
- AND s.consrc LIKE '%st_scale' || $4 || '(% = %')
+ AND s.consrc LIKE '%st_scale' || $4 || '(% = %')
-- if it is a comma separated list of two numbers then need to use round
- SELECT CASE WHEN split_part(c.val,',', 2) > ''
- THEN round( split_part(c.val, ',',1)::numeric, split_part(c.val,',',2)::integer )::float8
+ SELECT CASE WHEN split_part(c.val,',', 2) > ''
+ THEN round( split_part(c.val, ',',1)::numeric, split_part(c.val,',',2)::integer )::float8
ELSE c.val::float8 END
FROM c;
$$ LANGUAGE sql STABLE STRICT
@@ -7119,7 +7119,7 @@
CREATE OR REPLACE FUNCTION _raster_constraint_info_extent(rastschema name, rasttable name, rastcolumn name)
RETURNS geometry AS $$
SELECT
- trim(both '''' from split_part(trim(split_part(s.consrc, ' @ ', 2)), '::', 1))::geometry
+ trim(both '''' from split_part(trim(split_part(s.consrc, ' @ ', 2)), '::', 1))::@extschema at .geometry
FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
WHERE n.nspname = $1
AND c.relname = $2
@@ -7152,7 +7152,7 @@
|| fqtn
|| ' LIMIT 1;';
EXECUTE sql INTO srid;
-
+
cn := 'enforce_max_extent_' || $3;
sql := 'SELECT @extschema at .st_ashexewkb( @extschema at .st_setsrid( @extschema at .st_extent( @extschema at .st_envelope('
@@ -7166,7 +7166,7 @@
|| ' ADD CONSTRAINT ' || quote_ident(cn)
|| ' CHECK ( @extschema at .st_envelope('
|| quote_ident($3)
- || ') @ ''' || attr || '''::geometry) NOT VALID';
+ || ') @ ''' || attr || '''::@extschema at .geometry) NOT VALID';
RETURN @extschema at ._add_raster_constraint(cn, sql);
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
@@ -7249,7 +7249,7 @@
AND s.contype = 'x'
AND 0::smallint = ANY (s.conkey)
AND idx.indexrelid = s.conindid
- AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::geometry)'
+ AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::%geometry)'
AND s.conexclop[1] = op.oid
AND op.oprname = '=';
$$ LANGUAGE sql STABLE STRICT
@@ -7274,7 +7274,7 @@
sql := 'ALTER TABLE ' || fqtn ||
' ADD CONSTRAINT ' || quote_ident(cn) ||
- ' EXCLUDE ((' || quote_ident($3) || '::geometry) WITH =)';
+ ' EXCLUDE ((' || quote_ident($3) || '::@extschema at .geometry) WITH =)';
RETURN @extschema at ._add_raster_constraint(cn, sql);
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
@@ -7297,11 +7297,11 @@
AND s.contype = 'x'
AND 0::smallint = ANY (s.conkey)
AND idx.indexrelid = s.conindid
- AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::geometry)'
+ AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::%geometry)'
AND s.conexclop[1] = op.oid
AND op.oprname = '=';
- RETURN @extschema at ._drop_raster_constraint($1, $2, cn);
+ RETURN @extschema at ._drop_raster_constraint($1, $2, cn);
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100;
@@ -8255,7 +8255,7 @@
n.nspname AS r_table_schema,
c.relname AS r_table_name,
a.attname AS r_raster_column,
- COALESCE(_raster_constraint_info_srid(n.nspname, c.relname, a.attname), (SELECT ST_SRID('POINT(0 0)'::geometry))) AS srid,
+ COALESCE(_raster_constraint_info_srid(n.nspname, c.relname, a.attname), (SELECT ST_SRID('POINT(0 0)'::@extschema at .geometry))) AS srid,
_raster_constraint_info_scale(n.nspname, c.relname, a.attname, 'x') AS scale_x,
_raster_constraint_info_scale(n.nspname, c.relname, a.attname, 'y') AS scale_y,
_raster_constraint_info_blocksize(n.nspname, c.relname, a.attname, 'width') AS blocksize_x,
More information about the postgis-tickets
mailing list