In my SQL Server (2008 R2) on Azure, there's a table containing a lot of geographical 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 this points to determine, which of my stores are located in the area defined by the points.
Is there a native and fast way to build a polygon from the given points in T-SQL? The solutions I found are using the STGeomFromText
/STGeomFomWKB
methods to create a polygon, which seems very cumbersome and slow to me.
Something like:
SET @POLY = geometry::STPolyFromPoints(SELECT Position FROM MyPoints)
Assuming we have a table full of ordered longs and lats in this table:
CREATE TABLE [dbo].[LongAndLats](
[Longitude] [decimal](9, 6) NULL,
[Latitude] [decimal](9, 6) NULL,
[SortOrder] [int] NULL
)
This will convert those points into 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: