Skip navigation links
**Section Navigation** [Toggle]

- Documentation Library
**Table of Contents**- MySQL 5.7 Manual
- MySQL 5.6 Manual
- MySQL 5.5 Manual
- MySQL 5.1 Manual
- MySQL 5.0 Manual
- MySQL 3.23/4.0/4.1 Manual

MySQL 3.23, 4.0, 4.1 Reference Manual :: 16 Spatial Extensions :: 16.5 Analyzing Spatial Information :: 16.5.6 Functions That Test Spatial Relationships Between Geometries

- 16.5 Analyzing Spatial Information
- 16.5.1 Geometry Format Conversion Functions
- 16.5.2 Geometry Functions
- 16.5.3 Functions That Create New Geometries from Existing Ones
- 16.5.4 Functions for Testing Spatial Relations Between Geometric Objects
- 16.5.5 Relations on Geometry Minimal Bounding Rectangles (MBRs)
- 16.5.6 Functions That Test Spatial Relationships Between Geometries

The OpenGIS specification defines the following functions. They
test the relationship between two geometry values
`g1`

and `g2`

.

The return values 1 and 0 indicate true and false, respectively.

Note

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. This
includes functions in the following list other than
`Distance()`

and
`Related()`

.

Returns 1 or 0 to indicate whether

completely contains`g1`

. This tests the opposite relationship as`g2`

`Within()`

.Returns 1 if

spatially crosses`g1`

. Returns`g2`

`NULL`

if`g1`

is a`Polygon`

or a`MultiPolygon`

, or ifis a`g2`

`Point`

or a`MultiPoint`

. Otherwise, returns 0.The term

*spatially crosses*denotes a spatial relation between two given geometries that has the following properties:The two geometries intersect

Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries

Their intersection is not equal to either of the two given geometries

Returns 1 or 0 to indicate whether

is spatially disjoint from (does not intersect)`g1`

.`g2`

Returns as a double-precision number the shortest distance between any two points in the two geometries.

Returns 1 or 0 to indicate whether

is spatially equal to`g1`

.`g2`

Returns 1 or 0 to indicate whether

spatially intersects`g1`

.`g2`

Returns 1 or 0 to indicate whether

spatially overlaps`g1`

. The term`g2`

*spatially overlaps*is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.Returns 1 or 0 to indicate whether the spatial relationship specified by

exists between`pattern_matrix`

and`g1`

. Returns –1 if the arguments are`g2`

`NULL`

. The pattern matrix is a string. Its specification will be noted here if this function is implemented.Returns 1 or 0 to indicate whether

spatially touches`g1`

. Two geometries`g2`

*spatially touch*if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.Returns 1 or 0 to indicate whether

is spatially within`g1`

. This tests the opposite relationship as`g2`

`Contains()`

.

## User Comments

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."

"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 ]

*******************************

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.

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 ;

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

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

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 ;

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