CODEWITHSUNDEEP
mysqlwkt
Lefty
Lefty

Reputation: 103

MySQL 8.0 ST_Contains query

We're updating from MySQL 5.6 to 8.0. While working fine in 5.6, using 8.0 and Workbench to test the query below and error is returned.

Error Code: 3617. Latitude 127.000000 is out of range in function > > > st_geomfromtext. It must be within [-90.000000, 90.000000].

The POLYGON and POINT uses x = long and y = lat, the example co-ordinates are in the south east hemisphere and all latitude and longitude fields are in range.

From the error message it appears f.lon is being validated against max/min lat values, changing to x = lat and y = long produces a correct result.

The question is, doesn't WKT use x = long and y = lat? Any help or explanation would be appreciated.

To replicate:

CREATE DATABASE  IF NOT EXISTS `tempdata`;
USE `tempdata`;
DROP TABLE IF EXISTS `sample`;
CREATE TABLE `sample` (
  `Place` char(10) NOT NULL,
  `Lat` decimal(10,8) DEFAULT NULL,
  `Lon` decimal(11,8) DEFAULT NULL,
  PRIMARY KEY (`Place`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `sample` VALUES ('IN_SE',-29.10000000,26.30000000),
('OUT_NE',29.00000000,127.00000000),
('OUT_NW',30.00000000,-120.00000000),
('OUT_SE',-45.00000000,130.00000000),
('OUT_SW',-40.00000000,-45.00000000);

Query

SELECT f.Place, f.Lat, f.Lon FROM tempdata.sample f
WHERE ST_Contains(ST_GeomFromText('POLYGON((
20.92061458693626 -42.957921556353014, 
8.551680010173861 -30.52917617616417, 
21.9325174415095 -19.051246698460766, 
28.12566921079717 -16.967667823403218, 
38.428497878482574 -27.255151898241817, 
35.35015222665547 -32.90654636609105, 
20.92061458693626 -42.957921556353014))', 4326), 
ST_GeomFromText(CONCAT('POINT(', f.lon, ' ', f.lat, ')'), 4326));

Upvotes: 0

Views: 171

Answers (1)

nbk
nbk

Reputation: 49403

if you add a spatial reference point like in your code

SELECT ST_GeomFromText(CONCAT('POINT(91.7 92.7)'), 4326)

You have following restrictions

For geographic SRS geometry arguments, if any argument has a longitude or latitude that is out of range, an error occurs:

If a longitude value is not in the range (−180, 180], an ER_LONGITUDE_OUT_OF_RANGE error occurs.

If a latitude value is not in the range [−90, 90], an ER_LATITUDE_OUT_OF_RANGE error occurs.

see manual

so you need to convert your data

or use it without reference point, which hasn't that restriction.

SELECT ST_GeomFromText('POINT(91.7 92.7)')

Upvotes: 0

Related Questions