Should I use SqlGeometry or SqlGeography?

Question

We have a bit of internal conflict on this issue and, it seems, cannot reach a happy conclusion.

We will only store latitudes and longitudes and, possibly, simple polygons. All we need is to compute the distance between two points (and possibly to see if the point is inside the polygon), and all the information is in such close proximity to make flat estimates acceptable.

Since our requirements are so relaxed, half of the development team suggests using SqlGeometry types, which seem to be simpler. I am having problems with this, however, since we are storing geographic data that seems to be storing them in SqlGeography , this is the right thing. In addition, I do not find any substantial evidence that the SqlGeometry data type is much easier to work with the SqlGeography type.

Does anyone have any advice on which type would be more suitable for this relatively simple scenario?

Answer 1

It is not a matter of comparing functions, accuracy or simplicity - two spatial data types are designed to work with different data types.

As an analogy, suppose you choose the best data type for a column that contains a unique identifier for each row. If this UID contains only integer values, you should use int, whereas if it were a 6-digit alphanumeric value, you would use char (6). And if it had variable length unicode values, you would use nvarchar instead, right?

The same applies to spatial data - you select the appropriate data type based on the values ​​that this column contains; if you work with geographic coordinates (e.g. latitude / longitude), use the SqlGeography data type. It's simple.

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

Answer 2

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

SqlGeography Link

SqlGeometry Link

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

In addition, it has the following limitations:

• You cannot have a geography exceeding one hemisphere
• Ring order matters when creating a polygon

In addition, most of the available APIs and libraries (which I know) handle geometries better than geographic ones.

However, if the distance calculation needs to be accurate, you have large distances and coordinates around the world, geography is likely to be better aligned. Otherwise, and according to your description of the problem, you will be well served by the type of geometry.

Regarding your question: "Is it much easier to work?" It depends. In any case, and as a rule, for simple scenarios, I usually choose SqlGeometry.

In any case, IMHO, you should not worry too much about this decision. It is relatively easy to create a new column with a different type and transfer data if necessary.

Answer 3

Four years later, it became obvious that we should save the data in SqlGeometry instead of SqlGeography .

Why?

We imported information from the maps of the legislative districts, and their data was stored in SqlGeometry . In determining whether a particular lat / long period was within a certain boundary of the legislative district, we would get inconsistent results when the point was close to two borders.

This required us additional work to determine locations that were “close” to the border and manually verify that they were assigned to the appropriate area. Not perfect.

The moral of the story: if you rely on any data, think about what type it stores to help you make a decision.