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;

How do I define the interior of a polygon (ring)?

SQL Server applies a rule known as the "left-hand rule" (or, sometimes, the "left-foot rule"); if you imagine yourself walking along the ring of a geography Polygon, following the points in the order in which they are listed, SQL Server 2012 treats the area on the “left” of the line drawn between the Points of the ring as the interior of the Polygon, and the area on the “right” as the exterior.