基于Excel-VBA的公路線路坐標(biāo)正反算程序設(shè)計(jì)

鐘宏斌，劉慶元

(1.中南大學(xué) 地球科學(xué)與信息物理學(xué)院，湖南 長(zhǎng)沙 410083；2.長(zhǎng)沙市軌道交通集團(tuán)有限公司，湖南 長(zhǎng)沙 410009)

在公路與鐵路工程建設(shè)中，測(cè)量進(jìn)場(chǎng)后首要工作就是線路放樣要素的計(jì)算。Excel作為常用的辦公軟件具有較強(qiáng)的數(shù)據(jù)處理和編輯排版能力，它的公式、函數(shù)變化萬(wàn)千、應(yīng)用廣泛，但幾乎沒有在測(cè)量工程線路坐標(biāo)正反算中應(yīng)用。本文根據(jù)多年生產(chǎn)實(shí)踐和多個(gè)高速公路項(xiàng)目的實(shí)際工作經(jīng)驗(yàn)，編寫的基于Excel-VBA自定義函數(shù)的線路坐標(biāo)正反算程序，可實(shí)現(xiàn)與Excel的公式、函數(shù)融合應(yīng)用。

1 Gauss-Legendre通用公式

公路工程線型構(gòu)型復(fù)雜多變，但都是由直線、圓曲線和緩和曲線三種基本曲線組成[1-2]。本程序采用通用Gauss-Legendre公式[3-6]，可適用于這三種曲線。

1.1 曲線元上任意點(diǎn)處切線方位角的計(jì)算

文獻(xiàn)[3]已給出曲線元上任意點(diǎn)處切線方位角的計(jì)算公式：

(1)

式中，α為待求點(diǎn)切線方位角;αA為線元起點(diǎn)A處切線方位角;G為線元的偏轉(zhuǎn)系數(shù);ρA為線元起點(diǎn)A處曲率;l為待求點(diǎn)至起點(diǎn)A處曲線長(zhǎng);ρAB為線元起終點(diǎn)的曲率差;LS為線元長(zhǎng)度。

其中G的取值為1、-1或0，當(dāng)曲線元左偏時(shí)，G=-1；線元右偏時(shí)，G=1；線元為直線時(shí)，G=0。

1.2 曲線上任意點(diǎn)的坐標(biāo)計(jì)算公式

文獻(xiàn)[3]、[4]已給出線元任意點(diǎn)坐標(biāo)計(jì)算的Gauss-Legendre通用公式：

(2)

式中,X、Y為待求點(diǎn)坐標(biāo);XA、YA為線元起點(diǎn)A坐標(biāo)。

文獻(xiàn)[4]指出，當(dāng)節(jié)點(diǎn)數(shù)n=5時(shí)，式(2)即可滿足工程中所有的線路構(gòu)型的坐標(biāo)計(jì)算精度要求。5節(jié)點(diǎn)公式中Ri、Vi的值為：R1=R5=0.118 463 442 5;R2=R4=0.239 314 335 2;R3=0.284 444 444 4;V1=1-V5=0.046 910 077 0;V2=1-V4=0.230 765 344 9;V3=0.5。

將Ri、Vi的值代入式(2)，可用于計(jì)算線元上任意點(diǎn)的坐標(biāo)，即線路中樁坐標(biāo)。

邊樁坐標(biāo)的計(jì)算公式如下：

(3)

式中，B為邊樁到中樁的距離(左邊距為負(fù),右邊距為正)。

式(2)、(3)為線路任意點(diǎn)坐標(biāo)計(jì)算的Gauss-Legendre通用公式。

2 基于Excel-VBA的線路坐標(biāo)正反算程序?qū)崿F(xiàn)

本程序基于Excel-VBA，將曲線要素?cái)?shù)據(jù)庫(kù)直接錄入于工作表，計(jì)算過程以代碼封裝于VBA[7]，使用方法以函數(shù)的形式交由用戶，其程序運(yùn)行流程圖如圖1所示。

圖1 程序運(yùn)行流程圖

2.1 構(gòu)建曲線要素?cái)?shù)據(jù)庫(kù)

在空白工作表中構(gòu)建如表1所示表格格式(第一行為表名，第五行為表頭，第六行開始是線元數(shù)據(jù)，J3單元格輸入線元個(gè)數(shù)；有效數(shù)據(jù)輸入列為A-J列)。

為保證程序的正常運(yùn)行，作如下約定：(1)表格結(jié)構(gòu)須與表1一致；(2)要填寫正確的線元個(gè)數(shù)；(3)起點(diǎn)方位角單位為度(不能是度分秒的格式)，并保留盡量多的小數(shù)位數(shù)；(4)本工作表表名可以按需命名。

表1 曲線要素?cái)?shù)據(jù)庫(kù)格式

根據(jù)設(shè)計(jì)提供的《直線、曲線及轉(zhuǎn)角一覽表》，計(jì)算、提取線元要素，填寫曲線要素?cái)?shù)據(jù)庫(kù)的工作表，將工作簿命名存盤。

2.2 坐標(biāo)正算函數(shù)主程序

程序以自定義函數(shù)[8]的方式驅(qū)動(dòng)，故主程序?yàn)楹瘮?shù)，定義兩個(gè)函數(shù)名，其格式為：(1)坐標(biāo)正算X坐標(biāo)函數(shù)：ZSX("工作表名",里程,偏距)；(2)坐標(biāo)正算Y坐標(biāo)函數(shù)：ZSY("工作表名",里程,偏距)。注意函數(shù)中工作表名要用英文雙引號(hào)引起來。主程序?qū)崿F(xiàn)以下功能：

(1)判斷用戶輸入的里程、偏距和工作表名(即曲線要素?cái)?shù)據(jù)庫(kù)表名，下同)，提取相應(yīng)曲線要素。實(shí)現(xiàn)代碼[9-10]：

nCount = Sheets(WorksheetName).Cells(3, 10).Value

Fori= 1 TonCount

If Mileage >= Sheets(WorksheetName).Cells(i+ 5, 2).Value And Mileage <= Sheets(WorksheetName).Cells(i+ 5, 3).Value Then

ForJ= 1 To 9

quxian(J) = Sheets(WorksheetName).Cells(i+ 5,J+ 1).Value

NextJ

Nexti

(2)調(diào)用坐標(biāo)正算子程序計(jì)算，返回結(jié)果至單元格。

2.3 坐標(biāo)正算子程序

坐標(biāo)正算子程序采用5節(jié)點(diǎn)Gauss-Legendre通用公式，中樁坐標(biāo)計(jì)算實(shí)現(xiàn)代碼：

r(0) = 0.118 463 442 5:r(4) =r(0):r(1) = 0.239 314 335 2:r(3) =r(1):r(2)=0.284 444 444 4

v(0) = 0.046 910 077:v(4) = 1 -v(0):v(1) = 0.230 765 344 9:v(3) = 1 -v(1):v(2) = 0.5

Pi1 = Application.WorksheetFunction.pi()

Length1=Abs(MyMileage1-MyArray1(1))

resultsX1= MyArray1(3)

resultsY1= MyArray1(4)

Fori= 0 To 4

IntegralItem1 = MyArray1(5) + MyArray1(9) * (v(i) * Length1 / MyArray1(7) +Length1 ^ 2 * v(i) ^ 2 * (1 / MyArray1(8) - 1 / MyArray1(7)) / (2 * MyArray1(6)))

resultsX1 = resultsX1+ Length1 *r(i) * Cos(IntegralItem1)

resultsY1= resultsY1+ Length1 *r(i) * Sin(IntegralItem1)

Nexti

2.4 線路切線方位角計(jì)算程序

定義主程序函數(shù)：ZSA("工作表名",里程)。在函數(shù)中判斷用戶輸入的里程和工作表名，提取曲線要素，調(diào)用切線方位角子程序進(jìn)行計(jì)算，將結(jié)果返回至單元格。切線方位角子程序?qū)崿F(xiàn)代碼：

Length1 = Abs(MyMileage1 - MyArray1(1))

Azimuth1 = MyArray1(5) + MyArray1(9) * (Length1 / MyArray1(7) + (1 / MyArray1(8) - 1 / MyArray1(7)) * Length1 ^ 2 / (2 * MyArray1(6)))

2.5 坐標(biāo)反算程序

定義函數(shù)名：FSSlantDistance("工作表名",大概里程,X坐標(biāo),Y坐標(biāo))。坐標(biāo)反算程序用于根據(jù)坐標(biāo)反算里程、偏距，為防止因?yàn)槎嘀祮栴}(多由回頭曲線、水滴型曲線等特殊曲線線型產(chǎn)生)，需用戶提供大概里程(如無多值問題，大概里程可以隨意賦值，只要不超過線路里程范圍即可)。采用迭代法計(jì)算，以大概里程為里程初始值，以用戶輸入的坐標(biāo)到大概里程的中樁坐標(biāo)所在線路法線的距離為迭代值，迭代直至該距離為無窮小。實(shí)現(xiàn)過程如下：

(1)根據(jù)用戶輸入的大概里程ProbablyMileage，調(diào)用坐標(biāo)正算程序，求得該里程中樁坐標(biāo)CalX、CalY和切線方位角，并計(jì)算出切線方位角的法向方位角VerticalAzimuth。

(2)計(jì)算待求點(diǎn)(X,Y)到點(diǎn)(CalX、CalY)沿VerticalAzimuth方向的距離DeltaSlantDistance。實(shí)現(xiàn)代碼：

DeltaSlantDistance = (Y- CalY) * Cos(VerticalAzimuth) - (X- CalX) * Sin(VerticalAzimuth)

(3)判斷DeltaSlantDistance值大?。?/p>

若值足夠小，則求出點(diǎn)(X,Y)到點(diǎn)(CalX、CalY)的距離OutputSlantDistance1，返回反算結(jié)果(里程：ProbablyMileage，偏距OutputSlantDis-tance1)，跳出循環(huán)；

若值過大，以ProbablyMileage + DeltaSlantDistance作為新的大概里程，返回步驟(1)，進(jìn)入下一循環(huán)。

實(shí)現(xiàn)代碼：

If Abs(DeltaSlantDistance) < 10 ^ (-8) Then

OutputSlantDistance1 = ((Y- CalculationY) ^ 2 + (X-CalculationX) ^ 2) ^ 0.5

A1= VerticalAzimuth + 90 * PI / 180

X1= CalculationX:Y1= CalculationY:X2 =X1+ cos(A1):Y2 =Y1+ sin(A1)

If (Y1 -Y) * (X2 -X) - (X1 -X) * (Y2 -Y) > 0 Then OutputSlantDistance1 = -OutputSlantDistance1

Else

ProbablyMileage = ProbablyMileage + DeltaSlantDistance

End If

步驟(1)、(2)、(3)均在循環(huán)體內(nèi)，以上代碼省略了循環(huán)體。

至此，線路坐標(biāo)正反算的程序已具備相應(yīng)功能。

3 算 例

下面，以某項(xiàng)目的一段線路直曲表為例，驗(yàn)證編寫的線路坐標(biāo)正反算程序(以下稱本程序)正確性。設(shè)計(jì)提供曲線要素如表2所示。

表2 直線、曲線及轉(zhuǎn)角表

將表2的曲線要素按線元法錄入曲線要素?cái)?shù)據(jù)庫(kù)，并存于名為“右平”的工作表中。因該線路所有線元均以相切銜接，即下一線元起點(diǎn)坐標(biāo)、方位角為上一線元終點(diǎn)坐標(biāo)、方位角，故在錄入要素時(shí)，部分要素可用本程序直接計(jì)算。直線段半徑為無窮大，則半徑填接近于無窮大的數(shù)即可，如1090。所有曲線要素內(nèi)容如表3所示。

表3 程序曲線要素?cái)?shù)據(jù)庫(kù)工作表數(shù)據(jù)錄入表

計(jì)算結(jié)果即為完整的曲線要素表，如表4所示。

表4 程序曲線要素?cái)?shù)據(jù)庫(kù)工作表計(jì)算結(jié)果表

根據(jù)線元法的特性和以上曲線要素錄入方法，若某個(gè)線元出錯(cuò)，則線路終點(diǎn)坐標(biāo)、方位角必然出錯(cuò)，故只需復(fù)核線路終點(diǎn)的坐標(biāo)、方位角即可。程序計(jì)算結(jié)果與設(shè)計(jì)對(duì)比如表5所示。

由表5可知，本程序計(jì)算結(jié)果與設(shè)計(jì)幾乎一致，其誤差因設(shè)計(jì)《直曲表》精度低于本程序小數(shù)點(diǎn)保留位數(shù)引起。

表5 程序曲線要素?cái)?shù)據(jù)庫(kù)計(jì)算結(jié)果復(fù)核表

下面以里程K40+900右偏5 m為例，驗(yàn)證程序計(jì)算邊樁坐標(biāo)正確性。正算中樁坐標(biāo)、方位角：X=4 102.790 0;Y=8 996.693 8;α=167.305 173 179 975°。手算與本程序計(jì)算結(jié)果對(duì)比如表6所示。

表6 程序邊樁坐標(biāo)正算計(jì)算結(jié)果復(fù)核表

由表6可知，邊樁坐標(biāo)正算結(jié)果正確無誤。下面用坐標(biāo)正算程序來驗(yàn)證坐標(biāo)反算。其對(duì)比表如表7所示。

表7 程序坐標(biāo)反算計(jì)算結(jié)果復(fù)核表

由表7可知，坐標(biāo)反算的里程、偏距與正算時(shí)賦值一致，故坐標(biāo)反算程序計(jì)算正確。

筆者在多個(gè)項(xiàng)目的應(yīng)用中，也驗(yàn)證了本程序計(jì)算正確、精度達(dá)到施工要求。

4 結(jié) 語(yǔ)

隨著科技的進(jìn)步，測(cè)量?jī)x器越來越先進(jìn)，電腦與儀器的交互功能越來越強(qiáng)大、方便。本程序最大的優(yōu)勢(shì)在于：(1)通過電腦計(jì)算的線路坐標(biāo)，按照儀器所需的格式進(jìn)行編排，可方便的導(dǎo)入儀器；(2)從儀器導(dǎo)出的數(shù)據(jù)可在電腦上按施工和竣工要求進(jìn)行更多數(shù)據(jù)編輯處理任務(wù)；(3)特別是在現(xiàn)代測(cè)量過程中，坐標(biāo)計(jì)算、數(shù)據(jù)處理、格式轉(zhuǎn)換都需要與Office軟件相結(jié)合，以達(dá)到數(shù)據(jù)流無阻礙的流動(dòng)，形成我們需要的各種數(shù)據(jù)報(bào)表和應(yīng)用格式?；贓xcel-VBA自定義函數(shù)的公路線路坐標(biāo)正反算程序，采用Gauss-Legendre通用公式來統(tǒng)一計(jì)算模型，界面簡(jiǎn)潔明了，通俗易懂；可以計(jì)算直線、圓曲線、緩和曲線甚至包括不完全緩和曲線等類型線元，覆蓋了工程施工過程中所有的線路形式，功能強(qiáng)大、計(jì)算精度高。將計(jì)算程序轉(zhuǎn)為Excel自定義函數(shù)，與Excel自身功能融為一體，可隨意調(diào)用，便于排版，便于與其他軟件、儀器交互；簡(jiǎn)潔的函數(shù)輸入，避免了普通線路坐標(biāo)計(jì)算程序繁瑣的操作過程，加快了操作速度。基于Excel-VBA自定義函數(shù)的公路線路坐標(biāo)正反算程序，可以給測(cè)量人員、施工管理人員節(jié)省時(shí)間，提高工作效率，改善程序使用體驗(yàn)，適合于目前工程測(cè)量中數(shù)據(jù)的日常計(jì)算和批量處理。