| Bug #34075 | Geometry Precision Loss | ||
|---|---|---|---|
| Submitted: | 26 Jan 2008 6:20 | Modified: | 10 Dec 2008 12:22 |
| Reporter: | Nathan Rixham | ||
| Status: | In progress | ||
| Category: | Server: GIS | Severity: | S3 (Non-critical) |
| Version: | 5.x, 6.0 - BK | OS: | Any |
| Assigned to: | Alexey Botchkov | Target Version: | |
| Tags: | Geometry, spatial, precision | ||
| Triage: | Triaged: D2 (Serious) | ||
[26 Jan 2008 13: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 18: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 20:03]
Alexey Botchkov
Cannot be fixed now as we use DOUBLE to store spatial coordinates which means 15 digit precision.
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)