messi messi - 6 months ago 39
SQL Question

Finding shortest distance between two polygons(SqlGeography c# )

I want to find the shortest distance between two SqlGeography polygon. I know there is a method

( but it gives empty string while doing so.
Can anyone suggest me some alternate way to do so?
enter image description here


You should define a GEOGRAPHY in Counter Clockwise order. If you define it CW, it will be all the world except the region you are defining:

DECLARE @G1 GEOGRAPHY = 'POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))';
DECLARE @G2 GEOGRAPHY = 'POLYGON ((45 45, 45 46, 44 46, 44 45, 45 45))';

//This is what you have defined
DECLARE @G3 GEOGRAPHY = 'POLYGON ((45 45, 44 45, 44 46, 45 46, 45 45))';

@G1: point order is CCW so @G1 is a polygon containing POINT(2,2)

@G2: point order is CCW so @G2 is a polygon containing POINT(44.5,45.5)

@G3: point order is CW so @G3 is a polygon containing the entire world except the @G2 polygon. It also contains the @G1 polygon, so the shortest distance between @G1 and @G3 doesn't have any meaning.

Toggle Between CW and CCW geometries

Using the ReorientObject() method, you can toggle between CW and CCW geographies. So if you try:

SELECT @G3.ReorientObject().STDifference(@G2).STAsText();

The result would be GEOMETRYCOLLECTION EMPTY because they contain the same region. So they intersect and the result of ShortestLineTo returns a LINESTRING EMPTY because they intersect each other.

One more point

You can check if a polygon contains too large of a region by checking the EnvelopeAngle so you are made aware of mistakenly defined geographies.

Envelope angle demonstration screenshot

As shown in the picture, EnvelopeAngle=180 means that polygon contains a very large region of the world.