This example uses climate data in cities in California (CA), Texas (TX), and Washington (WA). The cities are represented by two-dimensional coordinates (latitude and longitude). The data are temperature (in degrees Fahrenheit), pressure (in Mbars), and dew point (in degrees Fahrenheit). The function outputs a coefficient model table and a meta table, which are used as input to the function IDWT2D (ML Engine).
Input
| state | city | longitude | latitude | temp_f | pressure_mbar | dewpoint_f |
|---|---|---|---|---|---|---|
| CA | ALPINE | -117 | 32 | 34.9 | 1020.5 | 28.9 |
| CA | ALTURAS | -121 | 41 | 36.6 | 1022 | 29.1 |
| CA | ANAHEIM | -118 | 33 | 33.9 | 1020 | 28.4 |
| CA | AUBURN | -122 | 38 | 34.9 | 1020.6 | 28.8 |
| CA | BAKER | -117 | 35 | 39.4 | 1022 | 29.3 |
| CA | BARSTOW | -118 | 34 | 32.3 | 1020.8 | 27.6 |
| CA | BRIDGEPORT | -120 | 38 | 33.9 | 1020.1 | 28.4 |
| CA | BURNEY | -122 | 40 | 32.3 | 1020.8 | 27.6 |
| CA | BUTTONWILLOW | -120 | 35 | 32.1 | 1021.3 | 27.4 |
| CA | CALISTOGA | -123 | 38 | 33.8 | 1021.7 | 28.2 |
| CA | CALLAHAN | -123 | 41 | 34.6 | 1020.2 | 28.8 |
| CA | CECILVILLE | -124 | 41 | 33.8 | 1021.7 | 28.3 |
| CA | CLOVERDALE | -124 | 38 | 32.1 | 1021.3 | 27.4 |
| CA | COVELO | -124 | 39 | 33.5 | 1020.2 | 28.3 |
| CA | GLENNVILLE | -119 | 35 | 33.8 | 1021.7 | 28.2 |
| CA | HAIWEE | -118 | 36 | 33.1 | 1020.2 | 28 |
| CA | HEMET | -117 | 33 | 33.4 | 1020.2 | 28.3 |
| CA | IMPERIAL | -116 | 32 | 34.4 | 1020.2 | 28.7 |
| CA | KENTFIELD | -123 | 37 | 32.7 | 1020 | 27.9 |
| CA | KLAMATH | -125 | 41 | 32.1 | 1021.3 | 27.5 |
| ... | ... | ... | ... | ... | ... | ... |
SQL Call
SELECT * FROM DWT2D (
ON twod_climate_data AS InputTable
OUT TABLE OutputTable (dwt2d_coeftable)
OUT TABLE MetaInformationTable (dwt2d_metatable)
USING
TargetColumns ('temp_f','pressure_mbar','dewpoint_f')
PartitionColumns ('state')
IndexColumns ('latitude', 'longitude')
Wavelet ('db2')
CompactOutput ('true')
WaveletTransformLevel (2)
) AS dt;
Output
messages ---------------------------- Dwt2D finished successfully!
SELECT * FROM dwt2d_coeftable ORDER BY state, waveletid, waveletcomponent;
state waveletid waveletcomponent temp_f pressure_mbar dewpoint_f ----- --------- ---------------- --------------------- -------------------- --------------------- ca 2 A2 0.17966670080766614 5.367577820865212 0.14864598865259468 ca 3 A2 -2.8794432544680917 -80.83794342982637 -2.3434013863320886 ca 4 A2 85.17099422732936 2535.209096707586 71.20340790342276 ca 5 A2 -0.43698818294264186 -14.301367416569166 -0.37656963780789143 ca 6 A2 -3.5518013494034726 -112.24731824224867 -3.0373591313812405 ca 7 A2 7.607131707178469 243.03773964415961 6.527150195494035 ca 8 A2 77.48362490347102 2309.1677654492873 64.67758744765001 ca 9 A2 -2.467185519590279 -72.80570197561599 -2.0559702715297323 ca 10 A2 4.307237263004058 138.9649861032441 3.698570590257976 ... tx 1 A2 -3.6011517287761174 -89.66062613652628 -2.533400946695857 tx 2 A2 -4.892774826100907 -122.68985857717036 -3.4903463430079986 tx 3 A2 32.55414532700897 781.9783426823088 22.511340139411953 tx 4 A2 52.65077802036848 1237.3018397594763 35.65115873239475 tx 5 A2 -18.081617481567218 -449.8871020273652 -12.869485244673767 tx 6 A2 5.199579156036784 125.1711586833209 3.5429928486997295 tx 7 A2 0.7131422719011882 22.194219313520705 0.5933788322995257 tx 8 A2 34.490654091515125 824.5284208268199 23.648074260046357 tx 9 A2 50.07836808898107 1177.1087409858105 33.8303478810003 tx 10 A2 -21.489198057562923 -517.9621336089269 -14.920617758952584 ... wa 1 A2 42.6254931061425 1468.4366999990582 35.28539169480957 wa 2 A2 47.21635578657956 1624.9053300999767 39.10397731411011 wa 3 A2 73.03586814995711 2509.1676792770127 60.485928056688806 wa 4 A2 29.612765080680177 1012.0954617994031 24.068537871549648 wa 5 A2 63.39836472182281 2184.274991658562 52.422101734722204 wa 6 A2 67.34481712227416 2319.5128100909124 55.70701016166999 wa 7 A2 82.16956338227835 2826.8562365727303 67.8884826575598 wa 8 A2 46.981578273758515 1609.3291255609581 38.192760986972914 wa 9 A2 40.32603519406097 1391.6270194096492 33.09817180508851 wa 10 A2 53.39390787028372 1851.1134440137755 43.76464770186756
CompactOutput('true') prevents rows in which all coefficient values have an absolute value less than 1e-12) from appearing in dwt2d_coeftable.
SELECT * FROM dwt2d_metatable ORDER BY state;
state meta content ----- --------------- -------------------------------------------------------------------------------- ca range (32,-125),(41,-116) ca extensionmode sym ca ilowpassfilter 0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604 ca waveletname db2 ca length (10, 10) ca highpassfilter -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604 ca ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342 ca lowpassfilter -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342 ca blocklength (4, 4),(4, 4),(4, 4),(4, 4),(6, 6),(6, 6),(6, 6) ca level 2 tx highpassfilter -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604 tx extensionmode sym tx lowpassfilter -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342 tx ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342 tx length (11, 11) tx waveletname db2 tx blocklength (5, 5),(5, 5),(5, 5),(5, 5),(7, 7),(7, 7),(7, 7) tx range (26,-105),(36,-95) tx level 2 tx ilowpassfilter 0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604 wa range (45,-125),(48,-118) wa highpassfilter -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604 wa level 2 wa ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342 wa extensionmode sym wa waveletname db2 wa blocklength (3, 4),(3, 4),(3, 4),(3, 4),(3, 5),(3, 5),(3, 5) wa ilowpassfilter 0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604 wa lowpassfilter -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342 wa length (4, 8)
Download a zip file of all examples and a SQL script file that creates their input tables.