SQL Server’s support of SQLCLR allows for very rich
user-defined types to be utilized. For example, a developer could create
a single object that contains multiple properties and can also perform
calculations internally (methods), yet still store it in a single column
in a single row in a database table. This allows multiple complex types
of data to be stored and queried in the database, instead of just
strings and numbers.
SQL Server 2008 makes use of SQLCLR to support two new .NET CLR data types for storing spatial data: GEOMETRY and GEOGRAPHY.
These types support methods and properties that allow for the creation,
comparison, analysis, and retrieval of spatial data. Spatial data types
provide a comprehensive, high-performance, and extensible data storage
solution for spatial data, enabling organizations of any scale to
integrate geospatial features into their applications and services.
The GEOMETRY data
type is a .NET CLR data type that supports the planar model/data, which
assumes a flat projection and is therefore sometimes called flat earth.
Geometry data represents information in a uniform two-dimensional plane
as points, lines, and polygons on a flat surface, such as maps and
interior floor plans where the curvature of the earth does not need to
be taken into account. For example, perhaps your user-defined coordinate
space is being used to represent a warehouse facility. Within that
coordinate space, you can use the GEOMETRY
data type to define areas that represent storage bays within the
warehouse. You can then store data in your database that tracks which
inventory is located in which area. You could then query the data to
determine which forklift driver is closest to a certain type of item,
for example.
The GEOGRAPHY data type provides a storage structure for geodetic data, sometimes referred to as round-earth data
because it assumes a roughly spherical model of the world. It provides a
storage structure for spatial data that is defined by latitude and
longitude coordinates using an industry standard ellipsoid such as
WGS84, the projection method used by Global Positioning System (GPS)
applications. The SQL Server GEOGRAPHY
data type uses latitude and longitude angles to identify points on the
earth. Latitude measures how far north (or south) of the equator a point
is, while longitude measures how far east (or west) of a prime meridian
a point is. Note that this coordinate system can be used to identify
points on any spherical object, be it a baseball, the earth, or even the
moon.
The GEOMETRY and GEOGRAPHY data types support seven instance types that you can create and work with in a database:
POINT—A POINT
is an exact location and is defined in terms of an X and Y pair of
coordinates, as well as optionally by Z (elevation) and M (measure)
coordinates. It does
not have a length or any area associated with it. These instance types
are used as the fundamental building blocks of more complex spatial
types.
MULTIPOINT—A MULTIPOINT is a collection of zero or more points.
LINESTRING—A LINESTRING is the path between a sequence of points (that is, a series of connected line segments). It is considered simple if it does not cross over itself and is considered a ring if the starting point is the same as the ending point. A LINESTRING is always considered to be a one-dimensional object; it has length but does not have area (even if it is a ring).
MULTILINESTRING—A MULTILINESTRING is a collection of zero or more GEOMETRY or GEOGRAPHY LINESTRING instances.
POLYGON—A POLYGON is a closed two-dimensional shape defined by a ring. It has both length and area and has at least three distinct points. A POLYGON may also have holes in its interior (a hole is defined by another POLYGON). Area within a hole is considered to be exterior to the POLYGON itself.
MULTIPOLYGON—A MULTIPOLYGON instance is a collection of zero or more POLYGON instances.
GEOMETRYCOLLECTION—A GEOMETRYCOLLECTION is a collection of zero or more GEOMETRY or GEOGRAPHY instances. A GEOMETRYCOLLECTION can be empty. This is similar to a list or an array in most programming languages. The most generic type of collection is the GEOMCOLLECTION, whose members can be of any type.
Representing Spatial Data
The Open Geospatial
Consortium, Inc. (OGC) is a nonprofit, international, voluntary
consensus standards organization that is leading the development of
standards for geospatial and location-based services. The OGC defines
different ways to represent geospatial information as bytes of data that
can then be interpreted by the GEOMETRY or GEOGRAPHY types as being POINTS, LINESTRINGS, and so on. SQL Server 2008 supports three such formats:
We stick to WKT examples because they are both concise and
somewhat readable. The syntax of WKT is not too difficult to understand,
so let’s look at some examples:
POINT(10 100)— Here, 10 and 100 represent X and Y values of the point.
POINT(10 100 10 1)— This example shows Z and M values in addition to X and Y.
LINESTRING(0 0, 10 100)— The first two values represent the starting point, and the last two values represent the end point of the line.
POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))— Each pair of numbers represents a point on the edge of the polygon. Note that the end point is the same as the starting point.
Working with Geometry Data
As mentioned previously, the geometry
data type is implemented as a common language runtime (CLR) data type
in SQL Server and is used to represent data in a Euclidean (flat)
coordinate system. The GEOMETRY type is predefined and available in each database. Any variable, parameter, or table column can be declared with the GEOMETRY
data type, and you can operate on geometry data in the same manner as
you would use other CLR types using the built-in methods to create,
validate, and query geometry data.
To assign a value to a column or variable of type GEOMETRY,
you must use one of the static methods to parse the representation of
the data into the spatial data type. For example, to parse geometry data
provided in a valid WKT syntax, you can use the STGeomFromText method:
Declare @geom GEOMETRY
Declare @geom2 GEOMETRY
SET @geom = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
SET @geom2 = geometry::STGeomFromText
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)
Note
The last parameter passed to
the method is the spatial reference ID (SRID) parameter. The SRID is
required. SQL Server 2008 does not perform calculations on pieces of
spatial information that belong to separate spatial reference systems
(for example, if one system uses centimeters and another uses miles, SQL
Server simply does not have the means to automatically convert units).
For the GEOMETRY type, the default SRID value is 0. The default SRID for GEOGRAPHY is 4326, which maps to the WGS 84 spatial reference system.
If you are declaring a LINESTRING specifically, you can use the STLineFromText static method that accepts only valid LINESTRINGs as input:
Declare @geom GEOMETRY
SET @geom = geometry::STLineFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
The GEOMETRY type, like other SQLCLR UDTs, supports implicit conversion to and from a string. The string format supported by the GEOMETRY type for implicit conversion is WKT. Due to this feature, all the following SET statements are functionally equivalent (the last two SET statements use an implicit SRID of 0):
DECLARE @geom GEOMETRY
SET @geom = geometry::STLineFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
set @geom = Geometry::Parse('LINESTRING (100 100, 20 180, 180 180)')
set @geom = 'LINESTRING (100 100, 20 180, 180 180)'
After defining a GEOMETRY instance, you can use the CLR UDT dot notation to access other properties and methods of the GEOGRAPHY instance. For example, the following code uses the STLength() method to return the length of the LINESTRING:
DECLARE @geom GEOMETRY
SET @geom = geometry::STLineFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
select @geom.STLength() as "Length"
go
Length
----------------------
273.137084989848
The following example uses the STIntersection() method to return the points where two GEOMETRY instances intersect:
DECLARE @geom1 GEOMETRY;
DECLARE @geom2 GEOMETRY;
DECLARE @result GEOMETRY;
SET @geom1 = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
SET @geom2 = geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0
0))', 0)
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();
go
----------------------------
LINESTRING (50 150, 100 100)
All the preceding examples use local variables in a batch. You also can declare columns in a table with the GEOMETRY type, and you can use the instance properties and methods against the columns as well:
CREATE TABLE #geom_demo
(
GeomID INT IDENTITY NOT NULL,
GeomCol GEOMETRY
)
INSERT INTO #geom_demo (GeomCol)
VALUES ('LINESTRING (100 100, 20 180, 180 180)'),
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))'),
('POINT(10 10)')
SELECT
GeomID,
GeomCol.ToString() AS WKT,
GeomCol.STLength() AS LENGTH,
GeomCol.STArea() as Area
FROM #geom_demo
drop table #geom_demo
go
GeomID WKT LENGTH Area
----------- -------------------------------------------- ----------------- ------
1 LINESTRING (100 100, 20 180, 180 180) 273.137084989848 0
2 POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0)) 600 22500
3 POINT (10 10) 0 0
