Using geometry rather than geography data types in SQL Server Spatial?

Question

Historically I've mainly worked with simple latitude and longitude coordinates in a SQL Server. I stored them as a geography datatype in SQL Server, and rendered them on a variety of displays (primarily Google Maps).

Recently I've started working with shapefile datasets from various sources, and almost exclusively they are geometry. Further, they use a variety of coordinate reference systems.

It's pretty maddening to work with.

The USGS, for some of their data, use a very rare and hard to ID CRS.
The county of Los Angels says they use the State Plane 5 (without reference a CRS specifically), and of course there are multiple "state plane 5's" to choose from in QGIS.

Are there really some advantages to these particular spatial reference systems that cannot be achieved by sticking with WGS84, considering that the VAST majority of people viewing and consuming will want it in that format?

geography is a system that covers the entire world the ellipsoid utilized makes it so that ground measurements are relatively inaccurate (as it relates to surveying). Each "geometry" CRS is related to a smaller area and is designed to give accurate measurements for that defined area. Surveyors carry this a step further with what they call ground systems or even local crs. They have a beginning control point and /or a network of control points and measure within that network to gain VERY high accuracy (sub-cent). Which causes a lot of work on our part to know the difference — Brad Nesom, Oct 13 '14 at 21:17
@BradNesom thanks for that. I think I've just been looking at it from my needs, not really down at a surveyor's level. — Eric, Oct 13 '14 at 21:23

score 16 · Accepted Answer · edited May 23 '17 at 12:39

16

There's a good answer over at Stack Overflow, which goes a little something like this:

The geography type is a little bit more restrictive than geometry. It can't cross different hemispheres and the outer ring must be drawn counter-clockwise.

The rest can be found in Geography data type vs. Geometry data type in SQL Server.

A Geometry vs Geography article at the SQL from the Trenches blog goes into more detail:

If you’re looking for the biggest difference between the two datatypes, you’ll see it’s the functionality. A Geometry object is just a 2D, flat polygon. This means, it doesn’t matter if you take a country on top of the earth (for example Canada, which is “curved”), or a country close to the Equator (for example Brazil, which is “flat”).

A Geography object on the other hand, is a 3D (or even 4D) polygon, that has the same curve as the shape of the earth. That means that the difference between 2 point isn’t calculated in a straight line, but it’s need to consider the curvature of the earth.

Another crucial difference is the ability store your data in standard coordinate systems, such as NAD_1983_StatePlane_California Zone 5, and use all the capabilities of the spatial database, spatial functions, etc. and most importantly the spatial accuracy of a localized coordinate system - whereas sticking with geography as your data type, you can only store your data in WGS84.

So I'd say if you have the option, go with geometry, use EPSG: 102645/102245 (you'll have to check what is the standard 'state plane zone 5' for SoCal) and you'd be all set for any analysis you want to undertake. If you want to share, export your datasets to WGS84 if that is preferred for sharing.

edited May 23 '17 at 12:39

Community

1

answered Oct 13 '14 at 20:10

DPSSpatial_BoycottingGISSE

18,790
4
66
110

Conceptually I get that, but in regards to Los Angeles County crime mapping, it's a single county. Even if it's the larger agreed upon projection for more data than I'm seeing (likely) I just keep struggling with (at minimum) the interopability headaches over whatever theoretical advantages can be quoted. – Eric Oct 13 '14 at 20:14
Then I'd say if you're aware of the limitations and you can operate with those boundaries then you should go for it! – DPSSpatial_BoycottingGISSE Oct 13 '14 at 20:17
I'd love to, if only I had a crystal ball to determine what "state plane 5" the county of LA means. I hope the 3mm improvement in accuracy it bought them is worth the headache everyone else gets trying to make sense of it. – Eric Oct 13 '14 at 20:19
updated my answer - using geometry as the data type you can store your data in state plane and you've nothing to worry about... let me know what you think. – DPSSpatial_BoycottingGISSE Oct 13 '14 at 20:28
3

@radpin I agree that learning this stuff is absolute, Kafkaeque existential horror from the perspective of a non-GIS person, and for the most part I agree public data for general consumption is best provided in WGS84. But those 3mm can mean a lot of money/lawsuits when it comes to property lines and potential tragedy when talking about utility pipes and wires. Additionally, here's a map of the California's survey areas, complete with LA County being a special case between datums. Fun. – Jason Scheirer Oct 13 '14 at 22:00
I think an important limitation of geography to consider are the restrictions in which functions can use spatial index https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-indexes-overview?view=sql-server-ver15#methods Geography lacks things like STContains – CervEd Feb 02 '22 at 12:27

score 6 · Answer 2 · edited Oct 14 '14 at 05:54

Maybe check out the properties of Projected Coordinate Systems, to get a view into their utility?

There are 3 aspects or properties presented in Projected Coordinate Systems that establish their utility and rationale. Any projection of 3D space on a 2D surface will of course exhibit distortion versus reality. Depending on your application, using a specific Projected Coordinate System can ensure a more accurate representation of reality, and in some applications, fine grain accuracy matters.

Three properties of Projected Coordinate Systems. You can have perfection in some, but never perfection in all:

Equidistant - Distances (from a particular spot) shown on the map are true to reality.

Conformal - When zoomed in, the angles shown on the map are true to reality.

Equal Area - Areas recorded on the map are equal to the areas of objects in reality.

score 0 · Answer 3 · answered Sep 07 '17 at 17:38

In California, the surveyors' offices use a Lambert Conformal Conic projection and divide the state into zones to minimize distortions. State Plane 5 is one of those zones.

http://www.conservation.ca.gov/cgs/information/geologic_mapping/state_plane

Using geometry rather than geography data types in SQL Server Spatial?

3 Answers3