Exercise 2: Understanding Valid Instances | H...

• Learn
• Courses
• Microsoft SQL Server 2008 R2 Update for Developers Training Course
• SQL Server 2008 - Location Awareness via Spatial Technologies
• Hands On Lab: Using Spatial Data in T-SQL

Exercise 2: ?Understandi?ng Valid Instances

Exercise 2: Understanding Valid Instances

It is possible to specify a set of points that on visual inspection look like a valid shape. However there are a set of imposed rules that define how points should be ordered, and shapes combined, in order for a shape to be valid. These are imposed so that the functions for calculating spatial properties and relationships work correctly.

Thankfully, most, if not all, shapes that are visually correct, but are not valid can be converted into a valid geometry. This is achieved using the MakeValid method.

It is important to understand this, especially when dealing with user input, as often user input of complex shapes can result in invalid shapes. Most of the useful methods on the shape do not work if the shape is not valid.

You may also encounter invalid shapes if you accidentally have your points in the wrong order.

Note:

Determining whether a shape is valid is an expensive operation. The cost is proportional to the number of line segments and how often they intersect. Because a shape has to be valid in order to use the spatial methods, the validation calculation is done when the data is created and not when a function is called. Therefore, it is important to not pass spatial data as WKT or WKB but use the base data type instead.

Note:
To execute the steps in this exercise you can continue working with the script created on the previous exercise in SQL Server Management Studio, or create a new one.

Task 1 – Creating an Invalid Polygon

A polygon is invalid when the exterior ring overlaps; an example of this is an hourglass shape. This is easily created by flipping the points of a square.

1. Declare the polygon below and show it is invalid.

   

   Figure 8 Polygon

   1	DECLARE @polygon GEOMETRY = 'POLYGON ((0 0, 10 0, 0 10, 10 10,0 0))'

2. Select the STIsValid method and also try and make it valid by calling the MakeValid method:

   1	SELECT @polygon.STIsValid() IsValidPolygon, @polygon.MakeValid().ToString() ValidPolygon

   Note:
   MakeValid does not mutate the original geometry but returns a new geometry that is valid. To store this value you have to assign it to a variable.

   

   Figure 9 Verifying the polygon is valid and making a valid version

   Note:
   Verifying the results you will see that the new geometry is a MULTIPOLYGON consisting of two triangles, one inverted and on top of the other. MULTIPOLYGON (((5 5, 10 10, 0 10, 5 5)), ((0 0, 10 0, 5 5, 0 0)))

   Note:
   Another invalid geometry is a POLYGON or MULTIPOLYGON that has overlapping shapes: declare @polygon geometry = 'MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10,0 0)),((2 2, 2 4, 4 4, 4 2, 2 2)))'