1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
CGarden
  • 169
  • 17
  • 2
    Related for moderate precision: https://gis.stackexchange.com/questions/2951/algorithm-for-offsetting-a-latitude-longitude-by-some-amount-of-meters – Mitch Jul 13 '22 at 21:37
  • Thank you, looks helpful. Not sure though how to move the lat/lngs in a certain direction. – CGarden Jul 13 '22 at 21:51

2 Answers2

3

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

enter image description here

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Probably a stupid question, but why did you set ```@LatM``` to -50 and the ```@LngM``` to 0? I'm clearly not very good at maths. – CGarden Jul 13 '22 at 22:44
  • 1
    @CGarden I was testing north and south. Bad copy/paste. Assuming Northern hemisphere you would want +50 meters not -50 meters. This example was ONE moving part, but you can adjust both at the same time. – John Cappelletti Jul 13 '22 at 22:47
  • If I keep one of the latitude or longitude zero and change the other then the distance calculation is accurate, but when I change both the result returned is completely out i.e. if I'm looking to place a coordinate 50m away it will come out as 75m away. Do you know why that is? – CGarden Jul 18 '22 at 15:45
  • 1
    @CGarden Think of the hypotenuse of a triangle. https://www.omnicalculator.com/math/hypotenuse#:~:text=Given%20two%20right%20triangle%20legs,%3D%20%E2%88%9A(a%C2%B2%20%2B%20b%C2%B2) – John Cappelletti Jul 18 '22 at 15:52
  • So I would need to change the current formula to take account of this? Thank you. – CGarden Jul 18 '22 at 16:09
  • 1
    @CGarden No. This is basic geometry. I ran the calculation for 50/0 and then 0/50 and then ran 50/50. See the last column TheromCheck ? Pretty much spot on. https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=926b73875e88417fc95d69bc8ff2263f – John Cappelletti Jul 18 '22 at 16:17
  • 1
    @CGarden Think about it. You're at a point x/y. You move 50m north ... your 50m away from your starting point. THEN you move 50m east, you've now "stretched" that distance. – John Cappelletti Jul 18 '22 at 16:24
  • Thank you. Yes that makes sense. I want to create a grid around my original lat/lng of 50m cells and perform a calculation at each intersection. – CGarden Jul 18 '22 at 16:26
  • 1
    @CGarden Got it. The math still works. You just need to be thinking of 50m cells with Top-Left and Lower-Right coordinates. – John Cappelletti Jul 18 '22 at 16:31
  • Thank you I'll give it a shot. Thank you again for all your patience/help. – CGarden Jul 18 '22 at 16:54
  • I created the grid but what I would like to do is, bring in those points that are on a diagonal from the center point, so that those points are either 50, 100, 150 or 200m away, not say 70m away. I'm sure this is quite basic but its been rattling around in my head for a few days and I can't work it out. Could you advise. Thank you. – CGarden Jul 22 '22 at 15:05
  • @CGarden I don't understand. Perhaps a visual would help. Corners can be adjacent and 50m from the NEXT cell, but as soon as you start measuring from the point of Pythagorean Theorem kicks in. – John Cappelletti Jul 22 '22 at 17:41
  • I have a table of geographic locations with monetary values. When I add a new location to that table I want to know what the worst case $ loss would be if there was a 200m blast radius. I want to move that blast radius around but always keep this new location within the blast radius. If I have a grid 400mx400m and 50m cells then when I move the blast to the outer diagonal cells the initial location I'm trying to capture loss for will not be in the radius anymore. So its how do I overcome this and move the blast around to get a good coverage of potential losses. – CGarden Jul 22 '22 at 18:52
  • @CGarden Looking for a little diversion this evening. You can drag & drop the PIN. http://chinrus.com/JustForFun.aspx – John Cappelletti Jul 23 '22 at 01:45
  • Looks phenomenal! That's really cool. – CGarden Jul 23 '22 at 09:08
  • If you picked one of those markers on the map and wanted to see the greatest exposure circle it contributed to by moving the circle around at set increments around that marker, how would you do that? How would you automate that in SQL? Thank you again. – CGarden Jul 23 '22 at 10:16
  • @CGarden I added another answer – John Cappelletti Jul 23 '22 at 19:38
1

Assume a table structure like this:

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

enter image description here

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

enter image description here

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.

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66