Calculating short distances between lat/long points

Question

I have a MySQL table with Spatial Points, and need to calculate distances. I found lots of material on doing this using the Haversine formula, however all of these assume a large distance between points. In my case, I only care about short distances (< 1 mile) so I don't need to correct for the earth's curvature. My intuition is using the Haversine formula will be inaccurate at such small distances. Any suggestions?

Answer 1

Your intuition is incorrect. Consider the haversine formula, and the definition of haversine, according to Wikipedia (φ is latitude and ψ is longitude):

There is a further fact that is relevant: for small values of θ, sin θ is approximately equal to θ; more relevantly, it is approximately linear in θ. Therefore, haversin θ will be approximately (θ/2)². This approximation gets better as θ approaches zero.

If the latitude and longitude are close together, then φ₂ - φ₁ and ψ₂ - ψ₁, which are what the haversine function is applied to here, will be close to zero, meaning that the formula is approximately

(d/2r)² = ((φ₂ - φ₁) / 2)² + cos(φ₁) cos(φ₂) ((ψ₂ - ψ₁) / 2)²

Now note that this formula has the same form as Euclidean distance in two dimensions with some arbitrary scaling factors (remembering that (kx)² = k² x² so we can move constants in and out of the squares):

k₁ d² = k₂ ∆φ² + k₃ ∆ψ²

Lastly, I assert without proof that those arbitrary scaling factors turn out to be the same ones which convert changes in latitude/longitude to linear distance.

Therefore, the haversine formula does not become inaccurate for small distances; it is precisely the same as an ordinary Euclidean distance calculation, in the limit of small distances.

Answer 2

1. Create your points using Point values of Geometry datatypes in MyISAM table

2. Create a SPATIAL index on these points

Use MBRContains() to find the values:

SELECT  *
FROM    table
WHERE   MBRContains(LineFromText(CONCAT(
        '('
        , @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
        , ' '
        , @lat + 10 / 111.1
        , ','
        , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
        , ' '
        , @lat - 10 / 111.1 
        , ')' )
        ,mypoint)

, or, in MySQL 5.1 and above:

SELECT  *
FROM    table
WHERE   MBRContains
                (
                LineString
                        (
                        Point
                                (
                                @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                @lat + 10 / 111.1
                                ) 
                        Point
                                (
                                @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                @lat - 10 / 111.1
                                ) 
                        ),
                mypoint
                )

This will select all points approximately within the box (@lat +/- 10 km, @lon +/- 10km).

This actually is not a box, but a spherical rectangle: latitude and longitude bound segment of the sphere. This may differ from a plain rectangle on the Franz Joseph Land, but quite close to it on most inhabited places.

Apply additional filtering to select everything inside the circle (not the square)

Possibly apply additional fine filtering to account for the big circle distance (for large distances)

here following solution to click