How can I add a distance in meters to the latitude value in SQL Server 2014

Question

Given

DECLARE @p1 geography
DECLARE @p2 geography
DECLARE @distance int

SET @distance = 10000 -- meters
SET @p1 = geography::Point(51.5001524, -0.1262362, 4326)

How can I set @p2 such that its latitude is @distance meters north or south of @p1?

Answer 1

I have no clue how well this will work in practice, but it can be done just using inbuilt functions.

DECLARE @Distance INT = 10000
DECLARE @latitude DECIMAL(20,10) = 51.5001524
DECLARE @longitude DECIMAL(20,10) = -0.1262362
DECLARE @OriginalPoint GEOGRAPHY = GEOGRAPHY::Point(@latitude, @longitude, 4326)
--This will create a point at the same longitude but at the south pole.
DECLARE @DueSouthPoint GEOGRAPHY = GEOGRAPHY::Point(-90, @longitude, 4326) 

DECLARE @SouthLineWithLengthOfDistance GEOGRAPHY = 
  @OriginalPoint.ShortestLineTo(@DueSouthPoint) --This is a line due south
  .STIntersection(                              --This will return the line segment inside the circle
    @OriginalPoint.STBuffer(@distance)          --This will draw a circle around the original point with a radius of @distance
  )                                 

  --Now we have to return the lower point on the line.  
  --It seems to be fairly inconsistent in which point is first in the line
  --I don't want to spend the time to figure it out, so I'm just using a case to determine which point to return.
  SELECT
    CASE 
      WHEN @OriginalPoint.STDistance(@SouthLineWithLengthOfDistance.STPointN(1)) = 0 
          THEN @SouthLineWithLengthOfDistance.STPointN(2)
      ELSE @SouthLineWithLengthOfDistance.STPointN(1)
    END