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.
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:
Our stored procedure also has the following logic:
The error handling logic will print an appropriate message and exit the code with the following dependencies:
-- ==============================================================-- 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
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:
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
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
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
Area= 2.5
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
Last Update: 2018-05-11
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.
View all my tipsRelated Resources