Find the distance between two points in MYSQL. (using the Point Datatype)

Suppose I have a 2 column table like this:

| user_id      | int(11) | NO   | UNI | NULL    |                |
| utm          | point   | NO   | MUL | NULL    |                |

As you can see, it's very simple. utm is a Point data-type. I insert it like this:

INSERT INTO mytable(user_id, utm) VALUES(1, PointFromWKB(point(50, 50)));

Then, I create a Spatial index.

ALTER TABLE mytable ...add spatial index on(utm) or something. (forgot)

Alright, everything is go开发者_JS百科od. Now , I want to select * where distance < 99999. But it doesn't work!

//This is supposed to select all where the distance is less than 99999999.
set @mypoint = PointFromWKB(point(20,20))
select * from mytable where GLength(LineString(utm, @mypoint)) < 9999999;
Empty set (0.00 sec)
select * from mytable where GLength(LineStringFromWKB(LineString(utm, @mypoint))) < 9999;
Empty set (0.00 sec)

By the way, I have tried to INSERT INTO without the PointFromWKB...and it didn't work...that's why someone suggested that PointFromWKB to me.

Solved. This is what I did:

where GLength(LineStringFromWKB(LineString(asbinary(utm), asbinary(@mypoint)))) < 9999999999999;

You can also do it this way. Not sure if it's faster or not.

select * from mytable where glength(geomfromtext(concat('linestring(', x(utm), ' ', y(utm), ',20 20', ')'))) < 99999999

As far as I know, u have to try this way-

select * from mytable
   where 
    (
        GLength(
          LineStringFromWKB(
            LineString(
              geoPoint, 
              GeomFromText('POINT(51.5177 -0.0968)')
            )
          )
        )
      ) < 99999999

More in this answer.

If you are considering points as longitude and latitude values on earth you can use ST_Distance_Sphere function to calculate distance between 2 points.

mysql> SET @p1 = ST_GeomFromText('POINT(3.13 52.5)');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @p2 = ST_GeomFromText('POINT(100.4 27.8)');
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT  ST_Distance_Sphere(@p1, @p2) AS distance;
+-------------------+
| distance          |
+-------------------+
| 8053869.706740822 |
+-------------------+
1 row in set (0.05 sec)

Value you get is from meters. And also you can give a preferred radius as an optional argument to the function ST_Distance_Sphere to calculate distance.

You can find more details on this from official documentation. https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html