Spatial Support in SQL Server 2008 - SQL Server 2008

Consider the task of storing the geographical coordinates of Boston, Massachusetts: latitude of 42.358543396 and longitude of –71.0595703125. To do this within a relational database, you could just use float datatypes. However, what if you wanted to store the boundary of ZIP code 02113 (in the downtown Boston area)? Now you would add some more floats to define the polygon for the ZIP code. Those would be easy enough to store, but when it comes to doing something with this information, unless you roll your own solution, simply storing the values isn’t very useful. Interacting with other applications, such as via a web service, is even more difficult with no standard way of representing spatial information. Wouldn’t it be great if there were a standard format so other users and applications could interact with a common spatial format? Well, there is a standard.

The Open Geospatial Consortium, Inc. (OGC) is an international industry consortium of more than 350 companies, government agencies, and universities, who all collaborate and develop publicly available interface specifications. These specifications, called OpenGIS Specifications, support interoperable solutions that “geo-enable” the web, wireless, and location-based services, as well as mainstream information technology (IT). The main idea is to empower developers to make complex spatial information and services accessible and useful, so that the information can be easily shared among any applications that are willing to conform to OpenGIS. SQL Server’s spatial implementation is based on these specifications.

Spatial support within SQL Server is actually composed of two datatypes:

  • GEOMETRY is about mapping data on a two-dimensional plane (x and y coordinate system).
  • GEOGRAPHY is about storing information relating to the Earth’s surface.

Let’s look at both of these types in more detail.

The GEOMETRY Type

The GEOMETRY datatype is a system .NET common language runtime (CLR) datatype in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system. For those of us who might not remember grade school math, two-dimensional Euclidean coordinates are also known as plane geometry. Plane geometry addresses things like points, lines, circles, polygons, and any shape that can live on a two-dimensional x-y plane.

Internally, spatial data is stored as binary within SQL Server. When extracting an ASCII text representation of spatial data, SQL uses the well-known text (WKT) representation defined by the OGC in Section of the Simple Features Specification for SQL (http://portal.opengeospatial.org/files/?artifact_id=829). Some examples of the WKT representation of spatial data are shown in Table.

Examples of Well-Known Text Representation of Spatial Data

Examples of Well-Known Text Representation of Spatial Data

The function that returns the WKT representations shown in Table is STAsText(). However, this is not the only function that supports geometry spatial data. There are actually more than 60 functions that support the GEOMETRY type (as well as the GEOGRAPHY type). Table lists some of these functions.

Some Functions That Support the GEOMETRY and GEOGRAPHY Types

Some Functions That Support the GEOMETRY and GEOGRAPHY Types

Each geometric type has an SRID. All methods that operate on multiple spatial data values require that the SRIDs of the data match. Functions that compare or manipulate multiple geometric data values will fail when these data values contain different SRIDs. The complete list of functions that support the GEOMETRY type can be found in SQL Server Books Online.

Let’s consider the problem of storing the boundaries of ZIP codes and doing something useful with that information. Figure shows the fictitious ZIP code area you’ll use for this example. For simplicity, the actual coordinates are presented as integers.

Fictitious ZIP code boundaries

Fictitious ZIP code boundaries

First, create a table to store the geometry data of the ZIP code:

CREATE TABLE ZipCodes
(ID INT PRIMARY KEY,
ZipGeometry GEOMETRY NOT NULL,
ZipAsTextASZipGeometry.STAsText()
)

The second column, ZipGeometry, will store the ZIP code, and you add a computed column that will display your geometry data as text, for convenience.

The next step is to populate this table with the data points:

DECLARE @ZipData GEOMETRY
SET @ZipData = geometry::STGeomFromText('POLYGON((1 10,
15 15, 30 13, 29 4, 7 1, 1 10))', 0)
INSERT INTO ZipCodes
VALUES ('1',GEOMETRY::STGeomFromText('POLYGON((1 10,
15 15, 30 13, 29 4, 7 1, 1 10))', 0))

The STGeomFromText() function takes OpenGIS WKT and stores it as binary within the SQL Server storage engine. In this case, you are storing a polygon that outlines a ZIP code.

Now that you have defined the ZIP code boundaries, you can perform various operations with that data using many of the built-in functions. For example, the following code snippet uses STArea() to get the area of the ZIP code:

DECLARE @ZipData GEOMETRY;
SET @ZipData=(SELECT ZipGeometry from ZipCodes where id=1)
SELECT @ZipData.STArea() as 'Area of zipcode'

table

What if the town decided to build a new high school at point 10, 10 and wanted to make sure this was within the ZIP code? The following code snippet applies the STWithin() function to answer this question:

DECLARE @HighSchool GEOMETRY
SET @HighSchool=GEOMETRY::STGeomFromText('POINT(10 10)',0);
IF (@HighSchool.STWithin(@ZipData)=1)
SELECT 'School is within ZipCode'
ELSE
SELECT 'School is outside of ZipCode' ------------------------
School is within ZipCode
(1 row(s) affected)

What if an elementary school is within the ZIP code area, and the town wants to know what the distance between this and the new high school will be? To answer this question, use the STDistance() function:

DECLARE @ElementarySchool GEOMETRY
SET @ElementarySchool=GEOMETRY::STGeomFromText('POINT(12 8)',0);
SELECT @ElementarySchool.STDistance(@HighSchool) ----------------------
2.82842712474619
(1 row(s) affected)

A river runs through the town. The planning and zoning committee wants to know if this river touches the border of the ZIP code. The following code snippet answers this question using STTouches():

DECLARE @River GEOMETRY
SET @River=GEOMETRY::STGeomFromText ('LINESTRING(.5 12, 1 10, 1 8, .5 3, 0 0)',0)
SELECT @River.STTouches(@ZipData) -----
1
(1 row(s) affected)

The function STTouches() returns 1 if the defined LINESTRING touches the polygon of the ZIP code defined in @ZipData. If not, the function returns 0.

These are just a sampling of the many functions that are available to the GEOMETRY type in SQL Server 2008. In addition to these functions, which manipulate or analyze the actual geometric values, a few functions help import or export the data values themselves. The previous examples used STGeomFromText(), which inputs the OpenGIS WKT format and stores this value as binary within the SQL Server storage engine. Another group of functions interacts with XML data. XML data that describes geographic data is called Geography Markup Language (GML). SQL Server has a restricted implementation of the OpenGIS GML model for SQL Server 2008. It is restricted in that it does not contain every single element defined in the model, but it does contain most of the frequently used elements, like Point, Polygon, and so on.

To see how GML works within SQL Server, let’s create a stored procedure that accepts two inputs: an XML input and an integer. The XML input will be an XML instance in GML format that describes a point. The integer will tell which ZIP code to compare. The function will determine if the point supplied via GML is within the ZIP code specified by the integer. Here is the stored procedure:

CREATE PROCEDURE IsInZipCode(@ZipGML as xml,@ZipCode as int)
AS
BEGIN
DECLARE @ZipData GEOMETRY;
SET @ZipData=(SELECT ZipGeometry from ZipCodes where id=@ZipCode)
DECLARE @PointAsGML GEOMETRY;
SET @PointAsGML=GEOMETRY::GeomFromGml(@ZipGML,0)
--SELECT @PointAsGML.STWithin(@ZipData)
IF (@PointAsGML.STWithin(@ZipData)=1)
SELECT 'Within Zipcode'
ELSE
SELECT 'Outside of Zipcode'
END

Let’s test this stored procedure by creating an XML variable and populating it with a point 10, 10. This point should be within the ZIP code with the ID of 1 (the only ZIP code added to the table).

DECLARE @MyPoint XML
SET @MyPoint='<Point xmlns="http://www.opengis.net/gml"><pos>10 10</pos></Point>'
EXEC IsInZipCode @MyPoint,1 --------------
Within Zipcode
(1 row(s) affected)

Here, you’ve added a simple Point, but you could have created any arrangement of LineStrings, Polygons, or anything else—after all, it’s just XML.

The GEOMETRY datatype examples shown thus far have been inside Transact-SQL (T-SQL) code. Since the spatial datatypes are system-defined user-defined types (UDTs) written using the CLR, it is also possible to call these functions using .NET. The GEOMETRY type is named SqlGeometry and lives in the Microsoft.SqlServer.Server.Types namespace.

The GEOGRAPHY Type

The Earth is not a sphere, as some may think. It actually bulges at the equator due to its rotation. (For those still in disbelief, check out this discussion on the shape of Earth at http://www.josleys.com/ show_gallery.php?galid=313.) If the Earth were a perfect consistent mass, the resulting shape due to this rotation would be an ellipsoid. However, the Earth is not a perfect mass, and thus not a perfect ellipsoid. Specialists in this field of study refer to this imperfect ellipsoid of Earth as a geoid.

GEOGRAPHY is the geodetic type, describing spatial objects on a geodetic plane (such as the Earth surface) and providing the corresponding operations on it. This datatype describes points in terms of longitude and latitude, and is often used to represent global positioning system (GPS) coordinates. Again, each object is associated with an SRID that encodes information about the standard that has been used (for example, WGS 84 for US GPS data).

GEOGRAPHY is very similar to GEOMETRY, and we can almost get away with one spatial datatype instead of two, but there are significant inherent semantic differences between working in the plane and working on an ellipsoid. For example, while we do not care about ring direction for polygons on the plane, we must take them into account on the sphere. Additionally, separating the two types allows the database engine to create more efficient indexes.

As it is prohibitive to compute distances using the real Earth, we need to create a computational model. If we consider all the mathematical models available today, a spherical model is a better choice than the plane, but the error is still substantial. The geoid would give us incredibly accurate results, but its computations are very complex, and the performance of our application may suffer. The best bet is for GEOGRAPHY to model its computations against an ellipsoid model.

The GEOGRAPHY datatype’s functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude. Also, the calculations that are performed will provide values as points on an ellipsoid, rather than as points in a plane. This is significant since, as the distances get larger, the error in calculation gets larger. Consider the case where an airline flies from Boston, Massachusetts to Seattle, Washington. If you calculated a flight plan using the Earth on a plane, you would end up traveling many more air miles than when calculating the distance taking into consideration the curvature of the Earth (flying up near the Canadian border is a shorter distance).


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics