I had the same problem with not seeing WeatherBug in the store from my N900, and being redirected after clicking on the link to the main store.

Here's what I did to solve this;

1. Access the Ovi Store from your N900.
2. Select the first app that you see (Firefox for me)
3. Edit the link now.
For example if you selected Firefox, delete everything on the URL and just leave http://store.ovi.mobi/content/26002
4. Replace 26002 with 36046 and hit ENTER.
5. Download WeatherBug.

Hope this helps!