How to reverse a JSON polygon order to adjust for ring orientation in SQL Server

Question

In a varchar column verticies, I store this JSON:

[{"lng":-82.82312393275788,"lat":27.982508364801642},
 {"lng":-82.8065586098819,"lat":27.984479050536944},
 {"lng":-82.808017731586,"lat":27.9704560800863},
 {"lng":-82.82166481105378,"lat":27.975004270258353},
 {"lng":-82.8230381020694,"lat":27.980537643052056},
 {"lng":-82.82312393275788,"lat":27.982508364801642}]

This was saved as a string from a Google maps polygon. My intent is to save it as a geography data type, and store it into the [geo] column.

My problem is when I'm trying to use:

geography::STGeomFromText('POLYGON(' + replace(replace(replace(replace(replace(vertices,'{"lng":',''),',"lat":',' '),'[','('),']',')'),'}','') + ')', 4326)

I get the geography error:

24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.

Looking at other stack overflow answers I'm told this is a ring orientation problem, and I need to reverse the Json. From what I understand, the first and last points are the start/stop points and they are good, and I need to reverse the order of all the other points.

My question: Is there any way to do this using a function in SQL Server?

Answer 1

If you are using SQL Server 2014+ and geometry type; your code should work perfectly:

declare @g geometry, @json nvarchar(400) = '[{"lng":-82.82312393275788,"lat":27.982508364801642},
 {"lng":-82.8065586098819,"lat":27.984479050536944},
 {"lng":-82.808017731586,"lat":27.9704560800863},
 {"lng":-82.82166481105378,"lat":27.975004270258353},
 {"lng":-82.8230381020694,"lat":27.980537643052056},
 {"lng":-82.82312393275788,"lat":27.982508364801642}]';

 set @g = geometry::STPolyFromText('POLYGON(' + replace(replace(replace(replace(replace(@json,'{"lng":',''),',"lat":',' '),'[','('),']',')'),'}','') + ')', 4326);  
SELECT @g.ToString(); 

[SQL Fiddle Demo]

---

But if your question is about creating a Scalar-Function, the code can be something like this:

create function dbo.GetPolygon 
(
    @inputJson nvarchar(max),
    @sid int
)
returns geometry
as
begin
    declare @g geometry, @json nvarchar(max) = @inputJson;
    
    set @g = geometry::STPolyFromText('POLYGON(' + replace(replace(replace(replace(replace(@json,'{"lng":',''),',"lat":',' '),'[','('),']',')'),'}','') + ')', @sid);  

    return @g;
end
go

Then use it just like this:

select dbo.GetPolygon(@json, 4326).ToString();

---

But if you are using geography type: you can't draw a polygon simply, it should covers some other conditions also:

Note: The geography type is a little bit more restrictive than geometry. It can't cross different hemispheres and the outer ring must be drawn counter-clockwise.^[1]

GEOGRAPHY is for terrestrial spatial data (that is, data on the curved surface of the Earth)^[2]

And for more Geographical info check this: What are the pros and cons of PostGIS geography and geometry types?

SELECT @g.ToString(), @g.STIsValid(); 
-- To test that geography is valid

BTW; This code will also work fine and is valid: [SQL Fiddle Demo]