SQL在森林资源二类调查数据整理中的应用——以广西贺州市八步区项目为例

刘建聪，石军南，徐 强

(中南林业科技大学, 湖南 长沙 410004)

SQL在森林资源二类调查数据整理中的应用
——以广西贺州市八步区项目为例

刘建聪，石军南，徐 强

(中南林业科技大学, 湖南 长沙 410004)

森林资源二类调查数据的录入和整理是一项繁琐而又费时的工作，数据的完整性、准确性等影响到数据库的质量。利用数据库中简单易学的标准SQL小程序实现森林资源二类调查数据库快速整理，如批量修改表结构，检查调查数据逻辑、重复小班号处理、多个关联表合成一个表、分类统计，数学模型拟合时剔除异常小班等。实验表明，利用SQL进行森林资源二类调查数据整理，节省了人力、物力、工作时间，提高了数据规范性和数据库质量。

SQL; 二类调查; 小班; Foxpro; 数据录入

1 引言

广西省林业厅2009年组织进行了全省森林资源二类调查。要求以国有林场、自然保护区、森林公园、营造林公司等森林经营单位或县级行政区域为调查总体，以小班为调查单元，以摸清森林、林地和林木资源种类、数量、质量与分布，客观反映调查区域自然、社会经济条件，综合分析与评价森林资源与经营管理现状，提出森林资源培育、保护与利用意见为主要任务的森林资源调查。本次调查主要工作包括， 以遥感图和地形图为底图，参考2003年生态公益林的区划界定数据和退耕还林工程检查验收数据, 进行小班区划与小班因子调查，并采用总体蓄积抽样控制调查方法控制森林蓄积调查质量，要求2009年底完成内业。由于森林资源二类调查数据的整理时间紧、工作任务重，多个管理系统数据库中表的数据结构不同，及多人的独自操作，数据比较分散，给数据库后期整理带来许多不便。本文主要采用在 Foxpro交互环境下SQL命令的应用来处理数据库, 以提高数据的处理质量，并进行不同数据源的快速合并，起到了节省工作时间，提高工作效率的作用[1]。以下以广西贺州市八步区森林资源二类调查数据整理为例，介绍在 Foxpro SQL交互环境下数据整理的方法与技巧。

2 SQL 简介

SQL全称是“结构化查询语言(Structured Query Language)”，SQL是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，使得具有完全不同底层结构的不同数据库系统可以使用相同的SQL语言作为数据输入与管理的接口。它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使他具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句，SQL语言结构简洁，功能强大，简单易学，所以SQL语言得到了广泛的应用。如今无论是像Oracle,Sybase,Informix,SQL server这些大型的数据库管理系统，还是像Visual Foxpro,PowerBuilder这些微机上常用的数据库开发系统，都支持SQL语言作为查询语言[2]。

Visual Foxpro除了支持传统的SQL语言命令之外,还提供了对程序语言的支持[3]。本文主要使用了Foxpro SQL中select(或sele 缩写)查询语句，用TXT文档写好程序(也可用Foxpro提供的编辑器)，再点击“程序——运行” 选择程序文件确认，即完成程序的操作(或直接在命令行中运行‘do *.txt’)，由于SQL利用程序代码少，易操作，能完成较复杂的数据整理，为非专业程序员提供很好的平台。

3 数据库表的合并

数据库表的合并是指把分散的小班调查表以统一数据结构合并到一起，便于后期的数据逻辑检查、汇总与分析。

以下是在数据整理中用到的部分数据库结构表：

列注译列标识类型列注译列标识类型乡(分场)号xhC土地所有权ssxjbhN村(工区)号chC土地种类tdzlC林班号lbhC出材率等级ccldjC经营班号jybhC林种lzC小班号xbhN采运可及度kjdC土地所有权ssxjbhN………………

3.1生成小班ID号

小班ID号(xbid)生成用于以后数据库与viewGIS生成的小班图层连库，以及表合并时起关键字索引作用，小班ID号要求为字符型9位(乡[2]村[2]林班[2]小班[3])才能与viewGIS小班图层对接,最高位没有数字时要用“0”填充。以下是用到的主要命令：

replace xbid with substr(str(1000000000+xh*10000000+ch*100000+lbh1000+xbh),2,9) all

例如xh=2,ch=3,lbh=2,xbh=1，经处理可得‘020302001’字符串。

3.2批量修改表结构及表的合并

由于不同森林二类调查数据管理软件，不同数据录入系统表结构名和数据类型不同，进行联接时会出错，所以必须统一格式，而近百个表结构如果手工逐个改正，且多个表要重复操作，非常繁琐，改过以后，也无法保证完全正确。如此一来，工作效率便非常低下。

由于命令重复率比较高，为了提高数据库表结构转换的效率，使用alter命令进行修改(* 为注译符)。

统一所有的表结构后用append 命令依次追加合并,最后得到统一的表。

settalkoffcopytoxbdc *将原始文件复制到新文件xbdc.dbf中。use*开闭当前表usexbdc*打开新表altertablexbdcrenamecolumnatoxh *改变表结构名乡镇号'a'为统一编号'xh'altertablexbdcalter xhfloat(10,1)*然后接改变表结构名‘xh’的数据类型为 float(10,1)…… *此处有近百条类似命令行因篇幅所限,未一一列出。settalkonreturn

4 小班不完全相同重复处理

由于输入调查卡任务量大、人比较多会出现一些不完全相同的重复数据，而用distinct命令只能删除完全相同的重复数据，在二类数据整理中由于表结构字符数据不统一，如填多个调查员时名字顺序不同、字符型日期格式不一样等情况。而出现不完全相同重复用编程来实现比用手动有效率，且不易出错。

以下程序是以‘小班ID号’(xbid)不重复为标准删除重复小班。

select distinct*from tmpp into table tmp1 *首先去除完全重复

drop table tmpp

alter table tmp1 add autoID num(10) *表中增加‘autoID’列用于标识

use tmp1 *使用tmp1表

k=0

scan *在表‘autoID’列中为每行数据符一个唯一编号

k=k+1

repl autoID with k

endscan

*选取xbid列中相同的编号最小的autoID表，去除小班号重复数据

select min(autoID) as autoID from tmp1 into table tmp2 group by xbid

*用autoID列表选取数据

select*into table 去重复 from tmp1 where autoID in(select autoID from tmp2)

drop table tmp1 *删除tmp1.dbf 临时表

drop table tmp2 *删除tmp2.dbf 临时表

use去重复 *打开‘去重复.dbf ’

以下是程序运行后的效果:

xbidautoIDauthor处理之前1020300323……张三、李四1020300324……李四、张三1020300425……王二、小刘处理之后1020300323……张三、李四1020300425……王二、小刘

5 检查调查数据逻辑

数据逻辑检查是整个调查数据好坏的关键步骤，需要认真把关。因为后期的数据处理都是以这个数据库为标准，对异常数据要及时找原数据进行修改，某些选项有逻辑关系的可通过逻辑判断修正。

数据逻辑检查首先是通过对树木生长规律及规划设计的要求进行条件判断，得到一个条件判断总表，然后查找列出要修改的行。

5.1显示逻辑错误

显示逻辑错误可用select、browse命令，为了便于修改用browse命令比较方便，可直接在列出表格中修改。使用示例： browse for 树高>50。

使用Select 可能要繁琐一些，不是特别情况逻辑检查不用它，主要用于查询表的导出。

5.2逻辑判断修正

在检查数据逻辑时，部分选项有逻辑关系的可通过逻辑判断修正，常会出现有些选项要输入时漏输或输错的，通过逻辑判断进行纠正，在Foxpro中主要用到‘replace(repl)列名称 with 新值for 条件 ’ 命令，也可用SQL中标准的更新语句:‘UPDATE 表名称 SET 列名称 = 新值 WHERE 条件’。

例如根据林学知识及广西森林资源规划设计的要求，对‘不是用材近成过熟林(或者是竹林)，出材率等级>0’(即是竹林或者不是用材近成过熟林的小班要求出材率等级不填)，下面通过查找代码表列出判断条件进行修正：

Repl ccldj with"for not(subs(lz,1,1)="3"and tdzl<>"13" and val(nlz)>2) and val(ccldj)>0

*条件定义：林种(lz="3")为用材林、土地种类(tdzl="13")为竹林、年龄组(nlz>2)为近成熟林，出材率等级(ccldj>0)为有出材率等级。

6 统计分析表的生成

统计分析表的生成，在统计分析表时，了解林业资源状况，有可能出现原有的管理系统报表不能满足需求，或者不同时期的报表不一样，但是短时间需要且不急于程序开发时，用Foxpro处理是一个很好的替代品。这里主要用到Select查询中group多元分组、聚类函数及JOIN连接命令使列排列变换为横排列。

下面假如要对小班数据表进行分类统计，要求得到分乡镇各树种的总面积。

sele 乡镇,树种,sum(面积) as 总面积 from 总表 group by乡镇,树种into table temp1

*由聚类函数(sum)生成分乡镇各树种的总面积表temp1.dbf

sele乡镇 from总表group by乡镇into table temp2

*生成乡镇表temp2.dbf

sele temp2.*,temp1.总面积 as总面积from temp2 left join temp1 on temp2. 乡镇= temp1. 乡镇and 树种=‘杉木’into table temp3

* JOIN连接命令使第一类列排列变换为横排列

sele temp3.*,temp1.总面积 as总面积from temp3 left join temp1 on temp3. 乡镇= temp1. 乡镇and 树种=‘马尾松’into table temp3

下面是上面命令一个示例

总表.dbfxbid乡镇树种面积20202001铺门马尾松1220202002铺门杉木221202003灵峰杉木521202004灵峰马尾松821202005灵峰马尾松16

temp3.dbf马尾松杉木铺门122灵峰245

7 异常数据的剔除

数据准备，以小班数据库为基础，分别优势树种(组)计算各年龄小班每公顷蓄积量的平均值和标准差，以0.5～1.0倍标准差剔除数据异常的小班后，得到模型拟合基础数据。

主要代码如下(&&为注译符)：

&& lbxj(xi):xj/mj 小班平均蓄积 pjxj(_x):sum(xj)/sum(mj) 各年龄平均蓄积

&& count: n 各年龄小班总数 xb_xj:(xi-_x)*(xi-_x) 小班蓄积的平方差

&& bjxj:all(xb_xj) 各年龄总体平方差 slxj：小班面积 yxmj：小班蓄积

sele 树种,年龄,sum(slxj)/sum(yxmj) as pjxj,count(nl1) as count from ccc group by 树种,年龄 into table ccc2

&& 由总表ccc.dbf生成各年龄小班总数与平均蓄积的表ccc2.dbf

&& 由 ccc.dbf,ccc2.dbf生成各小班蓄积的平方差及增加各年龄小班总数与平均蓄积列的表ccc3.dbf

sele.树种,年龄,sum(xb_xj)/count as bjxj from ccc3 group by 树种,年龄 into table ccc4&&由ccc3.dbf生成各小班平均蓄积ccc4.dbf

&&合并两个表ccc3.dbf,ccc4.dbf 为 ccc5.dbf

sele 树种,年龄,sum(slxj)/sum(yxmj),sum(slxj),sum(yxmj) where xb_xj<=bjxj from ccc5 group by 树种,年龄 into table ccc6

&&从ccc5.dbf中剔除‘小班平方差’少于‘总体平方差’异常数据后的表ccc6.dbf

8 结语

贺州市八步区森林资源二类调查数据库小班有 5万多个, 多人操作，多数据系统录入，如果采用手工操作把表合并到统一的数据库中, 工作量大、任务重、差错多。当管理系统功能还不完善时新增报表，或缺少程序员维护时，问题更为突出。而采用 SQL命令来处理数据库有以下优点: (1) 适应于各种数据库平台，省力、省时。 1名技术人员用编好的小程序直接运行就可把各种森林资源二类调查数据库统一起来, 能大大节省人力和工作时间, 工作效率明显提升。 (2) 数据处理灵活。专题信息管理系统都是针对某一方面固定功能而设计的，当出现急需解决的新情况时，SQL可以很好的处理[4-6]。采用 Foxpro SQL还可以对内外业数据进行有效性检查, 更好地把好数据检查关，能节省系统开发维护费用与工程完成的时间。

[1] 鲍永刚，张英福，王德高.SQL语言及其在关系数据库中的应用[M].北京：科学出版社,2007.

[2] Faroult,Robson. SQL语言艺术[M]. 北京：电子工业出版社,2008.3.

[3] 周亦民，熊跃进.Visual FoxPro 6.0实用教程[M].北京：科学出版社,2002.

[4] 储开江. Foxpro在嵊州市森林资源二类调查数据更新中的应用[J]. 林业勘查设计,2007(1).72-75.

[5] 李新平，梁志强. 森林资源二类调查数据管理系统[J]. 林业科技情报,2009，41(1)：41-42.

[6] 刘佳升，阚喜忠，朱洪坤. 森林资源二类调查数据处理系统介绍[J]. 林业勘查设计,2006(3)：21-22.

(责任编辑：谭著明)

2010 — 06 — 21

2010 — 09 — 08

S 757.2+2

B

1003 — 5710(2010)05 — 0067 — 04

10. 3969/j. issn. 1003 — 5710. 2010. 05. 021