Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)
like image 591
Gene Avatar asked Mar 16 '12 14:03

Gene


People also ask

What is difference between geography and geometry in SQL Server?

The connecting edge between two vertices in a geometry type is a straight line. However, the connecting edge between two vertices in a geography type is a short great elliptic arc between the two vertices.

Is geography a data type in SQL?

The geography spatial data type, geography, is implemented as a . NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

What is the datatype for latitude and longitude in SQL?

precision you should use DECIMAL . Latitudes range from -90 to +90 (degrees), so DECIMAL(10,8) is ok for that, but longitudes range from -180 to +180 (degrees) so you need DECIMAL(11,8) .

What is geometry type in SQL?

SQL Server supports two spatial data types: Geometry : Stores data based on a flat (Euclidean) coordinate system. The data type is often used to store the X and Y coordinates that represent lines, points, and polygons in two-dimensional spaces. Geography : Stores data based on a round-earth coordinate system.


2 Answers

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)
like image 191
g2server Avatar answered Sep 23 '22 06:09

g2server


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

like image 39
Tom Halladay Avatar answered Sep 21 '22 06:09

Tom Halladay