CODEWITHSUNDEEP
sqlspatialgeographysqlgeography
ccdrm
ccdrm

Reputation: 300

SQL Spatial / Issue With Incorrect Or Inconsistent Polygon Data

I am working on implementing a polygon accumulation process for an Insurance company. I'm importing GeoJSON data into SQL and transforming into a table that I can later link to a series of lat/long points.

I'm running into lots of trouble with the polygon data. SQL Server's telling me my polygons aren't valid, but so far I don't see an obvious problem. Here's the original polygon source: https://hub.arcgis.com/datasets/esri::usa-counties/explore?location=36.587667%2C-96.452016%2C4.81

Here's a sample polygon and SQL code to transform it from WKT (it may be invalid) to a SQL spatial geography object

select      Geography::STGeomFromText(
                'MULTIPOLYGON (((-84.976413007999952 29.721111997000037), (-84.976519975999963 29.721016060000068), (-84.976666054999953 29.721056006000026), (-84.976939993999963 29.721130048000077), (-84.977004007999938 29.721208937000029), (-84.977324076999935 29.721559927000044), (-84.977269923999984 29.722414001000061), (-84.977025066999943 29.723020043000076), (-84.976738926999985 29.723191025000062), (-84.976370053999972 29.723202056000048), (-84.975935996999965 29.722776022000062), (-84.97589605099995 29.72236302400006), (-84.97570701799998 29.722185022000076), (-84.975701000999948 29.722102957000061), (-84.975679941999942 29.721772025000064), (-84.976135057999954 29.72136303800005), (-84.976413007999952 29.721111997000037)))'
                , 4326)

I get this error:
Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24305: The Polygon input is not valid because the ring number 1 does not have enough points. Each ring of a polygon must contain at least four points.

Ok, so it doesn't think I have enough points. But at first glance I have plenty, and they don't look like they repeat other than the start and end point, per WKT requirements. One thought is that this is, of course, not a multi-polygon but instead a normal polygon.

I tried that in code:

select      Geography::STGeomFromText(
                'POLYGON (((-84.976413007999952 29.721111997000037), (-84.976519975999963 29.721016060000068), (-84.976666054999953 29.721056006000026), (-84.976939993999963 29.721130048000077), (-84.977004007999938 29.721208937000029), (-84.977324076999935 29.721559927000044), (-84.977269923999984 29.722414001000061), (-84.977025066999943 29.723020043000076), (-84.976738926999985 29.723191025000062), (-84.976370053999972 29.723202056000048), (-84.975935996999965 29.722776022000062), (-84.97589605099995 29.72236302400006), (-84.97570701799998 29.722185022000076), (-84.975701000999948 29.722102957000061), (-84.975679941999942 29.721772025000064), (-84.976135057999954 29.72136303800005), (-84.976413007999952 29.721111997000037)))'
                , 4326)

I get this error:
Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24141: A number is expected at position 30 of the input. The input has (-84.976413007999952.

I don't see an obvious problem here. The first coordinate is (-84.976413007999952 29.721111997000037) so perhaps it's tripping over the space in between the X and Y? Or maybe there is too much coordinate precision?

Also, I realize that the syntax for POLYGON uses two parenthesis rather than three. But if I use that syntax I'm seeing the first error again.

Feel as though I am missing something obvious but have worked with plenty of SQL spatial objects in the past and haven't run into this much trouble before. Any/all ideas are welcome and thank you!

Upvotes: 1

Views: 651

Answers (1)

ccdrm
ccdrm

Reputation: 300

Answered: removed the inner bounding parenthesis and that solved the issue.

Upvotes: 1

Related Questions