THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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;

image

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.

image

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.

image

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);

image

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);

image

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement