This is the first in what will (hopefully) be a series of posts demonstrating a few light-hearted uses of SQL Server 2008 spatial functions to solve some common mathematical/logical puzzles. To demonstrate the disappearing square puzzle, first of all create a table and insert four simple geometry shapes into it, as follows:
DECLARE @table1 TABLE( id CHAR(1), shape geometry ); INSERT INTO @table1 VALUES ('A','POLYGON((0 0, 8 0, 8 3, 0 0))'), ('B','POLYGON((8 0, 13 0, 13 2, 10 2, 10 1, 8 1, 8 0))'), ('C','POLYGON((8 3, 8 1, 10 1, 10 2, 13 2, 13 3, 8 3))'), ('D','POLYGON((8 3, 13 3, 13 5, 8 3))');
If you now select all of the shapes from the table and switch to the spatial results tab in SQL Server Management Studio, you can see that the individual shapes fit together to form a triangle:
SELECT id, shape FROM @table1;
Now suppose that we insert these same shapes into another table, but move them around slightly:
DECLARE @table2 TABLE ( id CHAR(1), shape geometry ); INSERT INTO @table2 VALUES ('A','POLYGON((5 2, 13 2, 13 5, 5 2))'), ('B','POLYGON((8 0, 13 0, 13 2, 10 2, 10 1, 8 1, 8 0))'), ('C','POLYGON((5 2, 5 0, 7 0, 7 1, 10 1, 10 2, 5 2))'), ('D','POLYGON((0 0, 5 0, 5 2, 0 0))'); SELECT id, shape FROM @table2;
Note that all of these shapes are identical to those used in the first example - they've just been rearranged. However, the triangle formed when the pieces are fitted together now contains a "hole" - 1 unit wide by 1 unit high.
So, where has the hole appeared from, and what is missing?
The area of a right-angled triangle can be calculated as half of the base multiplied by the height. Both 'triangles' appear to be 13 units wide x 5 units high, so we would expect them to have an area of 32.5 units. Let's test this:
SELECT SUM(shape.STArea()) FROM @table1; SELECT SUM(shape.STArea()) FROM @table2;
The result of the above query shows that the total area of the individual shapes contained in both triangles is the same - 32 units, not 32.5 as expected. The area of the bottom triangle, when including the 'missing' one unit square, is therefore 33 units.
In other words, although the two triangles appear similar, they are not the same, and neither one is the 13x5 right-angled triangle they appear to be. The first triangle is slightly smaller, and the second triangle is slightly larger.
To investigate further, we can find the area of difference between these triangles and the "true" 13 x 5 right-angled triangle defined as follows:
DECLARE @truetriangle geometry = geometry::STPolyFromText('POLYGON((0 0, 13 0, 13 5, 0 0))', 0);
First, we need to create a union aggregate of all the component shapes that form each triangle, and then compare this to the true triangle using the STSymDifference() method (note I also use the STConvexHull() method on the second triangle in order to fill in the missing 'hole'). If you’re using SQL 2012, you can use the UnionAggregate() method here but, if not, you can create a simple union aggregate using STUnion() as shown below:
-- Create union of all individual shapes in first triangle DECLARE @triangle1 geometry = 'GEOMETRYCOLLECTION EMPTY'; SELECT @triangle1 = @triangle1.STUnion(shape) FROM @table1; -- Create union of all individual shapes in second triangle (filling in the gap) DECLARE @triangle2 geometry = 'GEOMETRYCOLLECTION EMPTY'; SELECT @triangle2 = @triangle2.STUnion(shape).STConvexHull() FROM @table2; -- Work out the difference between each complete triangle and the true 13 x 5 triangle SELECT @triangle1.STDifference(@truetriangle), @triangle1.STDifference(@truetriangle).STArea() UNION ALL SELECT @triangle2.STDifference(@truetriangle), @triangle2.STDifference(@truetriangle).STArea();
The results illustrate two thin slivers of area where the two shapes differ from the true triangle, lying along the hypotenuse – each 0.5 units in area.
While both shapes formed from the component elements A, B, C, and D appear to be right-angled triangles, they are in fact polygons, since the hypotenuse of each is not a straight line. (You can check this by calling STNumPoints() on both @triangle1 and @triangle2 – you’ll see that each contains four distinct points). The hypotenuse of the @triangle1 is slightly concave, hence why the area is only 32 units and not 32.5 units as expected. The hypotenuse of the @triangle2 is slightly convex, leading to a total area of 33 units. When you add in the missing sliver to @triangle1, or take it away from @triangle2, you’ll end up with the true 13x5 triangle as expected.
The illusion is caused by the fact that the gradients of the component triangles A and D are not the same, therefore swapping them over affects the overall area of the shape. However, they are similar enough that your brain doesn't perceive the difference between them, and assumes that you're looking at a right-angled triangle in both cases.