I'll have to admit it, when I first saw that SQL Server 2008 was adding spatial data support, I thought of it as a niche. The province of geographers, cartographers, and maybe a few others. Complex, involving a lot of higher mathematics, each province having their own geographic encoding, and so on… And that level exists, to me it's the production of spatial reference data. When I think of spatial reference data, I think of map data you'd buy from ESRI and data posted by government agencies. Or made available by utilities, so you don't hit a power cable while digging in your garden. As opposed to spatial line of business data.
Now, before you go searching your LOB application for latitude and longitude columns, how about looking for columns that contain "address". It's a short hop from address to lat/long by using a geocoder. The one I used is the MapPoint web service. Now you have line-of-business spatial data. I'll bet every app has a field that contains address. And how about looking for the nearest salesperson for a potential customer? Or the nearest warehouse? Mapping programs like Virtual Earth, Google Earth, and Yahoo Maps can give you general business information and maps but how about encoding information in your own business?
I'm quite excitied over this upcoming "niche" feature and think it could make its way into each and every application. That's spatial data "for the masses" (so I'm not the greatest at sloganizing, forgive me).