Pages

Wednesday, September 24, 2014

Databases Galore - 2

I really got into the data and programming, yesterday, so I neglected to do a post (I plan to do two today to make up for it).

The databases shared by NOAA, a university, World Bank, and NASA, involve 7,280 individual weather stations in 236 countries and/or on 14 ships (which basically means all around the globe and oceans).

So you see, it takes a bit of time to write C++ programs to parse all that flat-file data, clean it up, and prepare it for a mySQL database with several tables.

The data have now been stored in a database named "climate_change" with seven tables (countries, weather stations, monthly temperatures, CO2 PPM, etc.) and are ready for queries to be written.

I have classes and programs that I have written and others I am still writing.

They utilize mySql++, a C++ library for SQL functionality involving mySQL databases (see e.g. mySQL, mySQL++) which are managed by a mySQL server.

I am showing that we can do this stuff at home or in class, since the scientists have shared the data transparently, and it is not mysterious or unreachable as the deniers tend to whine about (see e.g. How Fifth Graders Calculate Ice Volume - 5).

Just below is a sample output from one of those 7,280 weather stations, displaying monthly data in tables, with calculated averages on the rightmost column.

This is in response to an SQL query, where the first column of each row is January, the last column is December [before the brackets].

There are two rows per year, showing the monthly temperatures in Celsius and Fahrenheit (asterisks indicate missing monthly data):

Country, Station: [COSTA RICA, PUERTO LIMON]:

**** 24.94° 24.94° 25.94° 25.14° 25.24° 25.14° 25.24° 24.74° 25.24° 24.54° 23.64° [1961 avg = 24.98°] C
**** 76.89° 76.89° 78.69° 77.25° 77.43° 77.25° 77.43° 76.53° 77.43° 76.17° 74.55° [1961 avg = 76.96°] F

23.34° 23.74° 24.54° 25.04° 25.84° 26.64° 25.74° 25.34° 25.74° 25.84° 24.24° 23.44° [1962 avg = 24.96°] C
74.01° 74.73° 76.17° 77.07° 78.51° 79.95° 78.33° 77.61° 78.33° 78.51° 75.63° 74.19° [1962 avg = 76.92°] F

24.14° 24.04° 24.64° 24.54° 25.04° 25.44° 25.14° 25.44° 26.24° 25.34° 24.74° 24.34° [1963 avg = 24.92°] C
75.45° 75.27° 76.35° 76.17° 77.07° 77.79° 77.25° 77.79° 79.23° 77.61° 76.53° 75.81° [1963 avg = 76.86°] F

23.74° 25.34° 25.44° 25.44° 25.34° 25.24° 25.04° 25.64° 25.74° 25.44° 25.34° 25.14° [1964 avg = 25.24°] C
74.73° 77.61° 77.79° 77.79° 77.61° 77.43° 77.07° 78.15° 78.33° 77.79° 77.61° 77.25° [1964 avg = 77.43°] F

23.44° 25.04° 24.84° 25.14° 25.74° 25.64° 24.74° 24.74° 25.74° 25.54° 25.44° 24.94° [1965 avg = 25.08°] C
74.19° 77.07° 76.71° 77.25° 78.33° 78.15° 76.53° 76.53° 78.33° 77.97° 77.79° 76.89° [1965 avg = 77.15°] F

25.54° 25.64° 25.44° 26.14° 25.84° 25.64° 25.34° 25.44° 25.74° 25.94° 24.64° 24.74° [1966 avg = 25.51°] C
77.97° 78.15° 77.79° 79.05° 78.51° 78.15° 77.61° 77.79° 78.33° 78.69° 76.35° 76.53° [1966 avg = 77.91°] F

24.24° 24.44° 23.99° 24.89° 24.79° 24.59° 23.89° 24.79° **** 25.59° 24.59° 25.69° [1967 avg = 24.68°] C
75.63° 75.99° 75.18° 76.80° 76.62° 76.26° 75.00° 76.62° **** 78.06° 76.26° 78.24° [1967 avg = 76.43°] F

24.69° 23.69° 24.09° 24.09° 24.99° 25.19° 24.49° 24.49° 25.19° 25.79° 24.39° 24.69° [1968 avg = 24.65°] C
76.44° 74.64° 75.36° 75.36° 76.98° 77.34° 76.08° 76.08° 77.34° 78.42° 75.90° 76.44° [1968 avg = 76.37°] F

24.79° 24.39° 25.69° 25.99° 26.69° **** **** **** **** **** 25.09° 24.99° [1969 avg = 25.38°] C
76.62° 75.90° 78.24° 78.78° 80.04° **** **** **** **** **** 77.16° 76.98° [1969 avg = 77.68°] F

24.99° 23.99° 25.69° 26.19° 25.59° 26.59° 26.19° 25.99° 25.99° 26.29° 24.29° 24.81° [1970 avg = 25.55°] C
76.98° 75.18° 78.24° 79.14° 78.06° 79.86° 79.14° 78.78° 78.78° 79.32° 75.72° 76.66° [1970 avg = 77.99°] F

24.11° 24.01° 24.61° 25.31° 25.81° 25.51° 25.01° 25.51° 25.71° 25.61° 24.91° 24.11° [1971 avg = 25.02°] C
75.40° 75.22° 76.30° 77.56° 78.46° 77.92° 77.02° 77.92° 78.28° 78.10° 76.84° 75.40° [1971 avg = 77.03°] F

23.71° 24.41° 25.11° 25.91° 26.61° 26.61° 25.91° 26.11° 26.01° 25.91° 26.01° 25.51° [1972 avg = 25.65°] C
74.68° 75.94° 77.20° 78.64° 79.90° 79.90° 78.64° 79.00° 78.82° 78.64° 78.82° 77.92° [1972 avg = 78.17°] F

25.31° 25.51° 26.61° 27.01° 26.81° 26.61° 25.81° 26.61° 26.41° 26.11° 25.41° 23.61° [1973 avg = 25.99°] C
77.56° 77.92° 79.90° 80.62° 80.26° 79.90° 78.46° 79.90° 79.54° 79.00° 77.74° 74.50° [1973 avg = 78.77°] F

24.31° 23.81° 25.11° 25.11° 26.31° 26.31° 25.01° 25.91° 26.11° 25.51° 24.81° 24.81° [1974 avg = 25.26°] C
75.76° 74.86° 77.20° 77.20° 79.36° 79.36° 77.02° 78.64° 79.00° 77.92° 76.66° 76.66° [1974 avg = 77.47°] F

24.81° 24.41° 25.41° 25.51° 26.21° 25.51° 25.31° 25.31° 26.11° 26.11° 25.21° 23.61° [1975 avg = 25.29°] C
76.66° 75.94° 77.74° 77.92° 79.18° 77.92° 77.56° 77.56° 79.00° 79.00° 77.38° 74.50° [1975 avg = 77.53°] F

23.61° 23.11° 24.61° 26.01° 26.21° 26.21° 25.81° 25.81° 25.91° 26.11° 25.71° 25.61° [1976 avg = 25.39°] C
74.50° 73.60° 76.30° 78.82° 79.18° 79.18° 78.46° 78.46° 78.64° 79.00° 78.28° 78.10° [1976 avg = 77.71°] F

24.11° 24.81° 25.21° 25.71° 26.81° 26.21° 25.71° 25.71° 25.71° 25.71° 25.51° 25.31° [1977 avg = 25.54°] C
75.40° 76.66° 77.38° 78.28° 80.26° 79.18° 78.28° 78.28° 78.28° 78.28° 77.92° 77.56° [1977 avg = 77.98°] F

24.21° 24.91° 25.61° 26.31° 27.01° 26.11° 25.61° 26.11° 26.11° 25.71° 26.11° 25.31° [1978 avg = 25.76°] C
75.58° 76.84° 78.10° 79.36° 80.62° 79.00° 78.10° 79.00° 79.00° 78.28° 79.00° 77.56° [1978 avg = 78.37°] F

24.81° 24.91° 25.71° 26.11° 26.71° 26.41° 26.31° 26.01° 26.31° 26.11° 25.61° 24.61° [1979 avg = 25.80°] C
76.66° 76.84° 78.28° 79.00° 80.08° 79.54° 79.36° 78.82° 79.36° 79.00° 78.10° 76.30° [1979 avg = 78.44°] F

25.01° 24.51° 25.81° 26.21° 26.91° 26.41° 26.41° 26.61° 26.31° 25.91° 25.41° 24.01° [1980 avg = 25.79°] C
77.02° 76.12° 78.46° 79.18° 80.44° 79.54° 79.54° 79.90° 79.36° 78.64° 77.74° 75.22° [1980 avg = 78.43°] F

23.81° 24.71° 25.51° 25.71° 27.11° 26.31° 26.01° 26.21° 26.21° 26.01° 24.91° 24.71° [1981 avg = 25.60°] C
74.86° 76.48° 77.92° 78.28° 80.80° 79.36° 78.82° 79.18° 79.18° 78.82° 76.84° 76.48° [1981 avg = 78.08°] F

25.01° 25.41° 25.41° 25.91° 26.91° 26.51° 25.31° 25.81° 26.01° 25.21° 25.01° 24.91° [1982 avg = 25.62°] C
77.02° 77.74° 77.74° 78.64° 80.44° 79.72° 77.56° 78.46° 78.82° 77.38° 77.02° 76.84° [1982 avg = 78.11°] F

24.81° **** 26.61° 27.01° 26.81° 27.21° 25.61° 26.41° 26.51° 25.61° 25.51° 24.41° [1983 avg = 26.05°] C
76.66° **** 79.90° 80.62° 80.26° 80.98° 78.10° 79.54° 79.72° 78.10° 77.92° 75.94° [1983 avg = 78.88°] F

23.71° 24.51° 25.71° 26.51° 25.61° 25.61° 25.31° 24.91° 25.71° 25.61° 24.71° 24.31° [1984 avg = 25.18°] C
74.68° 76.12° 78.28° 79.72° 78.10° 78.10° 77.56° 76.84° 78.28° 78.10° 76.48° 75.76° [1984 avg = 77.33°] F

23.81° 24.31° 25.61° 25.91° 26.61° 26.41° 25.41° 25.81° 26.01° 25.91° 25.31° 24.71° [1985 avg = 25.49°] C
74.86° 75.76° 78.10° 78.64° 79.90° 79.54° 77.74° 78.46° 78.82° 78.64° 77.56° 76.48° [1985 avg = 77.87°] F

23.81° 24.71° 25.11° 25.61° 26.31° 26.31° 25.51° 26.01° 25.91° 25.91° 25.71° 25.61° [1986 avg = 25.54°] C
74.86° 76.48° 77.20° 78.10° 79.36° 79.36° 77.92° 78.82° 78.64° 78.64° 78.28° 78.10° [1986 avg = 77.98°] F

25.01° 25.01° 26.41° 25.51° 26.21° 26.41° 26.11° 26.11° 26.61° 25.71° 26.11° 25.61° [1987 avg = 25.90°] C
77.02° 77.02° 79.54° 77.92° 79.18° 79.54° 79.00° 79.00° 79.90° 78.28° 79.00° 78.10° [1987 avg = 78.62°] F

25.01° 24.91° 25.01° 26.41° 26.71° 26.51° 25.91° 26.41° 26.01° 25.91° 25.51° 24.41° [1988 avg = 25.73°] C
77.02° 76.84° 77.02° 79.54° 80.08° 79.72° 78.64° 79.54° 78.82° 78.64° 77.92° 75.94° [1988 avg = 78.31°] F

24.61° 23.61° 24.21° 25.21° 25.91° 25.91° 25.51° 26.11° 25.91° 25.91° 25.91° 24.61° [1989 avg = 25.28°] C
76.30° 74.50° 75.58° 77.38° 78.64° 78.64° 77.92° 79.00° 78.64° 78.64° 78.64° 76.30° [1989 avg = 77.51°] F

24.81° 24.81° 25.01° 25.91° 26.21° 26.31° 25.81° 26.11° 26.51° 26.51° 25.91° 24.61° [1990 avg = 25.71°] C
76.66° 76.66° 77.02° 78.64° 79.18° 79.36° 78.46° 79.00° 79.72° 79.72° 78.64° 76.30° [1990 avg = 78.28°] F

24.81° 24.51° 25.91° 25.81° 25.91° 26.41° 25.91° 25.91° 26.31° 26.11° 24.91° 24.61° [1991 avg = 25.59°] C
76.66° 76.12° 78.64° 78.46° 78.64° 79.54° 78.64° 78.64° 79.36° 79.00° 76.84° 76.30° [1991 avg = 78.07°] F

24.71° 25.21° 25.51° 26.21° 26.11° 27.31° 25.81° 25.41° 25.81° 25.61° 25.51° 25.21° [1992 avg = 25.70°] C
76.48° 77.38° 77.92° 79.18° 79.00° 81.16° 78.46° 77.74° 78.46° 78.10° 77.92° 77.38° [1992 avg = 78.26°] F

24.71° 24.81° 25.21° 26.81° 27.01° 26.61° 26.01° 26.21° 25.81° 26.11° 25.51° 24.81° [1993 avg = 25.80°] C
76.48° 76.66° 77.38° 80.26° 80.62° 79.90° 78.82° 79.18° 78.46° 79.00° 77.92° 76.66° [1993 avg = 78.44°] F

24.31° 24.41° 25.71° 25.71° 26.61° 26.01° 25.51° 26.01° 26.21° 26.31° 25.61° 24.81° [1994 avg = 25.60°] C
75.76° 75.94° 78.28° 78.28° 79.90° 78.82° 77.92° 78.82° 79.18° 79.36° 78.10° 76.66° [1994 avg = 78.08°] F

24.81° 24.81° 25.81° 26.31° 26.81° 26.81° 26.51° 26.91° 26.71° 26.61° 25.31° 25.51° [1995 avg = 26.08°] C
76.66° 76.66° 78.46° 79.36° 80.26° 80.26° 79.72° 80.44° 80.08° 79.90° 77.56° 77.92° [1995 avg = 78.94°] F

24.51° 24.31° 25.21° 26.01° 26.61° 26.21° 26.21° 26.01° 26.71° 26.61° 25.71° 24.41° [1996 avg = 25.71°] C
76.12° 75.76° 77.38° 78.82° 79.90° 79.18° 79.18° 78.82° 80.08° 79.90° 78.28° 75.94° [1996 avg = 78.28°] F

24.41° 24.11° 25.21° 26.31° 26.11° 26.61° 26.51° 26.61° 26.81° 26.41° 26.41° 25.51° [1997 avg = 25.92°] C
75.94° 75.40° 77.38° 79.36° 79.00° 79.90° 79.72° 79.90° 80.26° 79.54° 79.54° 77.92° [1997 avg = 78.65°] F

25.61° 26.41° 26.01° 26.71° 27.31° 26.91° 26.31° 26.51° 27.21° 26.51° 25.91° 25.01° [1998 avg = 26.37°] C
78.10° 79.54° 78.82° 80.08° 81.16° 80.44° 79.36° 79.72° 80.98° 79.72° 78.64° 77.02° [1998 avg = 79.46°] F

24.91° 24.81° 25.11° 26.01° 26.91° 26.21° 25.71° 26.11° 26.11° 25.91° 25.41° 23.91° [1999 avg = 25.59°] C
76.84° 76.66° 77.20° 78.82° 80.44° 79.18° 78.28° 79.00° 79.00° 78.64° 77.74° 75.04° [1999 avg = 78.07°] F

24.11° 24.01° 25.21° 25.91° 26.21° 26.31° 26.11° 26.01° 26.51° 26.21° 25.81° 24.91° [2000 avg = 25.61°] C
75.40° 75.22° 77.38° 78.64° 79.18° 79.36° 79.00° 78.82° 79.72° 79.18° 78.46° 76.84° [2000 avg = 78.10°] F

24.71° 25.01° 25.41° 25.91° 26.81° 26.51° 26.31° 26.71° 26.51° 27.11° 25.91° 25.81° [2001 avg = 26.06°] C
76.48° 77.02° 77.74° 78.64° 80.26° 79.72° 79.36° 80.08° 79.72° 80.80° 78.64° 78.46° [2001 avg = 78.91°] F

25.41° 25.11° 25.51° 25.71° 25.91° 27.01° 25.71° 26.01° 27.11° 25.91° 25.31° 25.51° [2002 avg = 25.85°] C
77.74° 77.20° 77.92° 78.28° 78.64° 80.62° 78.28° 78.82° 80.80° 78.64° 77.56° 77.92° [2002 avg = 78.53°] F

25.21° 25.91° 26.61° 27.01° 27.21° 27.11° 26.91° 26.71° 27.31° 27.41° 26.81° 25.71° [2003 avg = 26.66°] C
77.38° 78.64° 79.90° 80.62° 80.98° 80.80° 80.44° 80.08° 81.16° 81.34° 80.26° 78.28° [2003 avg = 79.99°] F

25.41° 25.71° 26.01° **** **** **** **** **** **** **** **** **** [2004 avg = 25.71°] C
77.74° 78.28° 78.82° **** **** **** **** **** **** **** **** **** [2004 avg = 78.28°] F

**** 24.70° 25.00° 25.80° 26.30° 26.70° 26.50° 26.60° 26.60° 26.80° 26.90° 25.60° [2006 avg = 26.14°] C
**** 76.46° 77.00° 78.44° 79.34° 80.06° 79.70° 79.88° 79.88° 80.24° 80.42° 78.08° [2006 avg = 79.05°] F

25.20° 25.10° 25.60° 26.30° 26.80° 26.90° 26.30° 26.90° 26.70° 26.80° 25.60° 25.00° [2007 avg = 26.10°] C
77.36° 77.18° 78.08° 79.34° 80.24° 80.42° 79.34° 80.42° 80.06° 80.24° 78.08° 77.00° [2007 avg = 78.98°] F

24.60° 25.00° 25.30° 26.20° 26.50° 26.80° 26.50° 26.60° 27.20° 26.90° 25.30° 25.40° [2008 avg = 26.02°] C
76.28° 77.00° 77.54° 79.16° 79.70° 80.24° 79.70° 79.88° 80.96° 80.42° 77.54° 77.72° [2008 avg = 78.84°] F

25.00° 24.70° 25.00° 26.10° 26.80° 27.00° 26.70° 26.70° 27.20° 26.80° 26.00° 26.10° [2009 avg = 26.17°] C
77.00° 76.46° 77.00° 78.98° 80.24° 80.60° 80.06° 80.06° 80.96° 80.24° 78.80° 78.98° [2009 avg = 79.11°] F

25.40° 26.00° 26.10° 27.10° 27.10° 27.20° 26.80° 26.90° 26.80° 26.50° 24.10° **** [2010 avg = 26.36°] C
77.72° 78.80° 78.98° 80.78° 80.78° 80.96° 80.24° 80.42° 80.24° 79.70° 75.38° **** [2010 avg = 79.45°] F

24.70° 25.30° 25.10° 25.60° 26.40° 27.00° 26.10° 26.70° 26.70° 25.80° 25.10° **** [2011 avg = 25.86°] C
76.46° 77.54° 77.18° 78.08° 79.52° 80.60° 78.98° 80.06° 80.06° 78.44° 77.18° **** [2011 avg = 78.55°] F

24.10° **** 25.10° **** 26.90° **** **** 26.50° 26.50° 26.40° **** 25.40° [2012 avg = 25.84°] C
75.38° **** 77.18° **** 80.42° **** **** 79.70° 79.70° 79.52° **** 77.72° [2012 avg = 78.52°] F

25.30° 25.10° 25.30° 26.30° 26.40° 26.70° 25.30° 26.40° 27.00° 26.50° 25.80° 25.20° [2013 avg = 25.94°] C
77.54° 77.18° 77.54° 79.34° 79.52° 80.06° 77.54° 79.52° 80.60° 79.70° 78.44° 77.36° [2013 avg = 78.69°] F

25.30° 25.20° 25.40° 25.80° 27.50° 26.60° **** **** **** **** **** **** [2014 avg = 25.97°] C
77.54° 77.36° 77.72° 78.44° 81.50° 79.88° **** **** **** **** **** **** [2014 avg = 78.74°] F



This example is typical, in that, the earliest years tend to show a lower annual average temperature than the more recent years do (a warming trend).
A home-made graph of the above data

What I am working on now is a program that goes through each of the 7,280 individual weather stations in those 236 countries and/or on those 14 ships.

It will collect, calculate, and display only the oldest year averages together with the most recent year averages, for a comprehensive comparison (e.g. there would only be the first two rows, and the last two rows in the example above).

The thing is that it will be a display for every weather station on the globe that has official recognition in these databases (@some 7,280 locations).

Then we can compare the PPM of CO2 which shows a similar pattern.

The next post in this series is here, the previous post in this series is here.

No comments:

Post a Comment