Should I use SqlGeometry or SqlGeography?

Question

We're in a bit of internal conflict on this issue, and can't seem to come to a happy conclusion.

We'll only be storing latitudes and longitudes, and possibly simple polygons. All we need it for is computing distance between two points (and possibly to see if a point is within a polygon), and the entirety of the data is in such close proximity to make planar estimations acceptable.

Since our requirements are so relaxed, half of the dev team suggests using SqlGeometry types, which are apparently simpler. I'm having trouble accepting this, though, since we're storing geographic data, which seems like storing them in SqlGeography is the right thing to do. Also, I'm not finding any substantive evidence that the SqlGeometry data type is that much easier to work with than the SqlGeography type.

Does anyone have advice as to which type would be more appropriate for this relatively simple scenario?

Answer 1

It's not a question of comparing features, or accuracy, or simplicity - the two spatial datatypes are for working with different sorts of data.

As an analogy, suppose you were choosing the best datatype for a column that contained a unique identifier for each row. If that UID only contained integer values, you'd use int, whereas if it was a 6-character alphanumeric value you'd use char(6). And if it had variable-length unicode values, you'd use nvarchar instead, right?

The same logic goes for spatial data - you choose the appropriate datatype based on the values that that column contains; if you're working with geographic (i.e. latitude/longitude) coordinates, use the SqlGeography datatype. It's that simple.

You can use SqlGeometry to store latitude/longitude values, but it would be like using nvarchar(max) to store an integer... and I promise you it will lead to further problems down the line (when all your area calculations come out measured in degrees squared, for example)

Answer 2

The SqlGeography type has less methods available than SqlGeometry (especially in Sql 2008).

SqlGeography reference

SqlGeometry reference

For example, suppose you want to get the centroid of a polygon in Sql2008. You have a native method for that in geometry, but not in geography.

Also, it has the following limitations:

• You can't have a geography exceeding one hemisphere
• The ring-order matters when creating the polygon

Also, most API and libraries available (that I know of) handle geometries better than geographies.

That said, if the distance calculation has to be precise, you have large distances and have coordinates all over the world, geography would probably be a better fit. Otherwise, and according to your description of the problem, you would be well served with the geometry type.

Regarding your question: "is that much easier to work?". It depends. Anyway, and as a rule of thumb, for simple scenarios I typically opt for SqlGeometry.

Anyway, IMHO you shouldn't worry too much on that decision. It's relatively easy to create a new column with the other type and migrate the data if necessary.