4

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; 
  • 1
    try update points set location = point(113.471190,52.051503 ) WHERE id = 1; – Ian Turton Aug 31 '16 at 9:14
4

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.

0

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));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.