[postgis-devel] Re: [postgis-users] create tables smarter

[Obe, Regina]

Actually I think ST_Geometry_Columns has to be a view rather than a real
table.  From what I can gather 
Looking at 
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/opt/rsbp4017.htm

and 
http://jtc1sc32.org/doc/N1101-1150/32N1107-WD13249-3--spatial.pdf

Looks like the definition includes SRS_Name which we can't very well
expect people to be typing in and we would easily get by joining with
our spatial_ref_sys.

and 

Looking at page 476 of this doc (443).  
http://jtc1sc32.org/doc/N1101-1150/32N1107-WD13249-3--spatial.pdf

The example implementation looks like below - granted I would say there
code is needlessly ugly (or at least needlessly complicated if we were
to implement similar logic in PostgreSQL I think the joins we would need
to do would be much simpler).


CREATE VIEW ST_GEOMETRY_COLUMNS AS 
WITH RECURSIVE TYPES ( TYPE_CATALOG, TYPE_SCHEMA, TYPE_NAME ) AS 
 ( VALUES ( ST_TypeCatalogName, ST_TypeSchemaName, 'ST_GEOMETRY' )
UNION ALL 
SELECT h.USER_DEFINED_TYPE_CATALOG, h.USER_DEFINED_TYPE_SCHEMA,
 h.USER_DEFINED_TYPE_NAME 
  FROM INFORMATION_SCHEMA.DIRECT_SUPERTYPES AS h 
JOIN 
 TYPES AS t ON 
( h.SUPERTYPE_CATALOG = t.TYPE_CATALOG AND 
  h.SUPERTYPE_SCHEMA  = t.TYPE_SCHEMA AND 
  h.SUPERTYPE_NAME    = t.TYPE_NAME ) 
 ) 
 ( SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, 
c.TABLE_NAME, c.COLUMN_NAME, g.SRS_NAME, 
( SELECT s.SRS_ID 
    FROM ST_DEFINITION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS 
AS s 
WHERE s.SRS_NAME = g.SRS_NAME 
) AS SRS_ID 
  FROM INFORMATION_SCHEMA.COLUMNS AS c 
LEFT OUTER JOIN 
 ST_DEFINITION_SCHEMA.ST_GEOMETRY_COLUMNS AS g ON 
( c.TABLE_CATALOG = g.TABLE_CATALOG AND 
  c.TABLE_SCHEMA  = g.TABLE_SCHEMA AND 
  c.TABLE_NAME    = g.TABLE_NAME AND 
  c.COLUMN_NAME   = g.COLUMN_NAME ) 
 WHERE ( c.UDT_CATALOG, c.UDT_SCHEMA, c.UDT_NAME ) IN 
( SELECT TYPE_CATALOG, TYPE_SCHEMA, TYPE_NAME FROM TYPES ) )
 

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of
Markus Schaber
Sent: Monday, June 09, 2008 11:04 AM
To: postgis-devel at postgis.refractions.net
Subject: Re: [postgis-devel] Re: [postgis-users] create tables smarter

Hi, Regina,

"Obe, Regina" <robe.dnd at cityofboston.gov> wrote:

> On a side note - I recall reading somewhere that the new name of
> Geometry_columns should be
> 
> ST_Geometry_Columns

It should easily possible to provide the View with both names, for
backwards compatibility.


Regards,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.