Shulou(Shulou.com)06/01 Report--

MySQL engine feature GIS-R-TREE

Geohash is used to encode the latitude and longitude of the location into a string, which supports WGS 84 Coordinate System

The spatial data types supported by MySQL include GEOMETRY,POINT,LINESTRING,POLYGON. GEOMETRY can represent any type of space, while the others require fixed and valid storage formats such as (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION).

Two kinds of standard insert data:

1. WTK text format, which is directly inserted with text when inserting data, such as point (1Magne2) and LINESTRING (0.0,1010,2025,5060). It is internally converted to a specific format and converted to the result format of WKT through ASTEXT ().

2. The WKB standard data is inserted and converted by the function GeomFromWKB. For example, point (1 / 2) contains 21 bytes. When the data is retrieved from the table, the result is converted into WKB by ASBINARY ().

# the storage used in the INNODB storage engine is BLOB type, and the stored data format is WKB format, plus 4 bytes of SRID (Spatial Reference Identifier. Spatial relation marking)

MBR introduction

R-TREE is specially used to represent spatial data types, and the stored record type is the rectangle with the smallest boundary that the spatial data can represent, referred to as MBR (minimum border rectangle minimum bounding rectangle).

MBR records the smallest rectangle that can surround these spaces. For horizontal or vertical lines, MBR actually records straight lines, while POINT MBR represents a rectangle reduced to a point.

Some features of R-TREE:

1. Leaf node records contain MBR and clustered index records pointed to. Non-leaf node records contain pointers to leaf nodes and MBR corresponding to leaf node records. Currently, only two-dimensional data indexes are supported.

2. Columns with key values of SPATIAL INDEX must be displayed as NOT NULL, and only columns with one spatial data type are supported.

3. R-TREE uses a completely different data retrieval method from B-TREE. In the retrieval process, SSN (SPLIT SEQ NUM) is mainly used to determine whether any data pages have been split.

4. What is stored in the RTREE is not the real data, but the MBR based on the data of the key-value column, so before inserting the data, the MBR needs to be calculated according to the passed WKB format, so the real geographic data must be obtained through the clustered index.

5. Currently, the data change operation of cached R-TREE and adaptive hashing are not supported, and NEXT-KEY can not be used to prevent phantom reading, so Predicate Lock is introduced to prevent phantom reading.

Analysis and calculation functions on spatial data types (all information can be obtained from official documents):

1. ST_GEOMFROMTEXT is used to convert spatial data from readable text types to internally stored binary types.

2. ST_ASTEXT converts spatial data into readable text types

3. The user of the POINT (arg1,arg2) function represents the location of a point in the geographical space. Arg1 is longitude and arg2 is dimension.

4. ST_DISTANCE_SPHERE: calculate the spherical distance between two locations in meters

5. MBRWITHIN: this function is used to find out the destination around you. The meaning of MBRWITHIN: return 1 if G1 is within the range of G2, otherwise return 0.

6. MBRCONTAINS: indicates whether a point is included in a multi-deformation or within a certain distance

7. MBRINTERSECTS: the user judges whether the two space types overlap or intersect

8. MBRDISJOINT: determine whether to intersect. If not, return 1.

9. MBROVERLAPS: returns 1 when G1 and G2 overlap. Overlap means that the intersection of two space types produces geometry of the same dimension, but not equal to G1 or G2

10. MBREQUAL: whether it is equal

11. MBRTOUCHES: returns 1 if there is coincidence but does not intersect, for example, a point returns 1 on a straight line

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.