1

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.

For 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?

  • How many decimal places the points use? – Matte Nov 8 '16 at 10:25
  • @Matte for the function test I used the coordinates provided by Google Maps, so something like this: 41.890162, 12.492199 Google Earth I don't know how to works because I only put 2 marker and then I use the ruler tool to calculate the distance between these 2 marker on the map – AndreaNobili Nov 8 '16 at 10:28
2

Google Earth is probably calculating distance over the earth as a spheroid, taking account for the not-quite-spherical nature of the earth. Also, your earth radius is probably a smidge big, try 6371 km.

Here's the radius used in PostGIS for spherical calculations, defined as the weighted average of the major and minor axes.

#define WGS84_MAJOR_AXIS 6378137.0
#define WGS84_INVERSE_FLATTENING 298.257223563
#define WGS84_MINOR_AXIS (WGS84_MAJOR_AXIS - WGS84_MAJOR_AXIS / WGS84_INVERSE_FLATTENING)
#define WGS84_RADIUS ((2.0 * WGS84_MAJOR_AXIS + WGS84_MINOR_AXIS ) / 3.0)

That said, my comparison of the distance from Milan to Rome over sphere/spheroid in PostGIS only yielded a difference in calculation of 200m.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.