question

DebbieEdwards-1726 avatar image
0 Votes"
DebbieEdwards-1726 asked MelissaMa-msft commented

SQL - Spatial data. STGeomFromText Error The Polygon input is not valid because the start and end points of the ring number 1 are not the same.

Hi We are using Spatial Functions in SQL to plot data against Polygons.

I have this table

CREATE TABLE [staging].[SpatialTable]
( id int IDENTITY (1,1),
geoType Varchar(255),
geoName Varchar(255),
GeogCol1 geography,
GeogCol2 AS GeogCol1.STAsText() );

And then I run the data in using just as an example

INSERT INTO [staging].[SpatialTable] (geotype, geoName ,GeogCol1)
VALUES ('Urban Rural','Rural village and dispersed West Dorset 007B' ,geography::STGeomFromText('POLYGON((-2.8005556762775852 50.771725901087734, -2.8058240840452289 50.768680571659267, -2.8097337336604231 50.772311490417344, -2.8160891081714792 50.772065411491987, -2.8131455966064212 50.770970575979554, -2.8159279496786112 50.768294852615867, -2.8197683467505792 50.769276699173666, -2.8232377337473848 50.7610716362243, -2.829778250277847 50.759884662541538, -2.829655805117103 50.747250845978961, -2.8406421651036018 50.746782790556018, -2.838442336798829 50.740176901372415, -2.850741180632983 50.743240259274828, -2.8544393723745412 50.739002734310915, -2.876226918692248 50.735980858538881, -2.8766625308586629 50.735923841118932, -2.8938468665095729 50.738041632047739, -2.8944517981048441 50.73276532815742, -2.8111854577669249 50.717902353298271, -2.7931085652214569 50.718040253825556, -2.767540314910522 50.71092362257717, -2.76450592926112 50.715892673463529, -2.759678687246776 50.714457767252192, -2.759845808289572 50.724806838295734, -2.7676238473258059 50.727155297146822, -2.767025621465967 50.732884137892981, -2.7710057130181061 50.733365552741844, -2.7798860700818748 50.740255608122574, -2.7800812537066628 50.7529851398293, -2.7955058267727648 50.76329371242732, -2.7927681099694812 50.767184667530628, -2.8005556762775852 50.771725901087734))', 4326));

Which then gives us geography information we can use to Insersect with data points from data.

However we are getting some errors

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24306: The Polygon input is not valid because the start and end points of the ring number 1 are not the same. Each ring of a polygon must have the same start and end points. System.FormatException: at Microsoft.SqlServer.Types.GeographyValidator.ValidatePolygonRing(Int32 iRing, Int32 cPoints, Double firstX, Double firstY, Double lastX, Double lastY) at Microsoft.SqlServer.Types.Validator.Execute(Transition transition) at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure() at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText() at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText() at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid) .

we know why it is. there are two example

81896-polygons1.jpg

this first example basically a gap the middle which causes the issue. The second example

81915-polygons2.jpg



So this is where are area is inside another area. The main surrounding outside are is causing the issue

I cant find any other SQL Spatial functions to help get over this issue, but if anyone has any ideas that could be achieved within SQL, Without having to restructure the spatial file I would be greatful

sql-server-generalsql-server-transact-sql
polygons1.jpg (23.5 KiB)
polygons2.jpg (27.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


How to reproduce the error?

0 Votes 0 ·

Hi @DebbieEdwards-1726

Welcome to Microsoft Q&A!

Please provide more details (like the query which reported this error or the spatial file you had) so that we could reproduce this error and proceed with help you.

Thank you for understanding!

Best regards
Melissa




0 Votes 0 ·

0 Answers