示例:返回关系类列表

所有项定义都存储在 GDB_Items 表的定义列中。要确定哪些项是关系类,必须将搜索限制为只包括类型为关系类的项。用于将项标识为关系类的信息存储在 GDB_ItemTypes 表的名称列中。可使用 GDB_Items 表的类型列(或 Oracle 中的 GDB_Items_vw 视图)和 GDB_ItemTypes 表的 UUID 列将两个表连接起来,然后查询 GDB_ItemTypes 表中的关系类。

以下示例查询关系类的名称及其所在的要素数据集(如果适用),以及参与关系类的表或要素类的名称。您可能会注意到,在查询地理数据库时,另外还返回了与要素关联的注记。这是因为与要素关联的注记是一种特殊类型的关系类。

--Queries a geodatabase in PostgreSQL
--Returns a list of relationship classes and their member tables

SELECT
 (xpath('//CatalogPath/text()', definition))::text AS "Relationship class and dataset",
 (xpath('//OriginClassNames/text()', definition))::text AS "Origin class",
 (xpath('//DestinationClassNames/text()', definition))::text AS "Destination class"
FROM 
 sde.gdb_items items INNER JOIN sde.gdb_itemtypes itemtypes
 ON items.type = itemtypes.uuid
WHERE 
 itemtypes.name = 'Relationship class';
--Queries a dbo-schema geodatabase in SQL Server
--Returns a list of relationship classes and their member tables

SELECT
	ITEMS.Definition.value('(/DERelationshipClassInfo/CatalogPath)[1]', 'nvarchar(max)') AS "Relationship Class and dataset",
 ITEMS.Definition.value('(/DERelationshipClassInfo/OriginClassNames/Name)[1]', 'nvarchar(max)') AS "Origin Class",
 ITEMS.Definition.value('(/DERelationshipClassInfo/DestinationClassNames/Name)[1]', 'nvarchar(max)') AS "Destination Class" 
FROM 
 dbo.GDB_ITEMS AS ITEMS INNER JOIN dbo.GDB_ITEMTYPES AS ITEMTYPES
 ON ITEMS.Type = ITEMTYPES.UUID
WHERE 
 ITEMTYPES.Name = 'Relationship Class';

--Queries a geodatabase in Oracle
--Returns a list of relationship classes and their member tables

SELECT
 EXTRACTVALUE(XMLType(Definition), '/DERelationshipClassInfo/CatalogPath') AS "Relationship Class and dataset",
 EXTRACTVALUE(XMLType(Definition), '/DERelationshipClassInfo/OriginClassNames/Name') AS "Origin Class",
 EXTRACTVALUE(XMLType(Definition), '/DERelationshipClassInfo/DestinationClassNames/Name') AS "Destination Class" 
FROM 
 sde.GDB_ITEMS_VW ITEMS INNER JOIN sde.GDB_ITEMTYPES ITEMTYPES
 ON ITEMS.Type = ITEMTYPES.UUID
WHERE 
 ITEMTYPES.Name = 'Relationship Class';

这些查询会返回关系类以及参与关系类的源类和目标类的列表。

Relationship class and dataset                  Origin class                 Destination class
-----------------------------------------       --------------------         -------------------------
mygdb.myuser.speciesrel		                        mygdb.myuser.habitat         mygdb.myuser.sptable
mygdb.uruser.water\\mygdb.uruser.mainanno		  mygdb.uruser.mains           mygdb.uruser.mdiam
mygdb.hisuser.roads\\rowgrantrel		          mygdb.hisuser.row            mygdb.hisuser.rowgrantors

结果表明地理数据库包含以下内容:


7/10/2012