Bug #66980 GEOMFROMTEXT consumes and rounds points only to 14 digits after dot
Submitted: 27 Sep 2012 4:05 Modified: 27 Sep 2012 20:27
Reporter: Anton Bulgakov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S2 (Serious)
Version:5.1.61-rel13.2-log - (Percona Server (GP OS:Windows (7)
Assigned to: CPU Architecture:Any
Tags: astext polygon 14 digits do point

[27 Sep 2012 4:05] Anton Bulgakov
Description:
GEOMFROMTEXT function rounds x and y coordinats of points to 14 digits after dot.

f.e 1.12345678901234567890 will be inserted as 1.12345678901235

this is only 14 digits after dot. but I need 17 minimum digits. because i am using latitude and longitude store in DB. right now i need format like float(21,17). but in future i think i will need more digits after dot.

How to repeat:
SET @g = 'POLYGON((1.12345678901234567890 73.4571075439453))';
INSERT INTO PG050100 VALUES ('0', GEOMFROMTEXT(@g), 9,'');

SELECT ASTEXT(PG05002) FROM PG050100 WHERE CONTAINS(PG05002,  GEOMFROMTEXT('POINT(1.12345678901234567890 73.4571075439453)'))

##POLYGON((1.12345678901235 73.4571075439453))

Suggested fix:
please increase digits after dot or add parameter to GEOMFROMTEXT function

like this: GEOMFROMTEXT(@g, float(21,17))
[27 Sep 2012 6:36] Valeriy Kravchuk
Please, send the output of:

show create table PG050100\G

to complete your test case.
[27 Sep 2012 7:49] Anton Bulgakov
CREATE TABLE IF NOT EXISTS `PG050100` (
  `PG05001` int(20) unsigned NOT NULL,
  `PG05002` polygon NOT NULL,
  `PG05003` int(5) unsigned NOT NULL,
  `PG05004` datetime NOT NULL,
  PRIMARY KEY (`PG05001`),
  SPATIAL KEY `PG05002` (`PG05002`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[27 Sep 2012 13:58] MySQL Verification Team
Please read comment http://bugs.mysql.com/bug.php?id=34075

[1 Aug 11:10] Alexander Barkov
http://en.wikipedia.org/wiki/Double-precision_floating-point_format

Double-precision numbers use 52 bits to store digits,
which is enough to provide precision of 15-16 decimal digits
(not 53 as in the report).

........

+--------------------+
| a                  |
+--------------------+
|  3144.163929178904 |
| 4044.3600286609967 |
+--------------------+
2 rows in set (0.00 sec)

Not a bug.

-------------------------------------------------------

mysql 5.5 >SELECT ASTEXT(PG05002) FROM PG050100 WHERE CONTAINS(PG05002,  GEOMFROMTEXT('POINT(1.12345678
    -> ;
+------------------------------------------------+
| ASTEXT(PG05002)                                |
+------------------------------------------------+
| POLYGON((1.1234567890123457 73.4571075439453)) |
+------------------------------------------------+
1 row in set (0.07 sec)

mysql 5.5 >
[27 Sep 2012 16:13] Anton Bulgakov
ok. now it is clear for me. thank you! ticket can be closed.
[27 Sep 2012 20:27] MySQL Verification Team
Thank you for the feedback.