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).
