x

What is the difference between the spatial data types

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?

more ▼

asked Oct 18, 2009 at 12:54 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 22, 2009 at 12:04 AM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 18, 2009 at 04:48 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Do you have more examples? What does this mean for me choosing a type for an application?
Oct 21, 2009 at 01:43 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left

Geography takes into account that the world is like a sphere. Geometry doesn't. It's all about plane surfaces.

more ▼

answered Oct 18, 2009 at 04:24 PM

Peso gravatar image

Peso
1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 21, 2009 at 10:36 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x9
x7
x4

asked: Oct 18, 2009 at 12:54 PM

Seen: 3435 times

Last Updated: Oct 18, 2009 at 02:25 PM