MySQL Space Distance

When the SNS products join LBS technology will make the mobile Internet area more colorful, such as: public reviews, street, Grand cut off these applications run on the smartphone side, when the user took out the phone can be based on your current location to recommend some useful information, such as: nearby food, shops, surrounding life information, such as

Have you ever thought about the technical implementation behind these applications, siege planners? How do I calculate and query the results of an attachment after I get the current coordinates on the phone?

Java program can achieve the Dijkstra algorithm to obtain the shortest path between points and multipoint calculation results, but I personally think is a violent approach, the development of the simplification and calculation of the efficiency of the implementation will not be very high.
Reference: http://baike.baidu.com/view/7839.htm

Then think about it, the use of database technology is inevitable, but not the coordinates of the node stored in the database to query the ordinary field, if compared with the Dijkstra algorithm will not simplify the workload and will not improve performance, but the use of the concept of spatial database in MySQL will be simplified a lot will also be improved performance, The open source MySQL spatial indexing mechanism can calculate the distance from point to multipoint, similar to spatial database, Postgis,spatialite.

My crap:
After obtaining the current coordinates on the Android phone, the data can be entered into the SQLite database of Android and the shortest path of the current point-to-multipoint is obtained, which means that the database of the Android phone can be used to complete the work in the situation where the geographic data is not updated. It is not necessary to use the server-side spatial database to complete the shortest path calculation.

MySQL spatial data several main types:
–geometry GEOMETRY is the root class of the hierarchy. It is a non-instantiated class, but has many properties that are common to all geometry values created by any geometry subclass.
–point represents a geometric class for a single position in the coordinate space, and his properties contain x-coordinate values, y-coordinate values.
The –linestring has the coordinates of the segment, defined by each successive point pair (two points). If only two points are included, linestring is line. If it is both simple and closed, linestring is linearring.
–polygon It is defined by a single outer boundary and 0 or more internal boundaries, where each inner boundary is defined as 1 holes in POLYGON. For example: on a regional map, polygon objects can represent forests.
–multipoint MULTIPOINT is a collection of geometric objects that are composed of point elements. These points are not connected or sorted in any way.
–multilinestring Multilinestring is a collection of Multicurve geometric objects composed of linestring elements, such as river systems or high-speed road systems.
–multipolygon Multipolygon is a collection of geometric objects composed of polygon elements. On the regional map, Multipolygon can represent the lake system.
–geometrycollection He is a geometric object composed of one or more arbitrary class geometry objects. All elements in the geometrycollection must have the same spatial reference system (that is, the same coordinate system).
The above types of dependency relationships:

  CREATE TABLE shop (
     shop_id Int ( 3) Primary key,
     location point,
     shop_na Vachar (+),
     shop_info Vachar (+)
    );

Insert store information for several merchants, which use the Geomfromtext method to insert the database of coordinates into the point field, for example:
INSERT into shop values (' XXX ', ', Geomfromtext (' point (1 1) '), ' xx store ', ' other information ');
The following will be based on the customer's current location in the MySQL query, search for a certain range near the current location of the store, and can do by distance from the near to the distance display, from the user to find the nearest store.
The current location of the customer can be set as a variable, for example: Set @center =geomfromtext (' point (10 10) ');

To find the nearest store, you can narrow the search radius, add search criteria
Example: Set @radius = 30;
WHERE SQRT (POW (ABS (location) –x (@center)), 2) + POW (ABS (location) –y (@center)), 2) < @radius

Recent store search, complete SQL example:
SELECT shop_id,shop_na, SQRT (Pow (X (location) –x (@center)), 2) + POW (ABS (Y (location) –y (@cent ER)), 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;

that is a line between two points Distance.
For example, now there are two point coordinates a (x1,y1), B (X2,y2) requires that the line segment AB length is calculated using this formula. Think of a as the current position B as a store, is not equivalent to calculate the current location to store the distance between the two points. Coordinate points have to be carried in the line, equal to now as long as the function can be used to express this formula can be. The
uses these three functions:
SQRT (x): Represents the square root of a number x. is equivalent to that radical. √x
Pow (x, y): contains two parameters to calculate the y power of x
For example POW (2,3) represents 23, then POW ((X1-X2), 2) is equivalent to (x1-x2) 〗^2
ABS (x): Represents the absolute value of the X. |x|  ABS (x1-x2) is equal to |x1-x2|.

That's a combination of that formula.
The whole sqrt (POW (ABS (location) –x (@center)), 2) + POW (ABS (Y (location) –y (@center)), 2) This sentence is used to denote this formula
,
The calculated value of this formula is the straight-line distance between two points.

Resources:
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 is from Nj-amt intern Yushan Analysis report.

–end–

MySQL Space Distance