MySQL normalizing points with latitude > 90

Question

Facing very strange problem, I've points table for storing location of users in location(POINT) column. Problem with st_distance_sphere function where I've stored some specific location.

update points set location = point(30.492039, 76.587216) WHERE id = 1;

its Working fine but when I've changed lat,lng to

update points set location = point(52.051503, 113.471190) WHERE id = 1;

its called:

Incorrect arguments to st_distance_sphere

Table:

CREATE TABLE `points` (
  `id` int(10) NOT NULL,
  `name` varchar(45) ,
  `location` point,
  PRIMARY KEY (`id`),
)

Query:

    SELECT id,ST_Distance_Sphere(point(52.408089, 113.489844),location)/1000 
AS distance FROM points HAVING distance < 100;

try update points set location = point(113.471190,52.051503 ) WHERE id = 1; — Ian Turton, Aug 31 '16 at 09:14

score 5 · Accepted Answer · answered Sep 05 '16 at 05:04

Looks like the POINT constructor format is POINT(longitude, latitude), this is, longitude first, latitude second.

MySQL wont complain if you save a point like point(52.408089, 113.489844) on a table field, but it won't be able to calculate distances since that point has a latitude greater than 90 degrees.

score 1 · Answer 2 · answered Nov 20 '17 at 21:43

What you need to do is coerce the points yourself. PostGIS does this for you,

SELECT ST_AsText(ST_MakePoint(52.408089, 113.489844)::geography);
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
         st_astext          
----------------------------
 POINT(52.408089 66.510156)
(1 row)

However you can do it yourself if your points are wonky, rather than

SELECT ST_AsText(point(-182.051503, 113.471190));

do this, with %

SELECT ST_AsText(point(-182.051503%180, 113.471190%90));

MySQL normalizing points with latitude > 90

2 Answers2