By: Eli Leiba | Updated: 2018-05-11 | Comments | Spatial Data Storage
How can we create a simple and dynamic SQL Server T-SQL script that will accept a string in of the form of 'X1 Y1, X2 Y2, X3Y3,………,Xn Yn' which is a textual representation of a two dimensional polygon. The x,y points are the values of the polygon from first to last where the first value should be equal to the last one. We need the SQL Server stored procedure to calculate the area of the polygon and draw its shape on the spatial results tab. How can we accomplish this using T-SQL?
The solution involves creating a T-SQL stored procedure (called dbo.usp_GetPolygonAreaAndShape) that accepts an input T-SQL string in this form 'X1 Y1, X2 Y2, X3Y3,………,Xn Yn' and generates two output values:
- A geometry variable type containing the shape of the polygon.
- The value of the area of the polygon as a real type number.
Our stored procedure also has the following logic:
- The procedure forms the polygon shape from the input list.
- The procedure checks the validity of the polygon by checking that the first and last points of the polygon are equal.
The error handling logic will print an appropriate message and exit the code with the following dependencies:
- The procedure uses the STGeomFromText function in order to get the geometry instance.
- The procedure uses the STArea function of the geometry instance from (b) in order to calculate the polygon's area.
SQL Server Stored Procedure to Calculate the Area of a Polygon and Draw the Shape
-- ============================================================== -- Author: Eli Leiba -- Create date: 04-2018 -- Description: get the area of a polygon and draw its shape -- =============================================================== CREATE PROC dbo.usp_GetPolygonAreaAndShape ( @vertixList NVARCHAR (max), @polygonShape geometry OUT, @polygonArea REAL OUT ) AS BEGIN DECLARE @g geometry; DECLARE @polygon NVARCHAR (max) DECLARE @isPolygonValid BIT DECLARE @revvertixList NVARCHAR (max) DECLARE @startpoint VARCHAR (30) DECLARE @endpoint VARCHAR (30) DECLARE @firstcommapos INT DECLARE @lastcommapos INT SET NOCOUNT ON SET @polygon = CONCAT ( 'POLYGON (( ', @vertixList, '))' ) SET @revvertixList = REVERSE (@vertixList) SET @firstcommapos = charindex(',', @vertixList, 1) SET @lastcommapos = charindex(',', @revvertixList, 1) SET @startpoint = rtrim(ltrim(LEFT(@vertixList, @firstcommapos - 1))) SET @endpoint = reverse (rtrim(ltrim(LEFT(@revvertixList, @lastcommapos - 1)))) IF @startpoint != @endpoint PRINT 'Given input parameter does not represent a valid polygon.' ELSE BEGIN SET @g = geometry::STGeomFromText(@polygon, 0); SET @isPolygonValid = @g.STIsValid() IF @isPolygonValid = 1 BEGIN SET @polygonShape = @polygon SET @polygonArea = @g.STArea(); END END SET NOCOUNT OFF END GO
Sample Execution - Triangle
Calculate the area and draw the shape of a triangle.
The input values are the values that will be drawn on the grid. In the example below the grid is a 2 by 2 grid. Each location has a value from 1 to 3 from bottom to top and from 1 to 3 from left to right.
To map our triangle we want to map out the grid points as follows:
- start at 1,1
- move to 3,3
- move to 3,1
- move back to the starting point of 1,1
To create the shape the input values are as follows: 1 1, 3 3, 3 1, 1 1 which we will use below.
DECLARE @polygonShape geometry DECLARE @polygonArea real EXEC dbo.usp_GetPolygonAreaAndShape '1 1, 3 3, 3 1, 1 1', @polygonShape OUT,@polygonArea out SELECT @polygonShape SELECT @polygonArea
Area = 2
Sample Execution - Square
Calculating the area and drawing shape of a square.
DECLARE @polygonShape geometry DECLARE @polygonArea real EXEC dbo.usp_GetPolygonAreaAndShape '1 1, 1 3, 3 3, 3 1, 1 1', @polygonShape OUT,@polygonArea out SELECT @polygonShape SELECT @polygonArea
Area = 4
Sample Execution - Pentagon
Calculating the area of a pentagon and drawing the shape.
DECLARE @polygonShape geometry DECLARE @polygonArea real EXEC dbo.usp_GetPolygonAreaAndShape '1 1, 3 3, 4 2, 3 2, 3 1, 1 1', @polygonShape OUT,@polygonArea out SELECT @polygonShape SELECT @polygonArea
Area = 2.5
Sample Execution - Invalid Polygon
See what happens if it is an invalid polygon (first and last points are not equal).
DECLARE @polygonShape geometry DECLARE @polygonArea real EXEC dbo.usp_GetPolygonAreaAndShape '1 1, 3 3, 4 2, 3 2, 3 1, 1 2', @polygonShape OUT,@polygonArea out SELECT @polygonShape SELECT @polygonArea
The result is: The given input parameter does not represent a valid polygon.
Area = NULL
- Create this simple SQL Server stored procedure in your user defined databases and use it as an option for calculating area.
- Consider this SQL Server stored procedure for various polygon shapes and in other geometric applications.
- Error handling functionality such as (BEGIN TRY and BEGIN CATCH blocks) can be added to the procedure. It was omitted here in order to simplify the procedure's code.
- The procedure was tested on Microsoft SQL Server 2014, 2016 and 2017.
- The procedure should be compatible with SQL Server 2012 and above (it uses the geometry type and the CONCAT function).
Last Updated: 2018-05-11
About the author
View all my tips