0

I am not so into database and GIS and I have the following doubt about this function used on a MySql database to calculate the distance between 2 points.

I am started from this tutorial where there is this function used to calculate the distance between 2 point field:

CREATE FUNCTION earth_circle_distance(point1 point, point2 point) RETURNS double
    DETERMINISTIC
begin
  declare lon1, lon2 double;
  declare lat1, lat2 double;
  declare td double;
  declare d_lat double;
  declare d_lon double;
  declare a, c, R double;

  set lon1 = X(GeomFromText(AsText(point1)));
  set lon2 = X(GeomFromText(AsText(point2)));
  set lat1 = Y(GeomFromText(AsText(point1)));
  set lat2 = Y(GeomFromText(AsText(point2)));

  set d_lat = radians(lat2 - lat1);
  set d_lon = radians(lon2 - lon1);

  set lat1 = radians(lat1);
  set lat2 = radians(lat2);

  set R = 6372.8; -- in kilometers

  set a = sin(d_lat / 2.0) * sin(d_lat / 2.0) + sin(d_lon / 2.0) * sin(d_lon / 2.0) * cos(lat1) * cos(lat2);
  set c = 2 * asin(sqrt(a));

  return R * c;
end

I think that it should return a value in km as unit measurment.

Then I have used it to calculate the distance between 2 point in the space (taken from Google Maps for example Rome and Milan cities, here in Italy).

I am obtaining reasonable values. My doubt is that, then I have check the distance using the ruler tool in Gooogle Earth and I am obtaining different values.

Foe example considering 2 points that are near (2 km of distance) I obtain something like 300 m of difference between the difference calculated by Google Earth and my previous function.

Considering 2 points that are very distant (for Example Rome and Milan) I obtain something like 100 km of difference between the value calculated by Google Earth and the one calculated by the previous function.

The previous function always give the bigger value.

So I have to use it to calculate on small distance (maximum 30 km) so the error should be little and could be ok for my pourpose.

But why I obtain these difference between my funcion and the distance calculated using Google Earth?

Tom-db
  • 6,528
  • 3
  • 30
  • 44
AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • The algorithm looks fine. Which distance does your function return? It should be about 470 km – Tom-db Nov 08 '16 at 10:44
  • @TommasoDiBucchianico Do you mean from Rome (Colosseo: 41.890365 12.492359) to Milan (Duomo: 45.464090 9.191884) it returns 535.8389424301964 km and not 470 km. Why? Is it something wrong in my function? – AndreaNobili Nov 08 '16 at 10:53
  • 1
    ah, you only used the wrong coordinate order. A point in MySql (and usually in other DBMS) is defined as X,Y - also lon/lat - while in Google Map you use Y,X (lat/lon). Just revert the coordinates: Rome 12.492359, 41.890365 and Milan 9.191884,45.464090 – Tom-db Nov 08 '16 at 12:10

1 Answers1

0

SQL Server returns 534.728893656916km using this code: declare @g geography = geography::Point(9.191884,45.464090,4326) -- Latitude, Longitude, SRID declare @h geography = geography::Point(12.492359,41.890365,4326) select @g.STDistance(@h)/1000.00

AAsk
  • 1,431
  • 4
  • 17
  • 25