When we define a a geography or a geometry most of think in two dimensions.

declare @LALocation geography 
set @LALocation = geography::STPointFromText('Point(-118.24 34.06 )',4326)
declare @LALocation2 Geometry
set @LALocation2 = geometry::STPointFromText('Point(-118.24 34.06 )',4326)

But Did you know that you can also store the Z and M Values may also be stored

Z  Coordinate

The Z Coordinate represents the height of or the elevation of the given point.The Heights are points above or below the surface measured using the vertical datum.

M Coordinate

The M coordinate stores the “measure” value of  a point . This coordinate can be used to represent any additional properties of a point that can expressed as a double-precision number. For instance Time or the position in a route.

So now using the code above we could simple add the z am coordinates as so

declare @LALocation geography 
set @LALocation = geography::STPointFromText('Point(-118.24 34.06  1 1)',4326)
declare @LALocation2 Geometry
set @LALocation2 = geometry::STPointFromText('Point(-118.24 34.06 1 1 )',4326)

The static methods provided by SQL server 2008  is based on WKT syntax , such as STPOINTFromText() support the creation and storage of z and m values as part of POINT. How ever these attributes are not included in the any calculations you do on the point.

Gotcha

example)

    Distance between point (0 0 0 0 ) and (3 4 12 1) is 5 (square root of the sum of the difference in the x and y dimensions) not 13 which account for z as well..

This enables you to not let any other Spatial Reference ID inserted into a spatial column. This is inherently allowed but when you want to do operations on the column they must all be of the same spatial reference.

alter table customers 
add constraint [enforce_SRID_GeogaphyColumn]
check (location.STSrid = 4326)

create TABLE customers
(
  FirstName nvarchar(30),
  LatName nvarchar(30)
)
alter table customers 
add location geography
alter table customers 
add constraint [enforce_SRID_GeogaphyColumn]
check (location.STSrid = 4326)
drop table customers

You just found out that sql server 2008 has spatial support and you want to add a spatail refrence to that the table? Here is how

create TABLE customers
(
  FirstName nvarchar(30),
  LatName nvarchar(30)
)
alter table customers 
add location geography

Overview

The most important feature of the geography data type is  that stores Geodetic Spatial Data.

this is geodetic model.

This model takes account of the curved shape of the earth. When you perform operations on spatial data using the geography data type, SQL server uses angular computations to work out the result.These computations are calculated based on the ellipsoid model of the earth defined by the spatial reference system of the data in question. For example, if you were to to define a line that connects two points on earths surface in the geography data type the line would curve to follow the surface of the earth

Every line drawn in the geography data types are actually great elliptic arc.

Coordinate System

The geography data type is based on a three dimensional, round model so you must use latitude and longitude for coordinates.

Units of Measure

Since we are using Latitude and Longitude to define our points the unit of measure is in degrees. Being degrees is helpful for locating a objects, but not very helpful telling us what the actual distance is between the them. For instance lets say we wanted to know the distance between Los Angeles and Dallas. It would not be very helpful to know that it is 13 degrees away. It would be much more valuable to know that it is 1439 miles away.

To account for this the clever folks at Microsoft tell us to put a spatial reference or SRID in of 4326

So lets take a look what kind of measurement we get when we take the distance for Los Angeles To Dallas

Declare @LosAngeles as geography = geography::Point(33.56,118.24,4326)
Declare @Dallas as geography = geography::Point(32.47,96.47,4326)
Select @LosAngeles.STDistance(@Dallas)

The Result  is

2033990.01214834 metes

2 033 990.01214834 meters = 1 263.8628 miles

We notice that this is different that the first measurement given that is because the first measurement didn't account for the measurement of the earth.

Size Limitations

Due to technical Limitations SQL Server limits the size of any single object must fit inside a single hemisphere of the earth. This also include any computation on these objects. To work around this you can break down these objects into smaller polygons and then and then combine those together.

Sweet so there is the first in a series of posts on SQL Server Spatial that are coming out.

almost forgot.

Ring Orientation

If are drawing a ellipsoid everything is stored counter clock wise.

Spatial it taking root.. Well more like people are starting to realize that spatial rocks and that all data takes on new meaning once you take in its spatial aspect. Representing your data is like seeing your 3D after viewing the world in the 2D for your entire life. Some may say that this is exaggerating but wait until your data tells a 3D story.

So Lets start to dig into spatial.

3D vs 2D

The world is three dimensional object yet most of things we use to view are in 2d. For instance a paper map or a computer screen. Luckily for us we have Virtual Earth and SQL Spatial which takes in both 3D(geography) and 2D(geometry) shapes and types and displays in them in ways that we can visualize them both.

A great visualization from Hanes that shoes the 2D vs 3D visualization

Coordinate System

The next thing that never keep in mind is the coordinate system. I never am always amazed at the amounts of different coordinate systems that are out there. I have worked with at least 5 different coordinate systems and there are some that are so complex and so ridiculous that i never seem to start laughing.

For these articles we are going to be using the "WGS84 coordinate system have to consider an ellipsoidal shape of the earth and they can be described as either Cartesian coordinates or through latitude and longitude. Latitudes can have values between +90 and -90 degrees where 0 is at the equator and longitudes can have values between +180 and -180 degrees where 0 has been defined as a place in the Royal Observatory in Greenwich / United Kingdom. As mentioned earlier Virtual Earth in 2D-mode can only work between +85 and -85 degrees because of the Mercator projection."

So now that we have our basic understanding of 2d vs 3d maps we have or coordinate system lets dig into the SQL spatial