| Bug #34075 | Geometry Precision Loss | ||
|---|---|---|---|
| Submitted: | 26 Jan 2008 5:20 | Modified: | 1 Aug 2012 11:10 |
| Reporter: | Nathan Rixham | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
| Version: | 5.x, 6.0 - BK | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | Geometry, precision, spatial | ||
[26 Jan 2008 12:45]
Sveta Smirnova
Thank you for the report.
Verified as described.
In version 6.0 results depend from client.
MySQL commmand-line client:
$mysql60
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 6.0.5-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT AsText(GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)'));
+----------------------------------------------------------------------------+
| AsText(GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)')) |
+----------------------------------------------------------------------------+
| POINT(3144.1639291789 4044.360028661) |
+----------------------------------------------------------------------------+
1 row in set (0.08 sec)
C API:
SELECT AsText(GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)'));
AsText(GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)'))
POINT(3144.163929178904 4044.3600286609967)
[27 Jan 2008 17:37]
Nathan Rixham
Further to my original report..
It appears that mysql is storing the full values correctly, and that it's only the AsText function that is loosing the precision.
How to repeat:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
mysql> CREATE TABLE `geotest` (`geocol` geometry NOT NULL, SPATIAL KEY `geoindex` (`geocol`)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO geotest (geocol) VALUES (GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)'));
Query OK, 1 row affected (0.00 sec)
mysql> SELECT AsText(geocol) FROM geotest WHERE MBREqual(geocol, GeomFromText('POINT(3144.1639291789 4044.360028661)'));
Empty set (0.00 sec)
mysql> SELECT AsText(geocol) FROM geotest WHERE MBREqual(AsText(geocol), GeomFromText('POINT(3144.1639291789 4044.360028661)'));
Empty set (0.00 sec)
mysql> SELECT AsText(geocol) FROM geotest WHERE MBREqual(GeomFromText(AsText(geocol)), GeomFromText('POINT(3144.1639291789 4044.360028661)'));
+---------------------------------------+
| AsText(geocol) |
+---------------------------------------+
| POINT(3144.1639291789 4044.360028661) |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT AsText(geocol) FROM geotest WHERE MBREqual(geocol, GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)'));
+---------------------------------------+
| AsText(geocol) |
+---------------------------------------+
| POINT(3144.1639291789 4044.360028661) |
+---------------------------------------+
1 row in set (0.00 sec)
the above shows that the data stored is what we expect, and that it's only the AsText() function truncating / loosing precision.
This may be the same for other functions.. *untested*
[3 Feb 2008 19:03]
Alexey Botchkov
Cannot be fixed now as we use DOUBLE to store spatial coordinates which means 15 digit precision.
[1 Aug 2012 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). MySQL does preserve 15-16 digits in Geometry, in exactly the same way with what happens in DOUBLE data type: mysql> drop table if exists t1; create table t1 (a double); insert into t1 values (3144.1639291789037156),(4044.3600286609968055); select * from t1; Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.29 sec) Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 +--------------------+ | a | +--------------------+ | 3144.163929178904 | | 4044.3600286609967 | +--------------------+ 2 rows in set (0.00 sec) Not a bug.
Description: Geometry values are stored as Double-Precision values, so should keep precision down to 53/54 places however I'm finding on all install's I've tested that geometry values are all sliced at 9/10 decimal places. Bug? How to repeat: SELECT AsText(GeomFromText('POINT(3144.1639291789037156 4044.3600286609968055)')); returns: POINT(3144.1639291789 4044.360028661)