Bug #79282 | Error querying POINT features over SHAPE | ||
---|---|---|---|
Submitted: | 14 Nov 2015 12:45 | Modified: | 24 Nov 2015 10:28 |
Reporter: | Luis Garrido | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
Version: | 5.7.9-log MySQL Community Server (GPL) | OS: | Windows (W7, 64 bits) |
Assigned to: | CPU Architecture: | Any | |
Tags: | point, regression, shape, srids |
[14 Nov 2015 12:45]
Luis Garrido
[19 Nov 2015 15:56]
MySQL Verification Team
Thank you for the bug report. Are you able to provide a dump file of the table(s) with insert data attaching it here using TAB Files?. Thanks.
[19 Nov 2015 23:58]
Luis Garrido
I needed to use the SFTP because the compressed file is 15 MB. I hope to have done correctly. Filename: mysql-bug-data-79282.zip. This zip contains tzv.sql generated by mysqldump. Thanks.
[24 Nov 2015 9:09]
MySQL Verification Team
Hello Luis Garrido, Thank you for the report and test case. Verified as described with 5.7.9 release build. Thanks, Umesh
[24 Nov 2015 9:17]
MySQL Verification Team
// 5.7.9 - affected [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 . mysql> SELECT tzid FROM tz_world WHERE ST_Contains(SHAPE, POINT('-122.392128','37.795653')); ERROR 3033 (HY000): Binary geometry function st_contains given two geometries of different srids: 1 and 0, which should have been identical. mysql> // 5.6.27 - not affacted [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT tzid FROM tz_world WHERE ST_Contains(SHAPE, POINT('-122.392128','37.795653')); +---------------------+ | tzid | +---------------------+ | America/Los_Angeles | +---------------------+ 1 row in set (0.01 sec) mysql>
[24 Nov 2015 10:28]
Norvald Ryeng
Posted by developer: I'm closing this as not a bug. The error message really says it all: ERROR 3033 (HY000): Binary geometry function st_contains given two geometries of different srids: 1 and 0, which should have been identical. You can't compare a shape in one spatial reference system (SRID 1) with a point in another spatial reference system (SRID 0). The POINT() function[1] will always return a point in SRID 0, which is the unitless, Cartesian default spatial reference system. In order to do the intended comparison, the point has to be in the same spatial reference system as the shape. E.g., use the SRID parameter of ST_GeomFromText()[2]: SELECT tzid FROM tz_world WHERE ST_Contains(SHAPE, ST_GeomFromText('POINT(-122.392128 37.795653)', 1)); This will create a point with SRID 1, the same as the shape. Regards, Norvald H. Ryeng [1] https://dev.mysql.com/doc/refman/5.7/en/gis-mysql-specific-functions.html#function_point [2] https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_st-geomfromtext