利用 Spatial Extender：从纬度和经度列数据创建自动维护的空间表（DB2 版本）

文档选项 未显示需要 JavaScript 的文档选项 讨论 样例代码

级别： 初级

Robert Uleman (uleman@us.ibm.com), Worldwide Technical Sales, IBM　

2006 年 5 月 08 日

如果数据库中的位置信息采用单独的纬度和经度（或 X 和 Y）列的形式，那么从这些信息中获得的价值就比较有限。为了充分利用数据中的空间信息，需要将单独的数字列转换为单一的空间列，这样就能够在地图上显示位置，并使用地理信息系统（GIS）或 IBM® DB2® Spatial Extender 提供的空间功能来分析空间关系。本文讲解如何使用一个多步骤的方法创建和维护这样的列，讨论了可能使这个简单过程复杂化的所有方面，包括地图投影、空间索引和错误处理。作为一个应用程序示例，展示了如何执行简单的基于距离的查询。最后的附录讨论了如何将来自另一个 DB2 实例（包括 iSeries™ 和 zSeries® 主机上的实例）的联邦表用作源。还提供了示例代码。

简介

无论是从地址信息中推导出来的、由 GPS 设备获得的还是以其他方式生成的，许多数据库都包含点位置的地理坐标，这些信息在数据库中保存为单独的数字列。但是，采用这种形式，就不能利用 DB2 Spatial Extender 的功能来探测空间关系。为了表达出这些坐标中隐含的信息，并在空间数据库查询或外部地理信息系统（GIS）软件中充分利用这些数据，需要将每行中单独的坐标值组合起来，创建一个单一点的几何值。在这样的点值列上可以建立索引来支持空间查询（例如，寻找感兴趣的地区内的所有点，或者距给定位置特定距离内的所有点），还可以将它注册供外部 GIS（比如 ESRI 的 ArcGIS Desktop）使用，以便进行更强大的可视化和分析。

在本文中，将把点列添加在一个单独的表中（见 图 1），因为这是最一般的形式。在许多情况下，您没有修改源表所需的特权，因为它由另一个部门或单独的应用程序控制（但是假设您有权在表上创建触发器）。在其他情况下，源表并不真的是一个表，而是别处（在另一个服务器上的另一个数据库中）的一个表的表示（“昵称”），可以以联邦方式访问它。DB2 尤其适合通过称为 DB2 Connect™ 的设施链接到大型机（zSeries）和 AS/400®（iSeries）主机上的其他 IBM 数据库；这种情况在具有各种 IBM 技术的客户站点上很常见。本文不讨论如何设置昵称，因为这是具有丰富文档可供参考的一般性（非空间）过程；我假设您能够访问源数据，无论源数据是否是本地的。

下面的 图 1 给出一个源表（Things，具有 Latitude 和 Longitude 列）和一个派生的点表（Things_pts）的示例。这两个表通过一个共同的键列（ID）进行联结。Address 和 CityStateZip 列作为源表中其他属性列的示例。派生的空间列 Location 使用原始的坐标：经度作为 X，纬度作为 Y。

创建和使用几何值（比如点）需要 DB2 Spatial Extender。本文假设读者熟悉这个产品；本文不讨论 Extender 的安装、配置和一般用法。更多信息请参见 参考资料。

回页首

设置空间表

在本节中，将采用一个包含经度列和纬度列的表，创建一个具有空间列的关联表，用来自源表的数据填充新表，并准备将这个表用在空间查询中。

源表

源表一般来说与 图 1 相似：一个或多个列组成主键（在这里是称为 ID 的单一列）；几个字母数字列；以及包含坐标值的两个列（在这里是 Latitude 和 Longitude）。这些坐标值可能是从同一个表中的地址数据推导出来的（通过称为 geocoding 的过程）、使用 GPS 技术收集的、从商业或公共来源获得的或者以其他方式创建的；对于本文的目的来说，这些数据来自哪里是无所谓的，对此不做假设。在代码示例和脚本中，对坐标值做如下假设：

• 坐标值代表纬度和经度：“地理” 坐标，不是投影坐标。换句话说，它们是角度度量，以带小数部分的度数表示，不是直线距离（采用米、英尺或类似的单位）。参见下一节 Spatial Reference System。
• 坐标值是数字（小数、浮点数等），不是字符串。（一些应用程序创建只包含字符数据类型的列的表。）
• 坐标列称为 “Latitude” 和 “Longitude”。

如果这些假设对于您的数据库不成立，那么必须做某些调整，这些调整可能很简单（比如对于列名），也可能比较复杂（比如对于投影坐标系）。代码示例和脚本中的适当位置提到了这些调整。

Spatial Reference System

坐标是数字值，代表角度或直线距离。为了让坐标值有意义，必须指定原点和单位：即参照系。在 Spatial Extender 术语中，这是 Spatial Reference System（SRS），它组合了基于地球的坐标系和一组数字转换参数。每个空间几何值包含一个 SRS ID（即 SRID）来标识相关联的 SRS。对 SRS 的全面讨论超出了本文的范围（参见 参考资料），但是重要的是，要意识到以下问题：

• 纬度和经度是地理坐标，是角度度量，常常表示为带小数部分的度数，在地球上的任何地方都有有效值；地理坐标系没有相关联的地图投影。
• 纸上或屏幕上的地图是平面的；它们需要从地球的曲面向平面进行转换：地图投影。投影坐标系的直线坐标是距离度量，表示为长度单位。
• 地理和投影坐标系都包含对地球形状的假设（一个略扁的球体）；这称为大地基准点。
• 地理坐标可以用来表示任何地方的点位置，但是不适合定义具有可度量长度的直线或计算距离。要按照距离进行查询，必须使用投影坐标系。
• Spatial Extender 可以在地理坐标和投影的 SRS 坐标之间进行双向转换，但是有一个重要的限制：源和目标 SRS 必须基于同样的大地基准点。从一种大地基准点转换到另一种需要专门的算法，而且要求用户受过训练，Spatial Extender 不支持这种转换。

正如上面提到的，必须假设源表包含纬度和经度值。数据库本身往往不包含关于大地基准点的信息，所以您必须从创建坐标的应用程序中查明大地基准点。它常常是全球大地基准点（比如 WGS-84，即 World Geodetic System 1984，由所有 GPS 设备使用），或地区性大地基准点（比如 NAD-83，即 North American Datum 1983）。

因为本文的目的是准备将数据用于空间分析，包括基于距离的查询，所以要根据一个投影坐标系为包含计算出的点的空间列选择一个 SRS。如何进行投影才最合适？这完全取决于数据覆盖的地区（“区域”）。对于比较小的区域，标准的投影是合适的，比如 UTM 或 US State Plane（必须选择区域）；对于大的区域（比如美国的大陆部分），需要覆盖更大范围的坐标系（比如，为北美预定义的圆锥投影之一）。SRS 的选择和指定是一个复杂的主题，需要用专门的文章来讨论；一般来说，这至少需要 GIS 和地图方面的一些背景知识。本文简单地假设已经设置了适当的 SRS，一个用于地理坐标，一个用于投影坐标（两者基于同一大地基准点）；而且知道它们各自的名称和 SRID。

本文中的代码示例使用以下 SRS：

注意，SRS 名称和 ID 是任意的，而且是每个数据库本地的，但是底层的坐标系常常是根据行业特权而预定义的。

表结构

空间表的结构很简单：一个惟一的整数列（objectid），组成主键的一个或多个列（从源表复制的），以及一个点几何列。使用一个 Spatial Extender 过程将点列注册为空间列，这会将它与正确的 SRID 关联起来。这里的 objectid 列用来支持某些 GIS 和地图查看软件，包括 ArcExplorer 程序（可以从 IBM 免费下载，参见 参考资料）。图 2 总结了本文中代码示例所使用的表结构，假设源表代表水井的位置。

在这个示例中，源表 wells 具有多列的键（id1、id2）；忽略其他列（由 “...” 表示）。表 wells_pts 中派生的空间列 location 现在使用投影坐标系：并不是将经度复制到 X，将纬度复制到 Y，而是根据相关联的投影转换来计算 X 和 Y。所有名称、类型和值只用于说明。另一列 objectid 在上面解释过了。

在这个示例中，源表的定义 如下：

                CREATE TABLE wells(
   id1       INTEGER NOT NULL,
   id2       INTEGER NOT NULL,
   latitude  DOUBLE,
   longitude DOUBLE,
   name      CHAR(30),
   PRIMARY KEY (id1, id2)
)
IN regtbs INDEX IN idxtbs LONG IN lobtbs;		(1)

• 注。所有示例假设数据库连接（连接到数据库 xypoints）已经存在，在与连接的用户相关联的模式中创建数据库对象（表、视图），而且连接的用户具有所需的所有特权。

注意，这个语句为表、索引和大对象（LOB）指定了表空间。当然，实际表空间配置和名称取决于系统环境；在示例中列出这些是为了强调总是应该指定表空间，不允许采用某些默认的初始系统设置。同样，键列的数量及其类型以及坐标列的类型也可能不一样。通常，坐标值存储在数字列中（浮点、小数，甚至是整数），具体的类型并不影响这里给出的代码。但是，有时候它们是字符列；在这种情况下，使用它们的所有代码都需要包含一个转换，例如 Float(Latitude) 而不只是 Latitude（考虑到空的或变形的数字串，可能需要额外的检验和错误处理）。最后，在这个示例中只包含 name 列作为属性列；在大多数情况下，会有更多的属性。

派生表的构造方法如下：

                CREATE TABLE wells_pts                  
(
  objectid INTEGER GENERATED ALWAYS AS IDENTITY,
  id1 INTEGER, id2 INTEGER,                          
  location db2gse.ST_Point               
)
IN regtbs INDEX IN idxtbs LONG IN lobtbs;		(2)

• 注。objectid 列由数据库维护：在每次插入行时，系统将分配一个单调地递增的整数（从 1 开始）。这保证列值是惟一的并避免您为这个列操心（但是，不要尝试为它赋值）。实际上，在本文余下的部分中，我就当这个列是隐藏的，不会在文字或图中提到它。

为了方便，创建一个视图，其中通过联结键列集成了新表的空间列和源表的属性列；这个视图看起来就像是添加了空间列的源表：

                CREATE VIEW wells_all
AS
  SELECT t.*, f.location
  FROM  wells  t, wells_pts f
  WHERE (t.id1, t.id2) = (f.id1, f.id2);		(3)

因为这是一个内联结，所以这个视图不包含出现在 wells 表中但没有出现在 wells_pts 表中的记录（因为这些记录具有空的或无效的坐标）。由于我们的目的是处理空间位置以及属性，所以这样做是有意义的；如果只希望处理属性并希望看到所有行，那么可以直接使用源表 wells。

• 注。如果您对源表有完全的控制权，那么可以通过在源表中直接添加空间列来实现语句 (2) 和 (3) 的效果：

  ALTER TABLE wells ADD COLUMN location db2gse.ST_Point;

  (2')

引用完整性

点表（wells_pts）和源表（wells）之间有外键关系。这意味着点表中每一行的键值（在这个示例中，由两个列值组成）必须也在源表中存在。数据库中的功能（包括约束）可以强制实现这种关系，但是您不需要为此操心，因为 wells_pts 是直接从 wells 派生出来的，而且只由自动过程（触发器）在 wells 表中出现改动时修改（参见 及时更新空间表）。只要没有其他更新 wells_pts 的方法（即，必须从所有其他用户那里撤消修改这个表的特权），那么引用完整性就是有保证的。

特别是，不能将 FOREIGN KEY 约束与下面描述的触发器一起使用，因为由 wells 中的插入触发的 wells_pts 中的任何插入都会导致试图读 wells，从而确认具有这个键值的行存在；而这个读操作会失败，因为它涉及的行仍然由插入这一行的过程锁定着。

几何列注册

为了将空间表 wells_pts 标为空间的，并让与 OpenGIS 兼容的客户机可以发现它，需要在 Spatial Extender 编目中注册 location 列。对于当前的示例，使用以下命令行：

                db2se register_spatial_column xypoints -tableName wells_pts
      -columnName location -srsName louisiana_utm	(4)

还可以对组合视图执行相同的操作：

                db2se register_spatial_column xypoints -tableName wells_all
      -columnName location -srsName louisiana_utm	(4a)

参数 tableName、columnName 和 srsName 无需解释；xypoints 是当前数据库名。注意，db2se 命令行实用程序使用 SRS 名称，而不是 SRID；以后在 SQL 语句中需要 SRID。

可以使用 DB2 Control Center 完成相同的步骤（图 3）：右击对象树中的当前数据库或内容面板中的特定表（wells_pts），选择 Spatial Extender | Spatial Columns ...；在 Spatial Columns 对话框中，选择这个表并点击 Register...；在 Select Spatial Reference System 对话框中，选择正确的 SRS（LOUISIANA_UTM）并点击 OK；关闭 Spatial Columns 对话框。

在注册空间列时，Spatial Extender 编目记录它的名称和相关联的 SRS（图 4）；另外，被注册列上的检查约束确保只有具有正确 SRID 的几何值可以插入。

下面的图显示 Spatial Extender 编目中注册的一个空间列（wells_pts 中的 location）。Spatial Extender 使用模式 DB2GSE。显示的编目表实际上是视图；对于每个表，这个图中忽略了几列。注意，坐标系的定义指定了大地基准点和投影参数，采用的是由 Open Geospatial Consortium 的 Simple Features Specification for SQL 指定的 Well-Known Text 格式。

对空间表进行初始化

现在可以复制源表中的现有行，从而对空间表进行初始化；当然，在这个过程中，单独的纬度和经度值要转换为点几何值。图 5 中描述了这个过程。

为空间列创建点几何值涉及到用纬度和经度坐标（地理坐标系，SRID = 1）来构造点几何值，然后将它转换为 UTM（投影）坐标（SRID = 1011），如下图所示。

在最简单的 SQL 中，完成这个步骤的代码如下：

                INSERT INTO wells_pts( id1, id2, location )
SELECT
  id1, id2,
  TREAT (
    db2gse.ST_Transform (
      db2gse.ST_Point (
        longitude,
        latitude,
        1
      ),
      1011
    ) AS db2gse.ST_Point
  )
FROM wells;						(5)

• 注。对于添加了几何列的源表，等效的语句是：
  

  UPDATE wells SET location = TREAT ( ... );

  (5')

  TREAT 中指定的表达式与语句 (5) 中相同。

仔细看看这个语句。选择和插入的前两列就是主键。第三列（粗体显示）是由 TREAT 中的两个嵌套函数构造的。内部调用（对 ST_Point()）创建一个具有纬度-经度坐标（SRID = 1）的点几何值。它返回一个 db2gse.ST_Point 类型的值。外部调用（对 ST_Transform()）将这个点几何值投影到由 SRID 1011 表示的 UTM 坐标系。这严格地按照图 5 所示的流程。但是，还需要做一件事：ST_Transform() 返回一个 db2gse.ST_Geometry 类型的值，这个类型是所有几何类型的超类型；作为一个应用于所有几何类型的函数，它实际上是超类型的一个方法。但是，空间列期望接受 ST_Point 值，不接受 ST_Geometry，所以需要将它从超类型 “向下造型” 为子类型。这就是 TREAT (... AS ...) 的作用。这能够起作用是因为这个例子中的 ST_Geometry 值实际上是 ST_Point 的实例。与相关且可能相似的 CAST 不同，在 TREAT 中不涉及转换、截断或其他变换。因为语句 (5) 中的整个粗体表达式会出现在许多代码片段中，所以从这里往后我将它简写为 TREAT ( ... ) 。

注意，这个批量复制操作做了一些假设，这在真实的应用程序中可能不安全。首先，它假设在最终点值的构造过程中没有发生错误。如果发生错误，函数之一抛出异常，那么整个过程回退。其次，它假设有足够的日志空间用来完成整个复制操作。以后我会回到这个操作上，对这个过程进行调整以便处理被拒绝的行并限制日志的使用。

空间索引

既然已经填充了空间表，就应该添加空间索引来支持空间查询了；在 DB2 中这是一个网格索引。因为每个数据集是不一样的，这个示例为网格单元格大小使用一个任意值：1 km。这应该适用于比较大的项目区域范围，包括路易斯安那州（本示例中的项目区域）；可能必须根据您项目的区域和数据密度来调整这个值（对于大区域和低密度，采用更大的单元格；反之亦然）。注意，为点数据指定多个网格级别是没有好处的。

建立空间索引的命令如下：

                CREATE INDEX wells_pts_grx
ON wells_pts (location)
EXTEND USING db2gse.spatial_index (1000, 0, 0);		(6)

• 对于添加了空间列的源表，命令是相同的，只是表名（可能还有索引名）不一样：

  CREATE INDEX wells_grx ON wells (location) EXTEND USING db2gse.spatial_index (1000, 0, 0);

  (6')

第一个参数（1000）以米为单位指定网格单元格大小，因为米是与 location 列相关联的 SRS 的直线单位。

在大多数情况下，还需要键列上的非空间索引；这是标准的数据库实践，这里不讨论这个索引。

回页首

及时更新空间表

触发器和过程

既然空间列已经就绪并填充了源表的初始内容，就需要确保空间列及时更新，总是反映源表的当前内容。这要使用数据库触发器来完成。可以为修改源表的每个操作设置一个触发器：插入、更新和删除。在理论上，可以在触发器体中指定需要的所有逻辑；在实践中，常常调用一个用户定义的过程并将所有逻辑放在这里。这样更强大并更灵活，尤其是在错误处理方面。

插入触发器

在源表中插入新行时，对应的一行（或者说特性，这是一个 GIS 术语，是指代表真实世界中的某些东西的数据库条目）必须添加进空间表中。这由以下触发器完成：

                CREATE PROCEDURE wells_ins_proc (
  id1 INTEGER, id2 INTEGER,
  latitude  FLOAT, longitude FLOAT
)
  SPECIFIC wells_ins_proc MODIFIES SQL DATA DETERMINISTIC
  CALLED ON NULL INPUT LANGUAGE SQL NO EXTERNAL ACTION
BEGIN ATOMIC
  INSERT INTO wells_pts( id1, id2, location )
  VALUES (
    id1, id2,
    TREAT ( ... );
END@ 							(7)
CREATE TRIGGER wells_ins
  AFTER INSERT ON wells
  REFERENCING NEW AS t
  FOR EACH ROW CALL wells_ins_proc (
    t.id1, t.id2,
    t.latitude, t.longitude
  )@ 							(8)

• 注。“@” 用来结束整个 CREATE PROCEDURE 语句，因为 “;” 需要用来结束过程中的语句。在命令行上，这要求使用 -d@ 选项。
• 注。对于添加了空间列的源表，不需要单独的插入过程 (7)；实际上，插入触发器调用与更新触发器（见下面）相同的过程，因为一旦插入了新行，需要做的就只是为空间列赋值：

  ... FOR EACH ROW CALL wells_upd_proc ...

  (8')

更新触发器

当更新源表中的坐标列时，必须构造一个新的点几何值。更新触发器如下：

                CREATE PROCEDURE wells_upd_proc (
  id1 INTEGER, id2 INTEGER,
  latitude FLOAT, longitude FLOAT
)
  SPECIFIC wells_upd_proc MODIFIES SQL DATA DETERMINISTIC
  CALLED ON NULL INPUT LANGUAGE SQL NO EXTERNAL ACTION
BEGIN ATOMIC
  UPDATE wells_pts f
  SET location = TREAT ( ... )
  WHERE (f.id1, f.id2) =
        (wells_upd_proc.id1, wells_upd_proc.id2);
END@ 							(9)
CREATE TRIGGER wells_upd
  AFTER UPDATE OF latitude, longitude ON wells
  REFERENCING NEW AS t
  FOR EACH ROW CALL wells_upd_proc (
    t.id1, t.id2,
    t.latitude,
    t.longitude
  )@ 							(10)

注意，这假设从不更新键列；一旦一行存在了，它的主键值就不变了。如果这个假设不成立，那么必须处理额外的情况。我在这里不解决这个问题。

• 注 对于添加了空间列的源表，更新过程为插入和更新触发器两者服务：

  ... UPDATE wells f ...

  (9')

删除触发器

当在源表中删除一行时，特性表中的对应行也必须删除（按照上面的解释，不能使用外键约束的 ON DELETE CASCADE 子句让系统自动完成这个操作）。触发器的定义如下：

                CREATE TRIGGER wells_del
  AFTER DELETE
  ON wells
  REFERENCING OLD AS t
  FOR EACH ROW
    DELETE FROM wells_pts f
    WHERE (f.id1, f.id2) = (t.id1, t.id2);		(11)

这个触发器非常简单，所以不需要过程。惟一可能 “出错” 的地方是没有限定特性必须存在，在特性不存在的情况下应该不做任何事情。

• 注 对于添加了空间列的源表，根本不需要这个触发器；一旦删除了一行，它就消失了。

回页首

null 值、异常和提交间隔

null 值

有时候，源表中的一行不具有有效的坐标值。最简单的情况是坐标值之一或两者是 null。在这种情况下，不创建相关联的点特性，因为位置值不是有意义的点几何值（ST_Point 构造函数返回 “POINT EMPTY” 而不是 null）。

• 注。这里的讨论并不应用于添加了空间列的源表。由于行存在，所以空间列必须有值，无论是 null 还是 “POINT EMPTY”。如果必须避免 “POINT EMPTY”（这常常不会造成任何问题），那么将触发器过程中的 UPDATE 语句放在

  IF latitude IS NOT NULL AND longitude IS NOT NULL ... END IF;

  块中。

在批量复制中，只选择在 latitude 和 longitude 列中没有 null 值的那些行：

                INSERT INTO wells_pts( id1, id2, location )
SELECT
  id1, id2,
  TREAT ( ... )
FROM wells
WHERE latitude IS NOT NULL AND longitude IS NOT NULL;	(12)

同样，对于坐标值之一为 null 的情况，在插入触发器中应该不做任何事情。不幸的是，不能防止触发器调用这个过程，因为在 DB2 中过程总是 CALLED ON NULL INPUT。因此，必须在过程内部探测 null（见语句 (5)；为了节省空间并保持简洁，这里只显示对以前例子的修改）：

                CREATE PROCEDURE wells_ins_proc (
  ... [common SQL code omitted]
BEGIN ATOMIC
    -- Insert the corresponding feature, unless any
    -- coordinate is null
  IF latitude IS NOT NULL AND longitude IS NOT NULL
    INSERT INTO wells_pts( id1, id2, location )
    VALUES ( id1, id2, TREAT ( ... ) );
  END IF;
END@							(13)

在更新触发器中，如果坐标值之一变成 null，那么需要删除对应的特性：

                CREATE PROCEDURE wells_upd_proc (
  ... [common SQL code omitted]
BEGIN ATOMIC
  IF latitude IS NULL OR longitude IS NULL THEN
    -- If the new row has null coordinates, delete the
    -- corresponding feature
    DELETE FROM wells_pts f
    WHERE (f.id1, f.id2) = (id1, id2);
  ELSE
    UPDATE wells_pts f SET location = TREAT ( ... )
    WHERE (f.id1, f.id2) =
          (wells_upd_proc.id1, wells_upd_proc.id2);
  END IF;
END@							(14)

注意，如果坐标值原来就是 null 或无效的，那么可能没有要删除的东西；但是，这不会造成错误。

删除触发器不依赖于坐标值，所以这里不需要修改。

触发器和过程中的异常

正如前面提到的，到目前为止给出的代码没有考虑到出错的可能性。当在触发器的处理过程中抛出异常时，触发器终止，导致触发器触发的整个语句回退。这可能不是您需要的，因为这会中断应用程序的正常流程。所以必须能够捕获异常并进行适当的处理：通常，这意味着什么也不做并让整个过程继续，悄悄地忽略错误。

在此代码中，可能导致出错的主要情况是 ST_Transform() 函数无法从给定的纬度和经度计算出投影坐标中的正确点值。通常，这是因为数据不正确，坐标表示的位置超出了期望的区域并超出了投影的范围；有时候，这是因为使用 “魔术” 值（比如 0.0）而不是 null 来表示缺少的数据。如果坐标完全是错的（例如，不可能存在的纬度值 100.0），那么 ST_Point() 函数可能引发异常。

DB2 存储过程语言允许为特定的情况创建异常处理函数；可以用来捕获可能出现的错误。（注意，其他情况也可能在 ST_Transform() 和 ST_Point() 中造成异常，比如与 SRS 定义相关的问题；但是，应该在开发和测试期间解决这些问题，一旦过程投入使用，就不应该再发生这些问题。）

在对点表内容进行初始化的批量复制中，需要捕获基于坐标的异常。因为动态 SQL 不支持异常处理函数，所以需要创建一个过程，调用它，然后（可选地）再删除这个过程：

                
                CREATE PROCEDURE wells_init_proc ()
  SPECIFIC wells_init_proc MODIFIES SQL DATA DETERMINISTIC
  CALLED ON NULL INPUT LANGUAGE SQL NO EXTERNAL ACTION
BEGIN
DECLARE dummy INTEGER;
init: FOR t AS copy_cursor CURSOR WITH HOLD FOR
    SELECT id1, id2, longitude, latitude FROM wells
    WHERE latitude IS NOT NULL AND longitude IS NOT NULL
  DO
ins: BEGIN
    -- If one of the coordinates is bad, the transform fails.
    -- Trap the exception and continue with the next row.
    DECLARE TransformFailed CONDITION FOR SQLSTATE '38SUC';
    DECLARE BadCoord        CONDITION FOR SQLSTATE '38SSG';
    DECLARE CONTINUE HANDLER FOR TransformFailed, BadCoord
      SET dummy = 1;
    INSERT INTO wells_pts ( id1, id2, location )
    VALUES ( id1, id2, TREAT ( ... ) );
  END ins;
  END FOR init;
END@
CALL wells_init_proc()@
DROP PROCEDURE wells_init_proc@				(15)

对 FOR 语句的具体语法和工作原理的讨论超出了本文的范围，但是需要 CURSOR WITH HOLD 子句，从而确保在捕获异常之后过程继续处理下一行，而不是执行 FOR 之后的下一个语句（这意味着退出过程）。另外，对于每种可能出现的异常的响应是不做任何事，但是 CONTINUE HANDLER 声明需要一个完整的 SQL 语句；因此使用伪赋值。用各自的 SQLSTATE 声明的特定情况在 Spatial Extender 文档中进行了描述（分别是消息 GSE3015N 和 GSE3416N）。

对插入触发器应用相似的异常处理：

                CREATE PROCEDURE wells_ins_proc (
  ... [common SQL code omitted]
BEGIN ATOMIC
  DECLARE dummy INTEGER;
  DECLARE TransformFailed CONDITION FOR SQLSTATE '38SUC';
  DECLARE BadCoord        CONDITION FOR SQLSTATE '38SSG';
  DECLARE EXIT HANDLER FOR TransformFailed, BadCoord
    SET dummy = 1;
  IF latitude IS NOT NULL AND ... [common SQL code omitted]
END@							(16)

更新触发器比较复杂：如果更新导致异常，那么必须删除现有的特性行，就像处理 null 坐标一样。如果没有找到此行（因为原来的坐标就是 null 或无效的），那么必须将更新转为插入；在这种情况下，应该忽略任何异常（不需要删除任何东西），所以必须覆盖异常处理函数。

                CREATE PROCEDURE wells_upd_proc (
  ... [common SQL code omitted]
BEGIN ATOMIC
  DECLARE dummy INTEGER;
  IF latitude IS NULL OR ... [common SQL code omitted]
  ELSE
Upd: BEGIN
    DECLARE TransformFailed CONDITION FOR SQLSTATE '38SUC';
    DECLARE BadCoord        CONDITION FOR SQLSTATE '38SSG';
    DECLARE EXIT HANDLER FOR TransformFailed, BadCoord
      DELETE FROM wells_pts f
      WHERE (f.id1, f.id2) = (id1, id2);
    DECLARE EXIT HANDLER FOR NOT FOUND
  nf: BEGIN
      DECLARE EXIT HANDLER FOR TransformFailed, BadCoord
        SET dummy = 1;
      INSERT INTO wells_pts( id1, id2, location )
      VALUES ( id1, id2, TREAT ( ... ) );
    END nf;
    UPDATE wells_pts f SET location = TREAT ( ... )
    WHERE (f.id1, f.id2) =
          (wells_upd_proc.id1, wells_upd_proc.id2);
  END Upd;
  END IF;
END@							(17)

删除触发器不需要异常处理。如果对应的空间行不存在，那么就不删除任何东西；这种情况不是异常，不会导致事务回退。

用日志悄悄地进行处理：错误表

有了触发器和批量复制过程中的异常处理，现在就可以确保错误的坐标不会阻塞源表上的事务，并确保应用程序用户不会看到（不是由他发起的）后台进程造成的消息。错误的坐标表现为缺少相关联的点几何值，触发器过程会悄悄地终止。这样做对用户比较友好，但是如果能够检查源表中的行是否造成了问题，并获得这些行的标识和对发生的情况的描述，那就更好了。为此，需要维护一个日志；批量装载和导入程序常常将日志写入一个文件，但是在数据库中需要使用一个表。有许多办法来维护日志；下面只给出一个建议的结构和机制。

除了 wells_pts 表之外，再创建一个用于日志记录的 wells_err 表。每个日志记录有一个时间戳、一个描述性字符串、来自源表的主键（用于标识出问题的行）以及原来的纬度和经度（因为大多数甚至所有故障都是由错误的坐标或 null 坐标导致的）。见 图 6。

图 6 显示利用错误表进行异常日志记录。在 “wells” 表中插入突出显示的记录时，插入触发器调用点构造函数和转换函数。后者引发一个异常，因为经度 +90.525（在东半球）使点位于西藏的某处，这超出了路易斯安那州的 UTM 区域的范围。在异常处理函数中，将一行插入错误日志表，这一行记录了时间和日期、原因编码、主键和坐标。检查错误表就能够方便地查明源表行以及出问题的坐标。

图 6 中的错误日志表的定义如下：

                CREATE TABLE wells_err
(
  when      TIMESTAMP,
  reason    CHAR(20),
  id1       INTEGER, id2 INTEGER,
  latitude  FLOAT,
  longitude FLOAT
)
IN regtbs INDEX IN idxtbs LONG IN lobtbs;		(18)

现在，可以在已经定义的各个过程中添加错误日志记录功能。我们先来看看触发器过程；在下一小节中再讨论批量复制，将错误日志记录机制用于其他用途。

插入触发器过程现在像下面这样。注意，伪变量声明已经没有了：不再需要它是因为现在在异常处理函数中有了实际的语句。另外，IF 语句已经反转了，现在检查 null 坐标（而不是 NOT NULL），如果找到了，就写日志记录，否则就继续处理插入。

                CREATE PROCEDURE wells_ins_proc (
  ... [common SQL code omitted]
BEGIN ATOMIC
  ... [condition declarations omitted]
  DECLARE EXIT HANDLER FOR TransformFailed, BadCoord
    INSERT INTO wells_err
    VALUES ( CURRENT TIMESTAMP, 'INS Invalid Coord',
             id1, id2, latitude, longitude );
  IF latitude IS NULL OR longitude IS NULL THEN
    INSERT INTO wells_err
    VALUES ( CURRENT TIMESTAMP, 'INS Null Coord',
             id1, id2, latitude, longitude );
  else
    ... [common SQL code omitted]
END@							(19)

对更新触发器过程进行相似的修改：

                CREATE PROCEDURE wells_upd_proc (
  ... [common SQL code omitted]
BEGIN ATOMIC
  IF latitude IS NULL OR longitude IS NULL THEN
    DELETE FROM wells_pts f
    WHERE (f.id1, f.id2) = (id1, id2);
    INSERT INTO wells_err
    VALUES ( CURRENT TIMESTAMP, 'UPD Null Coord",
             id1, id2, latitude, longitude );
  ELSE
Upd: BEGIN
    ... [condition declarations omitted]
    DECLARE EXIT HANDLER FOR TransformFailed, BadCoord
  tf: BEGIN
      DELETE FROM wells_pts f
      WHERE (f.id1, f.id2) = (id1, id2);
      INSERT INTO wells_err
      VALUES ( CURRENT TIMESTAMP, 'UPD Invalid Coord',
               id1, id2, latitude, longitude );
    END tf;
    DECLARE EXIT HANDLER FOR NOT FOUND
  nf: BEGIN
      DECLARE EXIT HANDLER FOR TransformFailed, BadCoord
        INSERT INTO wells_err
        VALUES ( CURRENT TIMESTAMP, 'UPD Invalid Coord',
                   id1, id2, latitude, longitude );
      INSERT INTO ... [common SQL code omitted]
END@							(20)

与以前一样，删除触发器不需要修改。

提交间隔

这里定义的所有触发器都针对每一行单独执行（“FOR EACH ROW”）。但是，用来对表进行初始化的批量复制在一个事务中处理来自源表的所有行。如果源表很大，那么这可能导致逻辑日志的溢出。因此，在从文件进行批量装载时，最好是定期发出 COMMIT 语句，这样就可以刷新逻辑日志文件。如下面的代码段所示，这需要维护一个计数器，以便能够知道什么时候进行提交并写日志记录，这个计数器不考虑导致异常的行。在这样做的同时，为了进行性能度量，将在每次执行 COMMIT 时以及过程的开头和结尾写诊断记录。注意，对 null 值的检查从 FOR 语句中消失了，因为希望通过写诊断记录来处理这种情况。

                CREATE PROCEDURE wells_init_proc ( commit_interval INTEGER )
  ... [common SQL code omitted]
BEGIN
  DECLARE count INTEGER DEFAULT 0;
  DECLARE failed INTEGER DEFAULT 0;
  IF commit_interval < 1 THEN SET commit_interval = 1; END IF;
  INSERT INTO wells_err (when, reason)
  VALUES ( CURRENT TIMESTAMP, 'CPY Start' );
init: FOR t AS copy_cursor CURSOR WITH HOLD FOR
    SELECT id1, id2, latitude, longitude FROM wells
  DO
ins: BEGIN
    ... [condition declarations omitted]
    DECLARE CONTINUE HANDLER FOR TransformFailed, BadCoord
tf: BEGIN
      INSERT INTO wells_err
      VALUES ( CURRENT TIMESTAMP, 'CPY Invalid Coord',
               id1, id2, latitude, longitude );
      SET failed = 1;
    END tf;
    IF latitude IS NULL OR longitude IS NULL THEN
      INSERT INTO wells_err
      VALUES ( CURRENT TIMESTAMP, 'CPY Null Coord',
               id1, id2, latitude, longitude );
    ELSE
      INSERT INTO wells_pts ( id1, id2, location )
      VALUES ( id1, id2, TREAT ( ... ) );
      IF failed = 0 THEN
        SET count = count + 1;
        IF Mod( count, commit_interval ) = 0 THEN
          INSERT INTO wells_err (when, reason)
          VALUES ( CURRENT TIMESTAMP,
                   'CPY Commit ' || CHAR(count) );
          COMMIT;
        END IF;
      ELSE
        SET failed = 0;
      END IF;
    END IF;
  END ins;
  END FOR init;
  INSERT INTO wells_err (when, reason)
  VALUES ( CURRENT TIMESTAMP, 'CPY Total ' || CHAR(count) );
  COMMIT;
END@
CALL wells_init_proc( 1000 )@
DROP PROCEDURE wells_init_proc@				(21)

回页首

结束语

在原理上，将给定的纬度和经度坐标转换为点位置是很简单的，但是要考虑到一些特殊的实际情况会使它复杂化。在本文中，按照由简到繁的次序，我们构建了维护包含点几何值的空间表所需的过程和触发器，并与包含纬度和经度值的源表关联起来。这使数据库准备好供 GIS 使用并支持 SQL 中实现的空间业务逻辑，包括基于距离的查询。

下载包 包含一个有完整文档说明的 Windows® 命令脚本，这个脚本设置并执行整个过程，包括错误处理、批量复制提交间隔和错误日志记录，几乎可以适用于任何源表。另外，它包含一个数据集示例，可以用它进行试验。这个脚本为每个步骤创建单独的 SQL 命令文件。其他脚本在提供的数据示例上测试实现的机制，并给出一个基于距离的查询示例，而且与地图图像结合在一起。按原样使用这些脚本，或者定制它们来满足自己的配置和需求。

如果本文的某些部分不够清晰、脚本中有 bug 或者您对改进或以后的文章有建议，那么请与作者联系。

回页首

附录 A：基于距离的查询

本文的最终目标是为进行空间查询（包括基于距离的查询）而准备现有的数据集。结果不一定是地图；它可能是另一个表，其中包含选择的属性列（非空间列），以便进行报告和分析（比如在 Excel® 中）。这个附录展示这种查询的样子。

最简单、最常见的查询是寻找距给定位置指定距离内的对象；通常，希望按照距离的升序对结果进行排序。以下查询针对本文中的示例（wells_all）执行这个操作，搜索距给定点 15 KM 内的所有位置，这个点的纬度和经度分别等于 30.4 度和 −91.1 度：

                SELECT id1, id2, name,
  DECIMAL (
    db2gse.ST_Distance ( w.location,
      db2gse.ST_Point( -91.1, 30.4, 1 ), 'KILOMETER' ),
    6, 2 ) AS km
FROM wells_all w
WHERE
  db2gse.ST_Distance ( w.location,
    db2gse.ST_Point( -91.1, 30.4, 1 ), 'KILOMETER' ) <= 15
ORDER BY 4;						(A1)

这里有两处需要注意。首先是 ST_Distance() 函数，这个函数度量两个几何位置之间的直线距离，由第三个参数指定度量的直线单位，而不管底层的 SRS（但是注意，这只对投影 SRS 有效）。这个函数使 SQL 代码的可读性更好。其次，ST_Distance() 函数隐式地对第二个参数（SRS = 1 的点几何值，属于非投影的坐标系）应用投影转换，将它转换为与第一个参数（location 列，具有投影的 SRS）相同的 SRS。也可以通过包含语句 (5) 中的整个 TREAT( ... ) 表达式（用给定值替换 longitude 和 latitude）来执行显式转换。这只是编写代码的个人喜好问题；并不影响查询的执行。

可以使用相似的查询填充一个结果表，供进一步分析使用：

                CREATE TABLE wells_found
(
  id1      INTEGER, id2 INTEGER,
  name     CHAR(30),
  distance FLOAT
) IN regtbs INDEX IN idxtbs LONG IN lobtbs;		(A2)
INSERT INTO wells_found ( id1, id2, name, distance )
SELECT id1, id2, name,
    db2gse.ST_Distance ( w.location, db2gse.ST_Point( -91.1, 30.4, 1 ),
                         'KILOMETER' )
FROM wells_all w
WHERE
    db2gse.ST_Distance ( w.location, db2gse.ST_Point( -91.1, 30.4, 1 ),
                         'KILOMETER' ) <= 15;	        (A3)

（在这种情况下，不需要按照距离排序，也不需要对距离值进行格式化。）

作为一个稍微复杂些的示例，下面给出一个涉及空间联结（即其中的联结谓词是空间函数的联结）的查询。在假想的对点位置空间分布的分析中，这个查询寻找附近（由给定的距离定义）有其他点的那些位置。以下语句搜索 wells_all，寻找 3 KM 距离内有邻居的所有位置：

                SELECT a.id1, a.id2, a.name, COUNT(*) AS number
FROM wells_all a, wells_all b
WHERE
    db2gse.ST_Distance ( a.location, b.location,
                         'KILOMETER' ) <= 3.0
AND NOT (a.id1, a.id2) = (b.id1, b.id2)
GROUP BY a.id1, a.id2, a.name;				(A4)

从 SQL 的角度来看，这个查询（以及它的 GROUP BY 子句和 COUNT(*) 聚合函数）不像前一个查询那么简单。但是，在空间方面，它仍然是一个简单明了的查询。它是一个自联结，这意味着将一个表与本身进行联结：它执行一次表扫描（这很花时间！），对于每一行，它搜索其 location 距当前行的 location 不超过指定距离的行。只对每个位置的邻居总数感兴趣，不需要详细的邻居列表，所以对每个位置的结果进行分组，并只要求计数。当然，根据这个查询的空间搜索条件，每个位置是它自己的邻居，所以要从搜索中排除这一行。

回页首

附录 B：联邦的源数据

创建单独的表（而不是在源表中添加空间列）的原因之一是，源表可能根本不是本地表，而是位于别处（另一个服务器上的另一个数据库中）的联邦表的表示（昵称）。DB2 尤其适合通过称为 DB2 Connect 的设施链接到大型机（zSeries）和 AS/400（iSeries）主机上的其他 IBM 数据库。因为这种情况在采用 DB2 技术的站点上很常见，所以本附录简要介绍一下如何处理这种情况。

昵称不支持触发器，也不支持物化查询表（Materialized Query Table，MQT，有时用来缓存联邦数据）。如果您能够得到远程主机的 DBA 的配合，那么可能能够为所需的数据设置复制，而不是联邦。复制目标（在本地系统上）是一个真正的表，所以本文中描述的基于触发器的机制是可行的。

如果不可能设置复制，那么仍然可以使用昵称作为源来填充相关联的空间表，并在组合视图中联结这两者。因为不能使用触发器来及时更新空间表，所以需要在定期调度机制中使用批量复制机制。如果空间表可以离线，那么首先清空这个表（DELETE FROM wells_pts;），然后调用来自语句 (21) 的过程（这里不用删除这个过程）。

显然，每次都对空间表进行重新初始化是低效的，但是这可能是惟一的办法。但是，如果源表不允许更新键和坐标列（例如，管理位置更改和纠正的办法可能是，创建新的特性并保留旧特性作为历史），那么可以不管现有行，只寻找新行（插入）和已经消失的行（删除）：

                BEGIN ATOMIC
 FOR new AS
  SELECT id1, id2, latitude, longitude
  FROM wells t
  WHERE NOT EXISTS (
    SELECT * FROM wells_pts f
    WHERE (f.id1, f.id2) = (t.id1, t.id2)
  )
 DO
  CALL wells_ins_proc ( id1, id2, latitude, longitude );
 END FOR;
END@							(B1)
DELETE FROM wells_pts f
WHERE NOT EXISTS (
  SELECT * FROM wells t
  WHERE (t.id1, t.id2) = (f.id1, f.id2)
);							(B2)

如果可能更新坐标列，但是源表包含一个时间戳列，其中记录了每行最后一次更新的时间，那么仍然可以避免批量复制。在这种情况下，可以将批处理过程设置为只考虑上一次运行以来更新过的行。例如，假设源表中有一个 TIMESTAMP 类型的 last_updated 列，而且批处理过程每 24 小时运行一次，那么可以使用以下代码：

                FOR changed AS
  SELECT id1, id2, latitude, longitude
  FROM wells
  WHERE Date(last_updated) >= CURRENT DATE - 1 DAYS
DO
  CALL wells_upd_proc ( id1, id2, latitude, longitude );	(B3)

当然，最好是比较 last_updated 值和批处理过程上一次成功运行的实际时间，但是这个思路您已经明白了。

回页首

下载

	关于下载方法的信息

参考资料

• 您可以参阅本文在 developerWorks 全球站点上的 英文原文。
  
  
• IBM Software - DB2 Spatial Extender for Linux, UNIX and Windows - Product Overview：针对 DB2 Spatial Extender 的公共概述性 Web 站点。
  
  
• DB2 Spatial Extender and Geodetic Extender User's Guide and Reference, V8.2：使用 DB2 Spatial Extender 的基础性指南，包括安装和配置。
  
  
• Setting up spatial reference systems：来自 DB2 UDB InfoCenter 的 Administering Spatial Extender 部分。
  
  
• Accessing the attributes of subtypes：来自 DB2 UDB InfoCenter 的 Database applications 部分。
  
  
• Geodetic Datum Overview：对大地基准点和相关主题的解释，来自 University of Colorado 的 Peter Dana。
  
  
• 在 developerWorks DB2 for Linux, UNIX, and Windows 页面 上了解关于 DB2 的更多信息。

• 试用版下载：IBM DB2 Universal Database Enterprise Server Edition V8.2：从 developerWorks 下载试用版。
  
  
• DB2 Spatial Extender for Linux, UNIX and Windows：下载 DB2 Spatial Extender 的试用版。
  
  
• IBM 试用产品下载：使用 IBM 试用软件构建您的下一个开发项目，这些软件可以从 developerWorks 直接下载。
  
  

• 参与论坛讨论。
  
  
• developerWorks blog：加入 developerWorks 社区。
  
  

关于作者

		Robert Uleman 在地理信息系统、遥感/图像处理和地球物理学软件的开发和销售方面有超过 20 年的经验。他从加入 Illustra（这是一家对象-关系数据库厂商）开始进入数据库行业，他在那里作为开发经理负责 Informix Geodetic DataBlade。Robert 现在是 IBM Information Management 技术销售团队的空间专家，负责支持全世界范围的客户和销售团队以及许多 IBM ESRI 战略联盟团队。

对本文的评价

太差！ (1) 需提高 (2) 一般；尚可 (3) 好文章 (4) 真棒！(5) 将您的建议发给我们或者通过参加讨论与其他人分享您的想法.

回页首