使用 IBM DB2 Spatial Extender 管理空间数据,第 1 部分: 获取空间数据和开发应用程序

开发有效的空间应用程序的技巧和技术

本系列教程介绍了使用 IBM DB2® Spatial Extender 管理空间数据的常见任务,包括导入和创建空间数据,构造和执行空间查询,使用 IBM、第三方和开源的空间工具,调节性能,以及在数据仓库环境中考虑空间环境。在本系列的第一篇文章中,我们将了解如何获取空间数据和构建应用程序。了解如何使用形状文件 (shapefile)、空间数据表和空间索引。

David Adler, 高级软件工程师, IBM China

David AdlerDavid Adler 已负责 IBM 空间数据库技术的开发工作 20 多年,最近 10 年当中主要从事 DB2 空间扩展程序开发工作。



2012 年 6 月 18 日

开始之前

免费下载:IBM® DB2® Express-C 10.1 免费版 或者 DB2® 10.1 for Linux®, UNIX®, and Windows® 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

了解能从本教程学到哪些知识,如何最充分地掌握这些知识。

关于本系列

10 多年来,DB2 Spatial Extender 提供了管理和分析 DB2 关系表中存储的空间数据的功能。可为点、线和多边形定义具有空间类型的表列,它们可表示以下对象:

点位置
客户、零售店、变压器、蜂窝塔
线
高速公路、海岸线、配送路线、电力传输线
多边形
销售/服务区域、洪水/火灾风险、国家/省/县

SQL 查询可合并空间函数来分析空间关系,比如查找一个洪灾区域的客户。清单 1 显示了合并空间函数的代码示例。

清单 1. 合并空间函数的查询
SELECT cust_name, cust_addr
FROM customers, floodzones
WHERE ST_Within(cust_loc, flood_loc) = 1

此查询的结果会生成一组其房屋面临洪水淹没风险的客户的姓名和住所地址。此信息在计算保险费率或发放洪灾保险购买时很有用。

本系列教程将介绍在 DB2 Spatial Extender 中处理空间数据的常见任务。这包括导入和创建空间数据,构造和执行空间查询,使用 IBM、第三方和开源的空间工具,调节性能,以及在数据仓库环境中考虑空间环境。

尽管本系列教程的重点是 Linux®、UNIX® 和 Windows® 操作系统上的 DB2 Spatial Extender,但许多概念同样适用于其他具有空间功能的 IBM 数据库产品,包括:

  • Spatial Support for DB2 for z/OS®
  • Informix® Spatial Datablade
  • Netezza® Spatial

本系列中的教程

5 部分系列教程 所计划包括的其他教程如下。

第 2 部分:实现典型的空间用例
空间应用程序中有许多典型的用例,包括空间连接、距离查询、地理围栏 (geofencing) 和近邻查询。该教程提供有效实现这些用例的示例。
第 3 部分:空间应用程序调节
应用程序性能始终是一个重要的考虑因素。该教程探讨空间应用程序性能和工具中的因素,以理解查询行为。考虑的一些因素包括内联数据、数据集群、压缩和泛化。
第 4 部分:数据仓库环境中的空间分析
在 InfoSphere Warehouse 无分享环境中实现良好的可伸缩性需要考虑额外的因素。该教程介绍实现良好的空间查询协作定位的技术。它还提供了在使用 Esri 中间件时需考虑的特殊因素。
第 5 部分:来自 IBM、第三方和开源的空间可视化工具
空间数据的可视化通常是空间应用程序的一个重要方面。该教程介绍一些可用的工具,包括免费的 IBM Geobrowser、Esri 软件、Web 地图服务(比如 Google 地图)和为 DB2 使用 GeoTools 驱动程序的开源软件。

关于本教程

本教程介绍 DB2 Spatial Extender 提供的技术。还提供了向 DB2 表填充空间数据的方法:在实现空间查询之前的一个关键步骤。本教程还介绍了执行空间查询的环境,包括 DB2 命令行处理器、IBM Data Studio 和使用 JDBC、CLI 和其他接口嵌入到应用程序中的环境。

目标

完成本教程后,您将:

  • 理解与空间类型和坐标系统相关的重要空间概念
  • 掌握导入和创建空间数据的方法
  • 理解空间索引
  • 熟悉执行空间查询的环境

前提条件

本教程适用于拥有 DB2 和 SQL 语言的实用知识的应用程序架构师和开发人员。

下载 一节将样例 SQL 脚本、Java 应用程序和样例数据下载到一个方便的目录,以便掌握本教程中的示例。

系统需求

要完成教程中的步骤,您需要一个安装并配置了 Spatial Extender 的有效 DB2 环境。请参阅 参考资料,了解有关设置此环境的信息。


开始

DB2 Spatial Extender 概述

安装和设置 DB2 Spatial Extender,提供以下主要功能和组件:

空间数据类型
一组数据类型,可用于定义将包含空间数据的表列。这包括用于原子空间值的 ST_Point、ST_Linestring 和 ST_Polygon。这还包括用于同类空间值集合的 ST_MultiPoint、ST_MultiLinestring 和 ST_MultiPolygon。
空间函数和谓词
大量 SQL UDF,用于创建空间值,返回有关空间值的信息,识别空间关系,以及在空间值上执行操作。这些空间 UDF 可合并到 SQL 查询中,这样可以利用 SQL 语言的所有功能。
空间索引
提供空间索引机制来支持空间数据的二维性质。一些工具可帮助指定空间索引。
空间命令行处理器 (CLP) - db2se
db2se CLP 为空间存储过程提供了一个方便的命令行界面来执行操作,比如为数据库启用空间功能和导入或导出空间数据。

请参阅 参考资料,在 DB2 信息中心中了解有关 Spatial Extender 的更多详细信息。

空间参考系统和坐标系统

空间数据通常由坐标值元组来表示(最常见形式为 x 和 y),但也支持 zm 坐标。本教程考虑具有 xy 坐标值的数据。它还将考虑仅使用经度和纬度(以度为单位的十进制值)的数据。请注意,当使用经度和纬度时,经度 对应于 x纬度 对应于 y

要正确执行空间操作,每个空间值必须有一个关联的坐标系统,用于描述坐标值与地球表面上的位置的关系。尽管 Spatial Extender 提供了 3,000 多个不同的预定义坐标系统,但本教程仅采用两个最常用的:用于北美的坐标 NAD83 和用于全球坐标 WGS84。请注意,大部分 GPS 设备都使用 WGS84 报告坐标。

为了有效地存储和处理空间数据,Spatial Extender 在内部将坐标表示为 64 位整数。Spatial Extender 使用一个空间参照系统 (SRS) 来管理此数据,该系统指定用于在双精度值的用户表示与内部表示之间来回转换的偏移和比例系数。每个 SRS 也有一个关联的坐标系统。实际上正是 SRS 与每个空间值相关联。SRS 既可以指它的 128 字符值名称(称为 srsNameSRS_NAME),也可以指它的 32 位整数标识符(称为 sridSRS_ID)。srid 用在 SQL 语句中,srsName 用于 db2se CLP,最常在导入空间数据时使用。

Spatial Extender 为名为 NAD83_SRS_1 的 SRS 提供了 srid 1,表示使用 NAD83 坐标系统的数据。Spatial Extender 为名为 WGS84_SRS_1003 的 SRS 提供 srid 1003,表示使用 WGS84 坐标系统的数据。如果需要使用不同的坐标系统,请参阅 DB2 信息中心中的文档


从形状文件导入

获取形状文件

以文件格式存储和交换空间数据的事实标准是 Esri 所创建的形状文件 表示。Esri 提供了样例数据的形状文件,可供下载和用在 Spatial Extender 中(参阅 参考资料),但为了方便起见,本教程所需的形状文件已包含在 下载 中。Esri 形状文件表示以下类型的数据:

  • 国家边界
  • 州、省边界
  • 县边界
  • 邮政编码边界和质心
  • 主要高速公路
  • 城市、机场、医院、学校
  • 河流

您可以从其他来源中下载更多其他的形状文件。一些形状文件可以免费使用,而另一些需要许可费用。

形状文件实际上是一组相关的文件集,这些文件具有相同的名称和不同的文件扩展名。最常见的文件和 Spatial Extender 使用的文件包括以下文件类型:

<名称>.shp
这是一个必需的二进制文件,包含一个描述数据类型(点、线、多边形和其他几何形状)、记录数量和实际坐标数据的头。
<名称>.dbf
这是一个必需的二进制文件,包含与每个记录关联的字符和数字属性。它实际上使用 DBASE 格式,可使用支持此格式的应用程序查看。此格式有一些重要的限制。属性名称长度仅限于 11 个字符,字符数据不得超过 255 个字节。
<名称>.prj
这是一个可选的文本文件,包含与空间数据关联的坐标系统的定义。.prj 文件中的坐标系统必须与将数据加载到的列的 SRS 的坐标系统相匹配。如果没有此文件,Spatial Extender 假设数据位于指定的 SRS 的坐标系统中。
<名称>.shx
这是一个可选的二进制文件,提供 .shp 文件的一个索引。此文件在 Spatial Extender 中的主要用途是让导入从一个记录开始,而不是从第一个记录开始。

您不需要知道这些文件的详细信息,但如果感兴趣,可以访问 参考资料 一节中有关 Rsri 规范的链接。

获得形状文件的详细信息

可以使用 db2se shape_info Spatial Extender 命令获取有关特殊形状文件的信息,包括几何形状类型、数据的地理范围和所有属性名称和类型。清单 2 提供了将该命令用于本教程中提供的 counties 形状文件样例的示例(参见 下载)。清单 2 中的属性列表已进行缩减,以节省空间。

清单 2. 形状文件属性
db2se shape_info -fileName counties.shp

Shape file information
----------------------
File code                  = 9994
File length (16-bit words) = 758872
Shape file version         = 1000
Shape type                 = 5 (ST_MULTIPOLYGON)
Number of records          = 3141

Minimum X coordinate = -178.217598
Maximum X coordinate = -66.969271
Minimum Y coordinate = 18.921786
Maximum Y coordinate = 71.406235

Shapes do not have Z coordinates.
Shapes do not have M coordinates.
Shape index file (extension .shx) is present.

Attribute file information
--------------------------
dBase file code                = 3
Date of last update            = 2002-02-04
Number of records              = 3141
Number of bytes in header      = 1569
Number of bytes in each record = 461
Number of columns              = 48

Column Number  Column Name      Data Type       Length  Decimal
-------------  ---------------  --------------  ------  -------
            1  NAME             C ( Character)      32        0
            2  STATE_NAME       C ( Character)      25        0
            3  STATE_FIPS       C ( Character)       2        0
...
           48  AVG_SALE97       N (   Numeric)       7        2

Coordinate system definition: "GEOGCS["GCS_North_American_1983",
DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137,298.257222101]],
PRIMEM["Greenwich",0],UNIT["Degree",0.0174532925199433]]"

使用最简单分方法导入形状文件

db2se import_shape Spatial Extender 命令提供了一种简单的方法来导入形状文件。您可以将此命令写入一个 SQL 脚本文件,比如 db2 -tvf import_counties.sql。清单 3 给出了一个简单示例。

清单 3. import_counties.sql 的内容
!erase counties.msg;

!db2se import_shape sample
-fileName         counties.shp
-srsName          nad83_srs_1
-tableSchema      test
-tableName        counties
-spatialColumn    shape
-client           1
-messagesFile     counties.msg
;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        counties
-columnName       shape
-srsName          nad83_srs_1
;

connect to sample;

create index test.countiesidx on test.counties(shape)
extend using db2gse.spatial_index(.5, 2.0, 0);

runstats on table test.counties and indexes all;

select count(*) from test.counties;

describe table test.counties;

备注:

  1. 可以在 DB2 脚本中包含操作系统命令,只要它们添加了感叹号作为前缀。
  2. 如果消息文件存在,erase counties.msg 命令会擦除它。如果消息文件已存在,import_shape 操作将终止。
  3. db2se import_shape 命令的大部分参数都非常简单。会创建一个包含指定模式和名称的表,其中的属性列会对应于形状文件的属性。如果该表已存在,该操作将终止。空间列名称是任意的,但通常会使用 shape
  4. -client 1 参数很重要。此命令在 DB2 客户端上运行导入操作,本地访问导入文件。如果该参数定义为 -client 0,导入操作将在服务器上运行,并需要文件规范位于一个可供 DB2 服务器访问的服务器上。
  5. db2se register_spatial_column 命令创建一个约束,让所有空间值都使用 SRS nad83_srs_1。它还在 db2gse.st_geometry_columns 视图中创建一个条目,将此 SRS 与空间列相关联。这很有帮助,一些可视化应用程序有时必须要求这么做。
  6. connect to sample 代码连接到样例数据库。
  7. 使用 create index 命令和合理的值创建一个空间索引。空间索引将在后面的 一节 中讨论。
  8. runstats 命令更新表统计数据,在加载后或进行重大更新后这么做总是一个不错的想法。
  9. select count(*) 命令允许统计导入的行数。
  10. describe table 命令帮助您统计创建的表列。

使用一个稍微复杂的示例导入形状文件

有时有必要在导入过程中实现更高的灵活性。具体来讲,您可能希望导入到不同但兼容的数据类型列中,选择要导入的列子集,或者使用与形状文件属性名称不同的列名称。清单 4 演示了这些功能。

清单 4. import_counties2.sql 的内容
!erase counties.msg;
connect to sample;

drop table test.counties;

create table test.counties(
   county_name    varchar(32)
  ,state_name     varchar(25)
  ,state_fips     varchar(2)
  ,county_fips    varchar(3)
  ,area           double
  ,population2000 integer
  ,shape          db2gse.st_multipolygon
  )
  ;

!db2se import_shape sample
-fileName         counties.shp
-inputAttrColumns N(name,state_name,state_fips,cnty_fips,area,pop2000)
-srsName          nad83_srs_1
-tableSchema      test
-tableName        counties
-tableAttrColumns county_name,state_name,state_fips,county_fips,area,population2000
-createTableFlag  0
-spatialColumn    shape
-typeSchema       db2gse
-typeName         st_multipolygon
-messagesFile     counties.msg
-client 1
;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        counties
-columnName       shape
-srsName          nad83_srs_1
;

create index test.countiesidx on test.counties(shape)
extend using db2gse.spatial_index(.5, 2.0, 0);

runstats on table test.counties and indexes all;

select count(*) from test.counties;

describe table test.counties;

备注:

  1. 使用想要的列、数据类型和列名称创建该表。
  2. -inputAttrColumns 参数指定要导入的形状文件中的属性名称。
  3. -tableAttrColumns 指定要将数据导入到的表列的名称。请注意,-inputAttrColumns 和 -tableAttrColumns 名称是不同的。
  4. -createTableFlag 参数指示该表已存在,不应再创建。
  5. -typeSchema-typeName 参数指定空间列的类型。形状文件格式的一个特征是它不指定空间类型是否是一个集合 (multi),所以 import_shape 默认为 multi 类型。例如,没有必要指定 multi 类型,因为 county 多边形实际上是 MultiPolygons。但对于有些应用程序来说,要导入的数据不是 multi 类型,您可以将它们导入非 multi 列。

从点坐标创建空间表

理解点位置

分析中的许多重要的空间数据都基于点位置,包括以下位置:

  • 客户家庭位置
  • 客户移动位置
  • 店铺位置
  • 服务位置
  • 蜂窝塔位置
  • 医院

最常见的点位置表示方法是,使用纬度和经度度数,这种方法源自一种地理编码 流程、一个 GPS 或可能源自地图上的坐标。地理编码是将街道地址转换为纬度和经度值的过程。这通常由专用软件使用一个庞大的地址或街道路段数据集来执行。通常会在批量模式下执行此过程来处理大量的地址,虽然 Web 服务通常也会对用户输入的地址进行地理编码。许多公司已安装了这种软件,将位置保存在一个文件或数据库列中。请参阅 参考资料 中的 developerWorks 文章,了解使用 DB2 Spatial Extender 进行地理编码的更多详细信息。

加载和维护空间值

教程的这一节探讨加载和维护一个表中的空间值,假设已存在纬度和经度值。此示例展示了对一个医院位置表的管理。

请参见 下载 部分下载本节中使用的数据和 SQL 脚本。

从一个文件导入和创建

清单 5 给出了您可以使用命令 db2 -tvf create_hospitals.sql 执行的一个示例。

清单 5. create_hospitals.sql 的内容
drop table test.hospitals;

create table test.hospitals(name varchar(70), longitude double, latitude double);

import from "hospitals.csv" of del
modified by coldel, decpt.
method p (1, 2, 3)
messages "import.msg"
insert into test.hospitals (name, longitude, latitude);

alter table test.hospitals add column location db2gse.st_point;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        hospitals
-columnName       location
-srsName          nad83_srs_1
;

update test.hospitals
set location = db2gse.st_point(longitude, latitude,1)
;

reorg table test.hospitals;

create index test.hospitalsidx on test.hospitals(location)
extend using db2gse.spatial_index(.1, 0, 0);

runstats on table test.hospitals and indexes all;

select count(*) from test.hospitals;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

备注:

  1. 如果该表已存在,则丢弃它。仅出于测试用途才这么做。
  2. 使用名称、纬度和经度列创建医院表。
  3. 使用 DB2 import 实用工具来向一个包含分割字段的文件中的表填充名称、经度和纬度数据。
  4. 调整该表以添加一个类型为 db2gse.ST_Point 的列。如果该表已包含纬度和经度,则可以跳过前面的步骤。
  5. db2se register_spatial_column 命令创建一个约束,要求所有空间值使用 SRS nad83_srs_1。它还在 db2gse.st_geometry_columns 视图中创建一个条目,将此 SRS 与这个空间列相关联。
  6. 创建空间值的工作可使用一个 SQL 更新语句轻松完成,该语句将位置列设置为 ST_Point 构造函数的结果,该函数接受经度、纬度和 srid 值。一定要注意,首先是经度值,您常常会按相反的顺序使用。srid 值 1 表示应该使用 NAD83 的空间参考系统。
  7. 在更新位置列的流程增加了行大小并可能导致页面溢出时,调用 DB2 reorg 实用程序。
  8. 使用合理的值创建一个空间索引。
  9. 执行 runstats 命令来更新表统计信息。在加载数据之后或进行重大的数据更新之后这么做始终是一个不错的想法。
  10. 查询行数并选中 New York 医院的一些行。使用 db2gse.ST_AsText 空间函数来返回空间值的可读表示。

维护空间值

一个位置表很可能需要更新内容,并且重要的是确保在插入或更新行时适当地设置或更新空间值。完成此任务的 SQL 机制是在更改纬度和经度值时在表上建立触发器。

清单 6 给出了一个创建触发器和测试结果的示例。

清单 6. create_hospitals_triggers.sql 的内容
create trigger test.hosp_loc_update no cascade
before update of latitude, longitude
on test.hospitals
referencing  new as n
for each row mode db2sql
set n.location = db2gse.st_point(n.longitude, n.latitude, 1)
;

create trigger test.hosp_loc_insert no cascade
before insert
on test.hospitals
referencing new as n
for each row mode db2sql
set n.location = db2gse.st_point(n.longitude, n.latitude, 1)
;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

insert into test.hospitals(name, longitude, latitude)
values('New York2', -76.5, -42.3)
;

update test.hospitals
set (longitude, latitude) = (-76.5, -42.3)
where name = 'New York Hospital'
;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

备注:

  1. 创建了两个独立但非常相似的触发器:一个针对更新,另一个针对插入。在插入或更新行数据之前,调用空间构造函数来返回 ST_Point 值,该值将存储在位置列中。
  2. 要查看触发器的效果,我们选中了将修改的行,如 清单 7 所示。
清单 7. 要修改的行
NAME                      LONGITUDE  LATITUDE   LOCATION_WKT
------------------------- ---------- ---------- ------------------------------
New York Hospital           -73.9537    40.7645 POINT (-73.953751 40.764542)
New York Hospital           -73.7537    41.0275 POINT (-73.753746 41.027599)
New York State Hospital     -73.9517    41.5273 POINT (-73.951797 41.527317)
  3 record(s) selected.
  1. 插入了一个新行,并且更新了 New York Hospital 行。您可以看到更新的位置值,如 清单 8 所示。
清单 8. 更新的位置值
.
NAME                      LONGITUDE  LATITUDE   LOCATION_WKT
------------------------- ---------- ---------- ------------------------------
New York Hospital           -76.5000   -42.3000 POINT (-76.500000 -42.300000)
New York Hospital           -76.5000   -42.3000 POINT (-76.500000 -42.300000)
New York State Hospital     -73.9517    41.5273 POINT (-73.951797 41.527317)
New York2                   -76.5000   -42.3000 POINT (-76.500000 -42.300000)
  4 record(s) selected.
  1. 出于可读性目的,我们选择了名称的第一部分,将经度和纬度转换为了十进制值,将位置列转换为已知文本 (well-known text, wkt) 表示。

使用空间索引

在大部分数据库应用程序中,一个重要的任务是定义列上的索引,以避免扫描一个表的所有列,并检查每一列是否满足查询的条件。这对于空间查询尤其重要,因为空间检查比检查简单的数字或字符值更加复杂。

在关系数据库中,通常会对于值按简单线性顺序从最小到最大排序的列创建一个索引。它还支持创建一个二进制树或 B-树 (B-tree) 索引,使数据库能够快速导航到特定的值或值的顺序范围。

空间数据在本质上是二维的(或更多维),这无法直接适用于 B-树索引,所以人们已开发出了不同的技术。Spatial Extender 中使用的方法是一种网格索引 (grid index),它将空间值与一个正方形网格相关联。线和多边形空间值可能与多个网格单元相交,而点空间值最多与一个网格单元相交。当定义网格单元大小时,您需要权衡最小化与空间值相交的单元数量的需求与最小化一个网格单元中包含的空间值数量的需求。为了有效地处理具有显著不同的大小的空间值,您可以指定最多 3 种网格单元大小。然后 Spatial Extender 会使用 DB2 B-树索引为网格单元创建索引。

使用 DB2 create index 命令和一个附加的 extend using 子句创建一个空间索引,如 清单 9 所示。

清单 9. 使用 create index 命令
create index test.countiesidx on test.counties(shape)
extend using db2gse.spatial_index(1.2, 3.0, 14.0);

指定网格单元大小的单位与坐标系统的单位相同,也就是县数据的纬度和经度。您可能已想到,县具有各种大小,所以指定最适合不同的县大小的不同的网格单元大小是有意义的。

纬度上的一度大约为 70 英里或 110 千米。经度上的一度在不同纬度上具有不同长度,在赤道上大约为 70 英里,而在 50 度的纬度上大约为 45 英里或 72 千米。

确定空间索引网格单元大小

确定一个最佳的网格单元大小或多个网格单元大小可能不太容易。一般而言,对于线和多边形空间值,网格单元大小应该比线或多边形的平均大小稍微大一点。对于点空间值,网格单元大小应该为典型查询区域宽度的 1/10。

为了简化这一确定过程,我们提供了两个工具来分析一个表中的空间值,并提供建议的网格单元大小供在创建索引时使用。不要担心这个值不太准确,在索引参数的一定变化范围内,性能都会不错。

使用 gseidx 索引建议工具

gseidx 命令行工具在安装 Spatial Extender 时就已提供,可用于所有支持的平台上。有关空间索引的重要信息和 gseidx 的详细信息,请访问 DB2 信息中心中的使用索引和视图访问空间数据 一节。

清单 10 给出了一个使用 gseidx 的简单示例。

清单 10. advise_counties.sql 的内容
!gseidx "connect to sample
  get geometry statistics
  for column test.counties(shape)
  advise";

此命令可使用命令 db2 -tvf advise_counties.sql 来执行,将产生如 清单 11 中所示的输出。

清单 11. db2 -tvf advise_counties.sql 命令的输出
Number of Rows: 3141
Number of non-empty Geometries: 3141
Number of empty Geometries: 0
Number of null values: 0

Extent covered by data:
    Minimum X: -178.217598
    Maximum X: -66.969271
    Minimum Y: 18.921786
    Maximum Y: 71.406235

Query Window Size:     Suggested Grid Sizes:           Index Entry Cost:
--------------------   -----------------------------   ----------------------
      0.01:              1.2,         3,        14             4.6
      0.02:              1.2,         3,        14             4.6
      0.05:              1.2,         3,        14             4.7
       0.1:              1.2,         3,        14             4.9
       0.2:              1.2,         3,        14             5.3
       0.5:              1.2,         3,        14             6.6
         1:              1.2,         3,        14             9.3
         2:              1.2,         3,        14              16
         5:              1.8,       3.6,        13              49
        10:              2.9,       8.7,        26             130
        20:              4.6,        14,        49             390
        50:               12,        72,         0            1700

清单 11 的前两节提供了有关分析的空间数据的统计信息。最后一节的中间 3 列提供了在创建关键空间索引时使用的建议的网格大小。

第一列包含对应于典型的查询窗口大小的值。对于显示地图的应用程序,这是将显示的区域的典型宽度。例如,如果用户最常显示一个横跨 10 英里的地图,这可能要对应于大约 0.25 度。查看该表,您可以看到建议的网格大小与 0.2 和 0.5 度的查询窗口大小是相同的,所以可以使用 (1.2, 3, 14) 来创建索引。

最后一列不是特别重要。它是将引用来满足查询的索引条目的估计数量。

Java 索引建议工具

您也可以从 Spatial Extender 网站下载一个基于 Java 的空间索引建议工具。此工具提供了一个用户界面来选择要分析的空间表和列。请参阅 参考资料 了解从何处获取此工具。

Java 索引建议工具的一个用户界面示例如 图 1 所示。

图 1. Java 索引建议工具用户界面
索引建议工具

此索引建议工具中使用的算法与 gseidx 工具不同,所得出的推荐标准也不同。但是,建议的值应该也有效。cost 值是将引用的索引页面的估算数量,不是 gseidx 工具报告的索引条目数。


探索常见问题和技巧

简化空间函数语法

所有空间函数在模式 db2gse 中定义,必须添加函数名称作为前缀,它才能被 DB2 正确识别。可采用两种方式简化空间语法。

检查 db2gse

第一种简化空间语法的方式是,告诉 DB2 假设它应该在解析函数时检查模式 db2gse。这是通过在执行涉及空间函数的查询之前使用 set current function path 语句而实现的。这样,您就能够消除函数调用上的模式名称。清单 12 给出了一个示例。

清单 12. set current function path 语句
set current function path = current function path, db2gse;
select
   substr(name, 1, 25) as name
  ,st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      DISTANCE
------------------------- ------------------------
Adventist Home              +2.42936545898570E+001
Greene County Memorial Ho   +2.27887983783443E+001
  2 record(s) selected.

使用方法表示法

第二种简化空间语法的方式是使用方法 表示法(DB2 LUW 支持此表示法),但这与其他 IBM 空间数据库产品不兼容。使用方法表示法,您可以在空间值后指定空间函数的名称,如 清单 13 所示。

清单 13. 使用方法表示法
set current function path = current function path, db2gse;
select
   substr(name, 1, 25) as name
  ,location..st_distance(st_point(-74.237449, 42.036976,1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      DISTANCE
------------------------- ------------------------
Adventist Home              +2.42936545898570E+001
Greene County Memorial Ho   +2.27887983783443E+001
  2 record(s) selected.

在后面各节中,示例没有指定模式,因为它们假设已设置了函数路径。

排序坐标

当使用纬度和经度坐标时,经度对应于 x,纬度对应于 y。当人们提供位置坐标或使用 GPS 等设备时,坐标通常采用纬度、经度的顺序,所以用户在使用纬度和经度时,非常容易按相反的顺序错误地指定坐标。

例如,要查找在纽约 Woodstock(纬度为 42.036976,经度为 -74.237449)20 英里范围内的医院,查询将按 清单 14 中那样编写。

清单 14. 坐标示例
select name
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

使用已知文本

Spatial Extender 支持使用空间值的已知文本 (WKT) 表示作为空间查询中的常量。WKT 表示是 Open Geospatial Consortium 创建的一种国际规范。请参阅 DB2 信息中心的空间一节(参阅 参考资料),了解有关 Spatial Extender 中的 WKT 支持的详细信息。

使用 WKT,清单 14 中的示例查询可以写成如 清单 15 所示。

清单 15. WKT 中的坐标示例
select name
from test.hospitals
where st_distance(location,st_point('POINT (-74.237449 42.036976)',1), 'STATUTE MILE')
      < 25.0

请注意,坐标使用空格分开,而不是您想象的使用逗号分开。

WKT 也可用于表示线和多边形空间值。一定要注意,定义坐标对的值使用空格分开,坐标对使用逗号分开。另外,当定义多边形值时,在定义多边形的坐标列表开头和末尾使用双圆括号,如 清单 16 所示。

清单 16. 定义多边形值
select name
from test.hospitals
where st_within(location,st_polygon(
'POLYGON ((-74.1 42.0, -74.1 42.1, -74.0 42.1, -74.0 42.0, -74.1 42.0))',1)) = 1

选择空间值

最常见的是,在查询中使用了空间值,但实际上不需要在结果集中将它们返回给用户或应用程序,就像前一个示例中一样。如果执行 select * from test.hospitalsselect location from test.hospitals 这样的查询,您将可能看到不是很有用的十六进制长字符串,因为这是空间数据的内部、压缩的表示方式。如果希望以可读的形式查看坐标,您需要使用 ST_AsText 函数返回 WKT 表示法的空间值。使用 SUBSTR 或 VARCHAR SQL 函数限制返回的字符数据长度是一个不错的想法,如 清单 17 中所示。

清单 17. 使用 st_astext 函数
select
   substr(name, 1, 25) as name
  ,varchar(st_astext(location), 30) as location
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LOCATION
------------------------- ------------------------------
Adventist Home            POINT (-73.785400 42.139526)
Greene County Memorial Ho POINT (-73.878181 42.230648)
  2 record(s) selected.

另一种可读的格式是地理标记语言 (Geography Markup Language, GML),它是 Open Geospatial Consortium 定义的空间值的一种 XML 表示法。您可以使用 ST_AsGML 函数选择这种格式的空间值,如 清单 18 所示。

清单 18. 使用 st_asgml 函数
select
   substr(name, 1, 25) as name
  ,varchar(st_asgml(location), 90) as location
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LOCATION
------------------------- ----------------------------------------
Adventist Home            <gml:Point srsName="EPSG:4269"><gml:pos>
                          -73.785400 42.139526</gml:pos></gml:Point>

Greene County Memorial Ho <gml:Point srsName="EPSG:4269"><gml:pos>
                          -73.878181 42.230648</gml:pos></gml:Point>
  2 record(s) selected.

当使用 Web 地图应用程序并显示数据库中的点对象的标记时,选择 x 和 y 坐标(或更可能选择经度和纬度)常常很有用。这个可以使用 ST_XST_Y 函数轻松完成,如 清单 19 中所示。

清单 19. 使用 st_x 和 st_y 函数
select
   substr(name, 1, 25) as name
  ,st_x(location) as longitude
  ,st_y(location) as latitude
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LONGITUDE                LATITUDE
------------------------- ------------------------ ------------------------
Adventist Home              -7.37854000000000E+001   +4.21395260000000E+001
Greene County Memorial Ho   -7.38781810000000E+001   +4.22306480000000E+001
  2 record(s) selected.

何时处理空间值

根据文档规定,许多能创建不同类型空间值的空间函数会返回一般类型 ST_Geometry。如果您尝试使用仅对特定空间类型有效的空间函数,您将得到一个错误,如 清单 20 所示。

清单 20. 针对不兼容的空间类型的错误消息
The invocation of routine "ST_PERIMETER" is ambiguous. The argument in position "1"
does not have a best fit.. SQLCODE=-245, SQLSTATE=428F5, DRIVER=3.62.80

例如,清单 21 中的代码返回此错误,因为函数 st_buffer 的返回类型为 st_geometry,即使实际结果将为 st_polygon 类型。

清单 21. 使用不兼容的空间类型的代码示例
 select
  name
 ,st_perimeter(st_buffer(location, .1)) as perimeter
from test.hospitals
where name like 'New York%'

要解决此问题,可告诉 DB2,他应该将结果处理st_polygon,如 清单 22 所示。

清单 22. 使用处理函数
select
  name
 ,st_perimeter(TREAT(st_buffer(location, .1) as db2gse.st_polygon))
  as perimeter
from test.hospitals
where name like 'New York%'

该代码可以正常执行并返回 清单 23 所示的输出。

清单 23. 使用处理函数的结果
NAME                    PERIMETER
----------------------- ------------------------
New York Hospital         +6.28206837256265E-001
New York Hospital         +6.28206837256265E-001
New York State Hospital   +6.28206837256265E-001
  3 record(s) selected.

执行空间查询

因为空间查询与任何其他 SQL 查询没什么不同,所以任何可用于执行 SQL 查询的环境都可用于执行包含空间操作的查询。在前面几节中,使用 DB2 命令行处理器,引用一个 SQL 语句文件来执行空间查询。这一节探讨其他一些执行空间查询的环境,包括 IBM Data Studio、Java 语言应用程序和 C 语言应用程序。

使用 IBM Data Studio

IBM Data Studio 是一个免费的、基于 Eclipse 的环境,用于开发和测试数据库应用程序。Data Studio 可取代 DB2 Control Center,后者已不推荐使用,不再包含在 DB2 Version 10 中。请参阅 参考资料,了解下载和设置 Data Studio 的信息。

IBM Data Studio 完整客户端支持运行 SQL 命令以及执行 Java 开发。建议在本教程的以下各节中使用完整客户端。

当安装完 IBM Data Studio 并启动它之后,执行以下步骤来设置一个执行查询的环境。

  1. 单机 File > New > Data Development Project
  2. 为项目指定一个名称,比如 SpatialProject
  3. 选择要连接到数据库。
  4. DB2GSE 附加到默认路径上,单击 Finish
  5. 右键单击左侧窗格中的项目名称,选择 New SQL or XQuery Script
  6. 为您的项目指定类似 query_hospital_counties 这样的名称,单击 Finish
  7. 将 清单 24 中的代码复制到脚本输入区域中。
清单 24. 脚本输入区域的代码
SELECT h.name, st_astext(h.location)
FROM test.hospitals AS h, test.counties AS c
WHERE st_within(h.location, c.shape) = 1
AND c.state_name = 'New York'
AND c.county_name = 'Greene';
  1. Ctrl+S 保存脚本。
  2. F5 运行查询。查询将运行并显示一个屏幕,如 图 2 所示。单击 Result1 选项卡以查看您的实际结果。
图 2. Data Studio 中的县查询中的医院
第一次查询运行的 Data Studio 结果

点击查看大图

图 2. Data Studio 中的县查询中的医院

第一次查询运行的 Data Studio 结果

对 清单 25 中所示距离的 SQL 查询重复上述步骤。

清单 25. SQL 距离查询
select
  name
 ,st_astext(location) as location_wkt
 ,st_distance(location, st_point(-74.237449, 42.036976, 1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location, st_point(-74.237449, 42.036976, 1), 'STATUTE MILE') < 25.0
order by distance

查询运行并显示如 图 3 所示的屏幕。

图 3. Data Studio 中的医院距离查询
Data Studio 医院距离查询结果

在 Java 应用程序中使用 JDBC 接口

本节介绍如何在 Data Studio 环境中使用 JDBC 运行空间查询。如果安装了一个 JAVA SDK,也可以在一个命令行环境中简单地编译和执行 下载 部分中的示例 JDBC 程序。

完成以下步骤以在 Data Studio 中创建一个 Java 开发项目,执行您作为 SQL 脚本运行的相同查询。

  1. 单击 File > New > Java Development Project
  2. 为您的项目指定一个名称,比如 SpatialJDBCProject,单击 Next
  3. 在 Java Settings 窗口中,单击 Libraries 表。
  4. 单击 Add External JARs 按钮,并导航到您的 DB2 安装中的 sqllib\java 目录。
  5. 选择 db2jcc.jardb2jcc_license_cu.jar 文件,并单击 Finish
  6. 右键单击新项目下的 src 节点,并单击 New > Class
  7. 指定 JDBCSpatialQuery 作为您的类名称,并单击 Finish
  8. 在文本编辑器中打开 下载 部分中的示例 Java 程序,复制所有文本,并替换 Data Studio 编辑器窗口中最简单的类定义。
  9. 单击 Ctrl+S 保存文件。
  10. 如果未使用 SAMPLE 数据库,那么修改源代码以替换您的数据库的名称,并保存文件。
  11. 在编辑器窗口中右键单击,并选择 Run As > Java Application。图 4 显示了底部窗格中的控制台窗口。
图 4. Data Studio 中的 JDBC 查询
JDBCSpatialQuery 的 Data Studio 输出

应用程序解释

静态的 main 函数使用默认的当前用户身份验证或指定的用户 ID 和密码(如果已作为参数提供),创建到 SAMPLE 数据库的连接。创建该类的一个实例,然后调用每个实例方法,使用数据库连接和合适的参数执行查询。

函数 hospitalInCounty1

本节介绍了最简单但不是最有效的方法。在 Data Studio 脚本编辑器中,采用与本教程前面执行的查询相同的方法来构造 SQL 查询,将字符串中针对县和州名称的参数值替换为常量。创建一个 Java statement 对象,然后将 SQL 查询传递给 executeQuery 方法。然后设置一个循环来读取结果集的每一行,以文本字符串的形式获取名称和位置(它们是控制台上的输出)。完成此过程后,结果集和语句将关闭。清单 26 给出了一个示例。

清单 26. 使用常量的示例
 void hospitalInCounty1(Connection con, String stateName, String countyName)
                        throws SQLException {
                Statement stmt;
                ResultSet rs;
                // Query hospitals that are within the specified state and county
                String sel1 = "SELECT h.name, db2gse.st_astext(h.location) "
                                + "FROM test.hospitals AS h, test.counties AS c "
                                + "WHERE db2gse.st_within(h.location, c.shape) = 1 "
                                + "AND c.state_name = '" + stateName + "' "
                                + "AND c.county_name = '" + countyName + "' ";

                System.out.println("\n\nQuery hospitals in " + countyName + ", "
                                + stateName);
                stmt = con.createStatement();
                rs = stmt.executeQuery(sel1);

                // display the result set
                // rs.next() returns false when there are no more rows
                while (rs.next()) {
                        String name1 = rs.getString(1);
                        String location = rs.getString(2);
                        System.out.println("Hospital name: '" + name1 + "'; location: "
                                        + location);
                }
                rs.close();
                stmt.close();
        }

函数 hospitalInCounty2

此函数类似于 清单 26,但它在参数中使用参数标记来代替常量。如果将重复执行类似的 SQL 语句,这特别重要,因为 DB2 将缓存最近编译的 SQL 语句并在它再次看到它们时重用,即使它们来自不同的应用程序。在多个用户使用不同的值运行同一个应用程序时,这可能很有用。

要使用参数标记,请在 SQL 语句中放置一个问号 (?) 字符,代替实际的参数值。从连接中创建一个 PreparedStatement 对象,传入 SQL 语句。然后调用 setString 方法来设置州和县名称的参数值。该逻辑的剩余部分相同。

如果将在同一个应用程序中多次执行同一条 SQL 语句,保存 PreparedStatement 并在提供新参数值时重用它甚至更有效。清单 27 给出了一个示例。

清单 27. 使用问号代替参数值的示例
void hospitalInCounty2(Connection con, String stateName, String countyName)
                        throws SQLException {
                PreparedStatement pstmt;
                ResultSet rs;
                // Query hospitals that are within the specified state and county
                String sel1 = "SELECT h.name, db2gse.st_astext(h.location) "
                                + "FROM test.hospitals AS h, test.counties AS c "
                                + "WHERE db2gse.st_within(h.location, c.shape) = 1 "
                                + "AND c.state_name = ?" + "AND c.county_name = ? ";

                System.out.println("\n\nQuery hospitals in " + countyName + ", "
                                + stateName);
                pstmt = con.prepareStatement(sel1);
                pstmt.setString(1, stateName); // set state name parameter
                pstmt.setString(2, countyName); // set county name parameter
                rs = pstmt.executeQuery();

                // display the result set
                // rs.next() returns false when there are no more rows
                while (rs.next()) {
                        String name1 = rs.getString(1);
                        String location = rs.getString(2);
                        System.out.println("Hospital name: '" + name1 + "'; location: "
                                        + location);
                }
                rs.close();
                pstmt.close();
        }

函数 hospitalDistance

这个 hospitalDistance 函数实现查找离指定位置 25 英里内的医院的查询。该方法也为指定位置的坐标使用参数标记。请注意,在 SQL 语句中使用了 CAST 表达式。清单 28 给出了一个示例。

清单 28. 使用 hospitalDistance 函数
String sel2 = "SELECT name, db2gse.st_astext(location), "
            + "db2gse.st_distance(location, db2gse.st_point(CAST (? AS DOUBLE), "
            + "CAST (? AS DOUBLE), 1), 'STATUTE MILE') AS distance "
            + "FROM test.hospitals "
            + "WHERE db2gse.st_distance(location, "
            + "db2gse.st_point(CAST (? AS DOUBLE), CAST (? AS DOUBLE), 1), "
            + "STATUTE MILE')  < 25.0 "
            + "ORDER BY distance";

为了正确识别点构造函数,有必要显式将经度和纬度参数转换为双精度值。

在 C 语言应用程序中使用 CLI 接口

DB2 Spatial Extender 的安装在 sqllib/samples/extenders/spatial 目录中包含一个样例程序 gseRunGseDemo,可直接执行它来验证空间安装,方法是加载空间数据,执行各种空间存储过程,然后对加载的数据执行示例空间查询。gseRunGseDemo 是一个 C 语言应用程序,它使用 DB2 CLI 接口并以源代码形式提供,您可以在自己的环境中检查、修改和编译它。该应用程序记录在 DB2 信息中心的编写应用程序和使用示例程序 主题下的空间一节中(请参阅 参考资料)。

使用其他应用程序语言

一般而言,任何支持对 DB2 执行 SQL 语句的语言都可用于执行空间查询。这对于面向 Web 的语言尤其有用,比如 PHP、Ruby 和 Perl,可将它们与 Google、Yahoo 和 Esri ArcGIS Online 等服务所提供的 Web 地图 API 结合使用。


结束语

本系列的第一篇教程介绍了 DB2 空间功能、导入和创建空间数据的方法,以及执行空间查询的环境。后续教程将介绍典型的空间用例,调价以实现最佳性能,使用数据仓库环境,以及使用可视化工具。

致谢

感谢 Amyris Rada 为改进本教程而进行的审核和提供的建议。


下载

描述名字大小
本教程的样例 SQL 脚本和 Java 代码sqlscripts.zip5KB
本教程的样例空间数据sampledata.zip1400KB

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=821543
ArticleTitle=使用 IBM DB2 Spatial Extender 管理空间数据,第 1 部分: 获取空间数据和开发应用程序
publish-date=06182012