Bare Bones have recently released a utility called Weathercal:

WeatherCal presents the latest available weather conditions and forecast (up to five days in advance) for your desired locations, as all-day events in iCal. You’ll always know the predicted weather well ahead of time.

(Via Daring Fireball)

However, as pointed out by David Friedman on Twitter this is also achievable for free from wunderground.com, by simply searching for your city/town and then subscribing to the ICAL link in the top right hand corner.

ICAL format calendars can be subscribed to from iCal (OS X) and from Google Calendar. Add this to Google Mobile Sync and you have a constantly updating local weather forecast in your iPhone calendar as well.