What is the difference between the two spatial data types in SQL Server 2008? I know they are geography and geometry, but how do they differ?
asked
What is the difference between the spatial data types
Geometric data deals with points, lines, curves, and areas on flat surfaces, while geographic data deals with these same "objects" mapped to the earth's surface. I say the earth's surface, rather than a curved surface, because the earth is not a perfect sphere. It bulges in the middle.
Objects in a geometric system are defined with coordinate pairs that are simply X and Y points on a flat grid. Objects in a geographic system are defined using coordinate pairs that represent latitude and longitude. The absolutely CRITICAL distinction is in terms of size, distance and distortion.
Distance is the easiest example. On a geometric coordinate system, the distance between two points can be calculated using the pythagorean theorem and the x,y coordinates of the points involved.
That doesn't work for lat/long pairs in a geographic system because of the earth's curvature. When you travel between two points on the globe, you are traveling a curve. (Airlines routinely fly routes that would look like big curves if plotted on a wall map, but which are the shortest distance when plotted on a globe.)
It also doesn't work because of the nature of lat/long coordinates. Did you ever notice how on a globe the horizontal lines (latitude) run parallel, while the vertical lines (longitude) get closer together as they near the poles? If two points on the equator are exactly one degree of longitude apart, they are MUCH farther apart than two points in Alaska or South Africa that are exactly one degree apart. Remember that all lines of longitude converge at the poles, so if they are near to the north or south pole a degree of separation would translate into only a few inches.
Obviously what effects distance also distorts size and shape. But when you are using geographic data types, the built in functions for distance and area account for all the vagaries of a rotund earth.
Geographic mapping systems have grown increasingly more precise over time, and the geography datatype can be defined in terms of a number of different standards, each of which has a unique number in MS-SQL. Its important to be aware of this, because you may be loading data from a source that uses a different standard than the one you are using as the default in your database. Spatial routines in SQL can translate that for you, as long as you correctly identify it.
Geography takes into account that the world is like a sphere. Geometry doesn't. It's all about plane surfaces.
Geometry has a bounding box - i.e. you are dealing with a set of co-ordinate data that has a fixed beginning and end. Geography is co-ordinate data that has no set limits, and, as Peso says, accounts for the spherical nature of the co-ordinate system.
Both types have very similar methods, both static and instance based.
If you're talking about places on Earth, you should use geography. This is then compatible with mapping applications, and will help put Bing map backgrounds on your SQL 2008 R2 reports, and so on.
If you're talking about points on a miscellaneous flat surface, then use geometry. This could be to let you plan a house, without needing to know where in the world the house is.
