- 01 mar 2013, 13:47
#56019
Hallo allen,
Het omrekenen is geen probleem, wat er nu gebeurd is dat ik in msql een taak heb lopen die alle data van 5 ISS stations omrekend, naar de europese waarden, niet alleen temp maar ook afstand en snelheid, engesle text naar nederlands winddnelheid,het is geen data di uit een console komt of data van wl/davis/noem maar op, het is RAW data waar je niets mee kan.
voorbeeld voor 1 station
insert into station (RecDateTime,Channelindex,tempout,hitempout,lowtempout,humout,dewpoint,lowwindchill,Humidex,heatindex,date,time,Datum)
SELECT `RecDateTime`, `Channelindex`,
CASE WHEN `issdata`.TempOut = 32767 THEN NULL ELSE (((`issdata`.TempOut /10) -32) /1.8) END,
CASE WHEN issdata.HiTempOut = -32768 THEN NULL ELSE (((`issdata`.HiTempOut /10) -32) /1.8) END,
CASE when issdata.LowTempOut = 32767 THEN NULL ELSE(((`issdata`.LowTempOut /10) -32) /1.8) END,
CASE WHEN issdata.humout = 255 THEN NULL ELSE `issdata`.humout END,
CASE WHEN issdata.DewPoint = 32767 THEN NULL ELSE (((`issdata`.DewPoint /10) -32) /1.8) END,
CASE When issdata.LowWindChill = 32767 Then NULL ELSE (((`issdata`.LowWindChill /10) -32) /1.8) END,
((((`issdata`.`TempOut` /10) -32) /1.8) + 5/9 * ((6.112 * exp (17.62 * (((`issdata`.`TempOut` /10) -32) /1.8) / (243.12 + (((`issdata`.`TempOut` /10) -32) /1.8)))*( `issdata`.`humout`)/100) - 10)),
CASE When issdata.Heatindex = 32767 THEN NULL ELSE(((`issdata`.Heatindex /10) -32) /1.8) END ,
cast(`issdata`.`RecDateTime` as date),
cast(`issdata`.`RecDateTime` as time),
Date_format(`issdata`.`RecDateTime`, '%d/%m/%Y') AS Datum
FROM `issdata`
WHERE `ChannelIndex`=0 and `issdata`.`RecDateTime` NOT IN (select RecDateTime From station )
ORDER BY `recdatetime` DESC;
Insert into station1 (RecDateTime,ChannelIndex,windomschrijving,Windbft,windspeed,scaleravgwinddir,hiwindspeed,hiwinddir,dominantdir,WindRichting,uv,hiuv,solarrad,hisolarrad,Date,time,Datum)
SELECT `RecDateTime`,`ChannelIndex`,
CASE
WHEN windspeed <=19 THEN 'stil'
WHEN windspeed <=59 Then 'stil'
WHEN windspeed <=119 Then 'zwak'
WHEN windspeed <=199 Then 'zwak'
WHEN windspeed <=289 Then 'matig'
WHEN windspeed <=389 Then 'matig'
WHEN windspeed <=619 Then 'krachtig'
WHEN windspeed <=59 Then 'hard'
WHEN windspeed <=749 Then 'stormachtig'
WHEN windspeed <=889 Then 'storm'
WHEN windspeed <=1029 Then 'zware storm'
WHEN windspeed <=1169 Then 'orkaanachtig'
WHEN windspeed <=1170 Then 'orkaan'
ELSE '-' END,
CASE WHEN windspeed <=19 THEN '0'
WHEN windspeed <=59 Then '1'
WHEN windspeed <=119 Then '2'
WHEN windspeed <=199 Then '3'
WHEN windspeed <=289 Then '4'
WHEN windspeed <=389 Then '5'
WHEN windspeed <=619 Then '6'
WHEN windspeed <=59 Then '7'
WHEN windspeed <=749 Then '8'
WHEN windspeed <=889 Then '9'
WHEN windspeed <=1029 Then '10'
WHEN windspeed <=1169 Then '11'
WHEN windspeed <=1170 Then '12'
Else '' End
as windspeed,(windspeed/10),scaleravgwinddir,(hiwindspeed/10),
CASE WHEN`issdata`.hiwinddir = 255 THEN "---" ELSE issdata.hiwinddir END,
CASE WHEN`issdata`.dominantdir =255 Then "---" ELSE issdata.dominantdir END,
case Dominantdir
When '255 ' THEN 'Windstil'
When '15 ' THEN 'NNW'
When '14' THEN 'NW'
When '13' THEN 'WNW'
When '12' THEN 'W'
When '11' THEN 'WNW'
When '10' THEN 'ZW'
When '9' THEN 'ZZW'
When '8' THEN 'Z'
when '7' THEN 'ZZO'
When '6' THEN 'ZO'
When '5' THEN 'OZO'
When '4' THEN 'O'
When '3' THEN 'ONO'
When '2' THEN 'NO'
When '1' THEN 'NNO'
When '0' THEN 'N'
Else 'Onbekend'
End as WindRichting,
CASE WHEN `issdata`.uv = 255 THEN NULL ELSE(`issdata`.uv /10) END,
CASE WHEN `issdata`.hiuv = 255 THEN NULL ELSE (`issdata`.hiuv /10) END,
CASE WHEN `issdata`.solarrad = 32767 THEN NULL ELSE `issdata`.solarrad END,
CASE WHEN `issdata`.hisolarrad = 32767 THEN NULL ELSE `issdata`.hisolarrad END,
cast(`issdata`.`RecDateTime` as date),
cast(`issdata`.`RecDateTime` as time),
Date_format(`RecDateTime`, '%d/%m/%Y') AS Datum
FROM `issdata`
WHERE `ChannelIndex`=1 and `issdata`.`RecDateTime` NOT IN (select RecDateTime From station1 )
ORDER BY `RecDateTime` DESC;
