SQL之点是否在区域内

一、SQL之点是否在区域内

一、SQL之点是否在区域内

MySQL 常用函数积累，提升性能

是否包含线上的点？MBRWithin、MBRContains和java实现包含线上的点，ST_Contains、ST_Within、GISWithin不包含线上的点

查询效率：mysql5.6存储引擎Innodb无法创建空间索引，若无法创建空间索引，使用mysql函数和java哪种查询效率更高？

mysql空间索引：对于空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：GEOMETRY,POINT,LINESTRING,POLYGON，MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引，创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建

使用函数时，多边形的第一个点和最后一个点的gps数据要相同。

方法一：使用mysql函数MBRWithin(A,B) –> A在B中

SELECT
    MBRWithin (
    GeomFromText ( 'POINT(113.937612 22.534883)' ),
    GeomFromText ( 'POLYGON((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488))' )
);
 
SELECT
    MBRWithin (
    GeomFromText ( 'POINT(113.971694 22.549804)' ),
    GeomFromText ( 'MULTIPOLYGON(((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488)),
                                 ((113.972125 22.553275,113.971694 22.549804,113.980461 22.549637,113.978449 22.555345,113.974461 22.556713,113.972125 22.553275)),
                                 ((113.971047 22.57033,113.979455 22.563288,113.986139 22.569362,113.981647 22.573167,113.973814 22.573434,113.971047 22.57033)))'
                 )
    );

方法二：使用mysql函数MBRContains(A,B) –> A包含B

SELECT
    MBRContains (
    GeomFromText ( 'POLYGON((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488))' ),
    GeomFromText ( 'POINT(113.980461 22.549637)' )
    );
     
     
SELECT
    MBRContains (
    GeomFromText ( 'MULTIPOLYGON(((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488)),
                                 ((113.972125 22.553275,113.971694 22.549804,113.980461 22.549637,113.978449 22.555345,113.974461 22.556713,113.972125 22.553275)),
                                 ((113.971047 22.57033,113.979455 22.563288,113.986139 22.569362,113.981647 22.573167,113.973814 22.573434,113.971047 22.57033)))' ),
    GeomFromText ( 'POINT(113.971694 22.549804)' )
    );

方法三：使用mysql函数ST_Contains(A,B) –> A是否包含B

SELECT
    ST_Contains (
    GeomFromText ( 'POLYGON((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488))' ),
    GeomFromText ( 'POINT(113.980461 22.549637)' ) );
     
     
SELECT
    ST_Contains (
    GeomFromText ( 'MULTIPOLYGON(((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488)),
                                 ((113.972125 22.553275,113.971694 22.549804,113.980461 22.549637,113.978449 22.555345,113.974461 22.556713,113.972125 22.553275)),
                                 ((113.971047 22.57033,113.979455 22.563288,113.986139 22.569362,113.981647 22.573167,113.973814 22.573434,113.971047 22.57033)))' ),
    GeomFromText ( 'POINT(113.971694 22.549804)' ) );

方法四：使用mysql函数ST_Within(A,B) –> A是否在B中

SELECT
    ST_Within (
    GeomFromText ( 'POINT(113.937612 22.534883)' ),
    GeomFromText ( 'POLYGON((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488))' ) );
     
     
SELECT
    ST_Within (
    GeomFromText ( 'POINT(113.971694 22.549804)' ),
    GeomFromText ( 'MULTIPOLYGON(((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488)),
                                 ((113.972125 22.553275,113.971694 22.549804,113.980461 22.549637,113.978449 22.555345,113.974461 22.556713,113.972125 22.553275)),
                                 ((113.971047 22.57033,113.979455 22.563288,113.986139 22.569362,113.981647 22.573167,113.973814 22.573434,113.971047 22.57033)))' )
    );

方法五：创建函数GISWithin

CREATE FUNCTION `GISWithin`(pt POINT, mp MULTIPOLYGON) RETURNS int(1) DETERMINISTIC
BEGIN
DECLARE str, xy TEXT;
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE p, pb, pe INT DEFAULT 0;
SELECT MBRWithin(pt, mp) INTO p;
IF p != 1 OR ISNULL(p) THEN
RETURN p;
END IF;
SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str;
SET str = REPLACE(str, 'POLYGON((','');
SET str = REPLACE(str, '))', '');
SET str = CONCAT(str, ',');
SET pb = 1;
SET pe = LOCATE(',', str);
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p1x = SUBSTRING(xy, 1, p - 1);
SET p1y = SUBSTRING(xy, p + 1);
SET str = CONCAT(str, xy, ',');
WHILE pe > 0 DO
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p2x = SUBSTRING(xy, 1, p - 1);
SET p2y = SUBSTRING(xy, p + 1);
IF p1y < p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y > m THEN
IF p1y > p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y <= m THEN
IF p1x > p2x THEN SET m = p1x; ELSE SET m = p2x; END IF;
IF x <= m THEN
IF p1y != p2y THEN
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x;
END IF;
IF p1x = p2x OR x <= xinters THEN
SET counter = counter + 1;
END IF;
END IF;
END IF;
END IF;
SET p1x = p2x;
SET p1y = p2y;
SET pb = pe + 1;
SET pe = LOCATE(',', str, pb);
END WHILE;
RETURN counter % 2;
END;
 
 
# 查询语句如下：
select GISWithin(GeomFromText('POINT(113.980461 22.549637)'),
                 GeomFromText('POLYGON((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488))'));
  
select GISWithin(GeomFromText('POINT(113.971694 22.549804)'),GeomFromText('MULTIPOLYGON(((113.942248 22.538488,113.937612 22.534883,113.937756 22.530477,113.954644 22.530811,113.951662 22.538522,113.942248 22.538488)),((113.972125 22.553275,113.971694 22.549804,113.980461 22.549637,113.978449 22.555345,113.974461 22.556713,113.972125 22.553275)),((113.971047 22.57033,113.979455 22.563288,113.986139 22.569362,113.981647 22.573167,113.973814 22.573434,113.971047 22.57033)))'));

方法六：使用java代码判断

方法六：使用java代码判断
PointUtil.java
import java.awt.geom.Point2D;
import java.util.ArrayList;
import java.util.List;

/**
 * @author User
 * @date 2018-08-22 22:20
 * @desc
 */
public class PointUtil {
    /**
     * 判断点是否在多边形内
     *
     * @param point 检测点
     * @param pts 多边形的顶点
     * @return 点在多边形内返回true, 否则返回false
     */
    public static boolean IsPtInPoly(Point2D.Double point, List<Point2D.Double> pts) {

        int N = pts.size();
        boolean boundOrVertex = true; //如果点位于多边形的顶点或边上，也算做点在多边形内，直接返回true
        int intersectCount = 0;//cross points count of x
        double precision = 2e-10; //浮点类型计算时候与0比较时候的容差
        Point2D.Double p1, p2;//neighbour bound vertices
        Point2D.Double p = point; //当前点

        p1 = pts.get(0);//left vertex
        for (int i = 1; i <= N; ++i) {//check all rays
            if (p.equals(p1)) {
                return boundOrVertex;//p is an vertex
            }

            p2 = pts.get(i % N);//right vertex
            if (p.x < Math.min(p1.x, p2.x) || p.x > Math.max(p1.x, p2.x)) {//ray is outside of our interests
                p1 = p2;
                continue;//next ray left point
            }

            if (p.x > Math.min(p1.x, p2.x) && p.x < Math.max(p1.x, p2.x)) {//ray is crossing over by the algorithm (common part of)
                if (p.y <= Math.max(p1.y, p2.y)) {//x is before of ray
                    if (p1.x == p2.x && p.y >= Math.min(p1.y, p2.y)) {//overlies on a horizontal ray
                        return boundOrVertex;
                    }

                    if (p1.y == p2.y) {//ray is vertical
                        if (p1.y == p.y) {//overlies on a vertical ray
                            return boundOrVertex;
                        } else {//before ray
                            ++intersectCount;
                        }
                    } else {//cross point on the left side
                        double xinters = (p.x - p1.x) * (p2.y - p1.y) / (p2.x - p1.x) + p1.y;//cross point of y
                        if (Math.abs(p.y - xinters) < precision) {//overlies on a ray
                            return boundOrVertex;
                        }

                        if (p.y < xinters) {//before ray
                            ++intersectCount;
                        }
                    }
                }
            } else {//special case when ray is crossing through the vertex
                if (p.x == p2.x && p.y <= p2.y) {//p crossing over p2
                    Point2D.Double p3 = pts.get((i + 1) % N); //next vertex
                    if (p.x >= Math.min(p1.x, p3.x) && p.x <= Math.max(p1.x, p3.x)) {//p.x lies between p1.x & p3.x
                        ++intersectCount;
                    } else {
                        intersectCount += 2;
                    }
                }
            }
            p1 = p2;//next ray left point
        }

        if (intersectCount % 2 == 0) {//偶数在多边形外
            return false;
        } else { //奇数在多边形内
            return true;
        }

    }

}
 
 
PointUtilTest.java

import java.awt.geom.Point2D;
import java.util.ArrayList;
import java.util.List;

/**
 * @author User
 * @date 2018-08-22 22:20
 * @desc
 */
public class PointUtilTest {
    public static void main(String[] args) {
        Point2D.Double point = new Point2D.Double(116.404072, 39.916605);

        List<Point2D.Double> pts = new ArrayList<Point2D.Double>();
        pts.add(new Point2D.Double(116.395, 39.910));
        pts.add(new Point2D.Double(116.394, 39.914));
        pts.add(new Point2D.Double(116.403, 39.920));
        pts.add(new Point2D.Double(116.402, 39.914));
        pts.add(new Point2D.Double(116.410, 39.913));

        if(PointUtil.IsPtInPoly(point, pts)){
            System.out.println("点在多边形内");
        }else{
            System.out.println("点在多边形外");
        }

    }
}

“非暴力沟通的第一个要素是观察。将观察和评论混为一谈，别人就会倾向于听到批评，并反驳我们。非暴力沟通是动态的语言，不主张绝对化的结论。它提倡在特定的时间和情境中进行观察，并清楚地描述观察结果。例如，它会说“欧文在过去的5场比赛中没有进一个球"，而不 是说“欧文是个差劲的前锋"。

欢迎拍砖,多多交流,转载请注明出处:[没长正的技术专栏](http://blog.meizhangzheng.com) 如涉及侵权问题，请发送邮件到xsj34567@163.com，如情况属实本人将会尽快删除。

SQL之点是否在区域内

一、SQL之点是否在区域内

Comments