Note: MySQL spatial extensions

The best Spatial Database (Spatial Database) is of course the Oracle house, but unfortunately it has never been used. The best open-source Spatial Database is generally recognized as postgis. It has been used for a while before and is especially troublesome to install. However, it provides a wide range of functions. Some time ago, I tried MySQL's spatial extensions and recorded some usage experiences below:

1. MySQL spatial extensions (hereinafter referred to as MySQL spatial) is not fully functional. At least this is similar to postgis. It only supports a subset of OpenGIS (a standard) and contains a limited number of spatial data types (such as point, linestring, and polygon ), few functions are supported. For example, the distance function for calculating two points does not exist...

2. the installation and configuration of MySQL spatial is very simple. In fact, it does not need to be installed. The default MySQL configuration can use these spatial data types. This is very different from postgis. postgis requires another extension package to be installed on PostgreSQL.

3. Different storage engines are different. Both MyISAM and InnoDB support spatial extensions, but the difference is that if MyISAM is used, you can create a spatial index, which is not supported by InnoDB. This difference is critical in some scenarios. We will discuss the spatial index in detail later.

4. Use of point. Point is the most basic and most commonly used spatial data type. MySQL spatial uses point to represent a vertex. For example, you can create a table:

Create Table address (

Address char (80) not null,

Address_loc point not null,

Primary Key (address ),

Spatial key (address_loc)

);

Address_loc is a point type, indicating that address_loc is a point.

Insert a vertex:

Insert into address values ('foobar street 12', geomfromtext ('point (2671 2500 )'));

Read a vertex:

Select astext (address_loc) FROM address...

A troublesome question is, how do we calculate the distance between two points? As mentioned before, MySQL spatial does not provide the distance function. The practice of the official guide is as follows:

Glength (linestringfromwkb (linestring (point1, point2 )))

This statement generates a linestring type with two points, and then calls glength to obtain the line length.

This is also true.

Yes because it does calculate the distance, but this method calculates the distance of the European space. Or simply, it calculates the linear distance. If the two points are geographical coordinates, such as point (116.34, 39.28), and you want to calculate the distance between the geographical locations, this is definitely not the case. The correct method should be to use a formula dedicated to geographic location calculation.

 

5. Use of MySQL spatial index. Use the following statement:
Alter table address add spatial index (address_loc );

You can create a spatial index on the spatial data type. This function is only supported by MyISAM. The essence of index is actually a R-TREE, which is also the most commonly used as a multi-dimensional data index data structure.

So, how to use this index?

For example, if you want to find all vertices in a rectangle, you can use the following method:
Select * From address where (x (address_loc)> 116.3952) and (x (address_loc) <116.4052) and (y (address_loc)> 39.8603) and (y (address_loc) <1, 39.8703 );

Suppose we have created the spatial index on the column address_loc, so the above query should be very fast. Unfortunately, this is not a fact. The preceding query scans all the data in the table and performs calculations one by one. The created index does not work at all.

The correct method is to use some built-in spatial-related functions in the query. Only these functions can effectively use the index. For example, the correct query should be:
Select astext (address_loc) FROM address where mbrcontains (geomfromtext (polygon (115.3073 40.3821, 115.3173 40.3821, 115.3173 40.4021, 115.3073 40.4021, 115.3073 40.3821), address_loc );

The mbrcontains function is used to determine whether a point is in the specified polygon. This function can use the previously created spatial index. You can perform a test to compare the processing time of the previous two queries. You will find that the latter is much faster.

 

In general, if you only need to do some simple GIS or lbs applications, the spatial extensions provided by MySQL can meet the requirements. However, if the required functions are more complex, the functions provided by MySQL spatial extensions may not be enough. You need to implement more logic on MySQL or change to postgis.

 

Reference:

[1] official documents: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

[2] official documents: http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html