Spatial SQL for the Geographer – Part 3 – More Basic Spatial SQL Scripts

The tutorial continues…

If you haven’t already read the first three parts of this guide check out:

• Spatial SQL for the Geographer – Introduction
• Spatial SQL for the Geographer – Part 1 – Welcome to Spatial SQL
• Spatial SQL for the Geographer – Part 2 – Basic Spatial SQL Scripts

The first three parts of the guide give some background information on SQL Server, covers some basic spatial and database topics, and provides links to download some software and data. Once the user has completed the first three part of this guide they will be able to understand this tutorial, which will provide some additional basic spatial SQL scripts.

Tutorial

In the last tutorial several scripts were introduced and examples were given.  In the last tutorial I covered the process to validate shapes, calculating centroids, and generate buffers.   In this tutorial several additional scripts will be covered, including generating the bounding box/extent of a feature, intersection and union operations, and difference operators.

Calculate Extent

Calculating the extent of a feature is something I use from time to time.  To find these values, such as the northern bounding latitude or eastern bounding longitude, can be calculated through Spatial SQL.  Spatial SQL has a built in method that will calculate the bounding box of a record, STEnvelope().  In its simplest context the user can generate the bounding box for any feature in a spatial table:

use Spatial_Database
go

select
geom.STEnvelope() as boundingbox
from dbo.States_Provinces
where NAME_1 = 'Massachusetts'

This example will return a field called “boundingbox” for the record where NAME_1 equals ‘Massachusetts’ that contains the minimum area rectangle that encompasses the shape.    The data in the “boundingbox” field is relatively useless the user adds some additional code to the query.  Building on the example provided by Microsoft the user can add the ToString() method to the query:

use Spatial_Database
go

select
geom.STEnvelope() as BoundingBox,
geom.STEnvelope().ToString() as BoundingBoxString
from dbo.States_Provinces
where NAME_1 = 'Massachusetts'

Here the user will see a new column, “BoundingBoxString” that returns a polygon with the five points that represent it.  You may ask yourself, why are there five points when you only need four to create the bounding box?  Well, for SQL Server to create a polygon the first point needs to be represented twice, so that the polygon closes itself properly.

Intersect

The intersect table operator is one of the most popular tools in the GIS work belt. Intersect is an overlay operation, where the area of the input feature is used as the basis to select features from the second feature, the intersect feature.  The following illustration will explain what an intersect is better than I can with words…

In spatial SQL the STIntersects() tool is straight forward and easy to use.   However, it does not return a shape as other overlay methods do.  When run, STIntersects() will return a value of one if the two records spatially intersect and a zero if they do not.  Also, this method only tests the relationship between a single record in one table against a single record in another table.  The following provides an example of intersecting a single line object against a polygon object:

use Spatial_Database
go

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
From dbo.States_Provinces
where dbo.States_Provinces.name_1 like '%Massachusetts%'

DECLARE @line_geom geometry
select @line_geom = geom.STAsText()
from dbo.Roads
where ID = 2974

SELECT @poly_geom.STIntersects(@line_geom) as IntersectValue, @poly_geom as input1, @line_geom as input2

The Massachusetts record form the State and Provinces table is set as the polygon of interest and a single line object from the roads table is also selected.  Now, I know that the line object is in Massachusetts so the value returned in IntersectValue field should be a value of one.

Contains

Another spatial overlay method available in spatial SQL is STContains().  This is a yes/no operation.  If a geometry instance is completely within another geometry a value of one is returned. If the geometry instance is not wholly inside the other, a value of zero is returned.  The following provides a simple example of STContains():

use Spatial_Database
go

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
From dbo.States_Provinces
where dbo.States_Provinces.name_1 = 'Massachusetts'

DECLARE @pointgeom geometry
select @pointgeom = geom.STAsText()
from Spatial_Database.dbo.Populated_Places
where NAMEASCII = 'Boston'

SELECT @poly_geom.STContains(@pointgeom) as ContainValue, @pointgeom as input1, @poly_geom as input2

In this query I am simply testing if the point that represents the city of Boston is within the polygon that represents the state of Massachusetts.  After all the variables are declared the select statement tests the point against the polygon.  The results, in the ContainValue field returns a one, meaning that the selected point is within the selected polygon.

Union

The union operator is an overlay analysis, like the intersect, however the results of a union are the combination of the two features, with overlapping areas obtaining the attributes of both.  Again, let’s turn to Esri for a nice graphic:

Like the intersect operation, the STUnion() operation analyzes the spatial relationship between two different records.  The basic union query will return the area represented in the union.  In the following example the query will select two countries from the world countries table to union.  The results of the query return the unioned geomerty of the two selected polygons.

use Spatial_Database
go

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
from dbo.world_countries
where dbo.world_countries.ADMIN = 'Germany'

DECLARE @poly_geom2 geometry
select @poly_geom2 = geom.STAsText()
from dbo.world_countries
where dbo.world_countries.ADMIN = 'Switzerland'

SELECT @poly_geom2.STUnion(@poly_geom) as UnionGeo

The initial input are two distinct polygon records, one representing Germany and the other, Switzerland. If the user were to query these two records from the table they would see the following spatial result:

However, during the union the geometry of these two records are morphed into a single feature, as seen below:

Using the example script the user could then add additional fields to the select query or perform any number of additional operations on the unioned record.

Difference

The final spatial operator that will be covered will be STDifference().  The difference overlay operation uses two layers with the area of one being removed from the overlapping area of the other.

In spatial SQL the difference method, STDifference(), returns the geometry of the difference.  In the following example the user will find the shape that is left behind between the difference of the United States and Massachusetts:

use Spatial_Database
go

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
From dbo.States_Provinces
where dbo.States_Provinces.name_1 = 'Massachusetts'

DECLARE @USA geometry
select @USA = geom.STAsText()
from dbo.world_countries
where dbo.world_countries.ID = 236

SELECT @USA.STDifference(@poly_geom) as Part1andPart2

The results, as seen below, displays the area that Massachusetts represents in the world_countries table.

Conclusion

Well, that is it for this tutorial.  If I have some time over the next month I’d like to explore spatial indexes within spatial SQL and perhaps cover some more in-depth spatial queries.  All these queries will work within 2008 R2 and the user can, if desired, use tables from ArcSDE within the queries.

The tutorial and the examples are very simple and I do not profess to be an expert in spatial SQL, so, if there is a way to represent any of these queries more efficiently let me know.