示例:返回关系类列表
所有项定义都存储在 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
结果表明地理数据库包含以下内容:
- 名为 speciesrel 的独立关系类,由要素类 habitat 和表 sptable 参与构建
- water 要素数据集中与要素关联的注记类,由 mains 和 mdiam 要素类参与构建
- 关系类 rowgrantrel,位于 roads 要素数据集中,并且由 row 表和 rowgrantors 表参与构建
9/15/2013