From 892ff3af4a91c8a46a22a1e19b5a88bbece2b08a Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Wed, 22 Feb 2012 12:49:08 +0000 Subject: [PATCH] Check SRID passed to UpdateGeometrySRID, patch by Mike Toews (#1596) Adds regression testing git-svn-id: http://svn.osgeo.org/postgis/trunk@9253 b70326c6-7e19-0410-871a-916f4a2858ee --- postgis/postgis.sql.in.c | 20 ++++++++++++++++++-- regress/tickets.sql | 14 ++++++++++++++ regress/tickets_expected | 11 +++++++++++ 3 files changed, 43 insertions(+), 2 deletions(-) diff --git a/postgis/postgis.sql.in.c b/postgis/postgis.sql.in.c index 502041406..0b9cc490d 100644 --- a/postgis/postgis.sql.in.c +++ b/postgis/postgis.sql.in.c @@ -2102,7 +2102,7 @@ LANGUAGE 'sql' VOLATILE STRICT; -- Change SRID of all features in a spatially-enabled table -- ----------------------------------------------------------------------- -CREATE OR REPLACE FUNCTION UpdateGeometrySRID(catalogn_name varchar,schema_name varchar,table_name varchar,column_name varchar,new_srid integer) +CREATE OR REPLACE FUNCTION UpdateGeometrySRID(catalogn_name varchar,schema_name varchar,table_name varchar,column_name varchar,new_srid_in integer) RETURNS text AS $$ @@ -2111,6 +2111,8 @@ DECLARE okay boolean; cname varchar; real_schema name; + unknown_srid integer; + new_srid integer := new_srid_in; BEGIN @@ -2132,7 +2134,7 @@ BEGIN SELECT INTO real_schema current_schema()::text; END IF; - -- Find out if the column is in the geometry_columns table + -- Ensure that column_name is in geometry_columns okay = false; FOR myrec IN SELECT type, coord_dimension FROM geometry_columns WHERE f_table_schema = text(real_schema) and f_table_name = table_name and f_geometry_column = column_name LOOP okay := true; @@ -2142,6 +2144,20 @@ BEGIN RETURN false; END IF; + -- Ensure that new_srid is valid + IF ( new_srid > 0 ) THEN + IF ( SELECT count(*) = 0 from spatial_ref_sys where srid = new_srid ) THEN + RAISE EXCEPTION 'invalid SRID: % not found in spatial_ref_sys', new_srid; + RETURN false; + END IF; + ELSE + unknown_srid := ST_SRID('POINT EMPTY'::geometry); + IF ( new_srid != unknown_srid ) THEN + new_srid := unknown_srid; + RAISE NOTICE 'SRID value % converted to the officially unknown SRID value %', new_srid_in, new_srid; + END IF; + END IF; + IF postgis_constraint_srid(schema_name, table_name, column_name) IS NOT NULL THEN -- srid was enforced with constraints before, keep it that way. -- Make up constraint name diff --git a/regress/tickets.sql b/regress/tickets.sql index 38ebb9d5e..c87a564ee 100644 --- a/regress/tickets.sql +++ b/regress/tickets.sql @@ -608,6 +608,20 @@ select '#1580.1', ST_Summary(ST_Transform('SRID=4326;POINT(0 0)'::geometry, 3395 select '#1580.2', ST_Transform('SRID=4326;POINT(180 90)'::geometry, 3395); -- fails select '#1580.3', ST_Summary(ST_Transform('SRID=4326;POINT(0 0)'::geometry, 3395)); +-- #1596 -- +CREATE TABLE road_pg (ID INTEGER, NAME VARCHAR(32)); +SELECT '#1596.1', AddGeometryColumn( 'road_pg','roads_geom', 3395, 'POINT', 2 ); +SELECT '#1596.2', UpdateGeometrySRID( 'road_pg','roads_geom', 330000); +SELECT '#1596.3', srid FROM geometry_columns + WHERE f_table_name = 'road_pg' AND f_geometry_column = 'roads_geom'; +SELECT '#1596.4', UpdateGeometrySRID( 'road_pg','roads_geom', 999000); +SELECT '#1596.5', srid FROM geometry_columns + WHERE f_table_name = 'road_pg' AND f_geometry_column = 'roads_geom'; +SELECT '#1596.6', UpdateGeometrySRID( 'road_pg','roads_geom', -1); +SELECT '#1596.7', srid FROM geometry_columns + WHERE f_table_name = 'road_pg' AND f_geometry_column = 'roads_geom'; +DROP TABLE road_pg; + -- Clean up DELETE FROM spatial_ref_sys; diff --git a/regress/tickets_expected b/regress/tickets_expected index e23cd97be..5f0e8216f 100644 --- a/regress/tickets_expected +++ b/regress/tickets_expected @@ -201,3 +201,14 @@ ERROR: AddToPROJ4SRSCache: couldn't parse proj4 string: '': (null) #1580.1|Point[B] ERROR: transform: couldn't project point (180 90 0): tolerance condition error (-20) #1580.3|Point[B] +#1596.1|public.road_pg.roads_geom SRID:3395 TYPE:POINT DIMS:2 +ERROR: invalid SRID: 330000 not found in spatial_ref_sys +PL/pgSQL function "updategeometrysrid" line 4 at SQL statement +#1596.3|3395 +ERROR: invalid SRID: 999000 not found in spatial_ref_sys +PL/pgSQL function "updategeometrysrid" line 4 at SQL statement +#1596.5|3395 +NOTICE: SRID value -1 converted to the officially unknown SRID value 0 +PL/pgSQL function "updategeometrysrid" line 4 at SQL statement +#1596.6|public.road_pg.roads_geom SRID changed to 0 +#1596.7|0 -- 2.40.0