劉建聰，石軍南，徐 強(qiáng)

(中南林業(yè)科技大學(xué), 湖南 長(zhǎng)沙 410004)

SQL在森林資源二類調(diào)查數(shù)據(jù)整理中的應(yīng)用
——以廣西賀州市八步區(qū)項(xiàng)目為例

劉建聰，石軍南，徐 強(qiáng)

(中南林業(yè)科技大學(xué), 湖南 長(zhǎng)沙 410004)

森林資源二類調(diào)查數(shù)據(jù)的錄入和整理是一項(xiàng)繁瑣而又費(fèi)時(shí)的工作，數(shù)據(jù)的完整性、準(zhǔn)確性等影響到數(shù)據(jù)庫(kù)的質(zhì)量。利用數(shù)據(jù)庫(kù)中簡(jiǎn)單易學(xué)的標(biāo)準(zhǔn)SQL小程序?qū)崿F(xiàn)森林資源二類調(diào)查數(shù)據(jù)庫(kù)快速整理，如批量修改表結(jié)構(gòu)，檢查調(diào)查數(shù)據(jù)邏輯、重復(fù)小班號(hào)處理、多個(gè)關(guān)聯(lián)表合成一個(gè)表、分類統(tǒng)計(jì)，數(shù)學(xué)模型擬合時(shí)剔除異常小班等。實(shí)驗(yàn)表明，利用SQL進(jìn)行森林資源二類調(diào)查數(shù)據(jù)整理，節(jié)省了人力、物力、工作時(shí)間，提高了數(shù)據(jù)規(guī)范性和數(shù)據(jù)庫(kù)質(zhì)量。

SQL; 二類調(diào)查; 小班; Foxpro; 數(shù)據(jù)錄入

1 引言

廣西省林業(yè)廳2009年組織進(jìn)行了全省森林資源二類調(diào)查。要求以國(guó)有林場(chǎng)、自然保護(hù)區(qū)、森林公園、營(yíng)造林公司等森林經(jīng)營(yíng)單位或縣級(jí)行政區(qū)域?yàn)檎{(diào)查總體，以小班為調(diào)查單元，以摸清森林、林地和林木資源種類、數(shù)量、質(zhì)量與分布，客觀反映調(diào)查區(qū)域自然、社會(huì)經(jīng)濟(jì)條件，綜合分析與評(píng)價(jià)森林資源與經(jīng)營(yíng)管理現(xiàn)狀，提出森林資源培育、保護(hù)與利用意見(jiàn)為主要任務(wù)的森林資源調(diào)查。本次調(diào)查主要工作包括， 以遙感圖和地形圖為底圖，參考2003年生態(tài)公益林的區(qū)劃界定數(shù)據(jù)和退耕還林工程檢查驗(yàn)收數(shù)據(jù), 進(jìn)行小班區(qū)劃與小班因子調(diào)查，并采用總體蓄積抽樣控制調(diào)查方法控制森林蓄積調(diào)查質(zhì)量，要求2009年底完成內(nèi)業(yè)。由于森林資源二類調(diào)查數(shù)據(jù)的整理時(shí)間緊、工作任務(wù)重，多個(gè)管理系統(tǒng)數(shù)據(jù)庫(kù)中表的數(shù)據(jù)結(jié)構(gòu)不同，及多人的獨(dú)自操作，數(shù)據(jù)比較分散，給數(shù)據(jù)庫(kù)后期整理帶來(lái)許多不便。本文主要采用在 Foxpro交互環(huán)境下SQL命令的應(yīng)用來(lái)處理數(shù)據(jù)庫(kù), 以提高數(shù)據(jù)的處理質(zhì)量，并進(jìn)行不同數(shù)據(jù)源的快速合并，起到了節(jié)省工作時(shí)間，提高工作效率的作用[1]。以下以廣西賀州市八步區(qū)森林資源二類調(diào)查數(shù)據(jù)整理為例，介紹在 Foxpro SQL交互環(huán)境下數(shù)據(jù)整理的方法與技巧。

2 SQL 簡(jiǎn)介

SQL全稱是“結(jié)構(gòu)化查詢語(yǔ)言(Structured Query Language)”，SQL是高級(jí)的非過(guò)程化編程語(yǔ)言，允許用戶在高層數(shù)據(jù)結(jié)構(gòu)上工作。它不要求用戶指定對(duì)數(shù)據(jù)的存放方法，也不需要用戶了解具體的數(shù)據(jù)存放方式，使得具有完全不同底層結(jié)構(gòu)的不同數(shù)據(jù)庫(kù)系統(tǒng)可以使用相同的SQL語(yǔ)言作為數(shù)據(jù)輸入與管理的接口。它以記錄集合作為操作對(duì)象，所有SQL語(yǔ)句接受集合作為輸入，返回集合作為輸出，這種集合特性允許一條SQL語(yǔ)句的輸出作為另一條SQL語(yǔ)句的輸入，所以SQL語(yǔ)句可以嵌套，這使他具有極大的靈活性和強(qiáng)大的功能，在多數(shù)情況下，在其他語(yǔ)言中需要一大段程序?qū)崿F(xiàn)的功能只需要一個(gè)SQL語(yǔ)句就可以達(dá)到目的，這也意味著用SQL語(yǔ)言可以寫出非常復(fù)雜的語(yǔ)句，SQL語(yǔ)言結(jié)構(gòu)簡(jiǎn)潔，功能強(qiáng)大，簡(jiǎn)單易學(xué)，所以SQL語(yǔ)言得到了廣泛的應(yīng)用。如今無(wú)論是像Oracle,Sybase,Informix,SQL server這些大型的數(shù)據(jù)庫(kù)管理系統(tǒng)，還是像Visual Foxpro,PowerBuilder這些微機(jī)上常用的數(shù)據(jù)庫(kù)開(kāi)發(fā)系統(tǒng)，都支持SQL語(yǔ)言作為查詢語(yǔ)言[2]。

Visual Foxpro除了支持傳統(tǒng)的SQL語(yǔ)言命令之外,還提供了對(duì)程序語(yǔ)言的支持[3]。本文主要使用了Foxpro SQL中select(或sele 縮寫)查詢語(yǔ)句，用TXT文檔寫好程序(也可用Foxpro提供的編輯器)，再點(diǎn)擊“程序——運(yùn)行” 選擇程序文件確認(rèn)，即完成程序的操作(或直接在命令行中運(yùn)行‘do *.txt’)，由于SQL利用程序代碼少，易操作，能完成較復(fù)雜的數(shù)據(jù)整理，為非專業(yè)程序員提供很好的平臺(tái)。

3 數(shù)據(jù)庫(kù)表的合并

數(shù)據(jù)庫(kù)表的合并是指把分散的小班調(diào)查表以統(tǒng)一數(shù)據(jù)結(jié)構(gòu)合并到一起，便于后期的數(shù)據(jù)邏輯檢查、匯總與分析。

以下是在數(shù)據(jù)整理中用到的部分?jǐn)?shù)據(jù)庫(kù)結(jié)構(gòu)表：

列注譯列標(biāo)識(shí)類型列注譯列標(biāo)識(shí)類型鄉(xiāng)(分場(chǎng))號(hào)xhC土地所有權(quán)ssxjbhN村(工區(qū))號(hào)chC土地種類tdzlC林班號(hào)lbhC出材率等級(jí)ccldjC經(jīng)營(yíng)班號(hào)jybhC林種lzC小班號(hào)xbhN采運(yùn)可及度kjdC土地所有權(quán)ssxjbhN………………

3.1生成小班ID號(hào)

小班ID號(hào)(xbid)生成用于以后數(shù)據(jù)庫(kù)與viewGIS生成的小班圖層連庫(kù)，以及表合并時(shí)起關(guān)鍵字索引作用，小班ID號(hào)要求為字符型9位(鄉(xiāng)[2]村[2]林班[2]小班[3])才能與viewGIS小班圖層對(duì)接,最高位沒(méi)有數(shù)字時(shí)要用“0”填充。以下是用到的主要命令：

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

例如xh=2,ch=3,lbh=2,xbh=1，經(jīng)處理可得‘020302001’字符串。

3.2批量修改表結(jié)構(gòu)及表的合并

由于不同森林二類調(diào)查數(shù)據(jù)管理軟件，不同數(shù)據(jù)錄入系統(tǒng)表結(jié)構(gòu)名和數(shù)據(jù)類型不同，進(jìn)行聯(lián)接時(shí)會(huì)出錯(cuò)，所以必須統(tǒng)一格式，而近百個(gè)表結(jié)構(gòu)如果手工逐個(gè)改正，且多個(gè)表要重復(fù)操作，非常繁瑣，改過(guò)以后，也無(wú)法保證完全正確。如此一來(lái)，工作效率便非常低下。

由于命令重復(fù)率比較高，為了提高數(shù)據(jù)庫(kù)表結(jié)構(gòu)轉(zhuǎn)換的效率，使用alter命令進(jìn)行修改(* 為注譯符)。

統(tǒng)一所有的表結(jié)構(gòu)后用append 命令依次追加合并,最后得到統(tǒng)一的表。

settalkoffcopytoxbdc *將原始文件復(fù)制到新文件xbdc.dbf中。use*開(kāi)閉當(dāng)前表usexbdc*打開(kāi)新表altertablexbdcrenamecolumnatoxh *改變表結(jié)構(gòu)名鄉(xiāng)鎮(zhèn)號(hào)'a'為統(tǒng)一編號(hào)'xh'altertablexbdcalter xhfloat(10,1)*然后接改變表結(jié)構(gòu)名‘xh’的數(shù)據(jù)類型為 float(10,1)…… *此處有近百條類似命令行因篇幅所限,未一一列出。settalkonreturn

4 小班不完全相同重復(fù)處理

由于輸入調(diào)查卡任務(wù)量大、人比較多會(huì)出現(xiàn)一些不完全相同的重復(fù)數(shù)據(jù)，而用distinct命令只能刪除完全相同的重復(fù)數(shù)據(jù)，在二類數(shù)據(jù)整理中由于表結(jié)構(gòu)字符數(shù)據(jù)不統(tǒng)一，如填多個(gè)調(diào)查員時(shí)名字順序不同、字符型日期格式不一樣等情況。而出現(xiàn)不完全相同重復(fù)用編程來(lái)實(shí)現(xiàn)比用手動(dòng)有效率，且不易出錯(cuò)。

以下程序是以‘小班ID號(hào)’(xbid)不重復(fù)為標(biāo)準(zhǔn)刪除重復(fù)小班。

select distinct*from tmpp into table tmp1 *首先去除完全重復(fù)

drop table tmpp

alter table tmp1 add autoID num(10) *表中增加‘a(chǎn)utoID’列用于標(biāo)識(shí)

use tmp1 *使用tmp1表

k=0

scan *在表‘a(chǎn)utoID’列中為每行數(shù)據(jù)符一個(gè)唯一編號(hào)

k=k+1

repl autoID with k

endscan

*選取xbid列中相同的編號(hào)最小的autoID表，去除小班號(hào)重復(fù)數(shù)據(jù)

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

*用autoID列表選取數(shù)據(jù)

select*into table 去重復(fù) from tmp1 where autoID in(select autoID from tmp2)

drop table tmp1 *刪除tmp1.dbf 臨時(shí)表

drop table tmp2 *刪除tmp2.dbf 臨時(shí)表

use去重復(fù) *打開(kāi)‘去重復(fù).dbf ’

以下是程序運(yùn)行后的效果:

xbidautoIDauthor處理之前1020300323……張三、李四1020300324……李四、張三1020300425……王二、小劉處理之后1020300323……張三、李四1020300425……王二、小劉

5 檢查調(diào)查數(shù)據(jù)邏輯

數(shù)據(jù)邏輯檢查是整個(gè)調(diào)查數(shù)據(jù)好壞的關(guān)鍵步驟，需要認(rèn)真把關(guān)。因?yàn)楹笃诘臄?shù)據(jù)處理都是以這個(gè)數(shù)據(jù)庫(kù)為標(biāo)準(zhǔn)，對(duì)異常數(shù)據(jù)要及時(shí)找原數(shù)據(jù)進(jìn)行修改，某些選項(xiàng)有邏輯關(guān)系的可通過(guò)邏輯判斷修正。

數(shù)據(jù)邏輯檢查首先是通過(guò)對(duì)樹(shù)木生長(zhǎng)規(guī)律及規(guī)劃設(shè)計(jì)的要求進(jìn)行條件判斷，得到一個(gè)條件判斷總表，然后查找列出要修改的行。

5.1顯示邏輯錯(cuò)誤

顯示邏輯錯(cuò)誤可用select、browse命令，為了便于修改用browse命令比較方便，可直接在列出表格中修改。使用示例： browse for 樹(shù)高>50。

使用Select 可能要繁瑣一些，不是特別情況邏輯檢查不用它，主要用于查詢表的導(dǎo)出。

5.2邏輯判斷修正

在檢查數(shù)據(jù)邏輯時(shí)，部分選項(xiàng)有邏輯關(guān)系的可通過(guò)邏輯判斷修正，常會(huì)出現(xiàn)有些選項(xiàng)要輸入時(shí)漏輸或輸錯(cuò)的，通過(guò)邏輯判斷進(jìn)行糾正，在Foxpro中主要用到‘replace(repl)列名稱 with 新值for 條件 ’ 命令，也可用SQL中標(biāo)準(zhǔn)的更新語(yǔ)句:‘UPDATE 表名稱 SET 列名稱 = 新值 WHERE 條件’。

例如根據(jù)林學(xué)知識(shí)及廣西森林資源規(guī)劃設(shè)計(jì)的要求，對(duì)‘不是用材近成過(guò)熟林(或者是竹林)，出材率等級(jí)>0’(即是竹林或者不是用材近成過(guò)熟林的小班要求出材率等級(jí)不填)，下面通過(guò)查找代碼表列出判斷條件進(jìn)行修正：

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)為近成熟林，出材率等級(jí)(ccldj>0)為有出材率等級(jí)。

6 統(tǒng)計(jì)分析表的生成

統(tǒng)計(jì)分析表的生成，在統(tǒng)計(jì)分析表時(shí)，了解林業(yè)資源狀況，有可能出現(xiàn)原有的管理系統(tǒng)報(bào)表不能滿足需求，或者不同時(shí)期的報(bào)表不一樣，但是短時(shí)間需要且不急于程序開(kāi)發(fā)時(shí)，用Foxpro處理是一個(gè)很好的替代品。這里主要用到Select查詢中g(shù)roup多元分組、聚類函數(shù)及JOIN連接命令使列排列變換為橫排列。

下面假如要對(duì)小班數(shù)據(jù)表進(jìn)行分類統(tǒng)計(jì)，要求得到分鄉(xiāng)鎮(zhèn)各樹(shù)種的總面積。

sele 鄉(xiāng)鎮(zhèn),樹(shù)種,sum(面積) as 總面積 from 總表 group by鄉(xiāng)鎮(zhèn),樹(shù)種into table temp1

*由聚類函數(shù)(sum)生成分鄉(xiāng)鎮(zhèn)各樹(shù)種的總面積表temp1.dbf

sele鄉(xiāng)鎮(zhèn) from總表group by鄉(xiāng)鎮(zhèn)into table temp2

*生成鄉(xiāng)鎮(zhèn)表temp2.dbf

sele temp2.*,temp1.總面積 as總面積from temp2 left join temp1 on temp2. 鄉(xiāng)鎮(zhèn)= temp1. 鄉(xiāng)鎮(zhèn)and 樹(shù)種=‘杉木’into table temp3

* JOIN連接命令使第一類列排列變換為橫排列

sele temp3.*,temp1.總面積 as總面積from temp3 left join temp1 on temp3. 鄉(xiāng)鎮(zhèn)= temp1. 鄉(xiāng)鎮(zhèn)and 樹(shù)種=‘馬尾松’into table temp3

下面是上面命令一個(gè)示例

總表.dbfxbid鄉(xiāng)鎮(zhèn)樹(shù)種面積20202001鋪門馬尾松1220202002鋪門杉木221202003靈峰杉木521202004靈峰馬尾松821202005靈峰馬尾松16

temp3.dbf馬尾松杉木鋪門122靈峰245

7 異常數(shù)據(jù)的剔除

數(shù)據(jù)準(zhǔn)備，以小班數(shù)據(jù)庫(kù)為基礎(chǔ)，分別優(yōu)勢(shì)樹(shù)種(組)計(jì)算各年齡小班每公頃蓄積量的平均值和標(biāo)準(zhǔn)差，以0.5～1.0倍標(biāo)準(zhǔn)差剔除數(shù)據(jù)異常的小班后，得到模型擬合基礎(chǔ)數(shù)據(jù)。

主要代碼如下(&&為注譯符)：

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

&& count: n 各年齡小班總數(shù) xb_xj:(xi-_x)*(xi-_x) 小班蓄積的平方差

&& bjxj:all(xb_xj) 各年齡總體平方差 slxj：小班面積 yxmj：小班蓄積

sele 樹(shù)種,年齡,sum(slxj)/sum(yxmj) as pjxj,count(nl1) as count from ccc group by 樹(shù)種,年齡 into table ccc2

&& 由總表ccc.dbf生成各年齡小班總數(shù)與平均蓄積的表ccc2.dbf

&& 由 ccc.dbf,ccc2.dbf生成各小班蓄積的平方差及增加各年齡小班總數(shù)與平均蓄積列的表ccc3.dbf

sele.樹(shù)種,年齡,sum(xb_xj)/count as bjxj from ccc3 group by 樹(shù)種,年齡 into table ccc4&&由ccc3.dbf生成各小班平均蓄積ccc4.dbf

&&合并兩個(gè)表ccc3.dbf,ccc4.dbf 為 ccc5.dbf

sele 樹(shù)種,年齡,sum(slxj)/sum(yxmj),sum(slxj),sum(yxmj) where xb_xj<=bjxj from ccc5 group by 樹(shù)種,年齡 into table ccc6

&&從ccc5.dbf中剔除‘小班平方差’少于‘總體平方差’異常數(shù)據(jù)后的表ccc6.dbf

8 結(jié)語(yǔ)

賀州市八步區(qū)森林資源二類調(diào)查數(shù)據(jù)庫(kù)小班有 5萬(wàn)多個(gè), 多人操作，多數(shù)據(jù)系統(tǒng)錄入，如果采用手工操作把表合并到統(tǒng)一的數(shù)據(jù)庫(kù)中, 工作量大、任務(wù)重、差錯(cuò)多。當(dāng)管理系統(tǒng)功能還不完善時(shí)新增報(bào)表，或缺少程序員維護(hù)時(shí)，問(wèn)題更為突出。而采用 SQL命令來(lái)處理數(shù)據(jù)庫(kù)有以下優(yōu)點(diǎn): (1) 適應(yīng)于各種數(shù)據(jù)庫(kù)平臺(tái)，省力、省時(shí)。 1名技術(shù)人員用編好的小程序直接運(yùn)行就可把各種森林資源二類調(diào)查數(shù)據(jù)庫(kù)統(tǒng)一起來(lái), 能大大節(jié)省人力和工作時(shí)間, 工作效率明顯提升。 (2) 數(shù)據(jù)處理靈活。專題信息管理系統(tǒng)都是針對(duì)某一方面固定功能而設(shè)計(jì)的，當(dāng)出現(xiàn)急需解決的新情況時(shí)，SQL可以很好的處理[4-6]。采用 Foxpro SQL還可以對(duì)內(nèi)外業(yè)數(shù)據(jù)進(jìn)行有效性檢查, 更好地把好數(shù)據(jù)檢查關(guān)，能節(jié)省系統(tǒng)開(kāi)發(fā)維護(hù)費(fèi)用與工程完成的時(shí)間。

[1] 鮑永剛，張英福，王德高.SQL語(yǔ)言及其在關(guān)系數(shù)據(jù)庫(kù)中的應(yīng)用[M].北京：科學(xué)出版社,2007.

[2] Faroult,Robson. SQL語(yǔ)言藝術(shù)[M]. 北京：電子工業(yè)出版社,2008.3.

[3] 周亦民，熊躍進(jìn).Visual FoxPro 6.0實(shí)用教程[M].北京：科學(xué)出版社,2002.

[4] 儲(chǔ)開(kāi)江. Foxpro在嵊州市森林資源二類調(diào)查數(shù)據(jù)更新中的應(yīng)用[J]. 林業(yè)勘查設(shè)計(jì),2007(1).72-75.

[5] 李新平，梁志強(qiáng). 森林資源二類調(diào)查數(shù)據(jù)管理系統(tǒng)[J]. 林業(yè)科技情報(bào),2009，41(1)：41-42.

[6] 劉佳升，闞喜忠，朱洪坤. 森林資源二類調(diào)查數(shù)據(jù)處理系統(tǒng)介紹[J]. 林業(yè)勘查設(shè)計(jì),2006(3)：21-22.

(責(zé)任編輯：譚著明)

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