mysql - 根据某经纬度 从区域列表内筛选符合条件的区域. 地图经纬度 坐标筛选, 区域是否重合

作者原创. 转载请注明来源

点是否在区域内

1. 我有一个区域列表. 每个区域都有一堆经纬度坐标集合 它们组成一个不规则图形. 然后我有个经纬度坐标 想筛选出这个坐标属于那个区域.mysql适合做这样的筛选吗?

    //创建区域坐标表
    CREATE TABLE regions (  
    	id INT AUTO_INCREMENT PRIMARY KEY,  
    	area POLYGON NOT NULL,  
    	SPATIAL INDEX(area)  
    );
    
    //查询区域坐标
    INSERT INTO regions (area) VALUES (ST_GeomFromText('POLYGON((39.92838530300306 116.44090025263336,
    	39.911754897377726 116.47737548069745,
    	39.888068874824064 116.43774098124152,
    	39.913517250947365 116.40830231411803,
    	39.92838530300306 116.44090025263336))')); 
    
    //查询某坐标在那个区域内
    SELECT *
    FROM regions
    WHERE ST_Contains(area, ST_GeomFromText('POINT(39.908738 116.436285)'));
   

2. https://lbs.qq.com/webDemoCenter/glAPI/glEditor/toolDraw 绘制几何图形 根据绘制的区域得到坐标集合

3. https://lbs.qq.com/webDemoCenter/glAPI/glPolygon/polygonCustom 自定义多边形样式 根据坐标画出区域
   new TMap.LatLng(39.92838530300306, 116.44090025263336),
   new TMap.LatLng(39.911754897377726, 116.47737548069745),
   new TMap.LatLng(39.888068874824064, 116.43774098124152),
   new TMap.LatLng(39.913517250947365, 116.40830231411803),
   new TMap.LatLng(39.92838530300306, 116.44090025263336)

4. https://lbs.qq.com/webDemoCenter/glAPI/glMap/mapPosition 点击地图拾取坐标 点击某一区域取坐标

区域是否重合

1. 创建表
   CREATE TABLE regions (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255),
   area POLYGON NOT NULL,
   SPATIAL INDEX(area)
   );
2. 加入数据
   INSERT INTO regions (name, area) VALUES
   ('Region2', ST_GeomFromText('POLYGON((40.016555630756024 116.28537829864581,
   40.00525843148395 116.3598133470914,
   39.947955502250714 116.32688355400387,
   39.96320592213123 116.24490210132717,
   40.016555630756024 116.28537829864581))'));
3. 重合数据示例
   #重合
   SELECT id, name
   FROM regions
   WHERE ST_Intersects(area, ST_GeomFromText('POLYGON(( 39.985286718896084 116.28812244599612,
   40.02154682237433 116.3193371432244,
   39.99737555867263 116.38313861724964,
   39.95742167960066 116.33477298762227,
   39.985286718896084 116.28812244599612))'));
4. 不重合示例
   #不重合
   SELECT id, name
   FROM regions
   WHERE ST_Intersects(area, ST_GeomFromText('POLYGON((39.89385196431428 116.27387925965377,
   39.91279799178657 116.34282600816005,
   39.86331680059243 116.36683732399615,
   39.84488383038246 116.29240226769252,
   39.89385196431428 116.27387925965377))'));