Finding the halfway point of two points when one point is at the North Pole

Question

Hi there

I'm having some issues trying to find a halfway point between two points when one of the points is at the North Pole. The two methods for determining this that I have discovered previously don't work, as they don't take into account that the co-ordinates "anything 90" will always be the North Pole.

I'm starting with a POLYGON which I'm putting into a table valued function:

DECLARE @Level1 TABLE
    (
      name VARCHAR(255) ,
      location GEOGRAPHY 
    );

INSERT INTO @Level1 VALUES

('1', 'POLYGON((0 0, 90 0, 0 90, 0 0))')


select
		t.*

from
		@Level1					i
			cross apply
		dbo.tessallate(i.location)		t

Here is the function - I know it's horrible, turning the geography into text and then back to geography, but not sure how else to do it - not that that's the issue at hand:

CREATE FUNCTION [dbo].[tessallate] 
(	
	@tile geography
)
RETURNS TABLE
AS
RETURN 
(
select

/*
		GEOGRAPHY::Point(
			@tile.STPointN(1).Lat,
			@tile.STPointN(1).Long,
            4326)																p1,
		GEOGRAPHY::Point(
			@tile.STPointN(2).Lat,
			@tile.STPointN(2).Long,
            4326)																p2,
		GEOGRAPHY::Point(
			@tile.STPointN(3).Lat,
			@tile.STPointN(3).Long,
            4326)																p3,
		GEOGRAPHY::Point(
			(@tile.STPointN(1).Lat + @tile.STPointN(2).Lat) * 0.5,
			(@tile.STPointN(1).Long + @tile.STPointN(2).Long) * 0.5,
            4326)																p1_2,
		GEOGRAPHY::Point(
			(@tile.STPointN(1).Lat + @tile.STPointN(3).Lat) * 0.5,
			(@tile.STPointN(1).Long + @tile.STPointN(3).Long) * 0.5,
            4326)																p1_3,
		GEOGRAPHY::Point(
			(@tile.STPointN(2).Lat + @tile.STPointN(3).Lat) * 0.5,
			(@tile.STPointN(2).Long + @tile.STPointN(3).Long) * 0.5,
            4326)																p2_3,


*/


		GEOGRAPHY::Parse(
			'POLYGON((' 

			+ CONVERT(
				VARCHAR,
				@tile.STPointN(1).Lat
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				@tile.STPointN(1).Long
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Lat + @tile.STPointN(3).Lat) * 0.5
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Long + @tile.STPointN(3).Long) * 0.5
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Lat + @tile.STPointN(2).Lat) * 0.5
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Long + @tile.STPointN(2).Long) * 0.5
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				@tile.STPointN(1).Lat
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				@tile.STPointN(1).Long
			  )


			+ '))'
		)																				location

UNION ALL

SELECT

		GEOGRAPHY::Parse(
			'POLYGON((' 

			+ CONVERT(
				VARCHAR,
				@tile.STPointN(2).Lat
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				@tile.STPointN(2).Long
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Lat + @tile.STPointN(2).Lat) * 0.5
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Long + @tile.STPointN(2).Long) * 0.5
			  )
			+ ', '




			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(2).Lat + @tile.STPointN(3).Lat) * 0.5
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(2).Long + @tile.STPointN(3).Long) * 0.5
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				@tile.STPointN(2).Lat
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				@tile.STPointN(2).Long
			  )


			+ '))'
		)																				location

UNION ALL

SELECT

		GEOGRAPHY::Parse(
			'POLYGON((' 

			+ CONVERT(
				VARCHAR,
				@tile.STPointN(3).Lat
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				@tile.STPointN(3).Long
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(2).Lat + @tile.STPointN(3).Lat) * 0.5
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(2).Long + @tile.STPointN(3).Long) * 0.5
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Lat + @tile.STPointN(3).Lat) * 0.5
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				(@tile.STPointN(1).Long + @tile.STPointN(3).Long) * 0.5
			  )
			+ ', '

			+ CONVERT(
				VARCHAR,
				@tile.STPointN(3).Lat
			  )
			+ ' '
			+ CONVERT(
				VARCHAR,
				@tile.STPointN(3).Long
			  )


			+ '))'
		)																				location
union all select @tile
)

Here's the result:

As you can see, the halfway point is calculated between 0,90 and 90,90, to be 45,45. As far as trig is concerned this is correct. However the actual value should be 90,45:

To produce the above result I put a CASE block in the function to check if the long was 90, but this increases the complexity by an order of magnitude and I feel it will not adequately cover all scenarios.

Also, if I change the POLYGON to be 'POLYGON((0 0, 90 0, 90 90, 0 0))' all the results are broken, so none of this is very robust.

There must be a simpler way. I think the problem lies with turning the lat and long into points and then acting on them as if they were in a grid (trig), but I'm just not experienced enough with spatial to know how else to effect this.

If I could draw the shortest straight line between two points on the earth, and then walk hallway along it, the problem would be solved.

Anyone with any idesa?

Thanks

Paul.

Answer 1

Hi Aylwyn,

What if, in you CASE statement, you simply check if distance in not 0 ?

The two points 0,90 and 90,90 are the same and STDistance() returns 0. 

Answer 2

To answer my own question...

The only way I have found is to use a case statement to check if the Lat is 90 or -90 and then change the x co-ordinate as appropriate. It is a bit hacky, but it's the only way I could get it to work.

I am now using a different version of finding the halfway point, but this does not address the core issue, it's just a better, more accurate method:

DECLARE	
	@tile geography = 'POLYGON((180 0, 270 0, 180 90, 180 0))'

	SELECT
			GEOMETRY::STGeomFromWKB(
				@tile.STPointN(1).STUnion(@tile.STPointN(2)).STAsBinary(),
				4326
			).STEnvelope().STCentroid().ToString()

Also, in SQL 2008 R2, you could end up with a ring orientation issue. In 2012 this is solved by checking the shape and then inverting if necessary. Check out this article by Alastair Aichison: Ring Orientation, Bigger-than-a-Hemisphere Polygons, and the ReorientObject method in SQL Server 2012