Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create geography polyline from points in T-SQL

I have a table schema that looks like this:

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

Sample data looks like this:

enter image description here

How can I convert these points into a geography polyline using TSQL?

like image 764
g2server Avatar asked Jun 17 '14 09:06

g2server


People also ask

What is geography :: Point in SQL?

GEOGRAPHY::Point(Latitude, Longitude, SRID) SRID stands for Spatial Reference Identifier. The most common SRID is 4326, which has the information in meters. For other type of SRID's run this query in SQLServer: select * from sys.spatial_reference_systems.

What is difference between geography and geometry in SQL Server?

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.

What is geometry data type in SQL?

The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system. SQL Server supports a set of methods for the geometry spatial data type.

What data types is used to store GPS coordinates in SQL Server?

The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.


1 Answers

try this: (note: the ordering of the points is important for the line to be generated correctly.)

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 = 'LINESTRING(' + @BuildString + ')';   
DECLARE @LineFromPoints geography = geography::STLineFromText(@BuildString, 4326);
SELECT @LineFromPoints
like image 141
g2server Avatar answered Oct 19 '22 03:10

g2server