CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2geography
Gene
Gene

Reputation: 4232

Create geography polygon from points in T-SQL

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)

Upvotes: 9

Views: 33439

Answers (2)

g2server
g2server

Reputation: 5367

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:

  • The polygon needs to be closed. ie. the first and last point should be the same.
  • Should have min 4 points.
  • The order of the points are important. It should follow the "left hand/foot rule" (areas lying to the left-hand side of the line drawn between the points are considered to be inside the Polygon)

Upvotes: 11

Tom Halladay
Tom Halladay

Reputation: 5761

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

Your best is to combine a scalar User Defined Function to concatenate a column of results into a single comma seperated string with the STPolyFromText that you have already read about.

UDF to Concatenate Column to CSV String

MSDN - STPolyFromText

Upvotes: 3

Related Questions