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

I don't know if you have any understanding of similar articles on how to search GeoHash in Mysql space. Today, I'm here to tell you a little bit about it. If you are interested, let's take a look at the text. I believe you will get something after reading how to search GeoHash in Mysql space.

Application scenario introduction:

In order to meet the needs of the company's new business, we hope to be able to quickly display shared information data in the current location. According to the support survey of 5.7new features, we use GeoHash to encapsulate a simple scheme of built-in database functions to realize the initial business.

Implementation process:

1. Build the table

CREATE TABLE `roomzhou` (

`id`INT (11) NOT NULL AUTO_INCREMENT

`cname` VARCHAR (255) COLLATE utf8mb4_unicode_ci DEFAULT NULL

`oint` POINT 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 `Jingzhou` (`cname`, oint) values ('Jingxin Garden', GEOMFROMTEXT ('POINT (39.8885917679 116.6576038966)'))

Insert into `roomzhou` (`cname`, oint) values ('Jingtong Roosevelt Square', GEOMFROMTEXT ('POINT (39.8890214887 116.6473661241)'))

Insert into `roomzhou` (`cname`, oint) values ('Tongzhou District, Beijing, China', GEOMFROMTEXT ('POINT (39.8898670523 116.6566729546)'))

Insert into `roomzhou` (`cname`, oint) values ('Tongzhou District, Beijing, China', GEOMFROMTEXT ('POINT (39.8883852752 116.655728817)'))

Insert into `roomzhou` (`cname`, oint) values (22 Yangzhuang Road Hospital, GEOMFROMTEXT ('POINT (39.8984936518 116.6339063644)'))

Insert into `roomzhou` (`cname`, oint) values ('Chaoyang District, Beijing, Beijing, China', GEOMFROMTEXT ('POINT (39.8975388526 116.613779068)'))

Insert into `roomzhou` (`cname`, oint) values ('Beijing Chaoyang District third Hospital about 31 meters', GEOMFROMTEXT ('POINT (39.8873809413 116.603307724)'))

Insert into `Qingzhou` (`cname`, oint) values ('Shuangqiao Hot Spring Beili Community', GEOMFROMTEXT ('POINT (39.8918427053 116.6076636314)'))

Insert into `Jingzhou` (`cname`, oint) values ('Jingkelong Distribution Center', GEOMFROMTEXT ('POINT (39.8916616061 116.5908622742)'))

Insert into `roomzhou` (`cname`, oint) values ('Century Hotel', GEOMFROMTEXT ('POINT (39.8918591688 116.6024065018)'))

Insert into `Qingzhou` (`cname`, oint) values ('Shuangqiao No.6 well District-North District about 48 meters', GEOMFROMTEXT ('POINT (39.8899164443 116.6050457954)'))

Insert into `Qingzhou` (`cname`, oint) values ('Cuipingli District is about 190m', GEOMFROMTEXT ('POINT (39.8883194176 116.6495060921)'))

Insert into `roomzhou` (`cname`, oint) values ('Jingtong Roosevelt Square', GEOMFROMTEXT ('POINT (39.8894719148 116.6584646702)'))

Insert into `roomzhou` (`cname`, oint) values ('life New Life Plaza is about 87 meters', GEOMFROMTEXT ('POINT (39.8895542353 116.65579319)'))

Insert into `roomzhou` (`cname`, oint) values ('life New Life Plaza is about 74 meters', GEOMFROMTEXT ('POINT (39.8896242077 116.6568982601)'))

Insert into `roomzhou` (`cname`, oint) values ('life New Life Square', GEOMFROMTEXT ('POINT (39.8901428239 116.6564154625)'))

Insert into `roomzhou` (`cname`, oint) values ('Spring Garden', GEOMFROMTEXT ('POINT (39.8913364496 116.658115983)'))

Insert into `roomzhou` (`cname`, oint) values ('7-day chain hotel (Beijing Tongzhou Orchard Huandao Store) about 58 meters', GEOMFROMTEXT ('POINT (39.8915998677 116.6560935974)')

Insert into `roomzhou` (`cname`, oint) values ('Beijing Jinsong Hotel is about 77 meters', GEOMFROMTEXT ('POINT (39.9036172391 116.6577833891)'))

Insert into `Zhizhou` (`cname`, oint) values ('Xinhua South Road District-East District is about 32 meters', GEOMFROMTEXT ('POINT (39.9034444015 116.6588240862)'))

Insert into `roomzhou` (`cname`, oint) values ('Red Flag District is about 61 meters', GEOMFROMTEXT ('POINT (39.9046707164 116.6598540545)'))

Insert into `roomzhou` (`cname`, oint) values ('Xiangyun Heaven and Earth Home is about 62 meters', GEOMFROMTEXT ('POINT (39.9067899674 116.659129858)'))

Insert into `Zhizhou` (`cname`, oint) values ('about 40 meters from Liyuan MTR Station', GEOMFROMTEXT ('POINT (39.8832975966 116.6687965393)'))

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

SET @ jl=1; / / within how many kilometers

SET @ jd=118.1964111328

SET @ wd=39.5591182422; / / current location information

Oint is the latitude and longitude information that already exists.

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)

# Longitude and latitude Radian (1 °latitude = 111.12 kilometers), that is, 10 + 111.12, indicates that the search area is 10 km.

4. Test

Get some data through Baidu map, right

SET @ jd=118.1964111328

SET @ wd=39.5591182422

Update the value, analyze and obtain the scenic spot information within the range

5. Question:

If you take a series of expressions directly in WHERE, this will result in the inability to use spatial indexes, as shown in the figure:

Solution:

1. Process the expression after WHERE as a variable for reference, and then search around

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. Perform perimeter search

SELECT ASTEXT oint) FROM tongzhou WHERE MBRWITHIN (@ aa,oint)

Implement the effect of the plan by the way:

Other SQL writing methods add:

Indicates a point within a range of 3 kilometers:

DDL:

CREATE TABLE `shop_ id` (

`id`int (11) NOT NULL AUTO_INCREMENT

`lat` double DEFAULT NULL

`lng` double DEFAULT NULL

`oint` point NOT NULL

PRIMARY KEY (`id`)

SPATIAL KEY `sp_ index` (`oint`)

) ENGINE=InnoDB AUTO_INCREMENT=275915 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

SELECT id, ST_Distance_Sphere (POINT (- 73.951368, 40.716743), oint) AS dist, ST_AsText (oint) FROM shop_id

WHERE ST_Contains (ST_MakeEnvelope (POINT (- 73.951368 + (3max 111)), (40.716743 + (3max 111)

POINT (- 73.951368-(3max 111.12)), (40.716743-(3max 111.12), oint)

After reading the article on how to search GeoHash in Mysql space, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.