3 minutes to learn about MySQL space search Geohash

Brief introduction:

Mysql built-in function scheme, suitable for existing business, new add lbs function, add latitude and longitude field to avoid data migration , after 5.7.5 realize more multifunctional InnoDB space search method, the previous version is mainly for MyISAM support.

Prior to this, InnoDB stored geometry data as BLOB (binary large object) data, and only prefix indexes were created on spatial data, which was very inefficient when it came to spatial search, especially when complex geometry data was involved. In most cases, the only way to get results is to scan the table.

in the new version of MySQL,InnoDB supports spatial indexes, implemented by R-trees, making space search efficient, such as using built-in functions (Mbrwithin mbrcontains) to be very efficient. However , the current spatial index supports only two dimensions of data.

Application Scenario Description:

The needs of the company's new business, hoping to achieve the current location of the rapid display of shared information data, for the 5.7 new features of the support survey, the use of Geohash encapsulated into the built-in database functions of the simple solution, to achieve early initial business.

Implementation process:

1. Build a table

CREATE TABLE ' Tongzhou ' (

' ID ' INT (one) not NULL auto_increment,

' CNAME ' VARCHAR (255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

' Oint ' point is not NULL,

PRIMARY KEY (' id '),

SPATIAL KEY ' sp_index ' (' oint ')

) Engine=innodb auto_increment=1 DEFAULT charset=utf8mb4 collate=utf8mb4_unicode_ci

2. Construct Simple Data

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' King Hin Garden ', ' point (39.8885917679 116.6576038966) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Keisei Rose Plaza ', ' Point (39.8890214887 116.6473661241) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES ("Tongzhou District, Beijing, Beijing, China", ' Point (39.8898670523 116.6566729546) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES ("Tongzhou District, Beijing, Beijing, China", ' Point (39.8883852752 116.655728817) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Yang Zhuang Lu 22nd Yuan ', ' point (39.8984936518 116.6339063644) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Chaoyang District, Beijing, China ', ' point (39.8975388526 116.613779068) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' 31 m ', ' point (39.8873809413 116.603307724) ' of the third hospital in Chaoyang District, Beijing);

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Shuang Qiao Onsen North District ', ' Point (39.8918427053 116.6076636314) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Keikyu distribution center ', ' Point (39.8916616061 116.5908622742) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Century hotel ', ' point (39.8918591688 116.6024065018) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Double bridge sixth well plot-Bei District about 48 m ', ' point (39.8899164443 116.6050457954) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Cuiping in the community about 190 m ', ' point (39.8883194176 116.6495060921) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Keisei Rose Plaza ', ' Point (39.8894719148 116.6584646702) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' lives new life square approx. 87 m ', ' point (39.8895542353 116.65579319) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' lives new life square approx. 74 m ', ' point (39.8896242077 116.6568982601) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' New Life Plaza ', ' Point (39.8901428239 116.6564154625) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Diffuse Spring Garden ', ' point (39.8913364496 116.658115983) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' 7 Days Inn (Beijing Tongzhou Orchard Island) approx. 58 m ', ' point (39.8915998677 116.6560935974) ‘);

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Beijing Golden Pine Hotel approx. 77 m ', ' point (39.9036172391 116.6577833891) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' New South China Road community-east about 32 m ', ' point (39.9034444015 116.6588240862) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Red flag plot approx. 61 m ', ' point (39.9046707164 116.6598540545) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Xiangyun Tiandi home about 62 m ', ' point (39.9067899674 116.659129858) ');

Insert INTO ' Tongzhou ' (' cname ', ' Astext (oint) ') VALUES (' Pear Orchard MRT Station approx. 40 m ', ' point (39.8832975966 116.6687965393) ');

3. Search for location information within the current 1 km range

SET @jl = 1; How many kilometers in the range of

SET @jd = 118.1964111328;

SET @wd = 39.5591182422; Current location information

Oint is an actual existing latitude and longitude information

SELECT *,astext (oint) from Tongzhou WHERE mbrcontains (LINESTRING (Point (@wd + @jl/(111.12/cos (RADIANS (@jd))),

@jd + @jl/111.12),

Point (@wd-@jl/(111.12/cos (RADIANS (@jd))), @jd-@jl/111.12), oint)

# # #经纬弧度 (1°latitude = 111.12 kilometers) is 10/111.12, which means finding a nearby 10 km.

4. Testing

Through the Baidu map to obtain some data,

SET @jd =118.1964111328

SET @wd =39.5591182422

Value updates, analysis gets the range of attractions information

5. Questions:

If you take a series of expressions directly in where, this will result in the inability to use the spatial index:

650) this.width=650; "Src=" Https://s2.51cto.com/oss/201711/11/2fa268136801e296920360f50ace6d02.png-wh_500x0-wm_3 -wmp_4-s_2852158178.png "title=" A11.png "alt=" 2fa268136801e296920360f50ace6d02.png-wh_ "/>

Workaround:
1, the Where after the expression is processed into a variable to reference, and then the surrounding search

SET @aa = (SELECT LINESTRING (Point (39.5591182422 + 10/(111.12/cos (RADIANS (118.1964111328))),

118.1964111328 + 10/111.12),

Point (39.5591182422-10/(111.12/cos (RADIANS (118.1964111328)), 118.1964111328-10/111.12)) as Heji)

2, the implementation of the surrounding search

SELECT Astext (oint) from Tongzhou WHERE Mbrwithin (@aa, oint)

By passing the plan effect:

650) this.width=650; "Src=" Https://s3.51cto.com/oss/201711/11/052ca10b26528e25d9ca6f86d428f596.png-wh_500x0-wm_3 -wmp_4-s_39204127.png "title=" Gtid2.png "alt=" 052ca10b26528e25d9ca6f86d428f596.png-wh_ "/>

Shoot the bricks ...

This article is from the "Dbaspace" blog, make sure to keep this source http://dbaspace.blog.51cto.com/6873717/1980877

3 minutes to learn about MySQL space search Geohash