0

I want to get distance from the database entry; I've tried the below code so far

SELECT ST_Distance_Sphere( POINT(LNG1, LAT1), POINT(LNG2,LNG2) )* .000621371 ;

Though I've gotten a result, I'm not sure its accurate. I've compared my results with this website (https://www.nhc.noaa.gov/gccalc.shtml)

NOTE: I am trying to get it in miles.

SELECT ST_Distance_Sphere( POINT(-73.98568175135509, 40.7484381443023), POINT(-77.53989898824496, 38.993292463424126) )* .000621371 ; gives a result. ` 224.08790255056905`

is an example. it gives 224 while the website gave 196

anupamb
  • 37
  • 8
  • How far off is it from the website? – Barmar Jul 11 '22 at 16:43
  • Sometimes its close like 1.96 to 2mi from the website. Another example showed me 32mi to 28mi – anupamb Jul 11 '22 at 16:47
  • The web site says that the results are approximate. Although I can't imagine why it would use a different formula. – Barmar Jul 11 '22 at 16:48
  • You could implement the Haversine formula yourself. – Barmar Jul 11 '22 at 16:50
  • Please edit the question with example input and output. – Barmar Jul 11 '22 at 16:51
  • SELECT ST_Distance_Sphere( POINT(-73.98568175135509, 40.7484381443023), POINT(-77.53989898824496, 38.993292463424126) )* .000621371 ; gives a result. ` 224.08790255056905` while the website shows 194mi. If there is another website thats showing a different that would be helpful. I have applied haversine before. Im trying to be as mysql pure as possible just for this approach. – anupamb Jul 11 '22 at 17:25
  • 1
    Edit the question, not a comment. – Barmar Jul 11 '22 at 17:28
  • 1
    [this site](https://www.movable-type.co.uk/scripts/latlong.html) agrees with MySQL. 360.5 km = 224 miles – Barmar Jul 11 '22 at 17:30
  • Alright, thanks, for the site link. its coming close so far. – anupamb Jul 11 '22 at 17:36

2 Answers2

2

The website you used also reports 224 miles - it is just being nautical site, it defaults to nautical miles (n mi), and that gives you 196 n mi. Make sure you select sm (statute miles - or regular miles), you get 224 sm.

Michael Entin
  • 7,189
  • 3
  • 21
  • 26
0

ST_Distance_Sphere does employ Haversine internally. The website linked in the question was using nmi instead of mi, i.e. nautical miles instead of miles.

anupamb
  • 37
  • 8