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

问题描述

我的目标是使用mysql POINT（lat，long）在数据库中查找附近的实体。我正在尝试在本教程的底部做些什么 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL 。这是我得到的：

Table：

  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'）; 
  

声明距离函数：

<$ p $
CREATE FUNCTION`distance`
（a POINT，b POINT）
RETURNS double确定性
BEGIN
RETURN
round（glength（linestringfromwkb（linestring（asbinary（a），
asbinary（b）））））;
END $$
DELIMITER;

尝试使用该函数搜索例如：

  SELECT name，distance（mark.loc，GeomFromText（'POINT（31.5 42.2）'））AS cdist 
 FROM mark 
 ORDER BY 
 cdist限制10; 
  

或：

  SELECT DISTINCT 
 dest.name，
 distance（orig.loc，dest.loc）as sdistance 
 FROM 
 mark orig，
 mark dest 
具有sdistance< 10 
 ORDER BY 
 sdistance limit 10; 
  

我遇到的问题是：错误1367（22007）：非法非几何'aswkb（a @ 0）在解析过程中找到的值或
ERROR 1416（22003）：无法从发送到GEOMETRY字段的数据获取几何对象

我无法看到弄清楚如何解决这个问题。重要的是可以动态地使用'距离'功能。

我也试过这个解决方案：查找MYSQL中两点之间的距离。 （使用Point Datatype）

这是我的mysql版本mysql Ver 14.14 Distrib 5.5.23，用于Linux（x86_64）使用readline 5.1

希望有人能帮助我。干杯！

解决方案

所以我最终以此为查询计算距离，举个例子：

  SELECT glength（LineStringFromWKB（线段形式（GeomFromText（astext（PointFromWKB（POINT（63.424818,10.402457）））），GeomFromText（astext（PointFromWKB（POINT（663.422238,10.398996 ）））））））* 100 
 AS距离; 
  

我将它乘以100得到以公里为单位的近似值。结果并不确切，但确定。如果有人会知道更好的方法，请随时发表评论。

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:

Table:

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;

Inserting some test-data:

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

Declaring the distance function:

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

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;

or:

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

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.

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

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;

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屋！