Oracle stored procedure description for determining whether a point falls on the surface

Oracle stored procedure description for determining whether a point falls on the surface

1. Background

In the leting project, our engineering colleagues proposed that they should be able to determine whether the point is located in the leting administrative district in real time when the GPS reporting point is connected to the vehicle. If not, send an alarm.

There are two solutions: one is to pull the GPS point stored in the database from the front end in real time, and then determine whether the point falls into the region. Second, when the GPS data is connected to the GPS manufacturer in real time and stored in the corresponding table of our database, the system determines whether the point falls into the region. If not, then, it is marked in the judgment point-plane relationship field of the corresponding table.

Considering the efficiency and record storage, select the second solution, that is, operate on the database layer and save the judgment record method.

2. Algorithm for determining the relationship between points and planes

2.1 algorithm selection

The following algorithms are used to determine the relationship between points and planes:

A difference multiplication discriminant method (for Convex Polygon only)

B. Area discriminant method (for Convex Polygon only)

C. Angle and discriminant (Any Polygon can be used)

In order to make the stored procedure more universal in the future, the angle of use and the discriminant method are selected to better meet the requirements.

2.2 Principles of angle and discriminant Methods

Principle: P = {p1, p2 ,..., Pn, p1} is a vertex pi (xi, yi), I = 1, 2 ,..., N is a test point. PtPi is the vector connecting pt and pi. α I represents the angle from PtPi to PtPi + 1.

If Σ α I = 0 Pt is outside P;

If Σ α I = ± 2 π Pt is in P.

3. implement this algorithm using Stored Procedures

3.1 single angle acquisition (angle α I Algorithm)

3.2 sum of angles

3. Algorithm Optimization

The above angle and algorithm are relatively time-consuming. If we use the simplest method to filter and judge the relationship between points and planes, turning only the points that meet the requirements into the angle and algorithm judgment will be of greater help to the efficiency prompt.

Here, you can use the four-angle coordinates of the range to filter the points when the points are obtained.

4. algorithm Testing

Because GPS has a certain error range, we have extended the boundary of liting. Then, the extended range is converted into a json dot string in ring format. Select multiple points for testing.

Repeated tests of multiple samples meet the requirements.