使用 POINT 的 Mysql 空间距离 - 不工作 [英] Mysql spatial distance using POINT - Not working

问题描述

我的目标是使用 mysql POINT(lat,long) 在数据库中查找附近的实体.我正在尝试在本教程的底部执行类似操作 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL .这是我得到的:

My goal is to use mysql POINT(lat,long) to find nearby entities in the database. I'm trying to do something like in the bottom of this tutorial http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL . Here is what I have got:

表:

CREATE TABLE mark (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) DEFAULT NULL,
loc POINT NOT NULL,
SPATIAL KEY loc (loc)
) ENGINE=MyISAM;

插入一些测试数据:

 INSERT INTO mark (loc,name) VALUES (POINT(59.388433,10.415039), 'Somewhere 1');
 INSERT INTO mark (loc,name) VALUES (POINT(63.41972,10.39856), 'Somewhere 2');

声明距离函数:

 DELIMITER $$
 CREATE FUNCTION `distance`
 (a POINT, b POINT)
 RETURNS double DETERMINISTIC
 BEGIN
 RETURN
 round(glength(linestringfromwkb(linestring(asbinary(a),
 asbinary(b)))));
 END $$
 DELIMITER;     

尝试使用函数搜索ex.:

Trying to use the function to search ex.:

 SELECT name, distance(mark.loc, GeomFromText( ' POINT(31.5 42.2) ' )) AS cdist
 FROM mark
 ORDER BY
 cdist limit 10;

或:

 SELECT DISTINCT
 dest.name,
 distance(orig.loc, dest.loc) as sdistance
 FROM
 mark orig,
 mark dest
 having sdistance < 10
 ORDER BY
 sdistance limit 10;

我遇到的问题是:ERROR 1367 (22007): Illegal non geometry 'aswkb(a@0)' value found during parsing，或者ERROR 1416 (22003): 无法从您发送到 GEOMETRY 字段的数据中获取几何对象

The problem I am getting is: ERROR 1367 (22007): Illegal non geometric 'aswkb(a@0)' value found during parsing, or ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

我似乎无法弄清楚如何解决这个问题.重要的是距离"函数可以动态使用.

I can not seem to figure out how to solve this. The important thing is that the 'distance' function can be used dynamically.

我也试过这个解决方案:在MYSQL中求两点之间的距离.(使用点数据类型)

I have also tried this solution: Find the distance between two points in MYSQL. (using the Point Datatype)

这是我的 mysql 版本 mysql Ver 14.14 Distrib 5.5.23，适用于 Linux (x86_64)，使用 readline 5.1

This is my mysql version mysql Ver 14.14 Distrib 5.5.23, for Linux (x86_64) using readline 5.1

希望有人的专业知识可以帮助我.干杯！

Hope someones expertise can help me. Cheers!

推荐答案

所以我最终得到了这个作为计算距离的查询，一个例子:

So I ended up with this as query for calculating distance, an example:

 SELECT  glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(63.424818,10.402457)))),GeomFromText(astext(PointFromWKB(POINT(663.422238,10.398996)))))))*100 
 AS distance;

我将它乘以 100 得到一个以公里为单位的近似值.结果不准确，但ok".如果有人知道更好的方法，请随时发表评论.

I am multiplying it by 100 to get an approximation in kilometers. The result is not exact, but "ok". If someone would know a better way, feel free to comment.

这篇关于使用 POINT 的 Mysql 空间距离 - 不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！