distanceMySQL
I have a database with locations with their lat and long coordinates. The area of locations is quite small with lots of them quite near each other.
I'm using this stored procedure in my project. http://www.artfulsoftware.com/infotree/queries.php?&bw=1680#109
I'm having troubles with accuracy. I need it to return more accurate results (in metres) instead of just 0 distance.
Can it be modified so I get better results?
The name of the curve you are looking to interpolate along is called either a great circle for a sphere, or a geodesic for an earth-shaped ellipsoid of revolution.
Using GeographicLib, you can create an inverse geodesic between P1 and P2, and interpolate points in between, using distances in metres. There are bindings to the most popular programming languages, including Java. You can follow this example to see how a InverseLine object is created and used interpolate points.
GeodesicLine line = geod.InverseLine(50.0, 10.0, 50.01, 9.990,
GeodesicMask.DISTANCE_IN |
GeodesicMask.LATITUDE |
GeodesicMask.LONGITUDE);
// Here is the half-way point in the middle of the arc
GeodesicData g = line.Position(line.Distance() / 2.0,
GeodesicMask.LATITUDE |
GeodesicMask.LONGITUDE);
System.out.println("Mid-point: " + g.lat2 + " " + g.lon2);
Pre-Answers
The Answers
The discussions in "Why is law of cosines more preferable than haversine when calculating distance between two latitude-longitude points?" were done in the context of the earth as a sphere. The latitudes on a sphere are geocentric latitudes.
However, ground coordinates are always geodetic coordinates on an ellipsoid. Hence, the latitudes on an ellipsoid are geodetic latitudes. This is implied, seldom mentioned in explicit, and is a source of major confusion and erroneous calculations for the general as well as seasoned GIS users.
In the above figure (Copyright Wikipedia), PAB is a geocentric latitude on an ellipsoid (60 deg), while PCB is a geodetic latitude on the same ellipsoid (66 deg). Note that geodetic latitudes are always larger than geocentric latitudes except at the Equator and at the Poles where they are equal. This Wikipedia link explains the many types of latitude.
In the above-stated URL (i.e., plain Cosine-vs-Haversine), they were discussing the merits of the plain Law of Cosines over the Haversine formula. Both are formulas for the sphere. You should never (ever) apply spherical formulas on geodetic coordinates. Period.
Meanwhile, the Vincenty formula is time-tested and proven to compute "accurate" geodesic between two geodetic coordinates on an ellipsoid.
Misc Answers
Applying spherical formulas on geodetic coordinates is the biggest and most common mistakes today. Google Maps was the biggest culprit. In this forum itself, erroneous answers can be traced back as far as six to seven years.
GeoPy deprecated Vincenty since v1.13 and will remove it in v2.0 - not because Vincenty is inaccurate, but mainly because it was superseeded by the newer Karney formulas. Vincenty will fail if two geodetic coordinates are nearly antipodal, whereas Karney doesn't have this problem. All said, it is still perfectly OK to continue to use Vincenty.
The Questioner may wish to revisit his understanding about the "accuracy" of the GIS points that represent addresses. For e.g., does a point refers to the centroid of a building footprint, or the centroid of a piece land, and etc. In real life, no building or land is ever a point on earth.
TLDR
Thus far, the observant readers may (have) noticed that my answer had been silent on the fact that it is also incorrect to apply spherical formulas on any ellipsoids. This omission is deliberate and the explanation is as below.
If the coordinates on an ellipsoid were geocentric and not geodetic - then the (spherical) Haversine formula would give outputs "nearing" but never equal the correct answer. The delta will always be some distance + some ppm. Meaning, the further the geodesic distance between the two coordinates on the ellipsoid - the larger the delta between the correct answer and Haversine's output. Some Users can accept the delta magnitude because the data points are all close to each other, or they have low horizontal precision. But this is an erroneous decision nevertheless.
But the fact remains - ground coordinates are (almost) never geocentric, but geodetic. This, plus the previous statement, together explain why Haversine appeared to give "good" result when two geodetic coordinates are "near", but give "unsatisfactory" result when "far".
Remember - the lats and longs we get are geodetic. And applying spherical formulas (like the Haversine) on geodetic coordinates is a double whamy we could all avoid.
Best Answer
If the distances are so close together that single precision floats can't capture the distance, you probably don't need to account for the curvature of the earth at all — you must be dealing with phenomena only a few meters apart. The simplest solution is to then use double precision, as Rowland mentioned, or easier yet, just compute a linear distance between the two points, ignoring earth curvature.
In the highly unlikely event that you do in fact have high-precision, high-accuracy data, you can use the Vincenty's formula, which can give accurate results down to millimeters.