Ch03: Spatial Data Types

Chris Dant Presented this Chapter: Ch03.pptx

• 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.

The geometry Datatype

When using the geometry type to record local grid data, you should use SRID 0. This SRID denotes that the coordinates are not derived from any particular spatial reference system, and coordinate values should be treated as x and y values with no specific units of measurement.

Although you should always use the correct SRID with geometry data, it doesn't make a bit of difference for calculations - but it makes a big difference on where the data will show up on a map! So do it right.

The left hand rule does not apply to geometry types... its on an infinite plane, so the complement doesn't make sense here.

General rules about what to use

• If you have latitude and longitude coordinate data (collected from a GPS, from Google Earth, or most sources listed on the Web) use the geography datatype, normally using the default 4326 SRID.
• If you have x- and y-coordinate data (e.g., collected from a flat map), use the geometry datatype with an SRID to represent the map projection and datum used.
• If you have x- and y-coordinate data that are not defined in relation to any particular model of the Earth, use the geometry datatype with SRID = 0.

Converting between datatypes:

-- First, create a geography instance
DECLARE @geog geography;
SET @geog = geography::Point(23,32, 4326);
-- Convert to geometry via WKB
DECLARE @geom geometry;
SET @geom = geometry::STGeomFromWKB(@geog.STAsBinary(), @geog.STSrid);

Enforcing a common SRID

ALTER TABLE dbo.customer
ADD CONSTRAINT enforce_customerlocation_srid4199
CHECK (CustomerLocation.STSrid = 4199);