A Finland Swede in Bavaria

Monday, 12 October 2009

Munich or Helsinki: A Comparison of Marathons

With a bit of a bad conscience, I went for Block A yesterday. Block A was for runners with a goal time of below 3:45, Block B for the rest. My personal record was 3:52:20 and my target time that I had entered into the Munich Marathon website was 3:55. Thanks to perfect weather, less drinking and a relaxed running attitude, I fulfilled my "promise" to my fellow runners and finished at 3:43:42.

The topic of the blog entry isn't PRs but a comparison between my last two marathon runs: Helsinki 15 August 2009 and Munich 11 October 2009. Which city was more fun? Which run is better organised? What makes the atmosphere different?

Let's start with the objective facts.

1. Tradition: The marathons are more or less equally old, Helsinki a bit older. This year saw the 29th Helsinki City Marathon and the 24th Munich Marathon. Hence, both will have celebrations in 2010.

2. Size: The marathon runs are about equally popular, Munich slightly bigger. The press releases talk about 6472 (Munich) and 6041 (Helsinki) starters.

3. Elite: Helsinki had the clearly faster winner this year, with 2:22:32 over five minutes faster than Munich (2:28:11). Neither Munich nor Helsinki had any bigger rush of Kenyans this year.

4. International flair: Munich had participants from 61 countries, Helsinki from 46.

5. Finisher: Both races saw the ratio of finishers at slightly above 80%. In Munich 5397 of 6472 registered reached the finish line (83,4%), proportionally a bit more than in Helsinki (4939 of 6041 makes 81,8%).

6. Pace: The Munich runners clearly have much higher ambitions or at least a faster pace than those in Helsinki. In Munich, I finished at 1956th place (the 1811th male, with 145 ladies finishing before me). This means that 36 % were faster than me. With an 8 minutes slower time, I had just 25 % before me in Helsinki (finisher 1228 of 4939).

Let's now proceed to the more subjective perceptions.

7. Start: Munich is separated into two blocks, A for the faster ones, B for the slower ones. It appears as if the participants indulge in more wishful thinking in Munich than in Helsinki, when estimating their finishing time. In Helsinki I stood behind the pacemakers for 3:45 (in Finland we call them hares), and in only 35 seconds, I had passed the starting line. In Munich I was (with a bad conscience, as I already confessed) next to the 3:30 hares, yet got past the starting line only after 1 minute 31 seconds. To a large degree, this is due to point 6 above, i.e. faster runners in Munich.

8. Weather: Perfect in both cities. Not too hot, not too cold. Luckily, it was cloudy in Helsinki (as it would otherwise have been too hot in August) and sunny in Munich (as it would otherwise have been too cold in October).

9. Olympics: Helsinki 1952, Munich 1972. Hence both cities can offer the opportunity to finish at an Olympic stadium. This shouldn't be underestimated. The feeling is fantastic! I felt the difference clearly in Helsinki this year, as the Olympic stadium was closed for repairs.

10. Route: Both are great. In both cities, you can see the sights of the inner city; even if I'm patriotic, I have to confess that Munich is the winner on this account. In both cities, you run through nature; as I like water, Helsinki is the clear winner here, in spite of Europe's biggest inner-city park, Englischer Garten, in Munich. In Helsinki you see the elite runners at a later point in time and hence better, which is great. In Munich, the route is flatter -- about at km. 38 one runs into a bit of uphill in Helsinki (Tilkka), which leads many (including myself) to swap running for walking.

11. Music: Munich is the uncontested winner. Lots more music, hence also better atmosphere. The run through the Big Marathon Gate in the Olympic stadium is emotionally unbeatable, largely due to the music.

12. Transport: Helsinki was easier on local transport, at least for me. Public transport in Munich is generally near perfect, but the distances from the venue to trams or U-Bahn seem large especially after the run. Helsinki feels more intimate.

13. Provision: Both are very good. Munich still is the undisputed winner, because of the Weihenstephaner Hefeweizen. As a non-German, it's hard to imagine, but between km 37 and 38, not just water, bananas and energy drinks are on offer, but beer! Sure, alcohol free, but still. My stomach being very sensitive during long runs, I had my doubts in advance. Still, the beer tasted great and didn't cause any issues. Felt good!

14. Chip return: Helsinki is the clear winner. The chips are returned immediately after the goal. In Munich, you have to walk for what easily feels like another five km on tired legs, to return the chip somewhere far, far away. This was for me the single biggest disadvantage for Munich.

15. Showers and sauna: Helsinki is the winner. Both races offer showers in an Olympic swimming stadium. Due to cultural differences, this automatically includes a sauna visit in Finland. And what an atmosphere in the sauna! Satisfied faces, comparison of finishing times, encouragement. Pure joy in the chock full saunas. In Munich, I also went for the sauna (at an extra cost of 12,80 €). What a contrast! A place filled with silence. A majority of non-runners. Perfect for regeneration, but not necessarily as part of the marathon experience.

16. Gifts: Munich is the winner. Directly after the finish, you don't only get a medal (like in Helsinki), but you get your name and finishing time engraved on the medal. Not bad. Still, that's nowhere near the Weihenstephaner Hefeweizen as an unforgettable memory.

17. Finally: The time. If I'd only focus on the finishing time, the winner would be Munich, by 8 minutes 20 seconds. I hadn't trained any more, and the external conditions were similar. I believe the trick was drinking less. In Munich, I didn't feel sick at the end, but could just be happy about the whole marathon experience.

For me, as an avowed Runnist, it isn't about the time. It's about enjoying the run. And the fun was equally big in both Helsinki and Munich. Both races are worth a trip!


Monday, 14 September 2009

Käthe Arnö 30.3.1925-29.8.2009

For a long while I've thought about how I best share my mother's passing away with my friends and acquaintainces over the net. I didn't feel it was appropriate to publish such matters in a blog entry; it felt too "private".

Then I saw her obituary notice in Hufvudstadsbladet (getting the paper takes a while when you live in exile, like I do). "Husis" is the Finland Swedish newspaper par excellence, and for generations, it's been used also for announcements in the deaths column. However, in the current global generation, not every friend subscribes to Husis. Hence, I decided to write a counterpart of the obituary notice for the web. But let me start with the one in Husis:

My mother was 84 years when she passed away. She was completely present, intellectually and emotionally, still on her death bed. I had the fortune, together with Alexander, to be able to discuss with her for nearly two hours in the same night that she passed away at 3:15.

The funeral was one week later in Lovisa, and we will place the urn into the grave in Nagu on Saturday this week. By coincidence we were in Nagu when everything happened, and prolonged our stay by a week. Since the funeral, Kirsten and I have spent a good half week in Croatia (Krk, Porec); slowly, life is returning to normal.

Here is an excerpt from my commemorative speech at the funeral, translating from Swedish to English. I've retained the word "famo" which literally means "paternal grandmother" and comes from the words for "father's mother":


Dear funeral guests
Alexander and Sophia

Famo wants us to be happy. Her character was such that it was very easy to please her when she was still alive. Now that she's no longer with us, this particular wish feels hard to fulfil.

Yet we have so much to be happy about.

We can start with Famo's words on hear death bed: "I am satisfied with my life". Not everyone is satisfied with their lives. I'm happy she was. I am also happy that she was given a long and very prosperous marriage, with Fafa.

Alexander and I could personally say our goodbyes to her, while she still listened and answered. In the car on our way to the hospital, when we still didn't know whether we would make it in time, we had plenty of time to think about what we wanted to say to her -- so that it wouldn't have to wait until we spoke to completely deaf ears. But we made it, and we could say what we had on our minds, so that she could hear it, and answer.

Even though I don't sound very happy right now, I feel that our possibility to properly say goodbye is something to be very happy about. Famo prepared us in the best possible way for the time after her death. We had the time to speak about everything that needs speaking about. That we should think "now Famo is happy" when we let down her urn into the grave, next to Fafa. Which funeral undertaker she wanted. And especially, that we are very happy and deeply grateful for her contribution, her life, her character, her inspiration, her judgement.

I am happy she got her will, that her suffering ended.

I am happy she could live at home her whole life. I am happy and proud that she bravely endured becoming nearly blind and deaf. I am happy she took most things in life in good mood and with humour. A few weeks before she died, when she visited us in her own old house in Nagu, Alexander said "You do hear better than you can see, don't you?". Her reply was "What?". But a second later, she realised what Alexander had asked, whereupon Famo, Alexander and Sophia laughed heartily together.

I am glad she remained mentally agile all her life. She was a bit worried to become senile, but perhaps that is exactly what is needed, in order not to become senile.

I am glad she was so positive. "I am an incurable optimist", she used to say. She was an expert at giving whatever happened a positive interpretation, in seeing light in darkness. Without that ability, I'm sure she wouldn't have lived as long as she did.

I am happy she kept up to date with modern life. She played computer games since the early 1980s, first on my ABC80 and ABC800. Then, she bought Monty's Commodore. After that, a few Windows computers followed suite, and soon enough, there was Internet and surfing and email and Skype. She kept surfing and skyping until the last days of her life, even if it took ages for her to read headers and find the keys on the keyboard, using a magnifying glass. With stoic patience, she noted that she could have spent an eternity in Wikipedia, as she had browsed through everything interesting on the DVDs of the Nationalencyklopedin already, "but I can't see".

She put others before herself, and didn't even understand how much influence she gained by doing so. "The people of Finland shall be lead from the front", General Adolf Ehrnrooth used to say, and my mother lived by that exact thought. Her leadership was based on humbleness, respect, consideration, helpfulness, and empathy. I explained to her that, to the degree I am a good father, it's because of her being my role model. Her reaction wasn't primarily one of pride, but a certain heaviness of heart, that I hadn't properly considered my father in my thought.


This was a longish excerpt from my commemorative speech. I tried to pick such items that are descriptive of my mother as a person, without giving away what is too private. To begin with, I didn't want to say anything at all on the web, because of respect for her perhaps seeing it as self-assertion incompatible with her humbleness. I decided to write what I've written because I want my friends and acquaintances to know, to realise, to understand something that has characterised me so deeply as a person.

I want to conclude by thanking everyone that has given us their condolences. It hurts to lose one's primary role model in life.

Sunday, 26 July 2009

Why a world religion? And in 20 languages?

In a blog entry I've explained why I consider running a religion and why I want to spread this religion with social media. Still, what's the point with doing it worldwide? Isn't enough to do it close to my own front door, either at home in Munich, Germany or in my native Finland?

The reason relates to my interest for other countries, cultures and languages. Through my work, I've travelled a lot especially last year, as Ambassador of the product of my former employer. As I got the assignment, I had decided for the duration of my ambassadorial travels to give the first five minutes of my presentation in (one of) the local language(s). And that's what I ended up doing. It all started with Italian; of all languages that I don't speak, I speak Italian the best. Thereafter I presented in Japanese (Swedish sounds, Finnish Staccato). And after I could make myself reasonably understood by an audience in Chinese, I lost the respect for the difficulty of other languages; since then, I've added Russian, Turkish, Spanish, Portuguese, Czech, Estonian, Latvian und Lithuanian to the list. My respect for the value of other languages, on the contrary, grew immensely. The world doesn't become a better place through everyone speaking Bad English with each other.

Clearly, additional languages also represent an extra cost. I cannot present all aspects of Runnism in all languages. Hence I will mostly use the languages that I know best and that I have the most contact with. On that list, Swedish comes first, as my native language and connector to relatives, friends and acquaintances in Finland (and Sweden). On second place, there's German, as I live in Munich since three years and have a German wife (since 17 years). On third place, there's English, as a lingua franca for all friends and acquaintance that neither understand Swedish nor German.

As a next step I contemplated what Runnism should be called in other languages. I concentrated on languages of European origin, and added Chinese (蘭主义) and Japanese (ラニズム) for good measure and for global credibility. I ended up with a list of twenty languages, in which I might write a tweet or two. When translating Runnism I used the analogy to Buddhism. For example, Runnism is called Runnilaisuus in Finnish (analogously to Buddhalaisuus), Runizam in Croat (Budizam), Руннизм in Russian (Буддизм), Runnismo in Italian (Buddhismo) and Runizmas in Lithuanian (Budizmas).

Hence: A world religion, since running is good for all of us and since running connects us beyond the borders of countries and languages. And twenty languages, as I see it as a sign of respect to use the language of one's counterpart.

The Seven Legs of Runnism

Islam builds on Five Pillars. In Buddhism, there are Four Noble Truths and the Eightfold Path. For Judaism and Christianity, the Ten Commandments form a moral foundation.

Runnism has legs, seven of them.

The Seven Legs of Runnism form the Creed of the Religion of Running.

A true Runnist

  1. lives life to the fullest

  2. values long-term health

  3. understands well-being doesn't come without sacrifice

  4. competes only against his or her earlier self

  5. improves gradually, not overnight

  6. may occasionally feel pain but seldom suffers

  7. sees running as a gift, not a burden

Runnism worships physical well-being.

Saturday, 25 July 2009

Runnism, the Religion of Running -- why?

Last Wednesday wasn't a normal day for me. I went to a physical Twitter event here in Munich, where I announced my plans to start a new world religion.

This humble intention of mine may need some explanations, lest I will be declared insane. That may happen anyway, though.

Point one: I enjoy running. And when I run, I have time to think. Running rests my soul; when running, simple answers to complex questions often appear to me; I've learnt a lot from the community of runners. I think there's a lot of similarity between running and religions.

Point two: I like to share the pleasure of running with others. Many relate to running as something boring, tiresome, a burden. Before I became a runner, that's how I thought of it myself. Yet, no need for those negative thoughts! Today, running gives me inspiration, variation, a source of energy and of freedom. Running is a gift.

Point three: I believe that social media enable the spreading of that thinking. Through my work I have been exposed to and acquainted with what's called Web 2.0; the human contacts that grow from social media are unbeatable in both quantity and quality.

Consequently, I've decided to combine the above thoughts. So far, I've shared my plans only in German at the #Twittwoch meeting in Munich, which was also filmed.

Let's see what comes out of this plan!

Thursday, 14 May 2009

Home-Made Blog Statistics from WordPress MU

For long, I had wanted to get some statistics on the number of blog posts I do per month and blog on blogs.arno.fi. For even longer, I had had abstinence symptoms from not doing almost any coding at all this century. Today, I combined the desire for statistics with the desire for some semi-coding and got some statistics done. And this is how I did it.

First, I logged into MySQL on the server where my WordPress MU resides. And I did a "use wordpress" to start using the Wordpress MU database. A first SHOW TABLES showed a whopping 153 tables. Wow! That comes from me (and my family members) having 18 blogs, each of which requires 8 tables. The remainder, 153 - 8*18, is 9 tables. Here are the tables, and what I found out about them while eagerly SELECTing from them:

wp_blogs: The key WordPress table, listing the blogs

mysql> select blog_id,path,registered,last_updated from wp_blogs;
| blog_id | path | registered | last_updated |
| 1 | / | 2008-09-20 15:15:54 | 2008-10-19 14:39:15 |
| 2 | /fussball/ | 2008-09-20 15:19:12 | 2008-10-07 22:07:09 |
| 4 | /efib/ | 2008-09-20 15:41:14 | 2009-03-16 09:51:48 |
| 5 | /fib/ | 2008-10-13 10:44:16 | 2009-05-11 21:34:25 |
| 6 | /dolce_vita/ | 2008-10-25 00:54:01 | 2009-05-03 08:18:41 |
| 7 | /fandorin/ | 2008-10-25 02:35:46 | 2009-04-16 11:48:50 |
| 8 | /isit/ | 2008-10-27 14:39:57 | 2009-05-12 16:42:24 |
| 9 | /linnea/ | 2008-10-31 20:26:30 | 2008-11-16 19:13:43 |
| 10 | /poquito/ | 2008-11-03 22:15:21 | 2008-11-09 22:21:48 |
| 11 | /vaba_lava/ | 2008-11-17 08:33:04 | 2008-11-17 10:44:24 |
| 12 | /labrit/ | 2008-11-17 10:02:50 | 2008-11-19 09:10:03 |
| 13 | /laisvas_zodis/ | 2008-11-17 12:22:51 | 2008-11-18 06:48:00 |
| 14 | /alman_degilim/ | 2008-11-18 10:41:46 | 2008-11-20 21:25:48 |
| 15 | /sushi/ | 2008-11-18 23:51:00 | 2008-11-22 13:28:29 |
| 16 | /furuvik/ | 2008-12-07 14:19:10 | 2009-05-04 14:13:53 |
| 17 | /kajsql/ | 2009-02-11 10:25:26 | 2009-05-06 16:28:08 |
| 18 | /guanxi/ | 2009-05-06 18:26:24 | 2009-05-06 18:50:29 |
| 19 | /yilingyi/ | 2009-05-06 18:30:02 | 2009-05-06 20:00:25 |
18 rows in set (0.00 sec)

Conclusion: blog_id and path are the main fields

The other eight top-level tables

mysql> show tables where Tables_in_wordpress > "wp_a";
| Tables_in_wordpress |
| wp_blog_versions |
| wp_blogs |
| wp_registration_log |
| wp_signups |
| wp_site |
| wp_sitecategories |
| wp_sitemeta |
| wp_usermeta |
| wp_users |
9 rows in set (0.00 sec)

Browsing through the other tables, they seem to be best left for browsing and editing through the user interface of WordPress itself.

The eight blog-level tables in WordPress MU

The tables on the blog level are named wp_1_blahbah, wp_2_blahblah, wp_3_blahblah and so on. I can't say that this is a better table design than merely entering blog_id as a further key in each of the tables, but hey, I'm a happy WordPress user and not out to redesign their database structure. Besides, they may well have solved some scaling issues by adding eight new tables for each new blog. However, the complexity of the database structure will affect my job of doing statistics. And looking at life from a positive standpoint, it will increase the amount of programming joy I get out of my desire to do relevant blogging statistics.

There are, as said, eight tables for each blog. Let's pick my blog no 8, /isit/ (this very blog, "It's Some Interesting Topic").

mysql> show tables where Tables_in_wordpress like "wp_8_%";
| Tables_in_wordpress |
| wp_8_comments |
| wp_8_links |
| wp_8_options |
| wp_8_postmeta |
| wp_8_posts |
| wp_8_term_relationships |
| wp_8_term_taxonomy |
| wp_8_terms |
8 rows in set (0.01 sec)

The two most interesting ones here are wp_n_posts and wp_n_comments. The three tables named around "terms" are about tagging and automatically updated through WordPress. The wp_n_options (blog level options) and wp_n_links (blogrolls etc.) tables are relevant, but adequately browsable and editable through the WordPress user interface. The same goes for the field settings in wp_n_postmeta.

The wp_n_posts table

The wp_n_posts table has 24 fields. Browsing at them for a while, here are the key ones

  • ID: Primary key (1, 2, 3 ...)

  • post_date: Publishing date of the post. Crucial for statistics. Remember, you can backdate posts or write posts in advance.

  • post_modified: Editing date of the post. This is when you last touched the post.

  • post_type: This is usually "post", for normal, published blog posts. For what WordPress calls pages, it's "page". But there are also plenty of instances with "revision" (for intermediate versions of the blog post, if you save and edit it multiple times) and "attachment" for instance if you upload a .jpg file. Consequently, where post_type in ("page","post") becomes an essential part of many blog statistics SELECTs.

  • post_title: The header of the blog entry

  • comment_count: Number of approved comments

  • comment_status: Either "closed" or "open" depending on whether comments are allowed or not. I have combatted spam by disallowing comments for particularly infested entries, using the normal WordPress UI. I could bulk reallow comments on all closed entries with a simple UPDATE on comment_status, once I've installed better spam prevention.

  • guid: the complete URL to a post, such as http://blogs.arno.fi/isit/?p=211 (which then will be converted to http://blogs.arno.fi/isit/2009/03/16/a-digital-native-and-my-first-nokia/ by WordPress once you surf to it).

  • post_name: contains the "a-digital-native-and-my-first-nokia" part of the URL (which is neatly editable from within the WordPress UI).

  • post_status: it's "inherit" or "publish" depending on whether the post is live or not -- but I prefer using post_type to identify real entries from earlier versions

My first attempt at statistics

This knowledge enables compact statistics, such as:

mysql> select date_format(post_date,"%Y-%m") as Month, count(*) as Blogs
-> from wp_8_posts
-> where post_type in ("page","post")
-> group by 1;
| Month | Blogs |
| 2008-10 | 11 |
| 2008-11 | 15 |
| 2008-12 | 3 |
| 2009-01 | 4 |
| 2009-02 | 3 |
| 2009-03 | 3 |
| 2009-05 | 2 |
7 rows in set (0.00 sec)

However, what I'd really like to have is that very same piece of statistics, but for all my blogs in one report. I don't want to run eighteen reports, one for each blog.

And that's where my coding desire can be satisfied.

My idea is to create a new, aggregate table for statistics, where I insert one row for each blog entry in each of the eighteen blogs.

Let me first create the table, based on the above blog (numbered 8 and called "/isit/") with its 41 blog entries. It's a simple CREATE as documented as the second example on http://dev.mysql.com/doc/refman/5.1/en/create-table.html -- in my case like this:

mysql> CREATE TABLE kaj_wp_stats
-> SELECT 8 as blog_id,"/isit/" as path,
-> ID,post_type,post_date,post_title,comment_count,post_modified,guid
-> FROM wp_8_posts
-> WHERE post_type IN ("page","post");
Query OK, 41 rows affected (0.00 sec)
Records: 41 Duplicates: 0 Warnings: 0

I specifically mentioned 8 as blog_id and "/isit/" as path in order to later be able to separate blog entries by blog.

Then I take the very same SELECT statement and use it for another blog, but substitute the CREATE with the equivalent INSERT statement:

mysql> INSERT INTO kaj_wp_stats
-> SELECT 4 as blog_id,"/fib/" as path,
-> ID,post_type,post_date,post_title,comment_count,post_modified,guid
-> FROM wp_4_posts
-> WHERE post_type IN ("page","post");
Query OK, 27 rows affected (0.00 sec)
Records: 27 Duplicates: 0 Warnings: 0

Note the changes: "INSERT INTO", "4", "fib" and "wp_4_posts".

Generating SELECTs with SELECTs

Now, I could of course do a lot of error-prone manual labour and insert the following 16 blogs in the same way. But I'm lazy and I desire some coding, so instead, what I do is that I create the corresponding code with a SELECT statement from the wp_blogs file. This is my meta-code:

select concat("INSERT INTO kaj_wp_stats SELECT ",blog_id," as blog_id,'",path,
"' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_",
blog_id,"_posts WHERE post_type in ('page','post');") from wp_blogs;

In this metacode, the lower-case "select" and "from" is what is executed. The upper case INSERTs and SELECTs end up in the output, which looks like this:

INSERT INTO kaj_wp_stats SELECT 1 as blog_id,'/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_1_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 2 as blog_id,'/fussball/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_2_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 4 as blog_id,'/efib/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_4_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 5 as blog_id,'/fib/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_5_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 6 as blog_id,'/dolce_vita/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_6_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 7 as blog_id,'/fandorin/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_7_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 8 as blog_id,'/isit/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_8_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 9 as blog_id,'/linnea/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_9_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 10 as blog_id,'/poquito/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_10_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 11 as blog_id,'/vaba_lava/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_11_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 12 as blog_id,'/labrit/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_12_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 13 as blog_id,'/laisvas_zodis/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_13_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 14 as blog_id,'/alman_degilim/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_14_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 15 as blog_id,'/sushi/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_15_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 16 as blog_id,'/furuvik/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_16_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 17 as blog_id,'/kajsql/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_17_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 18 as blog_id,'/guanxi/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_18_posts WHERE post_type in ('page','post');
INSERT INTO kaj_wp_stats SELECT 19 as blog_id,'/yilingyi/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_19_posts WHERE post_type in ('page','post');

I then clean up the kaj_wp_stats table by removing it:

mysql> DROP TABLE kaj_wp_stats;
Query OK, 0 rows affected (0.00 sec)

Now I manually substitute the first INSERT INTO with a CREATE TABLE, and run the select-generated SELECTs.

However, I get Warnings: for each of the INSERT statements -- which is due to the unfortunate fact that the first blog has a path "/", which turns into a varchar(1) into which of course none of the other paths fit. Hence I manually add

ALTER TABLE kaj_wp_stats MODIFY path varchar(50);

right after the first CREATE TABLE statement, and then everything runs fine. Here's a shortened version of it all:

mysql> DROP TABLE kaj_wp_stats;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE kaj_wp_stats SELECT 1 as blog_id,'/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_1_posts WHERE post_type in ('page','post');
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE kaj_wp_stats MODIFY path varchar(50);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0


mysql> INSERT INTO kaj_wp_stats SELECT 5 as blog_id,'/fib/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_5_posts WHERE post_type in ('page','post');
Query OK, 85 rows affected (0.00 sec)
Records: 85 Duplicates: 0 Warnings: 0


mysql> INSERT INTO kaj_wp_stats SELECT 8 as blog_id,'/isit/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_8_posts WHERE post_type in ('page','post');
Query OK, 41 rows affected (0.00 sec)
Records: 41 Duplicates: 0 Warnings: 0


mysql> INSERT INTO kaj_wp_stats SELECT 19 as blog_id,'/yilingyi/' as path,ID,post_type,post_date,post_title,comment_count,post_modified,guid from wp_19_posts WHERE post_type in ('page','post');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Finally, the statistics I desire!

Now, I can do statistics such as this:

mysql> select blog_id,path,count(*) as blogs from kaj_wp_stats group by blog_id,path;
| blog_id | path | blogs |
| 1 | / | 1 |
| 2 | /fussball/ | 6 |
| 4 | /efib/ | 27 |
| 5 | /fib/ | 85 |
| 6 | /dolce_vita/ | 10 |
| 7 | /fandorin/ | 8 |
| 8 | /isit/ | 41 |
| 9 | /linnea/ | 13 |
| 10 | /poquito/ | 9 |
| 11 | /vaba_lava/ | 2 |
| 12 | /labrit/ | 1 |
| 13 | /laisvas_zodis/ | 1 |
| 14 | /alman_degilim/ | 4 |
| 15 | /sushi/ | 2 |
| 16 | /furuvik/ | 38 |
| 17 | /kajsql/ | 2 |
| 18 | /guanxi/ | 1 |
| 19 | /yilingyi/ | 2 |
18 rows in set (0.00 sec)

Or this

mysql> select date_format(post_date,"%Y-%m") as Month,
-> count(*) as Blogs,
-> sum(if(path="/isit/",1,0)) as isit,
-> sum(if(path="/fib/",1,0)) as fib,
-> sum(if(path="/efib/",1,0)) as efib,
-> sum(if(path="/furuvik/",1,0)) as furuvik,
-> sum(if(path="/dolce_vita/",1,0)) as dolce_vita,
-> sum(if(path="/fandorin/",1,0)) as fandorin,
-> sum(if(path="/poquito/",1,0)) as poquito
-> from kaj_wp_stats
-> where post_date > "2007-12-31"
-> group by 1;
| Month | Blogs | isit | fib | efib | furuvik | dolce_vita | fandorin | poquito |
| 2008-02 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2008-03 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2008-04 | 3 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
| 2008-05 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2008-06 | 5 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
| 2008-08 | 3 | 0 | 2 | 0 | 1 | 0 | 0 | 0 |
| 2008-09 | 8 | 0 | 4 | 0 | 0 | 0 | 0 | 0 |
| 2008-10 | 50 | 11 | 11 | 9 | 0 | 6 | 3 | 0 |
| 2008-11 | 59 | 15 | 14 | 3 | 4 | 1 | 3 | 9 |
| 2008-12 | 23 | 3 | 9 | 5 | 3 | 2 | 1 | 0 |
| 2009-01 | 29 | 4 | 7 | 6 | 12 | 0 | 0 | 0 |
| 2009-02 | 23 | 3 | 12 | 1 | 6 | 0 | 0 | 0 |
| 2009-03 | 23 | 3 | 11 | 3 | 6 | 0 | 0 | 0 |
| 2009-04 | 4 | 0 | 4 | 0 | 0 | 0 | 0 | 0 |
| 2009-05 | 12 | 2 | 2 | 0 | 5 | 0 | 0 | 0 |
| 2013-06 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
16 rows in set (0.00 sec)

Mission accomplished! The statistics are there, and I had fun "coding" with MySQL.

Tuesday, 12 May 2009

Photo Manager: How do you keep track of your pictures?

If you're like me, you have tens if not hundreds of thousands of digital pictures. Tendency: Growing. How do you sort them? Archive them? Tag them? Find the right picture? Keep order in your backups?

Personally, I have no good answer to the above questions. I am still looking for the right program -- ideally, Open Source Software that helps me keep track of my digital pictures. So if you have a good answer, please tell me!

This blog entry is an attempt to define the specifications of what I need. I have labelled the software "FOSS Photo Manager", but I do understand it needs a catchier name. Basically, I'm fairly happy about my software for editing pictures. But not for managing my collection of pictures. Perhaps I should call the dreamt-up software "Robfat" (for rename, order, backup, find, archivetag), as I want to remove excess fat from my HDs (and CD/DVD cabinets). I need a lean, easily accessible collection of all my pictures.  

This is what I have:

  • one Mac laptop with a small HD, storing <10 % of my pictures

  • three external HDs with my pictures (some for backup, some as primary storage)

  • over a hundred CDs and DVDs that I burned as backups prior to having several external HDs

Looking at the pictures, I have:

  • over 100.000 pictures in over 1.000 directories

  • mostly the same directories on the various HDs

I also have disorder:

  • some new pictures (edits) I've saved only onto some HDs

  • some directories have been cleaned of bad pictures that I want to throw away, but these pictures still exist on other HDs (and I don't want them to return from the dead) 

  • some CDs and DVDs are clearly unnecessary by now, but may contain some files found nowhere else

My basic needs are

  1. To simplify my workflow from the point in time where I've downloaded the pics to my HD until the point where I've identified the best pics, sorted them, renamed the best ones, and backed them up

  2. To create and maintain a directory structure (while browsing the pics) that makes the pics easier to find

  3. To quickly tag my pictures while browsing them or as a bulk update of an entire directory, with the tags to be stored in the file's own EXIF data in order for it to be accessible from everywhere 

  4. To quickly rank my pictures (quality, one-to-five) or use the ranking from other software

  5. To easily compare the contents of two directories, identify discrepancies and fix them (by deleting pics I don't want to have, and by copying pics I do want to have) so that directories are identical between original and backup

  6. To swiftly harvest CDs / DVDs for any pics (files) that I don't have anywhere on my HD (remembering that I may have renamed them since), so I can throw away the old CDs / DVDs with peace of mind

  7. To retain data about pics on my external HDs even when I don't have the external HDs around (directory structure, file names, dates, times, sizes, tags)

  8. To mark and bulk copy pictures to new directories (for sharing pics, backing them up, creating copies that other software can freely edit, identify pics to upload to Picasa / Flickr / Facebook, or to create copies

  9. To bulk separate portrait from landscape pictures when copying (for better use in digital frames, phones, iPods)

  10. To identify likely siblings of a picture (edited versions, renamed versions -- based on similar file name, file size, date).

  11. To create statistics of my picture collection: How many pics do I have? By year, by tag, by various other criteria.

Ideally (but this isn't a basic need), I would also like to keep track of where I've used which picture (photo books, phones, digiframes, printouts, screen savers, Picasa, Flickr, Facebook).

The deadly sins I don't want this program to commit are

  1. Keep track only of pictures currently accessible. Nope! I want also the external HDs that aren't around, and the CDs/DVDs.

  2. Touch my pixels. Nope! Don't even turn the pics 90 degrees. All editing should be done with separate software. OK, so I'm happy if this software creates thumbnail copies (using ImageMagick or whatnot), if I clearly instruct it to.

  3. Do stuff not related to keeping track of my pictures. Nope, don't email pics. Don't synch them to the web. Don't print pictures. Don't import pictures from cameras or memory cards. Just keep order on my file system.

  4. Be an island like iPhoto. Nope. Everything this software does to create order (structures, tags, albums) should be visible outside the program, in the directory structure, in file names, in EXIF tags.

  5. Force repetitive tasks on the user. Nope. If all pics in a directory need a particular tag, then make it easy to tag them all. If normal workflow requires frequent hand movements between the keyboard and the mouse, then redesign it. 

So: No reinvention of the wheel -- but yes please, an invention of sensible traffic management that relieves me of queuing in the rush hour! Do you have tips for me?

Wednesday, 6 May 2009

On Princes and Databases, and Retroblogging

Through a recommendation by Peter Vesterbacka on Facebook, I popped into the interesting blog of WILLIAM JULIUS OGEDENGBE, a NIGERIAN PRINCE who is skilled at blogging in ALL CAPS.

His posts, today NINE in total, are hilarious. They prompted me to retroblog something semi-Nigerian I wrote in January 2004, after a long working day for MySQL AB in Japan. You'll find it on my /kajsql/ blog.

Retroblog, I hear you ask. What's that? By  “retroblog” I mean a blog entry that is backdated to the date when it was actually written, albeit at that time not intended as a blog entry. So while in a sense this is a milder genre of creative historical revisionism, the only blatant lie is the backdating of the blog entry. The text itself isn't backdated. (Stalin would still likely be proud of me, had I ever been a Soviet citizen).

Modification before hitting "Publish": Googling on "retroblogging" gave me 159 000 hits, so there is clearly prior art when it comes to defining the concept. What a shame. Still, "backdated blog without backdating text" is the only relevant part of my private little definition, anyway.

Monday, 16 March 2009

A digital native (and my first Nokia)

Ola Ahlvarsson, the founder of SIME (a yearly Interactive Media Event in Stockholm) last week came with the best description I've ever heard of a digital native.

We, born before Internet and mobile phones, are digital immigrants. Natives are people, whose first Nokia (ok, I admit, this may be a bit too Finland specific) was not a pair of rubber boots, nor like in my case, an orange swimming ring. They think and talk differently. For example, they just say "camera", not "digital camera". For natives, there are heaps of things that are self evident, that puzzle the immigrants.

The digital native in Ola's true story is a little girl of about 3-4 years (as old as I am in the picture with the swimming ring). The girl has a father who constantly makes tricks and fun. Normally, the girl likes the jokes of the father, but once, in the countryside with Grandma and Grandpa, the father went too far. The girl ran crying to Grandma and explained how Dad had teased her: He had tied the phone into the wall!

My first Nokia, in the summer of 1967. The thing was analog, inflated, orange and taught me how to swim.

Saturday, 7 March 2009

Rasmus Snabb and the new logos

Ask experts for help! That was my seventh New Year Resolution, as described in February. And now I've done it. In this instance, "experts" refers to the graphic designer Rasmus Snabb (from my native Finland but now active at Danmarks Designskole in Denmark). The help was about a logotype for my house Furuvik in Finland, for invitations, flags and signs (described closer in a Swedish language blog entry).

The difference is huge. This is what I could come up with, "all by myself":

And this is Rasmus's creation:

It's fairly easy to distinguish the amateur work from that of the professional.

Consequently, Rasmus got an additional assignment. I had created another home made logotype for kaj.arno.fi. If I may say so myself, it was at least a bit better than the one I made for Furuvik:

However,  I wanted to be visually more consistent when it comes to fonts. Consistency (or lack thereof) becomes especially apparent in my blog banners. And as Rasmus was so quick to find the Art Nouveau font Kolo LP, I asked him to suggest a sans serif font with Nordic or Germanic connotations. He had twelve suggestions, from which my daughter Sophia and I picked the font Avenir by Adrian Frutiger, a Swiss graphic designer born 1928 but still professionally active this century.

Rasmus wasn't fond of the red A in my logo. Furthermore, it wasn't compatible with the upcoming diversity of colours in my blog banners (Swedish, German, English etc.). Hence, Rasmus got the task to redesign the logotype, which now looks like this:

The "irritating detail" here is the too small capital K. Colour versions can be easily combined with several blog palettes. "Kaj" is light (above it's white, in colour it's often yellow), "arnö" is black, and the background somewhere in between. And, as graphic designers often insist, there is a large do-not-touch area around the text.

Perhaps logos don't belong to the necessities of life. But at least they are fairly environmentally friendly (at least in their electronic form) and the good ones are very compatible with my ninth New Year Resolution, Aesthetic values are appealing: Surround yourself with beauty, simplicity and order!
If you buy, then only if it’s functional, useful and beautiful.

Thanks, Rasmus!


Wordpress: The "Painter" theme is good but has too many options

I am a fan of WordPress. It's a great blogging tool, and it's open source. It also has a number of add ons, such as "Themes" whereby banners and colours can be adjusted to personal tastes. Fine.

Now, I downloaded and installed the Painter 0.9 theme by the Brasilian web developer Marcelo Mesquita. It enables exactly what I want: The possibility to configure a banner. That banner has a predefined size of 120 px high times 980 px wide, with which I can live just fine. So far, I have designed two banners: one for my Swedish language blog and one for my blog named Furuvik, about an Art Nouveau building I'm renovating in Finland.

Next come the unwanted side effects.

First, Painter 0.9 introduces hard coded English texts. Normally, WordPress enables the adjustment of most texts (with the exception of dates) so that out-of-context foreign (= English) words don't appear in a normal (e.g. Swedish or Portuguese language) blog. Painter 0.9 introduces the uncustomisable word "News" as a unnecessary header. I'd prefer the header to not even exist, but if it has to be there, then the ugly foreign (= English) word should at least be customisable. The same goes for the word "Home" that appears just below the in the sub header: It's unnecessary, and it's not changeable.

Second, finding my way amongst the oodles of customisable colours takes lots and lots of time. There are too many options! Sure, Marcelo says it's "highly customisable". But I don't need to control that many colours. If I wanted to exert more control, then 

  1. I'd like to express dates in languages other than English

  2. I'd like to determine the order of the areas in the sidebar

  3. I'd like to turn on the usual (but here disappeared) option for navigating from one blog entry to the previous/next one (like in WordPress by default)

  4. I'd like to adjust the font used for the contents (and headers)

  5. I'd like to allow other banner proportions than 120x980 (adjusting, if necessary, the column widths)

Instead, I count a whopping 57 colours (fiftyseven!). Using the Furuvik blog as an example, this is what I changed:

  1. The areas around the banner, to the same colour as the banner background (yellow #F3D539): Dater Background, Menu Background

  2. The subheader backgrounds, to a lighter tone of the banner background (#F1E292): Content Session Title Background, Sidebar Session Title Background, Footer Background

  3. The sidebar blog article listing backgrounds, every second (Sidebar background) to white (#FFFFFF), every second (Alternate sidebar background) to a very light yellow (#FFEDC7)

  4. The blockquote etc. box backgrounds (Box border, Box background), to the same very light yellow (#FFEDC7)

  5. The context backgrounds (Content Post background, Content Alternate post background) to white, so as to work well with embedded images

  6. Most bread text to black #000000 (Dater link, Menu link, Post text, Footer text, Footer links)

  7. Some header type text to dark grey #444444 (Content Session Title, Content Post title, Content Box text, Sidebar Session title, Sidebar text, Sidebar links)

Ideally, I would just have changed each of the above seven colours once and not 23 times. Sure, I understand others may want to influence things in more detail. But on the other hand, I left 57-23=34 colours unchanged, and they will likely now pop up when some esoteric combination of events happens, and my colour palette will look ugly. Ideally, also the 34 untouched colours would somehow be derived from the 7 colours I changed. At any rate, from a usability perspective, the colour changing consumes lots of time. And sadly, to some degree the colour changing is mandatory, as the defaults may not fit with the banner.

All in all, I am still quite happy with Painter. I understand there is a Painter 1.0, but I don't know which of my above wishes are taken into account in 1.0 so I don't know whether I should spend the time to upgrade. 

Obrigado, Marcelo!

Friday, 27 February 2009

A Pictorial Report From An Ascent To Kilimanjaro

The end result was not that my son Alexander and I stood at Gilman's Point, 5681 m above sea level.

Nor was the end result that I stood at Uhuru Peak, 5895 m above sea level, and one of the world's Seven Summits.

Sure, both of those experiences were unforgettable, and they were also personal quarterly goals that I had explicitly stated for myself, as I have described in my New Year's Resolution.

The end result was a resetting of my brain. "So there's not much left, then", you may think -- and you may be right. Still, camping, trekking, walking and climbing for six days and nights gave the feeling of a mental cleansing operation.

That mental cleansing resulted in plenty of blog entries that I wrote without web access while waiting for the flight home via Nairobi and Addis Ababa. Right now, there are six entries in Swedish:

And one entry in German:

Plus one in English:

It may well be that I am too lazy to translate any of the blogs into multiple languages. So here goes the blog entry that describes the trip from a general tourist's perspective, in English!

We started the trip at Rongai Gate, 2200 m above sea level.

The porters weighed our luggage; no porter should carry more than 20 kg.

The pine forest looked almost as at home in Finland.

The outhouse style toilets, in Kilimanjaro dubbed "Internet Café", most definitely did not look like at home in Finland.

We formed a tiny group of three people climbing the Kilimanjaro with the German Alpine Association DAV Summit Club. The third person was Luidger from Gelsenkirchen.

The planted pine trees near Rongai (pronounced Wrong Guy, with the emphasis on "guy") gate had long needless.

This is the usual view on a trek: The shoes of the person in front. In my case, luckily, my son Alexander.

If you look really closely, you'll see a white-tailed monkey in this picture of the rainforest.

We stayed in this Fjällräven tent also at subzero temperatures.

As it rained at some point nearly every day, it was good to be able to eat "indoors" in this tent.

"Internet Cafés" could be found very often. I feared I would receive emails with attachments too often, but luckily, I didn't.

On the second day of walking, from Simba Camp to Third Cave, we got our first glimpse of Kilimanjaro.

The vegetation changed and became more sparse.

We had lunch break at Second Cave.

At Third Cave, there was plenty of space. And here we stayed two nights, to get acclimatised at 3800 m.

Not much vegetation is left at this altitude.

The third day, we went up from 3800 m to 4400 m and came back. That altitude had nearly just a stone desert.

Not that you can see it on this picture of a break at 4200 m, but just before this picture was taken, we saw buffalo footprints (they come here to enjoy licking minerals, something buffaloes can't say no to).

There were landscapes that looked as if the Voyager could land in them anytime.

The fourth day, we walked up from 3800 m (Third Cave) to 4700 m (Kibo Hut). We didn't stay in the hut, but in our tents.

Staying in tents at temperatures well below freezing wasn't all that cold.

The fifth day was summit day, and it actually started with a wake-up call att 22:50 the previous evening. Here is Alexander finishing his porridge, already dressed for the ascent.

We believed we wouldn't even be allowed to climb, as there was a strong thunderstorm. But it didn't rain. Instead, it snowed. And at the beginning, it wasn't all that many degrees below freezing.

After this night, the uppermost 1200 m were all covered in white snow.

I can tell you it is a bit boring to climb for six hours in total darkness, just using a headlamp.

Alexander got a severe headache and vomited several times.

He still made it all the way to the crater rim of Kilimanjaro. At that point, you're "on top" of the mountain, and many climbers return. So did Alexander.

Finally, the sun gets up. Here's the caldera, i.e. the inside of the Kilimanjaro crater.

My own subjective feeling here was not about being tired, or cold. It was about being out of breath. I breathed, in and out, in less than a second during the steeper ascents at close to 6000 m.

This is the classic picture one is supposed to have. And yes, my hands were freezing. I had taken the previous pictures with my heavy Canon 20D and its 8 mm lens.

Don't you agree it's a good idea to have a backup picture of important motives?

This glacier is supposed to have melted away within 20 years.

Day six was the final day. Here we start, 2200 m down from Uhuru Peak, at Horombo on the Marangu route -- often named "the Coca-Cola route" because it's full, it's supposed to be the easiest, and it has huts all the way. And GSM connectivity at the huts. By contrast, Rongai or "the Nature route" had none of the above.

This final day provided lots of nice views, a descent of 2000 m and a 21 km long walk.

The sunrises were beautiful everywhere, and on the Marangu route, they were even more visible.

As opposed to day four, the summit was covered in snow day six.

Strange botanic experiences were on offer.

But also, familiar botanic experiences. This bush looks like what I call "ljung" and find on the islands between Finland and Sweden.

The climbing had by now deteriorated into Nordic Walking.

We had plenty of porters.

And more botanic experiences.

Although the final day of descent, we were still above the clouds.

Kilimanjaro looked beautiful also from the by now longer distance.

There was a fire here, at the end of 2008 -- about 2-3 months ago. Supposedly it started because of natural causes.

Also my son Alexander has mutated into a Nordic Walker.

And 21 km is a fair distance to walk. Only the last third or so is rain forest.

To the left, Kilimanjaro. To the right, Mawenzi, also over 5000 m.

More botany!

No, we don't have anything similar in Finland.

Ah, the rain forest! Very different climatic / botanic zones in quite a compact area.

I tested an alternate method of descent, for people who don't wish to walk. I preferred the walking, and luckily didn't have to resort to wheels.

This is just a few short kilometres from the open areas a couple of pictures earlier.

This is a monument to Hans Meyer, who climbed "Kaiser Wilhelm-Spitze", as it then was called, in 1889.

Hans Meyer was "the first whiteman to climb Mt Kilimanjaro", my son noted with astonishment.

This sign should rather have been at the start of my pictures, but we only descended using the Marangu route. Our ascent was through Rongai.

The reward of the 21 km of walking during the last day? A cold bottle of Kilimanjaro beer!