Search code examples
sql-servert-sqlsql-server-2008-r2geography

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

  • 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