Although that sounds easy, it can be counter-intuitive. The problem is the precision of coordinates. For example, in our project ( http://dobrotvor.com/ ) we bumped into this due to the fact that the precision of geo coordinates incapsulates the zoom value of Google Maps points. It's not right just to compare (X1 == X2) AND (Y1 == Y2) since the results can be wrong in many cases.
To make it work right, it's better to use special function (couldn't find anything better built-in):
[code] DELIMITER //
DROP FUNCTION IF EXISTS ArePointsEqual; // CREATE FUNCTION ArePointsEqual(p1 POINT, p2 POINT) RETURNS BOOLEAN NO SQL DETERMINISTIC BEGIN RETURN IsZero(X(p1) - X(p2)) AND IsZero(Y(p1) - Y(p2)); END; //
DROP FUNCTION IF EXISTS IsZero; // CREATE FUNCTION IsZero(n DOUBLE) RETURNS BOOLEAN NO SQL DETERMINISTIC BEGIN DECLARE epsilon DOUBLE DEFAULT 0.00000000001; RETURN (ABS(n) <= epsilon); END; // [/code]
The trick is not to use 0 as "zero value", but to use a very small difference (called "epsilon") instead for comparison.
© 2013, Oracle Corporation and/or its affiliates
User Comments
Re comparing equality of 2 points.
Although that sounds easy, it can be counter-intuitive. The problem is the precision of coordinates. For example, in our project ( http://dobrotvor.com/ ) we bumped into this due to the fact that the precision of geo coordinates incapsulates the zoom value of Google Maps points. It's not right just to compare (X1 == X2) AND (Y1 == Y2) since the results can be wrong in many cases.
To make it work right, it's better to use special function (couldn't find anything better built-in):
[code]
DELIMITER //
DROP FUNCTION IF EXISTS ArePointsEqual; //
CREATE FUNCTION ArePointsEqual(p1 POINT, p2 POINT) RETURNS BOOLEAN NO SQL DETERMINISTIC
BEGIN
RETURN IsZero(X(p1) - X(p2)) AND IsZero(Y(p1) - Y(p2));
END; //
DROP FUNCTION IF EXISTS IsZero; //
CREATE FUNCTION IsZero(n DOUBLE) RETURNS BOOLEAN NO SQL DETERMINISTIC
BEGIN
DECLARE epsilon DOUBLE DEFAULT 0.00000000001;
RETURN (ABS(n) <= epsilon);
END; //
[/code]
The trick is not to use 0 as "zero value", but to use a very small difference (called "epsilon") instead for comparison.
Add your own comment.