CLIWOC for the modern era

the horrorfoto by David Nash

A friend of mine presented a talk on "The Little Age at Sea" and behind him on the screen was - THE HORROR - an MS ACCESS database.
Naturally I asked him about it - since friends don't let friends work with Access - and apparently there was this rich dataset 'CLIWOC' which was in an unwieldy format and the Access database was the most managable way to handle it. For his and my sanity I've converted the unwieldy text file into a few more managable formats for all to enjoy.

These datasets are based on the complete textfile download from http://projects.knmi.nl/cliwoc/download/cliwoc21.htm. The main change is the addition of a latitude and longitude column in 'normal' decimal degrees: ranging from -90 to +90 in latitude and -180 to 180 degrees in longitude. The fileformats available are: .ods(68.1MB); open office spreadsheet format, .tsv(167.3MB); a tab-delimited text file and .gpkg (190.7MB); geopackage, which is a geografic dataformat. Below is a copy of the datadescription as found on the KNMI website with the addition of the latitude and longitude columns. The Start and End columns in the datadescription have no meaning, but I'm too lazy to remove those from the HTML file.

Any questions on these changes can be directed to Steven.

CLIWOC Release 2.1

This file contains the description of the full database in ASCII format, which contains 287,114 records of fixed length. Since the record length is 8,571 characters, it is recommended to use either the Access version (97, 2000 or 2002-2003), the core version or the limited version (not containing large memo-fileds). The records are built according to the proposed format, given in the IMMA document, updated 14 March 2007 (www.cdc.noaa.gov/coads/e-doc/imma/imma.pdf). The idea behind this format is that the user can easily start with the data, without having to concern about unit conversions. The first 108 characters of the complete records are the same as those in the core version CLIWOC21CORE.txt. The attachment that follows the core, starts with an identification group and the attachment itself follows. The actual attachment begins at character 114. The attachment has fixed field lengths. The explanation of the fields is given below.
All data in the core part is expressed in present day SI-units (e.g. UTC, Celsius, nautical miles, degrees, m/s and hPa). 
In the process of keying the data from different sources, duplicate records may have been keyed when more than one source was available from the same voyage. Usually these records are not exact copies, but are more or less complementary to each other. The identification of the duplicates is handled in the ShipLogbookID file (ShipLogbookid21.txt). If the user wants to avoid duplicates, he/she has to ignore those ID’s that have the Duplicate-field in the ShipLogbookID file set to other values than 0.
In the following tables the record in the file CLIWOC21.txt is explained. The first table gives an explanation of the core part of the complete record, while the second table gives the explanation of the complete attachment 
 
Variable Start End Format Description
YR 1 4 A4 Year (UTC)
MO 5 6 A2 Month (UTC)
DY 7 8 A2 Day (UTC)
HR 9 12 A4 Hour (UTC)
LAT 13 17 A5 Latitude in hundredths of a degree; North of the equator is positive, South of the equator is negative
LON 18 23 A6 Longitude in hundredths of a degree; convention: 0-359.99, East of Greenwich
latitude - - - Latitude from the LAT column converted to 'normal' decimal degrees: North of the equator is positive, South of the equator is negative
longitude 18 23 A6 Longitude from the LON column converted to 'normal' decimal degrees; West of the Greenwich meridian is negative, East of the Greenwich meridian is positive
IM 24 25 A2 IMMA version; 0 = this prototype version
ATTC 26 26 A1 Attachment Count; in this version there is one attachment, so the count is 1
TI 27 27 A1 Time indicator; only 0 (= nearest whole hour)
LI 28 28 A1 Latitude/longitude indicator; all 6 (= other)
DS 29 29 A1 Not in use
VS 30 30 A1 Not in use
NID 31 32 A2 Not in use
II 33 34 A2 ID indicator; all 10 (= composite information from early ship data).
ID 35 43 A9 Identification; In this release a number is given that identifies the ship and the ships' logbook and the possibility that the current record is a duplicate.
With this number you can look up this information in the file: ShipLogbookid21.txt
C1 44 45 A2 Country code; gives the nationality of the ship that made the observation:
ES = Spain
FR = France
NL = The Netherlands
UK = United Kingdom
SE = Sweden
US = USA
DE = Germany, in this case especially it is restricted to Hamburg
DK = Denmark
DI 46 46 A1 Not in use
D 47 49 A3 Wind direction (from which the wind is blowing) in whole degrees from:
1-360; 361 = calm, 362 = variable. It is not clear from many logbook entries whether the wind direction has been corrected for the magnetic variation (or declination). To avoid any misunderstanding, all winds from reports that included a valid position in the period 1750-1854 were corrected. All other wind directions are given without being corrected.
WI 50 50 A1 Wind speed indicator; all reported wind forces are more or less given in Beaufort classes (WI = 5);
W 51 53 A3 Wind speed; given in tenths of a meter per second. From the descriptive terms of wind force given in the observations, a first division into “Beaufort” classes resulted in the m/s given here. Most of the values are the midpoints of each Beaufort class, cf. WMO-1100. In those cases that more information was available, other values may have been used. Bft 0: 0-0.2 m/s
Bft 1: 0.3-1.5 m/s
Bft 2: 1.6-3.3 m/s
Bft 3: 3.4-5.4 m/s
Bft 4: 5.5-7.9 m/s
Bft 5: 8.0-10.7 m/s
Bft 6: 10.8-13.8 m/s
Bft 7: 13.9-17.1 m/s
Bft 8 17.2-20.7 m/s
Bft 9: 20.8-24.4 m/s
Bft 10: 24.5-28.4 m/s
Bft 11: 28.5-32.6 m/s
Bft 12: >32.6 m/s
VI 54 54 A1 Not in use
VV 55 56 A2 Not in use
WW 57 58 A2 Not in use
W1 59 59 A1 Not in use
SLP 60 64 A5 Sea level pressure in tenths of hPa. Still under study; No temperature correction was applied and for this moment the following factors have been used for the conversion to hPa:
2.54 cm in an inch
13.596 specific gravity of mercury
980.65 cm/s gravity at 45° latitude
A 65 65 A1 Not in use
PPP 66 68 A3 Not in use
IT 69 69 A1 Indicator for air temperatures, originating from:
3 = mixed precision Celsius
7 = mixed precision Fahrenheit
9 = mixed precision Réaumur
AT 70 73 A4 Air temperature in tenths of a degree Celsius
WBTI 74 74 A1 Not in use
WBT 75 78 A4 Not in use
DPTI 79 79 A1 Not in use
DPT 80 83 A4 Not in use
SI 84 85 A2 Method by which SST was taken; 10 = implied bucket
SST 86 89 A4 Sea surface temperature in tenths of a degree Celsius
N 90 90 A1 Not in use
NH 91 91 A1 Not in use
CL 92 92 A1 Not in use
HI 93 93 A1 Not in use
H 94 94 A1 Not in use
CM 95 95 A1 Not in use
CH 96 96 A1 Not in use
WD 97 98 A2 Not in use
WP 99 100 A2 Not in use
WH 101 102 A2 Not in use
SD 103 104 A2 Not in use
SP 105 106 A2 Not in use
SH 107 108 A2 Not in use
ATTI 109 110 A2 Attachment ID (=99)
ATTL 111 112 A2 Attachment length (set at 0, meaning "unspecified")
ATTE 113 113 A1 Attachment encoding (blank: ASCII)

 

Here follows the description of the attachment to the core. The total record length (core + attachment) is 8569, excluding CR/LF.

Variable Start End Format Description Remarks
InstAbbr 114 121 A8 Abbreviation of the Institute where the original data is stored  
InstName 122 171 A50 Name of the Institute where the original data is stored  
InstPlace 172 181 A10 Place of the Institute where the original data is stored  
InstLand 182 195 A14 Country of the Institute where the original data is stored  
NumberEntry 196 210 A15 Administrative number under which the data is found within the Institute  
NameArchiveSet 211 227 A17 Administrative name under which the data is found within the Institute  
ArchivePart 228 266 A39 Part of the archive set in which the data is found  
Specification 267 297 A31 Specification of the part of the archive set in which the data is found  
LogbookIdent 298 327 A30 Identification of the logbook that holds the underlying data a number or code
LogbookLanguage 328 334 A7 Language used in the logbook  
ImageNumber 335 357 A23 Identification Number of the original image  
Illustr 358 358 A1 True if there is an illustration available on the current page of the logbook.
 
drLatDeg 359 360 A2 Dead reckoning degrees latitude  
drLatMin 361 362 A2 Dead reckoning minutes latitude  
drLatSec 363 364 A2 Dead reckoning seconds latitude  
drLatHem 365 365 A1 Dead reckoning hemisphere (N/S)  
drLongDeg 366 368 A3 Dead reckoning degrees longitude  
drLongMin 369 370 A2 Dead reckoning minutes longitude  
drLongSec 371 372 A2 Dead reckoning seconds longitude  
drLongHem 373 373 A1 Dead reckoning hemishere (E/W)  
LatDeg 374 375 A2 True degrees latitude  
LatMin 376 377 A2 True minutes latitude  
LatSec 378 379 A2 True seconds latitude  
LatHem 380 380 A1 True Hemisphere (N/S)  
LongDeg 381 383 A3 True degrees longitude  
LongMin 384 385 A2 True minutes longitude  
LongSec 386 387 A2 True seconds longitude  
LongHem 388 388 A1 True Hemisphere (E/W)  
LatInd 389 390 A2 Position Indicator, explains what was the origin of the given final decimal latitude 1: originates from dead reckoning
2: originates from true navigation (bearing/distance, celestial)
3: Interpolated manually
4: Interpolated
5: Inserted actual position (ports, islands, etc.)
6: Missing
LonInd 391 392 A2 Position Indicator, explains what was the origin of the given final decimal longitude 1: originates from dead reckoning
2: originates from true navigation (bearing/distance, celestial)
3: Interpolated manually
4: Interpolated
5: Inserted actual position (ports, islands, etc.)
6: Missing
ZeroMeridian 393 435 A43 Meridian that is used as the reference for the ships' longitude See: Geodata21.txt
LMname1 436 485 A50 Name of first landmark of which a bearing was recorded  
LMdirection1 486 500 A15 Bearing of the first landmark  
LMdistance1 501 507 A7 Distance to the first landmark Units
LMname2 508 557 A50 Name of second landmark of which a bearing was recorded  
LMdirection2 558 572 A15 Bearing of the second landmark  
LMdistance2 573 579 A7 Distance to the second landmark Units
LMname3 580 629 A50 Name of third landmark of which a bearing was recorded  
LMdirection3 630 644 A15 Bearing of the third landmark  
LMdistance3 645 651 A7 Distance to the third landmark Units
PosCoastal 652 652 A1 1 If position is considered to be coastal (in port or near coastal disturbances)  
Calendar 653 654 A2 Calendar used in original: 1=Julian; 2=Gregorian  
Year 655 658 A4 Year of the observation from logbook  
Month 659 660 A2 Month of the observation from logbook  
Day 661 662 A2 Day of the observation from logbook  
TimeOB 663 664 A2 Time on board of the observation from logbook  
DayOfTheWeek 665 673 A9 Day of the week (e.g. Monday - Sunday)  
PartDay 674 683 A10 If applicable e.g morning, evening or night may be noted  
Watch 684 688 A5 Watch, name of the period of 4 hours which is known as a "watch" or duty on board ships  
Glasses 689 690 A2 Number of glasses within a watch; one watch equals 8 glasses  
StartDay 691 705 A15 Start time of the ships' day (midnight or midday)  
ShipName 706 735 A30 Name of the ship  
Nationality 736 743 A8 Nationality of ship and crew  
ShipType 744 758 A15 Type of ship; e.g. schooner, barque, frigate  
Company 759 792 A34 Company which owns or manages the ship  
OtherShipInformation 793 1601 A809 Other ship information  
Name1 1602 1631 A30 Name of principal observer  
Rank1 1632 1656 A25 Full rank of principal observer  
Name2 1657 1686 A30 Name of second observer  
Rank2 1687 1711 A25 Full rank of second observer  
Name3 1712 1741 A30 Name of third observer  
Rank3 1742 1766 A25 Full rank of third observer  
LifeOnBoard 1767 2445 A679 Any remarks, found in the logbook on this particular day, about life, illness, treatment, etc. on board.  
VoyageFrom 2446 2490 A45 Place or country where the ship departed from  
VoyageTo 2491 2540 A50 Place or country where the ship sailed to  
Anchored 2541 2541 A1 1 If ship at anchor or moored  
AnchorPlace 2542 2591 A50 Ship at anchor or moored at this place  
DASnumber 2592 2597 A6 Dutch Asiatic Shipping Number
  
VoyageIni 2598 2605 A8 Initial date of the start of the voyage of this ship. Format is year-month-day (YYYYMMDD) and is extracted from the logbooks date (i.e. it is not corrected to UTC)  
CMG 2606 2620 A15 Course made good; course of the ship during the past 24 hours  
ShipSpeed 2621 2625 A5 Ships' speed; average speed of the ship during the previous 24 hours  
Distance 2626 2632 A7 Distance travelled during the previous 24 hours Units
EncName 2633 2657 A25 Name of ship that was encountered For cross-references encounters with other ships or fleets were noted.
EncNat 2658 2682 A25 Nationality of ship that was encountered  
EncRem 2683 3361 A679 Remarks on encountering  
CargoMemo 3362 3569 A208 Any remark found in the logbook on this day about the cargo  
ShipAndRigMemo 3570 4433 A864 Any remark, found in the logbook on this particular day, about the ship, sails, rigging, etc.  
BiologyMemo 4434 4918 A485 Any remark, found in the logbook on this particular day, about any biological item  
WarsAndFightsMemo 4919 5480 A562 Any remark, found in the logbook on this particular day, about wars and fights in which this ship was involved  
OtherRem 5481 7252 A1772 Other remarks, that were not possible to put in the other remark fields  
AirThermReadingUnits 7253 7262 A10 Units of the (outside) air thermometer reading  
SSTReadingUnits 7263 7277 A15 Units of the sea surface thermometer reading  
AirPressureReadingUnits 7278 7296 A19 Units of the reading of the air pressure
 We've established 20 different units or ways of writing down the air pressure. All used notations are described here.
BarTempReadingUnits 7297 7306 A10 Units of the reading of the attached thermometer  
RefenceCourse 7307 7321 A15 Reference that is used for the ships' course (e.g. true north or magnetic north)  
ReferenceWindDirection 7322 7336 A15 Reference that is used for the wind direction (e.g. true north or magnetic north)  
Decl 7337 7340 A4 It is generally accepted by the CLIWOC team that the wind directions, reported by the ships’ officers, were relative to the magnetic North direction. Navigators were well aware of the difference between the true and magnetic north direction, but the compasses (we assumed that the bearing-compasses were mostly used for determining the wind direction) were not always adjusted. Therefore the wind direction has to be corrected. The magnetic declination (or variation, as it is known on board ships) is given for the whole CLIWOC period (1750-1850) for every 5x5 degree square. The number was added to the wind direction to get the true wind direction (D).  
DistUnits 7341 7360 A20 Units of distances, other than the following two fields  
DistToLandmarkUnits 7361 7367 A7 Units of distances to landmarks  
DistTravelledUnits 7368 7374 A7 Units of distances travelled by the ship  
LongitudeUnits 7375 7399 A25 Longitudes used (e.g. 180E-180W or 0-360E)  
UnitsOfMeasurement 7400 7444 A45 Units of other values that may be used (e.g. current speed units)  
HumidityUnits 7445 7451 A7 Units of the reading of the humidity instrument  
WaterAtThePumpUnits 7452 7466 A15 Units of the amount of water reported at the pump  
WindScale 7467 7486 A20 Name of, or reference to the wind scale that has been used  
BarometerType 7487 7499 A13 Barometer type In some occasions there were two readings in the logbook. One from the (mercury) barometer and one from the sympisiometer. When the first reading was not available, the reading of the sympisiometer was recorded in the database.
BarometerBrand 7500 7505 A6 Brand of the barometer
  
BarometerIndex 7506 7508 A3 Index correction to the barometer
  
HumidityMethod 7509 7518 A10 Method of measuring the humidity  
EstError 7519 7538 A20 Established error of the compass  
ApplError 7539 7558 A20 Applied correction to the compass  
TairReading 7559 7564 A6 Reading of the (outside) air thermometer Units
SSTReading 7565 7570 A6 Reading of the sea surface thermometer Units
BaroReading 7571 7578 A8 Reading of the air pressure Units
AllWindDirections 7579 7676 A98 All reported wind directions on this day; the wind direction is the direction that points to where the wind comes from.  
CurrentDir 7677 7691 A15 Direction of the current; This direction points to where the current is flowing towards to In many Dutch (extract) logbooks, symbols were used. These symbols were replaced by codes, to make entry of this data possible
CurrentSpeed 7692 7741 A50 Speed of the current Units
BarTempReading 7742 7745 A4 Reading of the attached thermometer Units
PumpWater 7746 7749 A4 Amount of water reported at the pump Units
HumReading 7750 7753 A4 Reading of the humidity instrument Units
AllWindForces 7754 7995 A242 All reported wind forces on this day  
Weather 7996 8145 A150 Weather description In many Dutch (extract) logbooks, symbols were used. These symbols were replaced by codes, to make entry of this data possible
PrecipitationDescriptor 8146 8245 A100 Information about the precipitation  
StateSea 8246 8395 A150 State of the sea In many Dutch (extract) logbooks, symbols were used. These symbols were replaced by codes, to make entry of this data possible
ShapeClouds 8396 8445 A50 Shape of the clouds In many Dutch (extract) logbooks, symbols were used. These symbols were replaced by codes, to make entry of this data possible
DirClouds 8446 8489 A44 Directions of the clouds  
Clearness 8490 8539 A50 Clearness/brightness of the sky In many Dutch (extract) logbooks, symbols were used. These symbols were replaced by codes, to make entry of this data possible
CloudFrac 8540 8541 A2 Clouded part of the sky  
Gusts 8542 8542 A1 1 If wind gusts are reported  
Rain 8543 8543 A1 1 If rain was reported  
Fog 8544 8544 A1 1 If fog was reported  
Snow 8545 8545 A1 1 If snow was reported  
Thunder 8546 8546 A1 1 If thunder and or lightning was reported  
Hail 8547 8547 A1 1 If hail was reported  
SeaIce 8548 8548 A1 1 If sea-ice or icebergs were reported  
TrivialCorrection 8549 8549 A1 1 If a trivial correction was made in this observation  
Release 5850 8569 A20 Number of the CLIWOC release in which the current record appeared first. If corrections/modifications were made to the observation, the release number was increased to the current version

Release so far:
0.4: 7 Oct 2003
1.0: 20 Nov 2003
1.1: 23 Jan 2004
1.5: 15 Apr 2004
2.0: 14 August 2007
2.1: 25 September 2007