Source code

Download source archive ↓

src/FluxDataCorrectionUtil_2.scala

1package cn.piflow.bundle.flux.util
2
3
4import cn.piflow.bundle.FluxJavaUtil.Theleastsquaremethod
5import org.apache.spark.sql.functions.monotonically_increasing_id
6import org.apache.spark.sql.{DataFrame, Row, SparkSession}
7
8import scala.collection.mutable.ArrayBuffer
9
10class FluxDataCorrectionUtil_2 extends Serializable {
11  val fluxDataCorrection = new FluxDataCorrectionUtil_2_Util
12
13//  二次坐标旋转(DR)
14
15  /**
16   * 二次坐标旋转(DR)
17   * @param spark
18   * @param originDF
19   * @param FcField    Fc         :  CO2通量
20   * @param LeField    Le         :  潜热通量
21   * @param HsField    Hs         :  显热通量
22   * @param u_starField    u_star         :  摩擦风速
23   * @param p06        cov_Uz_Uz	:  垂直风速方差
24   * @param p07        cov_Uz_Ux	:  垂直风速与X方向风速协方差
25   * @param p08        cov_Uz_Uy	:  垂直风速与Y方向风速协方差
26   * @param p09        cov_Uz_co2 :  垂直风速与CO2密度协方差
27   * @param p10        cov_Uz_h2o :  垂直风速与H2O密度协方差
28   * @param p11        cov_Uz_Ts  :  垂直风速与温度协方差
29   * @param p12        cov_Ux_Ux	:  X方向风速方差	p12
30   * @param p13        cov_Ux_Uy	:  X与Y方向风速协方差
31   * @param p14        cov_Ux_co2 :  X方向风速与CO2密度协方差
32   * @param p15        cov_Ux_h2o :  X方向风速与H2O密度协方差
33   * @param p16        cov_Ux_Ts  :  X方向风速与温度协方差
34   * @param p17        cov_Uy_Uy	:  Y方向风速方差
35   * @param p18        cov_Uy_co2 :  Y方向风速与CO2密度协方差
36   * @param p19        cov_Uy_h2o :  Y方向风速与H2O密度协方差
37   * @param p20        cov_Uy_Ts  :  Y方向风速与温度协方差
38   * @param p24        Ux_Avg     :  X方向平均风速
39   * @param p25        Uy_Avg     :  Y方向平均风速
40   * @param p26        Uz_Avg     :  Z方向平均风速
41   * @return
42   */
43    def DRCoordinateRotationUtil(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String,u_starField:String
44                                ,p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,p14:String,p15:String,p16:String,p17:String,
45                                 p18:String,p19:String,p20:String,
46                                 p24:String,p25:String,p26:String):DataFrame={
47
48      spark.udf.register("DrUdf" ,(p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,p14:String,p15:String,p16:String,p17:String,
49                                   p18:String,p19:String,p20:String,
50                                   p24:String,p25:String,p26:String)=>{
51
52        val coordinateRotationDoubles: Array[Double] = fluxDataCorrection.DRUtil(
53          p06.toDouble,p07.toDouble,p08.toDouble,p09.toDouble,p10.toDouble,p11.toDouble,p12.toDouble,p13.toDouble,
54          p14.toDouble,p15.toDouble,p16.toDouble,p17.toDouble, p18.toDouble,p19.toDouble,p20.toDouble,
55          p24.toDouble,p25.toDouble,p26.toDouble)
56        val Fc = coordinateRotationDoubles(0).formatted("%.5f")
57        val LE = coordinateRotationDoubles(1).formatted("%.5f")
58        val Hs = coordinateRotationDoubles(2).formatted("%.5f")
59        val u_star = coordinateRotationDoubles(3).formatted("%.5f")
60
61        Fc+","+LE+","+Hs+","+u_star
62      })
63
64      originDF.drop(FcField).drop(LeField).drop(HsField).drop(u_starField).createOrReplaceTempView("DRCoordinateRotation")
65      val resultDF = spark.sql(
66        s"""
67          |select *
68          |      ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[0] as ${FcField}
69          |      ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[1] as ${LeField}
70          |      ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[2] as ${HsField}
71          |      ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[3] as ${u_starField}
72          | from DRCoordinateRotation
73          |""".stripMargin)
74
75      resultDF
76    }
77
78  /**
79   * 平面拟合旋转(PF)
80   * @param spark
81   * @param originDF
82   * @param FcField    Fc         :  CO2通量
83   * @param LeField    Le         :  潜热通量
84   * @param HsField    Hs         :  显热通量
85   * @param u_starField    u_star         :  摩擦风速
86   * @param p06        cov_Uz_Uz	:  垂直风速方差
87   * @param p07        cov_Uz_Ux	:  垂直风速与X方向风速协方差
88   * @param p08        cov_Uz_Uy	:  垂直风速与Y方向风速协方差
89   * @param p09        cov_Uz_co2 :  垂直风速与CO2密度协方差
90   * @param p10        cov_Uz_h2o :  垂直风速与H2O密度协方差
91   * @param p11        cov_Uz_Ts  :  垂直风速与温度协方差
92   * @param p12        cov_Ux_Ux	:  X方向风速方差	p12
93   * @param p13        cov_Ux_Uy	:  X与Y方向风速协方差
94   * @param p14        cov_Ux_co2 :  X方向风速与CO2密度协方差
95   * @param p15        cov_Ux_h2o :  X方向风速与H2O密度协方差
96   * @param p16        cov_Ux_Ts  :  X方向风速与温度协方差
97   * @param p17        cov_Uy_Uy	:  Y方向风速方差
98   * @param p18        cov_Uy_co2 :  Y方向风速与CO2密度协方差
99   * @param p19        cov_Uy_h2o :  Y方向风速与H2O密度协方差
100   * @param p20        cov_Uy_Ts  :  Y方向风速与温度协方差
101   * @param p24        Ux_Avg     :  X方向平均风速
102   * @param p25        Uy_Avg     :  Y方向平均风速
103   * @param p26        Uz_Avg     :  Z方向平均风速
104   * @return
105   */
106  def PFCoordinateRotationUtil(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String,u_starField:String
107                               ,p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,p14:String,p15:String,p16:String,p17:String,
108                               p18:String,p19:String,p20:String,
109                               p24:String,p25:String,p26:String
110                              ):DataFrame={
111
112    //    平均垂直风速𝑤可以表示为水平风速(𝑢、𝑣)的线性关系函数(p24,p25)
113    // 利用最小二乘原理构建方程组,拟合回归求解 b0/b1/b2系数
114    val rows: Array[Row] = originDF.select(p24, p25, p26).collect()
115    println(rows.size)
116    val uvArray = new ArrayBuffer[Array[Double]](rows.size)
117    val wArray = ArrayBuffer[Double]()
118
119    rows.foreach(x=>{
120      val uvDouble = Array[Double](x(0).toString.toDouble, x(1).toString.toDouble)
121      uvArray.+=(uvDouble)
122      wArray.+=(x(2).toString.toDouble)
123    })
124
125    val doubles: Array[Double] = Theleastsquaremethod.train(wArray.toArray,uvArray.toArray)
126    val b0 = doubles(0)
127    val b1 = doubles(1)
128    val b2 = doubles(2)
129
130    spark.udf.register("PFUdf" ,(p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,
131                                 p14:String,p15:String,p16:String,p17:String, p18:String,p19:String,p20:String)=>{
132      val coordinateRotationDoubles: Array[Double] =fluxDataCorrection.PFUtil(
133        p06.toDouble,p07.toDouble,p08.toDouble,p09.toDouble,p10.toDouble,p11.toDouble,p12.toDouble,p13.toDouble,
134        p14.toDouble,p15.toDouble,p16.toDouble,p17.toDouble, p18.toDouble,p19.toDouble,p20.toDouble,b1,b2)
135
136      val Fc = coordinateRotationDoubles(0).formatted("%.5f")
137      val LE = coordinateRotationDoubles(1).formatted("%.5f")
138      val Hs = coordinateRotationDoubles(2).formatted("%.5f")
139      val u_star = coordinateRotationDoubles(3).formatted("%.5f")
140
141      Fc+","+LE+","+Hs+","+u_star
142    })
143
144    originDF.drop(FcField).drop(LeField).drop(HsField).drop(u_starField).createOrReplaceTempView("temp")
145    val resultDF = spark.sql(
146      s"""
147         |select *
148         |       ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[0],5)  as ${FcField}
149         |       ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[1],5)  as ${LeField}
150         |       ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[2],5)  as ${HsField}
151         |       ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[3],5)  as ${u_starField}
152         | from temp
153         |""".stripMargin)
154
155    resultDF
156  }
157
158  /**
159   *  WPL 校正
160   * @param spark
161   * @param originDF
162   * @param FcField        Fc         :  CO2通量
163   * @param LeField        Le         :  潜热通量
164   * @param HsField        Hs         :  显热通量
165   * @param p27            co2_Avg    :  平均CO2密度
166   * @param p28            h2o_Avg    :  平均H2O密度
167   * @param p29            Ts_Avg     :  平均超声空气温度
168   * @param p30            rho_a_Avg  :  平均空气密度
169   * @return
170   */
171  def wplCorrectionUtil(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String,p27:String,p28:String,p29:String,p30:String):DataFrame={
172
173    spark.udf.register("wplUdf" ,(FC:String,LE:String,Hs:String,p27:String,p28:String,p29:String,p30:String)=>{
174      
175      val wplDouble = fluxDataCorrection.wplUtil(FC.toDouble,LE.toDouble,Hs.toDouble,p27.toDouble,p28.toDouble,p29.toDouble,p30.toDouble)
176      val Fc_new = wplDouble(0).formatted("%.5f")
177      val Le_new = wplDouble(1).formatted("%.5f")
178      val Hs_new = wplDouble(2).formatted("%.5f")
179      Fc_new+","+Le_new+","+Hs_new
180    })
181
182
183    val stringBuilder = new StringBuilder
184    originDF.schema.fieldNames.foreach(x=>{
185      if(x.equals(FcField)) {
186        stringBuilder.append(s"split(wplUdf(${FcField},${LeField},${HsField},${p27},${p28},${p29},${p30}),',')[0] as  ${FcField}")
187      } else if(x.equals(LeField)){
188        stringBuilder.append(s"split(wplUdf(${FcField},${LeField},${HsField},${p27},${p28},${p29},${p30}),',')[1] as  ${LeField}")
189      } else if(x.equals(HsField)){
190        stringBuilder.append(s"split(wplUdf(${FcField},${LeField},${HsField},${p27},${p28},${p29},${p30}),',')[2] as  ${HsField}")
191      } else {
192        stringBuilder.append(x)
193      }
194      stringBuilder.append(",")
195    })
196
197    originDF.createOrReplaceTempView("wplCorrectionUtil")
198    val resultDF = spark.sql(
199      s"""
200        | select ${stringBuilder.dropRight(1)} from wplCorrectionUtil
201        |""".stripMargin)
202
203    resultDF
204  }
205
206  /**
207   * 通量储存项计算 涡度法(EC)
208   * @param spark
209   * @param originDF
210   * @param FcField    Fc	CO2通量	p1
211   * @param p27        co2_Avg	平均CO2密度	p27
212   * @param para_H     通量观测设备观测高度 39.0 米
213   * @param flagValue  标识位赋值,不符合条件
214   * @return
215   */
216  def FluxEC_new(spark:SparkSession,originDF:DataFrame,FcField:String, p27: String,para_H: String ,flagValue: String): DataFrame = {
217
218    val para_ht: Double = para_H.toDouble / 1800
219
220    val originIDDF = originDF.repartition(1).orderBy("day_id","hour")
221      .withColumn("id",monotonically_increasing_id()).repartition(2)
222    originIDDF.createOrReplaceTempView("originIDDF")
223
224    val Lv:Double= 2440
225    val Cp :Double= 1.00467*1000
226
227    originIDDF.select("id",p27).createOrReplaceTempView("local_temp")
228    val ecDF = spark.sql(
229      s"""
230        |select a.id
231        |   ,Round(if(a.${p27} = ${flagValue} or b.${p27}= ${flagValue} and (a.${p27}-b.${p27})>150,0, (a.${p27}-b.${p27}) * ${para_ht} ) ,5) as ec_fc
232        |from local_temp a
233        |left join local_temp b on a.id=b.id+1
234        |""".stripMargin)
235    ecDF.createOrReplaceTempView("ecDF")
236
237    val strSql: StringBuilder = new StringBuilder
238    originIDDF.schema.fieldNames.foreach(x=>{
239      if(     x.toUpperCase.equals(FcField.toUpperCase))  {
240        strSql.append(s"Round(if(ec_fc is not null and ec_fc <> ${flagValue} and ${FcField} <> ${flagValue},${FcField} + ec_fc ,${FcField}),5) as  ${FcField},")
241      }
242      else {
243        strSql.append(s"a.${x} ,")
244      }
245    })
246    val resultDF = spark.sql(
247      s"""
248        |
249        |select  ${strSql.toString.stripSuffix(",")}
250        |from originIDDF a
251        |left join ecDF b
252        |on a.id = b.id
253        |
254        |""".stripMargin)
255
256    resultDF
257  }
258
259  /**
260   * 储存项计算 涡度法(EC)
261   * @param spark
262   * @param originDF
263   * @param FcField    Fc	CO2通量	p1
264   * @param LeField    LE	潜热通量	p2
265   * @param HsField    Hs	显热通量	p3
266   * @param p27        co2_Avg	平均CO2密度	p27
267   * @param p28        h2o_Avg	平均H2O密度	p28
268   * @param p29        Ts_Avg	平均超声空气温度	p29
269   * @param para_H     通量观测设备观测高度 39.0 米
270   * @param flagValue  标识位赋值,不符合条件
271   * @return
272   */
273//  def FluxEC_new_bak(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String, p27: String,p28: String,p29: String,p30: String,para_H: String ,flagValue: String): DataFrame = {
274//
275//    val para_ht: Double = para_H.toDouble / 1800
276//
277//    val originIDDF = originDF.repartition(1).orderBy("day_id","hour")
278//      .withColumn("id",monotonically_increasing_id()).repartition(2)
279//    originIDDF.createOrReplaceTempView("originIDDF")
280//
281//    val Lv:Double= 2440
282//    val Cp :Double= 1.00467*1000
283//    //    val pa:Double = p30.toDouble * 1000 * 1000
284//
285//    //    val builder: StringBuilder = new StringBuilder
286//    //    originIDDF.schema.fieldNames.foreach(x=>{
287//    //      if(x.toUpperCase.equals(FcField.toUpperCase)) {
288//    //        builder.append(s"Round( if(a.${p27}<>${flagValue.toDouble}  and b.${p27}<>${flagValue.toDouble} and b.${p27} is not null, (b.${p27}-a.${p27}) * ${para_ht} , ${FcField}) ,5) as  ${FcField},")
289//    //
290//    //      } else if(x.toUpperCase.equals(FcField.toUpperCase)) {
291//    //        builder.append(s"Round( if(a.${p28}<>${flagValue.toDouble}  and b.${p28}<>${flagValue.toDouble} and b.${p28} is not null, (b.${p28}-a.${p28}) * ${para_ht} * ${Lv} , ${LeField}) ,5) as  ${LeField},")
292//    //
293//    //      } else if(x.toUpperCase.equals(FcField.toUpperCase)) {
294//    //        builder.append(s"Round( if(a.${p29}<>${flagValue.toDouble}  and b.${p29}<>${flagValue.toDouble} and b.${p29} is not null, (b.${p29}-a.${p29}) * ${para_ht} * ${Cp} * ${p30} * 1000 * 1000      + ${HsField}, ${HsField}) ,5) as  ${HsField},")
295//    //
296//    //      } else {
297//    //        builder.append(s"a.${x} ,")
298//    //      }
299//    //    })
300//
301//    originIDDF.select("id",p27,p28,p29,p30).createOrReplaceTempView("local_temp")
302//    val ecDF = spark.sql(
303//      s"""
304//         |select a.id
305//         |   ,Round(if(a.${p27} = ${flagValue} or b.${p27}= ${flagValue} and (a.${p27}-b.${p27})>150,0, (a.${p27}-b.${p27}) * ${para_ht} ) ,5) as ec_fc
306//         |   ,Round(if(a.${p28} = ${flagValue} or b.${p28}= ${flagValue} and (a.${p28}-b.${p28})>150,0, (a.${p28}-b.${p28}) * ${para_ht} * ${Lv}) ,5)  as ec_le
307//         |   ,Round(if(a.${p29} = ${flagValue} or b.${p29}= ${flagValue} and (a.${p29}-b.${p29})>150,0, (a.${p29}-b.${p29}) * ${para_ht} * ${Cp} * a.${p30} * 1000 * 1000) ,5) as  ec_hs
308//         |from local_temp a
309//         |left join local_temp b on a.id=b.id+1
310//         |""".stripMargin)
311//    ecDF.createOrReplaceTempView("ecDF")
312//
313//
314//    val strSql: StringBuilder = new StringBuilder
315//    originIDDF.schema.fieldNames.foreach(x=>{
316//      if(     x.toUpperCase.equals(FcField.toUpperCase))  strSql.append(s"Round(if(ec_fc is not null and ec_fc <> ${flagValue} and ${FcField} <> ${flagValue},${FcField} + ec_fc ,${FcField}),5) as  ${FcField},")
317//      else if(x.toUpperCase.equals(LeField.toUpperCase))  strSql.append(s"Round(if(ec_le is not null and ec_le <> ${flagValue} and ${LeField} <> ${flagValue},${LeField} + ec_le ,${LeField}),5) as  ${LeField},")
318//      else if(x.toUpperCase.equals(HsField.toUpperCase))  strSql.append(s"Round(if(ec_hs is not null and ec_hs <> ${flagValue} and ${HsField} <> ${flagValue},${HsField} + ec_hs ,${HsField}),5) as  ${HsField},")
319//      else strSql.append(s"a.${x} ,")
320//    })
321//
322//    val resultDF = spark.sql(
323//      s"""
324//         |
325//         |select  ${strSql.toString.stripSuffix(",")}
326//         |from originIDDF a
327//         |left join ecDF b
328//         |on a.id = b.id
329//         |
330//         |""".stripMargin)
331//
332//    resultDF
333//  }
334
335
336}