MySQL spatial database-the shortest path between Query points and multiple points

When SNS are added to the LBS technology, it will make the mobile Internet field more colorful, such as public comments, nearby, and Shanda jieke applications running on smartphones, when you pull out your mobile phone, you can recommend some useful information to you based on your current location, such as nearby food, shops, and surrounding life information. Are you an attacker?

When SNS are added to the LBS technology, it will make the mobile Internet field more colorful, such as public comments, nearby, and Shanda jieke applications running on smartphones, when you pull out your mobile phone, you can recommend some useful information to you based on your current location, such as nearby food, shops, and surrounding life information. Are you an attacker?

When SNS are added to the LBS technology, it will make the mobile Internet field more colorful, such as public comments, nearby, and Shanda jieke applications running on smartphones, when you pull out your mobile phone, you can recommend some useful information to you based on your current location, such as nearby food, shops, and surrounding life information.

Have you ever thought about the technical implementation behind these applications? After obtaining the current coordinates, how does one calculate and query the returned attachment results?

The Java program can implement the Dijkstra algorithm to obtain the calculation result of the shortest path between points and multiple points, but I personally think it is a brute force method, development simplification and computing execution efficiency are not very high.
References: http://baike.baidu.com/view/7839.htm

Next, I thought about the necessity of using the database technology, but I would not store the coordinates of nodes in common fields of the database for query, compared with the Dijkstra algorithm, the workload will not be simplified or the performance will be improved. However, the concept of using the spatial database in MySQL will be much simplified and the performance will be improved, the open-source MySQL Spatial index mechanism can calculate the distance between points and multiple points. Similar Spatial databases include PostGIS and SpatiaLite.

My nonsense:
After obtaining the current coordinates on the android phone, you can also obtain the Shortest Path of the current point to multiple points after entering the data into the SQLite database in android, that is to say, the Database on the android mobile phone can be used to complete this task without updating the geographic data. It is not necessary to use the Spatial Database on the server to complete the shortest path calculation.

MySQL spatial data types:
-GEOMETRY Geometry is the root class of the hierarchy. It is a non-instantiated class, but has many attributes that are common to all geometric values created by any ry subclass.
-POINT represents the ry of a single position in the coordinate space. Its attributes include X-coordinate values and Y-coordinate values.
-LINESTRING has the coordinate of a line segment, which is defined by each consecutive point pair (two points. If only two points are contained, LineString is Line. If it is both simple and closed, LineString is LinearRing.
-POLYGON is defined by a single external boundary and zero or multiple internal boundary. Each internal boundary is defined as one hole in Polygon. For example, a Polygon object can represent a forest on a region map.
-MULTIPOINT MultiPoint is a collection of geometric objects composed of Point elements. These points are not connected or sorted in any way.
-MULTILINESTRING MultiLineString is a set of MultiCurve geometric objects composed of LineString elements, such as river systems or highway systems.
-MULTIPOLYGON MultiPolygon is a collection of geometric objects composed of Polygon elements. On a region map, MultiPolygon can represent a lake system.
-GEOMETRYCOLLECTION is a geometric object composed of one or more geometric objects of any class. All elements in GeometryCollection must have the same spatial reference system (that is, the same coordinate system ).
The following types of dependency ,:

After learning about the basic knowledge above, we will create a merchant table and contain the POINT field of the defined spatial database:
Create table shop (
Shop_id int (3) primary key,
Location POINT,
Shop_na vachar (100 ),
Shop_info vachar (300)
);

Insert the store information of several sellers. The GeomFromText method is used to insert the coordinate database into the POINT field. For example:
Insert into shop values ('xxx', ', GeomFromText ('point (1)'), 'xx store', 'other information ');
The following queries the customer's current location in MySQL to search for a certain range of stores near the current location, and can be displayed in ascending order of distance, find the store closest to the user.
Set the current location of the customer to a variable, for example: set @ center = GeomFromText ('point (10 10 )');

Then, you can narrow down the search range and set the search condition to find the nearest store.
Example: set @ radius = 30;
Where sqrt (POW (ABS (X (location)-X (@ center), 2) + POW (ABS (Y (location)-Y (@ center )), 2) <@ radius

A complete SQL example is provided for the latest store search:
SELECT shop_id, shop_na, SQRT (POW (ABS (X (Location)-X (@ center), 2) + POW (ABS (Y (Location) -Y (@ center), 2) AS distance
FROM shop where sqrt (POW (ABS (X (location)-X (@ center), 2) + POW (ABS (Y (location)-Y (@ center )), 2) <@ radius
Order by distance;

The involved mathematical function SQRT (x): returns the square root of a number x. POW (x, y): contains two parameters representing the Power y of x. ABS (x): returns the absolute value of number X. The whole SQRT (POW (ABS (X (Location)-X (@ center), 2) + POW (ABS (Y (Location)-Y (@ center )), 2) This SQL statement implements an arithmetic expression.

That is, the linear distance between two points.
For example, if there are two coordinate points A (x1, y1) and B (x2, y2), the AB length of the Line Segment must be calculated using this formula. Think of A as the current location B as A store, isn't it equivalent to calculating the distance from the current location to the two points of the store. When the coordinate point is introduced, it can be expressed by using a function.
So these three functions are used:
SQRT (x): returns the square root of a number x. It is equivalent to the root number. √ X
POW (x, y): contains two parameters representing the Power y of x
For example, pow (2, 3) indicates 23, then POW (X1-X2), 2) is equivalent
ABS (x): returns the absolute value of number X. | X | ABS (x1-x2) is equal to | x1-x2 |.

Just combine the formula.
The whole SQRT (POW (ABS (X (Location)-X (@ center), 2) + POW (ABS (Y (Location)-Y (@ center )), 2) This sentence is used to represent this formula.
,
The calculated value is the linear distance between two points.

References:
Http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html
Http://en.wikipedia.org/wiki/Spatial_database

Saliva:
The above part of the content from the NJ-AMT intern Yu Shan analysis report.

-End-