mysql中g(shù)eometry類型的簡單使用

mysql中g(shù)eometry類型的簡單使用

編寫本文的目的：

讓和兩天前的我一樣的初學(xué)者，能夠更快的使用geometry類型存儲(chǔ)空間點(diǎn)數(shù)據(jù)

也是為了自己加深印象，更熟練的使用geometry類型

建表腳本

CREATE TABLE `z_gis` ( `id` varchar(45) NOT NULL, `name` varchar(10) NOT NULL COMMENT '姓名', `gis` geometry NOT NULL COMMENT '空間位置信息', `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,8)) VIRTUAL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), SPATIAL KEY `idx_gis` (`gis`), KEY `idx_geohash` (`geohash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空間位置信息'

這里我創(chuàng)建了一張位置信息表，每個(gè)人對(duì)應(yīng)的經(jīng)緯度都會(huì)以geometry類型存在表中，geohash字段是把坐標(biāo)系分成很多小方格，然后將經(jīng)緯度轉(zhuǎn)化成字符串，其原理可自行百度，在這里就不多說了。

哦，對(duì)了，geometry類型好像不能為null，所以建表時(shí)必須為not null。

插入表數(shù)據(jù)

insert into z_gis(id,name,gis) values (replace(uuid(),'-',''),'張三',geomfromtext('point(108.9498710632 34.2588125935)')), (replace(uuid(),'-',''),'李四',geomfromtext('point(108.9465236664 34.2598766768)')), (replace(uuid(),'-',''),'王五',geomfromtext('point(108.9477252960 34.2590342786)')), (replace(uuid(),'-',''),'趙六',geomfromtext('point(108.9437770844 34.2553719653)')), (replace(uuid(),'-',''),'小七',geomfromtext('point(108.9443349838 34.2595663206)')), (replace(uuid(),'-',''),'孫八',geomfromtext('point(108.9473497868 34.2643456798)')), (replace(uuid(),'-',''),'十九',geomfromtext('point(108.9530360699 34.2599476152)'));

名字是我隨便起的，不要噴我哦，經(jīng)緯度是我在地圖上隨便取的點(diǎn)，geomfromtext()函數(shù)是將字符串格式的點(diǎn)坐標(biāo)，轉(zhuǎn)化成geometry類型，還有個(gè)字段geohash是根據(jù)gis字段的值自動(dòng)生成的，可以仔細(xì)看看建表腳本。

接下來是幾個(gè)簡單的查詢例子

1. 查詢張三的經(jīng)緯度信息

select name, astext(gis) gis from z_gis where name = '張三';

astext()函數(shù)是將geometry類型轉(zhuǎn)化為字符串

sql執(zhí)行結(jié)果

2. 修改張三的位置信息

update z_gis set gis = geomfromtext('point(108.9465236664 34.2598766768)') where name = '張三';

我用的Mysql Workbench工具，修改時(shí)報(bào)錯(cuò)如下：

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

好像是除了用id修改，其他修改都會(huì)報(bào)這個(gè)錯(cuò)，下面這樣設(shè)置一下就OK了 。

set sql_safe_updates = 0;

3. 查詢張三和李四之間的距離

select floor(st_distance_sphere( (select gis from z_gis where name= '張三'), gis )) distance from z_gis where name= '李四';

本來想格式化sql語句的，但是發(fā)現(xiàn)格式化之后的sql 基本全變成大寫的了，我覺得辨識(shí)度更低了，所有大家就這樣將就看吧，st_distance_sphere()函數(shù)是計(jì)算兩點(diǎn)之間距離的，所以傳兩個(gè)參數(shù)，都是geometry類型的，floor()函數(shù)是把計(jì)算出的距離取整。

sql執(zhí)行結(jié)果

4. 查詢距離張三500米內(nèi)的所有人

SELECT name, FLOOR(ST_DISTANCE_SPHERE((SELECT gis FROM z_gis WHERE name = '張三'), gis)) distance, astext(gis) point FROM z_gis WHERE ST_DISTANCE_SPHERE((SELECT gis FROM z_gis WHERE name = '張三'), gis) < 500 AND name != '張三';

sql執(zhí)行結(jié)果

如果表中數(shù)據(jù)非常多時(shí)，這樣查效率會(huì)非常低，這時(shí)就會(huì)用到geohash字段查詢

sql語句如下：

SELECT name, floor(ST_DISTANCE_SPHERE((SELECT gis FROM z_gis WHERE name = '張三'), gis)) distance, astext(gis) point FROM z_gis WHERE geohash like concat(left((select geohash from z_gis where name = '張三'),6),'%') AND ST_DISTANCE_SPHERE((SELECT gis FROM z_gis WHERE name = '張三'), gis) < 500 AND name != '張三';

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。