CODEWITHSUNDEEP
mysqlmariadb
nic
nic

Reputation: 131

MySQL version fails to execute geometry query

If I run this query

SELECT region_id FROM shape_region WHERE ST_Within(point(-117.10480, 32.72204),shape_region.shape)=1

on MySQL MariaDB version 10.1.13-MariaDB, there are no problems.

But on MySQL version 5.7.16-0ubuntu0.16.04.1 I get this error

Binary geometry function st_within given two geometries of different srids: 0 and 1, which should have been identical.

I do not understand the error, is there a comparable query I can use on this version of MySQL?

Upvotes: 4

Views: 3900

Answers (1)

nic
nic

Reputation: 131

Answer found at this link

https://bugs.mysql.com/bug.php?id=79282

Compatible query that works on both MySQL and MariaDB:

SELECT region_id FROM shape_region WHERE ST_Contains( SHAPE, ST_GeomFromText( 'POINT(-122.392128 37.795653)', 1 ) )

As explained in the link

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]:

Upvotes: 8

Related Questions