CODEWITHSUNDEEP
sql-serverspatialspatial-query
dotnetdev
dotnetdev

Reputation: 29

SQL Server Spatial convert from Linestring to Polygon

Can someone help me with convert SQL Server Geometry Shape object to Geometry Polygon object.

I have have a Shape object which is of "Ohio state plan south" coordinate with SRID 32123 and I want to convert it to Geometry Polygon with SRID 3857

This is what I have so far... when I am running it getting below error.

Error: "24119: The Polygon input is not valid because the start and end points of the exterior ring are not the same. Each ring of a polygon must have the same start and end points."

DECLARE @predata varchar(MAX) = 'LINESTRING (544447.66750002524 219649.36696754955, 544446.556000026 219690.21900326665, 544443.99000002549 219720.61000326276, 544435.09000002721 219836.23000326566, 544431.66000001959 219896.72000326123, 544430.0600000252 219924.97000326123, 544422.56000002555 219999.65000326186, 544419.05563904892 220098.02782277763)';
DECLARE @data geometry;
DECLARE @linestring geometry = @predata;
DECLARE @srid int;
SET @predata = REPLACE(@predata, 'LINESTRING', 'POLYGON(') + ')';
SELECT @predata AS PolygonString

SET @data = geometry::STGeomFromText(@predata, 3857);
SELECT @data AS GeographyPolygon

Upvotes: 0

Views: 1504

Answers (2)

Salahuddin Ahmed
Salahuddin Ahmed

Reputation: 5650

As agreed with the previous answer please simply use your scripts with a bit change shown as below:

--start point should be same as end point
DECLARE @predata varchar(MAX) = 'LINESTRING (
544447.66750002524 219649.36696754955, 
544446.556000026 219690.21900326665, 
544443.99000002549 219720.61000326276, 
544435.09000002721 219836.23000326566, 
544431.66000001959 219896.72000326123, 
544430.0600000252 219924.97000326123, 
544422.56000002555 219999.65000326186, 
544447.66750002524 219649.36696754955)'; 
--end point should be same as start point
DECLARE @data geometry;
DECLARE @linestring geometry = @predata;
DECLARE @srid int;
SET @predata = REPLACE(@predata, 'LINESTRING', 'POLYGON(') + ')';
SELECT @predata AS PolygonString
SET @data = geometry::STGeomFromText(@predata, 3857);
SELECT @data AS GeographyPolygon

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32697

The error is telling you what the problem is. That is, polygons need to start and end with the same point. Your linestring does not (starts at 544447.66750002524 219649.36696754955, ends at 544419.05563904892 220098.02782277763). I was able to get it to work by taking the starting point of the linestring and appending it to the end. If this is a one-time operation, that should suffice.

Upvotes: 2

Related Questions