[postgis-users] shp2pgsql and schemas

[Carl Anderson]

On 12/15/2003 01:02:11 PM, strk wrote:
> I'd rather look at a working code :)
> 
> The problem with schema support is the policy to use to avoid
> breaking compatibility between postgis versions.
> 
> Maybe Carl has a working proposal (I've read back in archives).
> 
> --strk;
>

The way I have implemented schemas is to take a hint from the
way sequences work

select nextval('mytab');  is equivalent to current (previous) PostGIS  
behaviour

select nextval('myschema.mytab');  is what i enabled


   disallow all "." inside geometry table names.  OLEDB will force your     
hand here anyway.


  unqualified table names (no db or schema) (so cross DB support  
doesn't exist YET) use the search_path.

  qualified names force a schema

  the table geometry_columns is found by the search path (no explicit  
schema)


encoding in geometry_columns
  if f_table_database is empty ignore it
  if f_table_schema is empty ignore it   (yes, manually kill old data  
when upgrading to this method)
  if f_geometry_column is empty treat table as attributes only
  if a table is not visible to you ignore the line (you may lack  
permissions)
  of course don't assume write priviledges either

this way a PostGIS catalog can be built and used.

I have 4000+ tables, 500+ with geometries,  walking pg_class is way too  
time consuming.  ogr2ogr takes 50 seconds to open the driver. (I am  
working on mods to that driver and its OLEDB component).



Slighlty off topic.  There needs to be a agreed upon external string  
representation of a geometry table and geometry column.

I have been using
    <db>.<schema>.<table>(<column>)

   if <db> is left off, so is the dot seperator associatied with it
   if <schema> is left off, so is the dot seperator associatied with it
   if (<column>) is left off, the first geometry column found is used



use an auto split like below to break schemas from tables


-----------------------------------------------
find_srid

 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 =  
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;