SQL Stored Procedure to Calculate Area of a Polygon and Draw its Shape

Problem

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?

Solution

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:

1. A geometry variable type containing the shape of the polygon.
2. The value of the area of the polygon as a real type number.

Our stored procedure also has the following logic:

1. The procedure forms the polygon shape from the input list.
2. 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:

1. The procedure uses the STGeomFromText function in order to get the geometry instance.
2. 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

Polygon Results

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

Polygon Results

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

Results

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

Next Steps

• 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).

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019