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

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

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

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

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

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

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

1 引言

廣西省林業(yè)廳2009年組織進(jìn)行了全省森林資源二類調(diào)查。要求以國有林場、自然保護(hù)區(qū)、森林公園、營造林公司等森林經(jīng)營單位或縣級行政區(qū)域?yàn)檎{(diào)查總體，以小班為調(diào)查單元，以摸清森林、林地和林木資源種類、數(shù)量、質(zhì)量與分布，客觀反映調(diào)查區(qū)域自然、社會經(jīng)濟(jì)條件，綜合分析與評價森林資源與經(jīng)營管理現(xiàn)狀，提出森林資源培育、保護(hù)與利用意見為主要任務(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ù)的整理時間緊、工作任務(wù)重，多個管理系統(tǒng)數(shù)據(jù)庫中表的數(shù)據(jù)結(jié)構(gòu)不同，及多人的獨(dú)自操作，數(shù)據(jù)比較分散，給數(shù)據(jù)庫后期整理帶來許多不便。本文主要采用在 Foxpro交互環(huán)境下SQL命令的應(yīng)用來處理數(shù)據(jù)庫, 以提高數(shù)據(jù)的處理質(zhì)量，并進(jìn)行不同數(shù)據(jù)源的快速合并，起到了節(jié)省工作時間，提高工作效率的作用[1]。以下以廣西賀州市八步區(qū)森林資源二類調(diào)查數(shù)據(jù)整理為例，介紹在 Foxpro SQL交互環(huán)境下數(shù)據(jù)整理的方法與技巧。

2 SQL 簡介

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

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

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

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

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

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

3.1生成小班ID號

小班ID號(xbid)生成用于以后數(shù)據(jù)庫與viewGIS生成的小班圖層連庫，以及表合并時起關(guān)鍵字索引作用，小班ID號要求為字符型9位(鄉(xiāng)[2]村[2]林班[2]小班[3])才能與viewGIS小班圖層對接,最高位沒有數(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)接時會出錯，所以必須統(tǒng)一格式，而近百個表結(jié)構(gòu)如果手工逐個改正，且多個表要重復(fù)操作，非常繁瑣，改過以后，也無法保證完全正確。如此一來，工作效率便非常低下。

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

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

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

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

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

以下程序是以‘小班ID號’(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)識

use tmp1 *使用tmp1表

k=0

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

k=k+1

repl autoID with k

endscan

*選取xbid列中相同的編號最小的autoID表，去除小班號重復(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 臨時表

drop table tmp2 *刪除tmp2.dbf 臨時表

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

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

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

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

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

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

5.1顯示邏輯錯誤

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

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

5.2邏輯判斷修正

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

例如根據(jù)林學(xué)知識及廣西森林資源規(guī)劃設(shè)計的要求，對‘不是用材近成過熟林(或者是竹林)，出材率等級>0’(即是竹林或者不是用材近成過熟林的小班要求出材率等級不填)，下面通過查找代碼表列出判斷條件進(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)為近成熟林，出材率等級(ccldj>0)為有出材率等級。

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

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

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

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

*由聚類函數(shù)(sum)生成分鄉(xiāng)鎮(zhèn)各樹種的總面積表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 樹種=‘杉木’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 樹種=‘馬尾松’into table temp3

下面是上面命令一個示例

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

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

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

數(shù)據(jù)準(zhǔn)備，以小班數(shù)據(jù)庫為基礎(chǔ)，分別優(yōu)勢樹種(組)計算各年齡小班每公頃蓄積量的平均值和標(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 樹種,年齡,sum(slxj)/sum(yxmj) as pjxj,count(nl1) as count from ccc group by 樹種,年齡 into table ccc2

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

&& 由 ccc.dbf,ccc2.dbf生成各小班蓄積的平方差及增加各年齡小班總數(shù)與平均蓄積列的表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中剔除‘小班平方差’少于‘總體平方差’異常數(shù)據(jù)后的表ccc6.dbf

8 結(jié)語

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

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

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

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

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

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

[6] 劉佳升，闞喜忠，朱洪坤. 森林資源二類調(diào)查數(shù)據(jù)處理系統(tǒng)介紹[J]. 林業(yè)勘查設(shè)計,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