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)'));

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.

1.  https://lbs.qq.com/webDemoCenter/glAPI/glEditor/toolDraw 绘制几何图形 根据绘制的区域得到坐标集合
2.  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)
3.  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))'));

原创作者: kuangke 转载于: https://blog.51cto.com/kuangke/11850787