Skip navigation links

User Comments

Posted by [name withheld] on November 12 2006 11:20pm[Delete] [Edit]

people (including the author) have been known to have spent a lot of time 'working' with features that weren't working in the first place.
so read this sentence very, very carefully (taken from above):

"Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions."

Posted by ro bo on March 23 2007 6:36am[Delete] [Edit]

"Those that are implemented return the same result as the corresponding MBR-based functions."
The lack of the real implementation to those functions can be for now worked around by passing the result of the query to an appropriate function.
For example, a "within" condition query results with the MBRWithin-function and must then be passed to a "true-Within-function" like those explained by Paul Bourke at http://local.wasp.uwa.edu.au/~pbourke/geometry/.
More specifically:
You have a query:
SELECT AsText(theGeom) AS myPolygon FROM spatialTable WHERE Within(GeomFromText('POINT(463937.596407 4531626.73719)'), theGeom);
You whill loop the MBRWithin-like function result to the specific php function myWithin($row[myPolygon ],"POINT(463937.596407 4531626.73719)") to verify if "really" it is Within.
And here you are:
/******************************************************************************
*
* Purpose: Inside/outside polygon test of a point
* by calculating the number of time an horizontal ray
emanating from a point to the rigth intersects the lines
segments making up the polygon (even=no, odd=yes)
* Author: Paul Bourke, php adaptation: Roger Boily
* return boolean
*
******************************************************************************/
function myWithin($myPolygon,$point) {
$counter = 0;
// get rid of unnecessary stuff
$myPolygon = str_replace("POLYGON","",$myPolygon);
$myPolygon = str_replace("(","",$myPolygon);
$myPolygon = str_replace(")","",$myPolygon);
$point = str_replace("POINT","",$point);
$point = str_replace("(","",$point);
$point = str_replace(")","",$point);
// make an array of points of the polygon
$polygon = explode(",",$myPolygon);
// get the x and y coordinate of the point
$p = explode(" ",$point);
$px = $p[0];
$py = $p[1];
// number of points in the polygon
$n = count($polygon);
$poly1 = $polygon[0];
for ($i=1; $i <= $n; $i++) {
$poly1XY = explode(" ",$poly1);
$poly1x = $poly1XY[0];
$poly1y = $poly1XY[1];
$poly2 = $polygon[$i % $n];
$poly2XY = explode(" ",$poly2);
$poly2x = $poly2XY[0];
$poly2y = $poly2XY[1];
if ($py > min($poly1y,$poly2y)) {
if ($py <= max($poly1y,$poly2y)) {
if ($px <= max($poly1x,$poly2x)) {
if ($poly1y != $poly2y) {
$xinters = ($py-$poly1y)*($poly2x-$poly1x)/($poly2y-$poly1y)+$poly1x;
if ($poly1x == $poly2x || $px <= $xinters) {
$counter++;
}
}
}
}
}
$poly1 = $poly2;
} // end of While each polygon
if ($counter % 2 == 0) {
return(false); // outside
} else {
return(true); // inside
}
}
*******************************
Roger Boily, Gis Consulant [boily at bsw dot org ]
*******************************

Posted by Lenz Grimmer on January 29 2008 1:02pm[Delete] [Edit]

Some of the functions mentioned above have now been implemented using non-MBR geometry - take a look at http://forge.mysql.com/wiki/GIS_Functions for more details.

Posted by AndroNick Nad on May 9 2010 10:00am[Delete] [Edit]

My function is 6 times faster

CREATE FUNCTION myWithin(p POINT, poly POLYGON) RETURNS INT(1) DETERMINISTIC
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE pX DECIMAL(9,6);
DECLARE pY DECIMAL(9,6);
DECLARE ls LINESTRING;
DECLARE poly1 POINT;
DECLARE poly1X DECIMAL(9,6);
DECLARE poly1Y DECIMAL(9,6);
DECLARE poly2 POINT;
DECLARE poly2X DECIMAL(9,6);
DECLARE poly2Y DECIMAL(9,6);
DECLARE i INT DEFAULT 0;
DECLARE result INT(1) DEFAULT 0;
SET pX = X(p);
SET pY = Y(p);
SET ls = ExteriorRing(poly);
SET poly2 = EndPoint(ls);
SET poly2X = X(poly2);
SET poly2Y = Y(poly2);
SET n = NumPoints(ls);
WHILE i<n DO
SET poly1 = PointN(ls, (i+1));
SET poly1X = X(poly1);
SET poly1Y = Y(poly1);
IF ( ( ( ( poly1X <= pX ) && ( pX < poly2X ) ) || ( ( poly2X <= pX ) && ( pX < poly1X ) ) ) && ( pY > ( poly2Y - poly1Y ) * ( pX - poly1X ) / ( poly2X - poly1X ) + poly1Y ) ) THEN
SET result = !result;
END IF;
SET poly2X = poly1X;
SET poly2Y = poly1Y;
SET i = i + 1;
END WHILE;
RETURN result;
End;

Usage:

SET @point = PointFromText('POINT(5 5)') ;
SET @polygon = PolyFromText('POLYGON((0 0,10 0,10 10,0 10))') ;
SELECT myWithin(@point, @polygon) AS result ;

|1|

Все права принадлежат всем

Posted by Ryan Mott on June 11 2010 5:31pm[Delete] [Edit]

Here's an improvement on AndroNick's function that accommodates testing on polygons with Interior Rings ("holes" or nested regions):
http://mysql.pastebin.com/vn2teRh1

Posted by Simon Ferragne on November 3 2010 12:20am[Delete] [Edit]

This function will return the true within.

DELIMITER $$
CREATE FUNCTION trueWithin(p POINT, poly POLYGON)
RETURNS INT(1) DETERMINISTIC
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE nr INT DEFAULT 0;
DECLARE pX DECIMAL(9,6);
DECLARE pY DECIMAL(9,6);
DECLARE ls LINESTRING;
DECLARE poly1 POINT;
DECLARE poly1X DECIMAL(9,6);
DECLARE poly1Y DECIMAL(9,6);
DECLARE poly2 POINT;
DECLARE poly2X DECIMAL(9,6);
DECLARE poly2Y DECIMAL(9,6);
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 1;
DECLARE result INT(1) DEFAULT 0;
SET pX = X(p);
SET pY = Y(p);
SET ls = ExteriorRing(poly);
SET poly2 = EndPoint(ls);
SET poly2X = X(poly2);
SET poly2Y = Y(poly2);
SET n = NumPoints(ls);
WHILE i<n DO
SET poly1 = PointN(ls, (i+1));
SET poly1X = X(poly1);
SET poly1Y = Y(poly1);
IF ( ( ( (poly1X <= pX) && (pX < poly2X) ) || ( (poly2X <= pX) && (pX < poly1X) ) ) && ( pY > (poly2Y - poly1Y) * (pX - poly1X) / (poly2X - poly1X) + poly1Y ) ) THEN
SET result = !result;
END IF;
SET poly2X = poly1X;
SET poly2Y = poly1Y;
SET i = i + 1;
END WHILE;
SET nr = NumInteriorRings(poly);
WHILE j<=nr DO
SET ls = InteriorRingN(poly, j);
SET poly2 = EndPoint(ls);
SET poly2X = X(poly2);
SET poly2Y = Y(poly2);
SET n = NumPoints(ls);
SET i = 0;
WHILE i<n DO
SET poly1 = PointN(ls, (i+1));
SET poly1X = X(poly1);
SET poly1Y = Y(poly1);
IF ( ( ((poly1X <= pX) && (pX < poly2X)) || ((poly2X <= pX) && (pX < poly1X)) ) && ( pY > (poly2Y - poly1Y) * (pX - poly1X) / (poly2X - poly1X) + poly1Y ) ) THEN
SET result = !result;
END IF;
SET poly2X = poly1X;
SET poly2Y = poly1Y;
SET i = i + 1;
END WHILE;
SET j = j + 1;
END WHILE;
RETURN result;
End$$
DELIMITER ;

Posted by Markus Hall on March 13 2012 1:27pm[Delete] [Edit]

In order to make the above mentioned myWithin or trueWithin functions work with points/polygons using the SWEREF99 national coordinate system, you need alter the declaration of variables "pX" etc. to be able to hold the larger coordinate values used in that system, e.g. DECIMAL(10, 2).