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;

本文属于小编原创内容，未经许可不得转载！