查找MYSQL中两点之间的距离。 （使用点数据类型） [英] Find the distance between two points in MYSQL. (using the Point Datatype)

问题描述

假设我有一个这样的2列表：

Suppose I have a 2 column table like this:

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

正如你所看到的，它很简单。 utm是 Point 数据类型。我插入它像这样：

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

然后，我创建一个Spatial索引。

Then, I create a Spatial index.

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

好吧，一切都很好。现在，我想 select * where distance<
但不起作用。

Alright, everything is good. 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)

顺便说一句， PointFromWKB ...它没有工作...这就是为什么有人建议PointFromWKB给我。

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.

推荐答案

这是我做的：

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

这篇关于查找MYSQL中两点之间的距离。 （使用点数据类型）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！