Geography v Geometry - The last word (Or should that be the final straw?)

Question

You know when you're tired and confused, and your brain makes you doubt your understanding of things that you swore you knew?
Well, I just had one of those moments. (I blame my 10 month old son, who let me have the grand total of 4 hours sleep last night - thanks buddy).

Anyway, I'm fed up with the increasing number of sources on the internet that explain geometry as the 'flat-earth' type and geography as the 'round-earth' type. I suspect that at least some of these commentators are reciting these terms verbatim from a Microsoft press release without really knowing what they mean - I've seen at least one such commentator (an MVP, no less, but I won't mention names!) go on to demonstrate how to use the geography datatype to import U.S. Census ZCTA tables (which are in NAD83, SRID 4269 format) on a Virtual Earth map, be passing them straight through as if they were SRID 4326...

Anyway, I was trying to write a step-by-step beginner's guide of rules to explain in slightly more detail when one should use geometry -v- geography, as follows:

1.) If you have Northing / Easting co-ordinates from a projected co-ordinate system, such as obtained from a national grid system or map, then use the 'flat' Geometry Datatype. Any accounting for curvature of the Earth has already been done in the projection of the co-ordinates onto the flat surface of the map.

2.) If you have x / y co-ordinates from a local / engineering co-ordinate system, such as if you are measuring positions in a small-scale building site, use the 'flat' Geometry Datatype. Curvature of the Earth is negligible / irrelevant in these cases.

3.) If you have latitide / longitude co-ordinates from a geographic co-ordinate system, such as obtained from a GPS system, use the 'round' Geography Datatype. SQL Server accounts for curvature of the Earth when performing calculations on this type by using the reference ellipsoid of the specified spatial reference.

4.) In any case, STATE THE SRID OF THE SYSTEM FROM WHICH THE CO-ORDINATES WERE OBTAINED! (SRID = 0 in case 2)

5.) SQL Server 2008 does not support XYZ geocentric co-ordinates.

And then I had that moment - the doubt - that I'd actually just got this wrong myself. Have I over-simplified the rules? Obviously, you can use the geometry datatype to store latitude/longitude co-ordinates from a geographic CS (and sometimes it is useful to do so, to be able to use STConvexHull() etc. so long as you're not calculating any metrics that would have to be calculated on the ellipsoid), but I want this to be a beginner's guide, so I don't want to confuse things.

I've just got a nagging feeling I've missed / messed something, but I'm too tired to spot it! Any advice would be gratefully received.

Answer 1

1. Well, that might vary somewhat by the projection if I understand correctly. How do you "know" that a particular projection does? How can you say that this property is true of any particular projection? Can you cite a source? (note that I'm not saying that you are wrong in making this statement, I just don't know if its true for all projections. It might be true for all SRIDs supported in 2008, though.

 

2. Thats probably reasonable advice given that geometry supports a user-defined bounding box whereas geography is "northwest-stuck."

 

Do remember that geometry doesn't have to be used to store spatial data. For example, consider a bivariate relationship between members of set (books for example, and the amount of database content vs. the amount of spatial content). You could use Geometry to represent the relational space then query for similarity.

 

3. This is good advice on a case-by-case basis.

 

Remember that many tools can convert from one reference system to another. I've done ZCTAs in LL84 thanks to Safe's FME. I'm not a big fan of visualization of via Virtual Earth.

 

4. Abso-freaking-lutely. Or better yet, let people "discover" what happens when you have clashing SRIDs.

 

5. Thats not at all true since it certainly does store them. It would be better to say that SQL Server does not consider Z or M in the supported spatial-temporal methods.

 

Suggest adding:

 

6. Geometry supports a much richer set of methods so if you need to do manipulations via query, you probably better off using that type.

 

7. Although both types offer read and write as GML, support is (heavily, IMHO) limited. Reviewing the schema at {http://schemas.microsoft.com/sqlserver/profiles/gml/} (thanks Isaac) will help you get a handle on this.

 

8. Support for spatial types in Analysis Services, Reporting Services and Integration Services is limited. AS seems to be ignorant of the types, Reporting Services won't be very helpful (as somebody within MS recently said to me, not many non-GIS geeks are going to grok 'POLYGON(....)' or the WKB.

 

People serious about trying to using SSIS with spatial data better learn the WKB format.

 

Or just invest in Safe's FME.

 

9. Non-point spatial data tends to have a big foot print. Northwinds or Pubs might be a few 100kb. Northwinds is a few MB. My minimal spatial example is 3GB. That has all sort of implications for backup/recovery and query.

 

10. It should go without saying, but sometimes the obvious needs to be said SQL Server isn't Arc* and that's not what you should expect from it. Its data store that enables you to write applications, at a fundamental level, stuff like that. Emphasis on the "enables you to write applications."

 

I'll be happy to offer review services if you like.

Answer 2

Great additions - thanks Kent - particularly about reminding me of the limited (non-existant!) spatial support in SSIS, SSAS, and SSRS. I'm building up my own collection of workarounds and links to tools that make up for some of the lack of support in these areas though which I'll get round to publishing one day, but Safe FME is a great tool that does most of it (I just wished they'd update the SSIS extension to work with SSIS 2008, rather than having to use the workbench...)

I think you're absolutely right that GIS-heads will see a lot of the SQL Server 2008 functionality as pretty limited when compared to Arc*, Oracle, or others, which it is. However, I don't see that as the market it's aiming for, anyway.
One big plus that SQL Server has going for it is that spatial support is include 'out-of-the-box', even on SQL Server Express, which means that amateur and hobbyist developers (or even professional T-SQL developers who simply haven't had need to be exposed to spatial before) can start using it. Coupled with Virtual Earth, which might not be a serious GIS analysis platform, but from the clients I've demonstrated to it gets the 'wow!' factor more often than any other client tool (certainly amongst the 'casual' GIS customer) and it's quite an attractive prospect. Hence the reason for compiling the 'beginners guide' of points above.
I'm sure that most of the people currently browsing these forums are very familiar with concepts such as reference ellipsoids, co-ordinate transformations, geometry intersections etc. etc. but in the wider world this is a completely brand new area to most developers, and to be honest, it's a pretty complex one. (Not helped by poorly written standards in some areas, misused terminology (ellipsoid -v- spheroid, 'datum' used interchangeably with 'spatial reference', anyone?) Rather than add to the cloud of muddle, I thought I'd make sure I was providing the right information, and the first question that any new developer is going to come across is the whole "geometry -v- geography - what's the difference anyway?"

So thanks very much for the input, keep it coming!

Answer 3

A forest through the trees one:

 

geography methods like STDistance return results in the UOM of the SRID, geography is always UOM of the grid.

Answer 4

Good thread.

FYI, I have a whitepaper that should (hopefully) be published soon that should help with the general flat v. round issues.  I'll announce it when it goes live.  (There will be at least a couple spatial whitepapers, of which this is the first.)

If you're looking for differences between the two types, not just recommendations on when to use them, a few pop to mind:

• Geometry can use any SRID between 0 and 999999; geography must use an SRID listed in sys.spatial_reference_systems.
• Polygon ring orientation is irrelevant for geometry; ring orientation determines which side is "in" for geography.
• Polygons have a single outer ring and multiple interior rings for geometry; this distinction is irrelevant for geography.
• Geometry can ingest objects that are not OGC valid; geography cannot.
• You must provide a bounding box for a geometry index; you do not (cannot) for geography.
• Geography cannot be used for objects larger than a hemisphere.

I'm sure there are more...

BTW, the first few of these are design choices or are dictated by the space.  The last few of them are engineering limitaions that may very well be lifted in the future.

I will reiterate one point I've made before: SQL Server is now a spatially-enabled database, but is not, and does not aspire to be a GIS.  Some problems may be addressible without a GIS, and some may not, at least without substantial additonal work.  Various GIS vendors (e.g., Autodesk, ESRI, Manifold, MapInfo) build on top of us, and I don't expect SQL to be real competition to any of them.

Cheers,
-Isaac

Answer 5

Isaac Kunen wrote:

Geography cannot be used for objects larger than a hemisphere.I'm sure there are more... ...The last few of them are engineering limitaions that may very well be lifted in the future...

 

Yeah, no more clipping on Alaska would be great!

 

kt

Answer 6

Yes - there's another one - when using geometry, features that lie at the 'edge' of the Earth (or at least, the edge of the projected image - normally poor old Alaska or the Chukotka region of Russia) may have to be represented as multipolygons - with elements on either side of the projected image. Good ol' round earth Geography doesn't have this problem.

Answer 7

In the interests of getting at least one source of information crystal clear on these issues (and this seems as good a place as anywhere to do it), I just want to explore some of Kent's comments in a little more detail. (Sorry Kent - I hope you appreciate I'm not trying to pick holes here! I really appreciate your input, but I want to get these issues straight...)

ktegels_DevelopMentor wrote:

1. Well, that might vary somewhat by the projection if I understand correctly. How do you "know" that a particular projection does? How can you say that this property is true of any particular projection? Can you cite a source? (note that I'm not saying that you are wrong in making this statement, I just don't know if its true for all projections. It might be true for all SRIDs supported in 2008, though.

No, I certainly can't cite any source - I'm only a humble practitioner, not a GIS academic! I guess that was my understanding of the whole process of 'projection' - to take a three-dimensional model of the Earth and create a 2-dimensional image from it so that, for a given projected spatial reference system (based on a particular ellipsoid, datum, map projection, and prime meridian), calculations on the Earth's  surface could be performed using simple geometrical calculations of Cartesian co-ordinates without needing to apply any further allowance for the curvature of the Earth? Using projected co-ordinates in the geography datatype would be like accounting for the curvature twice?

ktegels_DevelopMentor wrote:

2. Thats probably reasonable advice given that geometry supports a user-defined bounding box whereas geography is "northwest-stuck."

I thought that this would actually be necessity rather than good advice - doesn't geography require angular co-ordinates of latitude longitde, rather than x / y co-ordinates as measured along the surface of the Earth?

 

ktegels_DevelopMentor wrote:

5. Thats not at all true since it certainly does store them. It would be better to say that SQL Server does not consider Z or M in the supported spatial-temporal methods.

I accept that you can store Z (and M) values as part of a point definition representing the (optional) elevation of a point in a geographic or projected co-ordinate system, but given the Z co-ordinate forms a vital component of a point definition in a geocentric co-ordinate system, wouldn't it be pointless to try to store geocentric co-ordinates when it would be impossible to use any of the methods on them?

Answer 8

1. Consider the distortion caused of the Mercator projection as you move out from the poles. My point is that not all projections have the same space preserving-behavior so be careful treating behaviors of a class of projections as universal.

2. What, other than domain of the allowed values, is ultimately the difference? Geography is currently limited to Earth-centric thinking due to the spatial referenced defined. By 2011, might we have reference systems for the Moon, Mars and other systems? Will they still use angular coordinates? I can answer the later question more confidentially since we are unlikely to abandon that model where ever we go in the future (it just works), but I hope that v.next++ aren't so Terracentric.

3. As the rules stand today for Z and M there's no semantic enforcement. Instead the values are user-defined and the domain of values is of floating-point type. That actually gives a bit of application-level extensibility if you don't really care about the semantic rules that apply in other cases. Why couldn't I store a time offset in M or Z, for example? The X and Y positions alone might give me enough data to work with from a spatial prospective.

 

Oh course, I pity the fool that gets such a dataset and tries to make TINs from it. I pity him.

 

And he'll despise me.

Answer 9

Hi Kent,

Regarding (1): Keep in mind that most people who are using flat-Earth models for real calculations (as opposed to simply pretty maps on the wall) don't use the usual Mercator projection at the pole.  They may use a transverse Mercator, which exhibits good behavior at the poles, or some other projection.

You're right, though, that different projections have different behaviors about what they preserve.  Mercator projections actually don't preseve either length or area, but do map rhumb lines to straight lines in the projection, which is a nifty property if you're going to go for a sail.

Cheers,
-Isaac

Answer 10

I hope you get a great sail or two in...

 

AFTER RTM!

 

kt

Answer 11

Hi Folks,

Time for a little house cleaning.  This wasn't really a question, but since it's listed as such, I keep getting pinged to tell me that it needs an answer.  Not a problem: I'll arbitrarily award one (or two).

Carry on.

Cheers,
-Isaac

Answer 12

Just a few comments...

 

The whole point of doing a projection is to project a round earth onto a flat piece of paper/screen/etc. So yes ANY projection will be suitable for use in the geometry model. I can't think of any exceptions to this.

 

Regarding whether to store geographic data as geometry. When doing this you are actually projecting your data to the Plate Carrée projection (very simple formula: x=long, y=lat). As you state, there is not much point in doing this unless you need some functionality that only the flat-earth model has to offer.

 

Furthermore, there was a mention about the SRIDs that MSSQL2008 supports... last time I checked, they only put the geographic ones in sys.spatial_reference_systems (why is that btw? I tried adding more to it but it's read only). Any SRIDs outside this are only valid for geometry. And saying that it "supports" it is a bit of a stretch. Apart from the distance/area measurements in geographic, MSSQL2008 doesn't use the SRID for anything, and not at all for geometry. It's pretty much just another column/attribute (although embedded into the geometry/geography instance).

 

Whether or not Virtual Earth is a serious GIS Analysis platform... have you checked out the ArcGIS Server Virtual Earth extension? It demonstrates that you can do pretty advanced GIS analysis using VE. It's just a matter of you providing the analysis - VE is just a visualisation tool and pretty good at it.

 

Technically you should be able to store Geocentric coordinates as Geometry (vertices do support Z), but you can probably not really count on using most of the methods or benefit much from the spatial index. However the formula between geocentric and geographic is fairly simple, so I really see the need for storing it in geocentric. Usually you only convert to geocentric if you need to do a datum transform, or are working with raw GPS data (GPS units actually calculate the satellites and your position using geocentric before outputting long/lat).

 

 

The round earth model is good for people who doesn't know what they are doing. For instance doing distance and area calculations over large distances in a projection can be very dangerous and give you very wrong results. Even if you know what you are doing, correcting for the distance and area distortions can be extremely complex.

Answer 13

Hi Morten,

I was wondering how long you could stay away from this thread. 

Just a few comments.  First, the reason you can't add to our list of reference systems is purely a technical limitation.  It is something we'd could add in the future, but it is non-trivial work, and I'm not sure how high priority it is.  I'd recommend weighing in on Connect if you feel it is important.

Second, perhaps I'm putting words in your mouth, but I disagree with your finial statement, insofar as you imply that the round-Earth model is only "good for people who don't know what they are doing."  Using projected coordinates for legacy data, or where it is required to operate with legacy systems seems quite sensible, but where possible, it seems preferable to keep unprojected coordinates unprojected except for display.  (And once we get those round displays...)

Your distance and area calculations show how to obtain truly accurate measurements for projected coordinate systems, but you have to essentially undo the projection to get them, which makes me question why (other than for legacy reasons) you would want to project them to begin with.

Cheers,
-Isaac

Answer 14

Sorry Isaac... I've been busy playing Rock Band lately ;-)

 

I didn't mean to be so harsh in that statement about who knows what they are doing. What I was getting at is that there are fewer things to be aware of with geographic as opposed to geometry. Its easy to think you can use normal euclidean equations on data that's for instance using meters as a unit. For instance for a Mercator projection the units are meters, but not everytone knows what happens if you try and calculate the area of Greenland without taking the distortion into account (funny side-note: Russia used to always show their country in Mercator because it made their country look bigger than everyone else's and I"ve seen americans think it was America because it was so big :-) )

 

As opposed to what I earlier have stated about the geography type, I've grown to really like it - especially when it comes to measuring stuff (I just wished the output units would always be meters to simplify the developer task, and that we could get rid of the annoying limitations it has). Now the only reason I see to project data, is if you want to display data in a specific projection and performance is too important to do it on the fly.

Answer 15

Excellent.  Let me know when you're ready to tour.

Rest assured, we're working on the annoying limitations...

-Isaac