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

By: Eli Leiba |  |   Related Tips:More >Spatial Data Storage

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.

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   )ASBEGIN   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 OFFENDGO

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 geometryDECLARE @polygonArea realEXEC dbo.usp_GetPolygonAreaAndShape '1 1, 3 3, 3 1, 1 1', @polygonShape OUT,@polygonArea outSELECT @polygonShapeSELECT @polygonArea

Polygon Results

Area = 2

Sample Execution - Square

Calculating the area and drawing shape of a square.

DECLARE @polygonShape geometryDECLARE @polygonArea  realEXEC dbo.usp_GetPolygonAreaAndShape '1 1, 1 3, 3 3, 3 1, 1 1', @polygonShape OUT,@polygonArea outSELECT @polygonShapeSELECT @polygonArea

Polygon Results

Area = 4

Sample Execution - Pentagon

Calculating the area of a pentagon and drawing the shape.

DECLARE @polygonShape geometryDECLARE @polygonArea realEXEC dbo.usp_GetPolygonAreaAndShape '1 1, 3 3, 4 2, 3 2, 3 1, 1 1', @polygonShape OUT,@polygonArea outSELECT @polygonShapeSELECT @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 geometryDECLARE @polygonArea realEXEC dbo.usp_GetPolygonAreaAndShape '1 1, 3 3, 4 2, 3 2, 3 1, 1 2', @polygonShape OUT,@polygonArea outSELECT @polygonShapeSELECT @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).

Last Update: 2018-05-11

About the author