Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   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
   )
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.

square

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

square

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

quadrant

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

triangle

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools