Spatial SQL for the Geographer – Part 2 – Basic Spatial SQL Scripts

The tutorial continues…

If you haven’t already read the first two parts of this guide please read:

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

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

Tutorial

Here the user will learn about a few basic Spatial SQL scripts.  Nothing too complicated, but enough to get the user started.  Hopefully the user will start to think about ways to expand the scripts into their own ideas.

This tutorial will cover the MakeValid, calculating centroids, and buffering a polygon.

Checking the Validity of Shapes

Before the user starts to calculate buffers, generate centroids, union or intersect tables or perform any other Spatial SQL methods the user should first run the MakeValid command. MakeValid will check the shape to see if the data set breaks any of SQL’s rules for storing the geomerty of the shape. As mentioned in the first part of this guide the geometry datatype is very flexible, but the user should run MakeValid first before doing any analysis because there still might be self intersecting polygons, bad ring order or any other number of topoligical errors within the dataset.

Many GIS programs won’t worry about these problems on the surface, but SQL will and users should perform these queries on all spatial datasets, especially polygons data, as it is usually full of errors.

This first example will update the State_Provinces table, checking the geometry of each record in the table.  This query will update the spatial data type column in a table so that the shape is valid, meaning that the shape meets SQL’s requirements for the datatype

In this example the State_Provinces table is being checked. The user will need to
run the MakeValid command against the column that stores the spatial data type, in this case, the geom column.

use Spatial_Database
go 
update dbo.States_Provinces
set geom = geom.MakeValid()

This query make not catch all errors in the data set.  The following scripts will check the data for validity as well.  The third script will check to see if any of the records still contain any invalid geometries.  These scripts were modified for the geometry datatype from Beginningspatial.com, a great resource the user should check out.

--Fix invalid shapes
update dbo.States_Provinces
set geom = geometry::STGeomFromWKB(geom.MakeValid().STAsBinary()
,geom.STSrid)

--Correct the order of the points in the polygon ring
update dbo.States_Provinces
set geom = geometry::STGeomFromWKB
(geom.STUnion(geom.STStartPoint()).STAsBinary(),geom.STSrid)

--Check to see if any records still have any invalid geometries. 
--The results should return an empty table.
select  ID, geom.STIsValid() as ValidCheck,
geom, geom.ToString() as Spatial_Text_String
from dbo.States_Provinces
where geom.STIsValid() != 1

When these queries are run the geometry in the table be updated.  From there the user will be able to repeat this process for each of the tables loaded in the first tutorial.  If the user does not run the MakeValid query some of the examples in this tutorial may not work correctly.

Calculating Centroids

The next set of queries will take a polygon layer, calculate the centroids, and convert the centroids into latitude and longitude.  Using the world_countries table in the Spatial_Database database the user can run this query and return the name of the feature and the X and Y of the polygon’s centroid.  This query uses a subquery where the centroid is first calculated.  Once this is done the outer query converts the centroid into latitude (Centroid.STX) and longitude (Centroid.STY).

select U.Region_Name, Centroid.STY as Longitude,
Centroid.STX as Latidude
 from
(	 select geom.STCentroid() as Centroid, NAME as Region_Name
	 from dbo.world_countries
)U

The following will expand on the previous query.  In the next example the user will calculate the centroid for a specific polygon and then buffer the centroid.  In the subquery a where statement is set to select a specific record that meets a certain requirement. In this example the user will select records from the name_1 column that equal ‘Massachusetts’.  Also, when the STCentroid() method is called no parameters are needed, unlike other spatial methods which can require several methods.

In the outer query the centroid is buffered by 0.05 decimal degrees, set in the STBuffer method. Why are decimal degrees used here?  Well, it is because the data were loaded in as WGS84.   There will be a future tutorial on how to tackle this problem.

The user can run this query and return a buffer around the centroid for the state of Massachusetts.

select U.Centroid, Centroid.STBuffer(0.05) as buffer, U.StateBound
from
(	select geom.STCentroid() as Centroid, geom as StateBound
	from dbo.states_Provinces
	where name_1 =  'Massachusetts'
) U

Calculating a Buffer

The last query that will be demonstrated will introduce the buffer, even though I just gave an example. The buffer operation is a common operation within GIS tools. The following script will give a basic example of how to generate a buffer in Spatial SQL.

The user will notice that the shape column, geom, is set and the buffer method, STBuffer, is called. STBuffer accepts a parameter, the size of the buffer.  In this example the buffer size will be 0.05 decimal degrees.  Also, in this example a where statement is set to query a specific record from the table. When this is done only that record would be buffered. Again, the States_Provinces table is used.

select geom.STBuffer(0.05) as buffer
from dbo.States_Provinces
where name_1 =  'Massachusetts'

What’s Next

That is it for this section of the tutorial. As I said earlier, pretty basic stuff. In the next tutorial I will get into some other topics including unions, intersects, spatial indexes, joins and other methods and operations will be covered. And, it looks like I may end up creating several more tutorials than I originally planned!

Unitl next time…