[postgis-users] shp2pgsql and schemas
carl.anderson at vadose.org
Tue Dec 16 05:09:47 PST 2003
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).
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
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
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
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
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
IF $1 IS NULL THEN
RAISE EXCEPTION 'find_srid() - schema is NULL!';
IF $2 IS NULL THEN
RAISE EXCEPTION 'find_srid() - table name is NULL!';
IF $3 IS NULL THEN
RAISE EXCEPTION 'find_srid() - column name is NULL!';
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);
schem = schem || '%';
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?';
More information about the postgis-users