0

I have a following piece of code

DECLARE @g geometry;
DECLARE @borders geography;   
SET @g = geometry::STGeomFromText('SOME WKT', 0); 
SET @borders = GEOGRAPHY::STGeomFromText(@g.MakeValid().STAsText(),4326)
SELECT @g;
SELECT @borders;

Since it's too long to paste it here, WKT Can be fond at this link: https://justpaste.it/6qw0a

Can someone please explain to me, why it displays well when I draw it as geometry, but when I try to draw it as a geography, it displays entire world instead of a small group of islands.

Here's the screenshot: enter image description here

Robert
  • 3,353
  • 4
  • 32
  • 50

2 Answers2

1

You have a ring orientation problem. For geography shapes, the order in which you specify the points on the border matters. That is, imagine a square with corners A, B, C, and D. (A, B, C, D, A) is not the same as (A, D, C, B, A). One of them specifies what you think it would while the other defines the rest of the world with a square shaped hole in it! But don't feel bad, this is a very common "gotcha!" in geographic data.

Your data is inconsistent in how it defines its points. That is, some specify the island border as clockwise while others are specified counterclockwise. All is not lost though. Using your WKT, I believe I was able to recover the desired shape(s).

DECLARE @wkt varchar(max) = '«your wkt here»';
DECLARE @borders geography;   
SET @borders = GEOGRAPHY::STGeomFromText(@wkt, 4326);

select geography::UnionAggregate(b2.g)
from Util.dbo.Numbers as n
cross apply (
    select @borders.STGeometryN(n.n) as g1,
        @borders.STGeometryN(n.n).ReorientObject() as g2
) as b
cross apply (
    select case when g1.EnvelopeAngle() > 90 then g2 else g1 end as g
) as b2
where n.n <= @borders.STNumGeometries();

By way of explanation, I'm picking out each individual geometry from the geometry collection by index, specifying both it, and it's re-oriented version via a cross apply, and then using the heuristic of "if the envelope angle of the geography is greater than 90°, it's probably mis-oriented" to choose the (likely to be) correct one. From there, I throw it all into a UnionAggregate to jam them all back into one geography instance.

Lastly, in case it's not obvious, Numbers is just a table of integers that I have lying around for occasions such as these.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Could you please explain Util.Numbers table a bit more? What kind of numbers and in what range do you have in this table? – Robert Jul 26 '18 at 06:09
  • They're just the counting numbers starting at 1. As to how many, I usually seed it with 10,000 or so. if I find that that's not sufficient, I'll add more. I use a variant of this (https://www.itprotoday.com/microsoft-sql-server/virtual-auxiliary-table-numbers) to populate the table. – Ben Thul Jul 26 '18 at 11:47
0
DECLARE @geom GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @geog GEOGRAPHY = @geom.MakeValid().STUnion(@geom.STStartPoint()).STAsText()

This topic had the answer to my question

Robert
  • 3,353
  • 4
  • 32
  • 50