mysql图形查询操作 点找面及面找点

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.****.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 ))