Story by Vasily Dubovoy.

July 13

Land Surface: I’m sorry I screwed up the print run.

While testing the comparison page, I couldn’t understand why Alaska turns out to be larger than Mongolia. On the projected maps Alaska has fewer pixels than Mongolia. I wasn’t able to notice this earlier as the two countries have been on the opposite sides of the poster since the beginning and in the table Alaska has always remained larger than Mongolia.

The data set has every single one of the 17 179 869 184 pixels and if any line has an extra pixel or misses one, this becomes instantly evident. I’ve been thinking hard about this, projected the maps several times over, but the result didn’t change. At the end I thought that this has something to do with antialiasing and was about to let go when I finally found a shifted cell belonging to Alaska in the data set. The same line had 7 cells of Canada data and the spreadsheet was set up in such a way that Excel counted the area of all these pixels in the line towards the area of Alaska.

Before we started the final typesetting, Marina and I checked the spreadsheet several times exactly to avoid something like this and I was absolutely positive that all data was entered correctly.

Then, knowing where and what to look for, I found four more cells shifted in the same way. At this point I couldn’t understand anymore what data goes where, how many of other similar cells there can be and how many errors they might cause.

Searching bugs like this in a spreadsheet of 36 000 cells is a horrible, inhumane task. Plus, the design of the spreadsheet lends itself poorly to catching errors like this by using logical formulas. It’s just like an old punch card. This was caused by my inexperience of working with data when the project started.

It was clear that we needed to recalculate everything from scratch, with the condition that we should never alter the data manually, that the data sets should never overlap and that we should exclude the human factor entirely from data gathering. I scanned all lines for pixels from scratch and put everything in a single list with no manual adjustments. Now Excel recognizes all lines in the pixel list, determines their area and assembles the data into country areas using nothing but logical formulas. Anyone can verify this, it should be verifiable enough for a scientific publication.

The only thing still done manually is calculating line areas in the INPUT sheet with AutoCAD. I still don’t know how to do this using Excel formulas only, but if I ever get Excel to do it, it would be able to handle data of any standard, and that would be a bomb!

Looking over the results, we can see that the old spreadsheet has 10 shifted cells that resulted in 29 errors. Plus some smaller errors three of which I covered in my previous letter.

I fixed everything, but this won’t save the posters that we already printed ;-(

Here are all the errors.

Printed Should be
Russia 16 523 291 16 523 293
Antarctica 10 255 933 12 447 692
China 9 279 961 9 279 960
Canada 8 851 148 8 951 513
Brazil 8 357 194 8 402 706
Greenland 2 108 634 2 148 362
Saudi Arabia 1 940 611 1 933 260
Alaska (US state) 1 585 264 1 445 171
Bolivia 1 122 444 1 076 932
Madagascar 591 278 590 710
France 539 416 545 040
Vietnam 316 653 324 000
Poland 312 722 307 097
Ecuador No total area
Guinea 245 957 245 958
East Malaysia (Malaysia) 197 072 197 022
Nepal 123 246 147 126
Jordan 112 966 89 086
Portugal No total area
Latvia Map stretched
Denmark No total area
New Caledonia (France) 18 466 18 532
Vanuatu 11 514 12 083
South Georgia and South Sandwich Islands (UK) Map stretched
Mauritius 2 057 1 991
Paracel Islands (Vietnam, China, Taiwan) Not on the poster
Clipperton Atoll (France) 2 3
Europe 22 298 366 22 298 369
Africa 29 679 701 29 679 067
Asia 30 866 977 30 866 922
Oceania 433 731 8 434 366
North America 22 904 475 22 904 476
Antarctica 10 267 732 12 459 496
Water 369 222 374 367 030 660
Land 141 985 520 144 177 233

July 14

That’s it! I calculated everything using only Excel formulas, no AutoCAD. My spreadsheet is now perfect in every way possible :-)

Now I just need to finish the Process part on geometry, refresh the data on the comparison page and we can launch the announcement.

July 31

Land surface: OK, Google! Let’s add the minor radius.

Another chapter of the Process that came out a bit late.

The solution was not mathematical, but if we were to make physical measures, it would be precise. Which means that this chapter of the Process describes a fact, but gives no proofs. Unlike the previous chapter, it means it has no practical use for surveyors and programmers.

August 3

I was able to find a radical solution to the surface area problem. Now it’s a proper investigation (with all the formulas, methods and a variety of examples from different points of view).