SQL SERVER – Beginning SQL 2012 – Spatial Unions and Collections

In business we often hear the phrase, “We had a good quarter.” Immediately, we know this means a three month span where sales and profits have been aggregated together for the company. Take a company like Costco that might have $65 billion in total sales during the 4^th Quarter (October, November and December). Of course, this total comes not from a single sale of a 65 billon dollar yacht, rather from millions of sales of common items like snacks, drinks, clothes, and light bulbs. We know how to use GROUP BY and SUM to calculate totals and combine similar data. In the case of Costco we group by calendar quarter and then sums on the sales. Although aggregates are commonly used with numbers, they can also be used with spatial land coordinates to assemble them together, much like a jigsaw puzzle.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 3” in the files SQLQueries2012Vol3Chapter6.0Setup.sql and SQLQueries2012Vol3Chapter6SpecialSetup.sql.

Spatial Unions and Collections

My elementary school was right across the street from my home. The playground in the back of the school was very large and had 3 full sized football (soccer) fields. Not all of this land belonged to the school. Across from the playing fields was the Boys and Girls Club, which is a charity for after school kids’ projects for parents that need to work late. Since they are typically not open during school hours, the land they owned was able to be enjoyed during the day by my school. In this case, the land of the school and the club are aggregated to make one giant play and exercise area. Shapes that overlap or touch can be aggregated into one single shape (known as a polygon).

OGC (Open Geospatial Consortium) Functions Basics

In this example of the school and club, we aggregated the land for two owners. This ability became available with SQL Server 2008. There would be no way to aggregate the land for three owners into a single query. Let’s see how SQL Server aggregated spatial results between two shapes with the following examples.

SELECT * FROM HumanResources.RoomChart

By narrowing our focus to look at only the Water Tower and the Yard shapes, we can easily see that the Water Tower is only part way on the property of the Yard owner.

SELECT *
 FROM HumanResources.RoomChart
WHERE R_ID IN (5,9)

What happens when an object occupies space in a portion of another object? When we ran the previous code, the results show how the Water Tower is partially in the Yard. Will the STContains() function see if  the Water Tower is contained within the Yard? Will the answer to this question be YES, or NO? How about the Parking Lot, is it contained in the Yard? The results for the following code sample is shown below:

DECLARE @Yard GEOMETRY
DECLARE @Wtr GEOMETRY
DECLARE @Prk GEOMETRY
SELECT @Yard = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 5
SELECT @Wtr = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 9
SELECT @Prk = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 8
SELECT @Yard.STContains(@Wtr) AS IsTowerInYard,
@Yard.STContains(@Prk) AS IsParkInYard

We can see how to bring two shapes together by using the STUnion() function with the GEOMETRY data stored in the Yard and Water Tower fields. Building the query is identical to how we previously determined if the Park or the Water Tower were contained in the Yard with the STContains() function. All we need to do is replacing the STContains () function with the STUnion () function. The graphical view can be seen if you run the code and click on the “Spatial results” tab.

DECLARE @Yard GEOMETRY
DECLARE @Wtr GEOMETRY
SELECT @Yard = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 5
SELECT @Wtr = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 9
SELECT GEOMETRY = @Yard.STUnion(@Wtr)

Aggregate Unions

There are other functions available for aggregating shapes together that eliminate the need for declaring and selecting variables.

Let’s look at an example of how to use the UnionAggregate() method and look at what the results are by running the this code.

SELECT GEOMETRY::UnionAggregate(RoomLocation)
FROM HumanResources.RoomChart
WHERE R_ID IN (5,9)

Aggregating more than 2 shapes

Let’s look at an example of some business case uses when working with shapes. We are going to use a special table named Ski Areas that is normally not in the basic database. To get this table you will need to run the SQLQueries2012Vol3Chapter6SpecialSetup.sql script. The data contained in the SkiAres will help solidify how we can easily combine shapes together to support different business requirements.

First, we will need to find out what kind of data resides in this table. The next code sample will show all the fields and records of the Ski Areas table.

USE dbBasics
GO
SELECT *
FROM SkiAreas

With five counties in two different states, a travel brochure would normally show three of these counties in Idaho and two counties in Montana. We can get a little more specific, by looking for just the ShapeName and the ShapeMetry fields. Click on the spatial results tab and in the ‘Select label column:’ drop-down menu, choose the ShapeName field to see the names for the shapes returned by the query.

SELECT ShapeName, ShapeMetry
FROM SkiAreas
WHERE ShapeName LIKE '%County'

Now that we have a working query that displays each county in the SkiAreas table. We can easily convert it to a query using the UnionAggregate() function to combine each county into a single shape for this marketing region.

SELECT GEOMETRY::UnionAggregate(ShapeMetry)
FROM SkiAreas
WHERE ShapeName LIKE '%County'

Book On Amazon

Reference: Pinal Dave (https://blog.sqlauthority.com)