Geographical distance sorting, range searching, and geographical distance sorting

Geographical distance sorting, range searching, and geographical distance sorting

Reference: Geospatial Distance Computing Optimization

First, this article describes two geographic models: sphere and elliptical. Generally, websites do not require much precision. For website users, you only need to get an approximate result. Therefore, the following content will be described using a sphere.

Find nearby points, that is, sort the distance between the points in the database and the current location.

1. On A sphere, calculate the distance between any two points A and B, which is equivalent to an arc length. The formula is:

Note: The circumference is times of the arc length.

As long as the angle θ is obtained, the arc length is obtained, that is, the distance is obtained.

2. Then, you need to convert the longitude and latitude of a point to the spatial coordinates of r = 1. The formula is:

X = r * cos B * cos a = cos B * cos

Y = r * cos B * sin a = cos B * sin

Z = r * sin B = sin B

Note: the longitude and latitude mean that the degree of first rotating around the X axis is the latitude value, and then the degree of rotation around the Z axis is the longitude value.

The spatial coordinates of A and B are calculated as (x1, y1, z1) and (x2, y2, z2) respectively ).

3. then calculate the angle between the two vectors of the space. The formula is:

Cos θ = (OA * OB)/(| OA | * | OB |) = (x1 * x2 + y1 * y2 + z1 * z2)/(r * r) = x1 * x2 + y1 * y2 + z1 * z2

Finally, use arccos to obtain the angle θ. Then the arc lengths of A and B are obtained by distance.

4. However, do you really need to find the distance between the two points for sorting the required distance?

Suppose θ 1 <θ 2, then θ 1 * π * r/180 <θ 2 * π * r/180, that is, d1 <d2.

Note: Here r is the earth's radius, not 1.

Obviously, distance is not needed, and angle sorting is used to replace distance sorting.

5. Do I need to find the vector angle represented by two points?

Suppose cos θ 1 <cos θ 2, then arccos (cos θ 1)> arccos (cos θ 2), that is, θ 1> θ 2.

Note: The monotonicity of the Function y = arccos x, xε [0, π] is a subtraction function, which is slightly proved.

The angle does not need to be obtained. cos θ is used to replace the angle sorting and distance sorting.

6. Search for a range, such as within 5 kilometers.

In the preceding formula, cos θ sorting is used to replace distance sorting. Therefore, the range cannot be calculated for exclusion.

Due to the sorting, you can use the binary calculation, comparison, and exclusion. However, this is a question about how SQL implements the binary calculation.

Note: You may choose not to make statistics, that is, there is no specific page, only the next page, and no total number is displayed. In this way, each read ordered data is compared at the business layer and then excluded.

With a limited range of values, you can perform pruning before sorting to reduce the number of computations and comparisons during sorting.

The method for pruning involves rectangular elimination. Currently, I have also studied whether there is a more efficient method for pruning. One Direction is to add.

However, this method only studies on the plane. The idea is as follows: the relationship between x + y and | OA | is between any point A (x, y) on the plane.

We know that x = | OA | * cos a, y = | OA | * sin.

Then x + y = | OA | * sina + | OA | * cos a = | OA | * √ 2 * sin (a-45 ).

As shown in the formula, when a is 45 degrees, the maximum value of sin (a-45) is 1, and the maximum value of x + y is | OA | * √ 2.

If the search range is 10, the maximum value of x + y is 10 * √ 2, and x + y <= 10 * √ 2 is used for the front branch, exclude vertices that do not conform to the inequality.

Whether this hypothetical pruning method is suitable for geographic range search is still being studied.

 

At this point, I don't know if my thinking is correct, because this article "geospatial Distance Computing optimization" does not cover it. I hope someone can help me with the details, I found a lot of information and did not find it.

This article only describes how to optimize the formula for calculating the distance.

Optimization 1:

For the formula cos θ = x1 * x2 + y1 * y2 + z1 * z2, to calculate the angle θ, calculate cos θ. Because cos θ is time-consuming, the article provides an optimization solution.

We know that sin θ ^ 2 + cos θ ^ 2 = 1, then the formula becomes sin θ = √ (1-(x1 * x2 + y1 * y2 + z1 * z2 )).

Furthermore, this optimization scheme applies to sin θ ≈ θ, I .e. √ (1-(x1 * x2 + y1 * y2 + z1 * z2) when the θ angle is very small )) ≈ θ.

Optimization 2:

This optimization scheme is also suitable for replacing the spherical arc length formula with the plane distance formula when the angle θ is very small.

Optimization 3:

Use Polynomials to fit cos trigonometric functions to eliminate cos trigonometric functions.

 

Other methods of geographic distance sorting include geohash.

This method generates an address string for the province, city, and village, just like our residential address.

Related Articles: how to sort by distance and search by range, and analysis of GeoHash Core Principles