mysql图形查询操作 点找面及面找点 Polygon获取中心点坐标 空间地理位置计算

SET @x = 121; SET @y = 30; --

SET @point = CONCAT('POINT(',@x,' ',@y,')');

set @point= Point(@x,@y); set @geometry=ST_GeomFromText('POLYGON((121 30, 121 30, ..., 121 30, 121 30))');

SELECT ST_Contains(@geometry,@point);//面包含点

SELECT ST_Within(@point, @geometry);//点在面

可以参考：

https://blog.csdn.net/qq_32201423/article/details/103870040?utm_medium=distribute.pc_relevant.none-task-blog-baidulandingword-1&spm=1001.2101.3001.4242

https://www.docs4dev.com/docs/zh/mysql/5.7/reference/contributors.html
 

 

MBRWITHIN 这个查出了多一条Id为54的：原因可能是MBRWITHIN 只能查点是否在四边形区域内.

改成 ST_Within 顺利解决问题

SELECT
    s.id AS `s__id`,
    s.uid AS `s__uid`,
    s.station_type AS `s__station_type`,
    s.zoneid AS `s__zoneid`,
    s.phone AS `s__phone`,
    astext(s.polygongeo) AS `s__polygongeo`,
    s.polygongeo_txt AS `s__polygongeo_txt`,
    s.STATUS AS `s__status`,
    s.beginTime AS `s__beginTime`,
    s.endTime AS `s__endTime`,
    s.money AS `s__money`,
    s.created AS `s__created`,
    s.modified AS `s__modified`,
    s.userName AS `s__userName`,
    s.city_id AS `s__city_id`,
    s.identity_path AS `s__identity_path` 
FROM
    station_masters s
    INNER JOIN station_master_zones sz ON s.zoneid = sz.id 
WHERE
    (
        s.STATUS = 1 
    AND s.beginTime < 1597390108 AND s.endTime > 1597390108 
    AND ST_Within ( POLYGONFROMTEXT ( 'POINT(113.92827 22.541516)' ),sz.polygongeo ))
 

 

当前所处在的位置（113.858202 , 22.583819 ）,需要查询我附近1000米内的小区，并安装由近到远的顺序排列 

SELECT
s.id,s.name,s.lng,s.lat,

round ((st_distance (point (lng, lat),point(113.858202,22.583819) ) / 0.0111)*1000) AS distance
FROM
wlsq_base.lt s

HAVING distance<10

ORDER BY distance

 

-- 计算两个地点的球面距离，单位千米
select city_name, round(st_distance_sphere(point(118.35, 24.49),point(lng, lat))/1000,2), update_time as distance from tb_vip_geo order by update_time

 

-- 查询与第一个坐标点(118.35, 24.49)相距小于500千米的坐标点
select city_name, round(st_distance_sphere(point(118.35, 24.49),point(lng, lat))/1000,2) as distance, update_time 
from tb_vip_geo 
HAVING distance > 0 and distance < 500
ORDER BY distance;

---

查询三千米以内的店铺

select
	s.id,
	s.title as `shop_name`, 
	round(st_distance(POINT(s.longitude, s.latitude), POINT(113.9189700000, 22.4922100000))* 111.195, 4) as `juli`
	 
from
  ewj_market.vdo_product s  
    group by id
having juli <= 3

---

---

Polygon获取中心点坐标

https://blog.csdn.net/benjmali/article/details/16119009

mysql 5.7.28 空间地理位置计算

https://blog.csdn.net/qq_32201423/article/details/103870040?utm_medium=distribute.pc_relevant.none-task-blog-baidulandingword-1&spm=1001.2101.3001.4242