SQL 2008 Geography and Geometry Data Type Problems

My plan was to take my application into SQL 08 from 05 since my hosting service now has it available. I was intent on killing all of my lat/long information and storing my geospatial data in geography columns.

Here is what I’m finding about the Geography and Geometry data types. Initially, I expected to that once in, I’d be able to get everything back out exactly as entered.

Not so much…

Here is what I am finding so far. When/if I figure each of these out I’ll post an update.

1. Geography and Geometry are across a uniform surface. That means no elevation. So if I’m trying to track the distance between points A and B I can only do it as if looking directly down at a map.
2. Data cannot be read by humans from geography and geometry types. Put the value in and see what it looks like when you select it back out. Not a big deal but it sucks when you’re trying to QA input procedures that have 600 points on a single track.
3. Similar to the previous item, data is for input only. If I put in a geography point, I cannot easily extract just the latitude only from that column. I can do it by parsing and using .ToString() but ugh!

So in my data I’ll be storing the latitude, longitude, elevation and ALSO the geography-coded value for the latitude/longitude.

The upside is that it should make it easier to calculate trip plans for individual users since this is part of my plan to try and pay for that big expensive camera that I’ll be using to snap a zillion panoramas. In the end, what I’ve found is that the geography and geometry data types are primarily useful for making geospatial calculations, not for viewing the actual geospatial data itself.