[MDEV-6310] WITHIN() fails to find point inside shape Created: 2014-06-05 Updated: 2014-08-04 Resolved: 2014-07-25 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | None |
| Affects Version/s: | 5.3.12, 5.5.38, 10.0.11 |
| Fix Version/s: | 5.5.39, 10.0.13 |
| Type: | Bug | Priority: | Major |
| Reporter: | Andrei Boros | Assignee: | Alexey Botchkov |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | None | ||
| Environment: |
Linux Slackware64-14.1 |
||
| Description |
|
The following 2 queries do exactly the same thing: try to determine if point with coordinates N44.4 E26.1 (center of Bucharest) are inside the Bucharest-Ilfov region as defined by NUTS_LAYER data. One uses the binary shape data, the other textual data of the same shape. Error formatting macro: code: java.lang.StackOverflowError
SELECT SELECT Both these queries return 0. Tried WITHIN() and ST_WITHIN(). Same result. Both queries return 1 if run on Mysql-5.0.67 and 5.1.56. (these I had available for testing). I discovered this bug while trying to determine why a certain functionality of www.opencaching.ro website did not work. This website is part of the Opencaching network and is based on OCPL code with live SVN sync with the project's repository. |
| Comments |
| Comment by Alexey Botchkov [ 2014-07-25 ] | |||||||||||||||
|
This is not a bug! The 5.1 behaviour can be reproduced with the MBR_WITHIN() function. | |||||||||||||||
| Comment by Alexey Botchkov [ 2014-07-25 ] | |||||||||||||||
|
That query returns 1 as You probably expect: | |||||||||||||||
| Comment by Andrei Boros [ 2014-08-01 ] | |||||||||||||||
|
Request REOPEN! select this also returns 0. point (-1 -1) which is external to the shape returns 0. | |||||||||||||||
| Comment by Andrei Boros [ 2014-08-01 ] | |||||||||||||||
|
I have:
I get :
and
| |||||||||||||||
| Comment by Andrei Boros [ 2014-08-01 ] | |||||||||||||||
|
the actual function is MBRWITHIN () without "_". On MySQL both within() and mbrwithin() return 1 for the above mentioned 2x2 square. | |||||||||||||||
| Comment by Sergei Golubchik [ 2014-08-01 ] | |||||||||||||||
|
Yes, within(geomfromtext('point(1 1)'), geomfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)')) returns 0, because you ask whether a point is within a line, and it means, by definition, whether a point lies on the line segment. And it does not, thus you get 0, as expected. Perhaps, you want to know whether a point lies within a polygon ? then you need to say that in the query. | |||||||||||||||
| Comment by Andrei Boros [ 2014-08-04 ] | |||||||||||||||
|
select within( geomfromtext('point(1 1)'), polyfromtext('linestring(0 0, 0 2, 2 2, 2 0, 0 0)') ); select within( geomfromtext('point(1 1)'), geomfromtext('polygon((0 0, 0 2, 2 2, 2 0, 0 0))') ); Is this correct and expected? If so, why polyfromtext('linestring... does not create a polygon? | |||||||||||||||
| Comment by Sergei Golubchik [ 2014-08-04 ] | |||||||||||||||
|
Because POLYFROMTEXT only creates a polygon if the text defines a polygon, and you have specified LINESTRING. As far as I understand, POLYFROMTEXT does the same as GEOMFROMTEXT, but only returns polygons or an error, if the text does not specify a polygon. But as far as I can see, in MySQL (and MariaDB) POLYFROMTEXT is simply a different name for GEOMFROMTEXT with no special checks that the geometry is a polygon. To understand better the difference between MySQL 5.5 and MariaDB, try
that's the difference between MBR calculations and exact. |