THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in John Paul Cook (Entire Site) Search

# SQL Server 2008 Spatial Data - Getting Started, Part 2

My first post on getting started with SQL Server 2008 spatial data showed the results of displaying geography data. Before moving forward to advanced topics, it's time to pull back and learn the basics. It's easier to learn the fundamentals by using the other spatial data type, geometry. We're going to figuratively get some graph paper and remember our high school algebra and geometry lessons. Don't worry, everybody's going to get a good grade!

The spatial data types and objects are described here in Books Online. We'll begin by creating a square box by using the polygon object. Although it is true that it takes four points to define a square, it takes five points to define a square using the polygon object. That's because we need to close the square by using the first point as the fifth and last point. Think of it as describing a path you are walking. After getting to the fourth point of the square, you want to walk back to the first point to close the loop. Here's the code to make and display a square two units in length on each side.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
select @smallBox;

A single box isn't an interesting as two boxes, especially when the two boxes have something in common. We'll continue by creating a larger box that partially intersects the first box.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox, @largeBox;

We see the result result as before. That's not what we want to see. We want to see both polygons, both squares.

In the previous code snippet, we treated the spatial objects as columns. That's why we didn't achieve the desired result. If the query is modified to return the large box first, only the large box appears. It is necessary to treat the spatial objects as rows to see all of them.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox
union all
select @largeBox;

Now we see both polygons. Notice that the intersection of the two squares is a darker shade and easily identified visually. This is only a visual effect. To programmatically define and work with the intersection of the two polygons, a spatial data method call is required.

To compute the intersection of two polygons, the STIntersection method is used. You can read more about geometry method calls here in Books Online.

select @smallBox.STIntersection(@largeBox);

If you modify the query slightly, you can get the coordinates of the polygon defining the intersection.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox.STIntersection(@largeBox).ToString();

POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))

Although the following query doesn't produce significantly different results in the spatial viewer, it is different because it is actually displaying three distinct polygons.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox
union all
select @largeBox
union all
select @smallBox.STIntersection(@largeBox);

Change the query as shown and use the STUnion operator instead of STIntersection.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox.STUnion(@largeBox);

Most geometry method calls don't return visual results. For example, STArea returns a scalar value for the area. By inspection, you can see that the total area of the union of the two square boxes is 12. STArea computes the area of a spatial object:

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox.STUnion(@largeBox);
12

Other methods are Boolean and return either a 1 or a 0. Instead of seeing the intersection of two polygons, sometimes all you need to know is if the polygons intersect.

declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';

select @smallBox.STIntersects(@largeBox);
1

More examples will be posted later.

Published Sunday, November 16, 2008 9:54 PM by John Paul Cook

#### Sam Kramer said:

Minor correction to STArea example:

select @smallBox.STUnion(@largeBox).STArea();

February 1, 2013 9:56 AM

#### Bidyut said:

This the best example on Polygons. Well explained.

May 28, 2014 7:46 PM

(required)
(required)
Submit