[postgis-users] Re: [Mapserver-users] Postgis "explain verbose"-Error [carl.anderson at vadose.org]

[David Blasby]

Carl,

I have modified your schema-patched find_srid() and commited it to the 
postgis CVS:

1. removed isstrict qualification and explicity throw an error if one of 
the arguments is null
2. use "LIKE" instead of "~" for pattern matching because "~" wasnt 
working on my system
3. throw an error if the the requested geometry couldnt be found.

The error throwing was added because it used to return NULL if it 
couldnt find the column.  This caused no end of trouble for people 
because the isstrict clause would cascade and NULL out all the 
geometries with no warning.

CREATE FUNCTION find_srid(varchar,varchar,varchar) RETURNS int4 AS
'DECLARE
   schem text;
   tabl text;
   sr int4;
BEGIN
   IF $1 IS NULL THEN
      RAISE EXCEPTION ''find_srid() - schema is NULL!'';
   END IF;
   IF $2 IS NULL THEN
      RAISE EXCEPTION ''find_srid() - table name is NULL!'';
   END IF;
   IF $3 IS NULL THEN
      RAISE EXCEPTION ''find_srid() - column name is NULL!'';
   END IF;
   schem = $1;
   tabl = $2;
-- if the table contains a . and the schema is empty
-- split the table into a schema and a table
-- otherwise drop through to default behavior
   IF ( schem = '''' and tabl LIKE ''%.%'' ) THEN
     schem = substr(tabl,1,strpos(tabl,''.'')-1);
     tabl = substr(tabl,length(schem)+2);
   ELSE
     schem = schem || ''%'';
   END IF;

   select SRID into sr from geometry_columns where f_table_schema like 
schem and f_table_name = tabl and f_geometry_column = $3;
   IF NOT FOUND THEN
       RAISE EXCEPTION ''find_srid() - couldnt find the corresponding 
SRID - is the geometry registered in the GEOMETRY_COLUMNS table?  Is 
there an uppercase/lowercase missmatch?'';
   END IF;
  return sr;
END;
'
LANGUAGE 'plpgsql' WITH (iscachable);



Orginal patch message:

Carl Anderson wrote:

> To allow support for schema enabled Postgis in Mapserver 6.3
> I request that find_srid () be changed in the following way.
> This is clearly a hack but will extend the use of PostGIS w/ schemas
> into Mapserver 3.6.x
>
> preserve existing behaviour
> extend behavior to automatically promote a namespace.table reference
> into a schema in the place of parameter 1
> into a table in place of parameter 2
>
> Mapserver 3.6.x  converts
>
>         CONNECTIONTYPE postgis
>         DATA 'shape from cities.annex_cases'
>
> into a Binary Cursor containing
>  .. find_srid('','cities.annex_cases','shape') ...
>
> We have geotables in schemas to provide a level of sanity and
> context to the geotables;
>
> Postgresql 7.3 by default will search the users named schema and then 
> the public schema.  This is an unacceptable exposure of the schema owner
> to web traffic.  We prefer to use extremely limited accounts for 
> mapserver.  So when the Pg user is different from the schema the tables
> are in the tables are not found.  The propsed style will allow a user 
> to explicitly specify the schema.
>
> the the above example the public.geometry_columns table contains
>  f_table_catalog | f_table_schema |  f_table_name  | f_geometry_column 
> | coord_dimension | srid |     type     | 
> -----------------+----------------+----------------+-------------------+-----------------+------+-------------- 
>
>                  | cities         | annex_cases    | shape             |
>        2 |   -1 | MULTIPOLYGON
>
>
>
> proposed new  find_srid()
> ------------------
> -- FIND_SRID( <schema/database>, <table>, <geom col> )
>
> CREATE FUNCTION find_srid(varchar,varchar,varchar) RETURNS int4 AS
> 'DECLARE
>   schem text;
>   tabl text;
>   sr int4;
> BEGIN
>   schem = $1;
>   tabl = $2;
> -- if the table contains a . and the schema is empty
> -- split the table into a schema and a table
> -- otherwise drop through to default behavior
>   if ( schem = '''' and tabl ~ ''\.'' ) then
>     schem = substr(tabl,1,strpos(tabl,''.'')-1);
>     tabl = substr(tabl,length(schem)+2);
>   else
>     schem = schem || ''%'';
>   end if;
>
>   select SRID into sr from geometry_columns where f_table_schema like 
> schem and f_table_name = tabl and f_geometry_column = $3;
>
>  return sr;
> END;
> '
> LANGUAGE 'plpgsql' WITH (iscachable,isstrict);
>
> C.