Move coordinates a set distance in meters

Question

In SQL Server I want to be able to take a single latitude and longitude (51.500709, -0.124646) and move these coordinates 50 meters north, but I'm not sure how to do this. I am aware of the geography spatial data type and have used the STDistance function to get the distance between points when writing other queries. But I'm struggling to see any information on moving coordinates by a fixed distance.

Answer 1

Assume a table structure like this:

Tablename: TempDistance
ID : Int
Lat: Float
Lng: Float
Val: Money

You can calculate the cost radius for every combination of points via a CROSS JOIN

with cte as (
Select A.ID
      ,BID =B.ID
      ,BVal=B.Val
      ,Distance = geography::Point(A.Lat, A.Lng, 4326).STDistance(geography::Point(B.Lat, B.Lng, 4326))
From  TempDistance  A
cross join TempDistance  B
)
Select Top 10
       A.ID
      ,Cnt = sum(1)
      ,Val = sum(BVal)
 From cte A
 Where Distance<=200
 Group By A.ID
 Order By sum(BVal) Desc

Results

JUST TO BE CLEAR. This approach can get expensive if you have thousands of records. In my sample table there are 500 records. The CROSS JOIN will generate 250,000 records. There are some methods that can be applied to REDUCE the number of distance calculations via the n/111111 estimate, but that would require further study.

Answer 2

I was curious about the n/111111 approach in the link provided by Mitch.

I am pleased to report it "Ain't half bad". In this example, we were 0.06 meters (2.3 inches) off.

Example

Declare @Lat  float = 51.500709
Declare @Lng  float = -0.124646
Declare @LatM float = -50
Declare @LngM float = 0

;with cte as (
Select OldLat = @Lat
      ,OldLng = @Lng
      ,NewLat = @Lat + @LatM/111111
      ,NewLng = @Lng + @LngM/(111111 * cos(radians(@Lat)) )
)
Select *
      ,DistanceCheck = geography::Point(OldLat, OldLng, 4326).STDistance(geography::Point(NewLat, NewLng, 4326))
 From  cte 

Results