GeographicInformationSystems/SpatialDataTypes

Spatial Data Types

• geography - geodetic vector spatial data
• geometry - planar vector spatial data

These data types are actual CLR classes and require SQLCLR code instead of T-SQL. Properties defined by CLR datatypes, such as ToString(), STArea(), or STIntersects(), are case-sensitive.

To create an item of geography or geometry data, you must use a static method belonging to the appropriate datatype as shown below. (Including several other operations)

SELECT geometry::Parse('POINT(30 40)');

CREATE TABLE geographypoints (
Location geography
);

INSERT INTO geographypoints VALUES
(geography::Point(51, 1, 4326)),
(geography::Point(52, –2, 4326)),
(geography::Point(50.7, –1.1, 4326));

SELECT Location.ToString()
FROM geographypoints;

DECLARE @point geometry = geometry::Point(12, 7, 0);
SELECT @point.STBuffer(5);

DECLARE @point geometry = geometry::Point(3, 5, 0);
SELECT @point.STBuffer(5).STArea();

SELECT Location.Lat, Location.Long
FROM geographypoints;

Changing the coordinate values associated with a geometry or geography instance requires you to create an entirely new geometry from a static method. However, certain properties of existing geometries can be both retrieved and set.

UPDATE geographypoints
SET Location.STSrid = 4269;

All of the functionality of the geography and geometry datatypes is contained in two libraries: Microsoft.SqlServer.Types.dll and SqlServerSpatial.dll

• Microsoft.SqlServer.Types.dll contains the managed (.NET) code necessary to define the spatial datatypes, and is installed by default in the \Program Files\Microsoft SQL Server\110\SDK\Assemblies directory.

• SqlServerSpatial.dll contains additional functionality required to perform spatial operations, written using native (C++) code. This assembly is installed by default in the \Windows\System32 directory.

The geography Datatype

All geography types need a SRID associated with them or they are useless. SRSs are listed in the sys.spatial_reference_systems table.

Try it:

SELECT *
FROM sys.spatial_reference_systems

There is an "extra" attribute called unit_of_measure that is used by the system to calculate such things as distance between points. Check out the unit_of_measure for WGS84:

SELECT unit_of_measure
FROM sys.spatial_reference_systems
WHERE authority_name = 'EPSG' AND authorized_spatial_reference_id = 4326;

--Distance from Paris to Berlin in Miles
DECLARE @Paris geography = geography::Point(48.87, 2.33, 4326);
DECLARE @Berlin geography = geography::Point(52.52, 13.4, 4326);
DECLARE @distance float;
SELECT @distance = @Paris.STDistance(@Berlin);
SELECT @distance*0.000621 as DistanceInMiles;