MYSQL 儲存引擎 gis空間匹配以及HotDB-MySQL分散式資料庫

由於之前沒有玩過gis，本文以小白視角開始學習，並大致設計並記錄一點空間函式。關於空間資料型別的分析計算函式，可以從官方文件上獲得全部資訊，小編在這裡就不做主要說明了，小編在這裡會對核心內容進行整理。有興趣的小盆友新增java高階244930845

  小編在這裡對空間欄位提供三種插入方法：

-- 插入資料 begin

   -- 方式一
       insert into T_GIS_ORG_MAP (ID,COORDINATES)
      values ('5f975235-917b-43d0-8fd5-c7816609e709',POLYGONFROMTEXT('POLYGON((116.50996 39.950466,116.514203                                   39.948422,116.511928 39.94757,116.508742 39.948315,116.506097 39.949902,116.50996 39.950466))'));

       insert into T_GIS_ORG_MAP (ID,COORDINATES)
     values ('8a1c6a3b-1301-4133-947f-84290aac8d7d',POLYGONFROMTEXT('POLYGON((116.453089 40.030805,116.465728              40.03023,116.467015 40.024348,116.465814 40.019008,116.461157 40.017676,116.453175 40.01863,116.445536    40.025695,116.448133 40.029918,116.453089 40.030805))'));


     insert into T_GIS_ORG_MAP (ID,COORDINATES)
    values ('c7a141ca-1889-4586-af2c-0df08f7d9274',POLYGONFROMTEXT('POLYGON((116.565432 39.99241,116.577728    39.993018,116.573136 39.983368,116.564918 39.983614,116.559145 39.991572,116.564681 39.994712,116.565432    39.99241))'));


    -- 方式二
    insert into  T_GIS_ORG_MAP (ID,COORDINATES)
    values  ('22a141ca-1889-4586-af2c-0df08f7d9274',ST_GeomFromText('POLYGON((116.565432 39.99241,116.577728          39.993018,116.573136 39.983368,116.564918 39.983614,116.559145 39.991572,116.564681 39.994712,116.565432   39.99241))'));


  -- 方式三
    insert into T_GIS_ORG_MAP (ID,COORDINATES)
   values ('22a141ca-1889-4586-af2c-0df08f7d9274',GeomFromText('POLYGON((116.565432 39.99241,116.577728   39.993018,116.573136 39.983368,116.564918 39.983614,116.559145 39.991572,116.564681 39.994712,116.565432     39.99241))',0));


-- 插入資料 end   如下圖

ST_CONTAINS是ST_WITHIN的反向操作，表示某個多邊形內是否包含某個點。

SET @g1 = Point(116.368904, 39.923423);
SET @g2 = Point(116.387271, 39.922501);
SELECT st_distance_sphere(@g1, @g2) ;

 SELECT  *
 ST_Distance_Sphere ( ST_GeomFromText (CONCAT('POINT(POINT(116.569613 39.989529))')),
 ST_GeomFromText (CONCAT('POINT(POINT(116.569613 39.989529))'))) / 1000 AS distance,
ST_AsGeoJSON (COORDINATES, 4) AS COORDINATES,
 ST_DISTANCE (COORDINATES,ST_GeomFromText (CONCAT('POINT(116.569613 39.989529)')'))) AS includeDistance
   FROM  表

-- 查詢點在這個片裡面小編在這裡也提供幾種方法便於大家在一起交流學習  
select * from T_GIS_ORG_MAP where ST_CONTAINS(COORDINATES,ST_GeomFromText('POINT(116.569613 39.989529)'));


-- 查詢點與多邊形的距離,st_distance 單位為公里，0說明在多邊形範圍內  不等於0說明不在片內，過濾條件小編在這裡推薦java8新特性分別區別，簡單函式，小編在這裡不便多說，大家可以自己查文件。空間欄位對應javaType小編認為String即可滿足。

list.stream().filter(entity ->
   entity.getIncludeDistance().equals("0")).collect(Collectors.toList());

或者

list.stream().filter(entity ->
   ！entity.getIncludeDistance().equals("0")).collect(Collectors.toList());

select ST_DISTANCE(COORDINATES,ST_GeomFromText('POINT(116.569613 39.989529)')) from T_GIS_ORG_MAP;


-- 查詢離點最近的3個網點範圍
select t.*,ST_DISTANCE(COORDINATES,ST_GeomFromText('POINT(116.569613 39.989529)')) as distance from T_GIS_ORG_MAP_OPT_1 t order by distance limit 3;


-- 查詢離點最近的20個網點範圍,且距離不超過10公里
select t.*,ST_DISTANCE(COORDINATES,ST_GeomFromText('POINT(116.569613 39.989529)'))/0.0111 as distance from T_GIS_ORG_MAP_OPT_1 t having distance < 10order by distance limit 20;

本文屬於小編原創內容，未經許可不得轉載！