5. Inter-table join query

 

4. Reverse pivot and sorting by the specified order

meteorolog.txt stores some observation stations’ meteorological data. stations.csv stores latitudes, longitudes and elevation data of certain observation stations. According to them, among the 100 records containing the smallest MIN values (the lowest temperatures) find the number of those where elevations of observation stations are above 200 meters and that of the records where data are obtained at observation stations whose longitudes are above 40 degrees.

Expected results:

SPL code:

A B
1 =T(“meteorolog.txt”) =file(“stations.csv”).import@ct()
2 =A1.id(STATION) =B1.select(A2.pos(STATION)>0)
3 =A1.switch(STATION,B2:STATION)
4 =A3.top(100;MIN)
5 =A4.count(STATION.LATITUDE>40) =A4.count(STATION.ELEVATION>200)

A1 and B1 respectivley reads data from the two files. B1 uses @c option to separate field values in each row by commas when reading the csv data. We can also type =T(“stations.csv”) in B1, and the function will automatically identify the file format. As B1 includes observation stations that do not appear in A1 and even repeated station names, it needs to be first filtered. A2 gets observation station names from the meteorological data, and B2 gets corresponding records from the stations data. A3 joins A1 and B2 using switch()function, which replaces the former’s STATION vlaues with the latter’s station records. A4 selects the 100 records containing the lowest temperatures; 100 being followed by the semicolon indicates that whole records will be retrieved after records are sorted by the field after secmicolon. The code is equivalent to =A3.top(100,MIN,~). Line 5 performs further computations to get the final results.


6. Precipitation data computation
Contents and Exercise Data