Create geographic polygon from points in T-SQL

Question

In my SQL Server (2008 R2) on Azure there is a table containing many geographic Points (latitude / longitude):

 CREATE TABLE MyPoints ( Region uniqueidentifier NOT NULL, Number int NOT NULL, Position geography NOT NULL, CONSTRAINT PK_MyPoints PRIMARY KEY(Region, Number) ) 

Now I want to create a Polygon from these points to determine which of my stores are located in the area defined by the points.

Is there a custom and quick way to build a polygon from given points in T-SQL? The solutions I found use the STGeomFromText / STGeomFomWKB to create a polygon, which seems to me very cumbersome and slow.

Sort of:

 SET @POLY = geometry::STPolyFromPoints(SELECT Position FROM MyPoints) 

Answer 1

As far as I know, there is no native function that takes a table as a parameter and converts it to a polygon.

It is best to combine a user-defined scalar function to combine the result column into a single comma-separated row with the STPolyFromText that you already read about.

UDF to merge columns into a CSV row

MSDN - STPolyFromText

Answer 2

Assuming that this table has a table full of ordered lengths and lats:

 CREATE TABLE [dbo].[LongAndLats]( [Longitude] [decimal](9, 6) NULL, [Latitude] [decimal](9, 6) NULL, [SortOrder] [int] NULL ) 

This converts these points to a polygon:

 DECLARE @BuildString NVARCHAR(MAX) SELECT @BuildString = COALESCE(@BuildString + ',', '') + CAST([Longitude] AS NVARCHAR(50)) + ' ' + CAST([Latitude] AS NVARCHAR(50)) FROM dbo.LongAndLats ORDER BY SortOrder SET @BuildString = 'POLYGON((' + @BuildString + '))'; DECLARE @PolygonFromPoints geography = geography::STPolyFromText(@BuildString, 4326); SELECT @PolygonFromPoints 

Some notes:

• The landfill must be closed. i.e. the first and last points must be the same.
• Must have a minimum of 4 points.
• The order of the items is important. He must follow the “left arm / leg rule” (areas lying on the left side of the line drawn between the points are counted inside the polygon)