使用 SQL 查询地理数据库系统表

了解四个地理数据库系统表之间的关系后，通过 SQL 进行检查是查看这些表如何协同工作的最佳方法。从 GDB_Items 表中选择所有行和列将返回与下表类似的结果集（还包含很多其他字段）：

请注意，“类型”列中的值采用 UUID 的形式进行存储。如地理数据库系统表概述中所述，这些 UUID 可通过 GDB_ItemTypes 表进行解析，如以下查询所示：

SELECT gdb_items.UUID, gdb_itemtypes.name AS "Type", gdb_items.name
   FROM gdb_items INNER JOIN gdb_itemtypes
   ON gdb_items.type = gdb_itemtypes.UUID;

这样返回的结果与在 GDB_Items 表上运行简单的 SELECT 语句时产生的结果类似，只不过“类型”列中的 UUID 已经被替换为可读性更强的字符串：

同样，GDB_ItemRelationships 表也包含在其他表作为关键值的 UUID。在上表 GDB_Items 中可以看出，每个关系包含两个 UUID 值：一个表示关系的目标项，另一个表示关系的源项。要将这些字符解析为便于阅读的字符串，必须将 GDB_ItemRelationships 表与 GDB_Items 表连接两次：

--SQL Server and PostgreSQL query
SELECT relationships.type,
   origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM gdb_items AS origin_items,
   gdb_itemrelationships AS relationships,
   gdb_items AS dest_items
WHERE
   origin_items.UUID = relationships.originid AND
   dest_items.UUID = relationships.destid;

--Oracle query
SELECT relationships.type,
   origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
   sde.gdb_itemrelationships relationships,
   sde.gdb_items dest_items
WHERE
   origin_items.UUID = relationships.originid AND
   dest_items.UUID = relationships.destid;

下图是通过之前的查询返回的结果集示例：

虽然该图显示了地理数据库中各项之间的关系，但仍需要关系类型。通过之前的查询进行扩展，使其包含与 GDB_ItemRelationshipTypes 表的连接来解析“类型 UUID”：

--SQL Server and PostgreSQL query
SELECT reltypes.name AS type,
   origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM gdb_items AS origin_items,
   gdb_itemrelationships AS relationships,
   gdb_items AS dest_items,
   gdb_itemrelationshiptypes AS reltypes
WHERE
   origin_items.UUID = relationships.originid AND
   dest_items.UUID = relationships.destid AND
   relationships.type = reltypes.UUID;

--Oracle query
SELECT reltypes.name AS type,
   origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
   sde.gdb_itemrelationships relationships,
   sde.gdb_items dest_items,
   sde.gdb_itemrelationshiptypes reltypes
WHERE
   origin_items.UUID = relationships.originid AND
   dest_items.UUID = relationships.destid AND
   relationships.type = reltypes.UUID;

将返回以下内容：