| 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 | ||
[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
Description: When a SELECT command is made like: SELECT tzid FROM tz_world WHERE ST_Contains(SHAPE, POINT('-122.392128','37.795653')); response error: ERROR 3033 (HY000): Binary geometry function st_contains given two geometries of different srids: 1 and 0, which should have been identical. How to repeat: tz_world is a table from a database imported trough ogr2ogr whit the command: ogr2ogr -progress -f "MySQL" MySQL:"tzw,user=root,host=localhost,password= password " -lco engine=MYISAM tz_world.shp The shape tz_world.shp can be downloaded from http://efele.net/maps/tz/world/ [http://efele.net/maps/tz/world/tz_world.zip] Database structure: +------------------+ | Tables_in_ tzw | +------------------+ | geometry_columns | | spatial_ref_sys | | tz_world | +------------------+ 3 rows in set (0.00 sec) mysql> describe geometry_columns; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | F_TABLE_CATALOG | varchar(256) | YES | | NULL | | | F_TABLE_SCHEMA | varchar(256) | YES | | NULL | | | F_TABLE_NAME | varchar(256) | NO | | NULL | | | F_GEOMETRY_COLUMN | varchar(256) | NO | | NULL | | | COORD_DIMENSION | int(11) | YES | | NULL | | | SRID | int(11) | YES | | NULL | | | TYPE | varchar(256) | NO | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> describe spatial_ref_sys; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | SRID | int(11) | NO | | NULL | | | AUTH_NAME | varchar(256) | YES | | NULL | | | AUTH_SRID | int(11) | YES | | NULL | | | SRTEXT | varchar(2048) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> describe tz_world; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | OGR_FID | int(11) | NO | PRI | NULL | auto_increment | | SHAPE | geometry | NO | MUL | NULL | | | tzid | varchar(30) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM geometry_columns LIMIT 0, 50; +-----------------+----------------+--------------+-------------------+-----------------+------+---------+ | F_TABLE_CATALOG | F_TABLE_SCHEMA | F_TABLE_NAME | F_GEOMETRY_COLUMN | COORD_DIMENSION | SRID | TYPE | +-----------------+----------------+--------------+-------------------+-----------------+------+---------+ | NULL | NULL | tz_world | SHAPE | 2 | 1 | POLYGON | +-----------------+----------------+--------------+-------------------+-----------------+------+---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM spatial_ref_sys LIMIT 0, 50; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SRID | AUTH_NAME | AUTH_SRID | SRTEXT | +------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | NULL | NULL | GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]] | +------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec) Suggested fix: ?