Mysql POINT列用于距离搜索(Mysql POINT column for distance search)

Mysql POINT列用于距离搜索(Mysql POINT column for distance search)

是的，我知道这个问题曾经问过很多时间，但所有看起来都过时了2012年，问题/问题的基础，我试图用列POINT执行经典搜索距离，但我有一些无法解决的麻烦..

我的POINT列是正常的吗？

0x00000000010100000085B1852007052040C0B2D2A414684840

这是我的所有步骤，我无法看到什么是错的，我根据最后的堆栈问题/答案做了。

我使用mariadb 10和Heideisql gui。

我有两个列lat和lon，我创建了一个geopoints POINT列，填充像这样的geopoint：

UPDATE geoFRA SET geopoints = GeomFromText(CONCAT('POINT (', lon, ' ', lat, ')'))

之后我的geopoints列如下所示：

0x00000000010100000085B1852007052040C0B2D2A414684840

然后我尝试以2个方式执行查询，首先尝试：

SET@lat = 48.88; SET@lon = 2.34; SELECT * FROM geoFRA WHERE MBRContains(LineFromText(CONCAT( '(' , @lon + 700 / ( 111.1 / cos(RADIANS(@lon))) , ' ' , @lat + 700 / 111.1 , ',' , @lon - 700 / ( 111.1 / cos(RADIANS(@lat))) , ' ' , @lat - 700 / 111.1 , ')' ) ,geopoints)

第二次尝试：

SET@lat = 48.88; SET@lon = 2.34; SET @kmRange = 172; -- = 50 Miles SELECT *, (3956 * 2 * ASIN(SQRT(POWER(SIN((@lat - abs(`lat`)) * pi()/180 / 2),2) + COS(@lat * pi()/180 ) * COS(abs(`lat`) * pi()/180) * POWER(SIN((lon - `lon`) * pi()/180 / 2), 2)))) as distance FROM `geoFRA` WHERE MBRContains(LineString(Point(@lat + @kmRange / 111.1, @lon + @kmRange / (111.1 / COS(RADIANS(@lat)))), Point(@lat - @kmRange / 111.1, @lon - @kmRange / (111.1 / COS(RADIANS(@lat))))), `geopoints`) Order By distance

我开始认为有一些mariadb不兼容？！ 还是我错过了什么？

感谢任何帮助..，flau

yes i know this question ever asked plenty of time but all seems outdated from 2012, base of thoses question/ansewers , i tried to perform the classic search distance with column POINT but i have some trouble unresolvable..

is normal my POINT column looks like this ?

0x00000000010100000085B1852007052040C0B2D2A414684840

Here is all my steps, i am not able to see whats wrong, i did based from last stack questions/answers.

I use mariadb 10 with Heideisql gui.

i have 2 colums lat and lon , i created a geopoints POINT column, populate geopoint like this:

UPDATE geoFRA SET geopoints = GeomFromText(CONCAT('POINT (', lon, ' ', lat, ')'))

After that my geopoints column looks like this :

0x00000000010100000085B1852007052040C0B2D2A414684840

Then i try to perform the query in 2 maners , first try :

SET@lat = 48.88; SET@lon = 2.34; SELECT * FROM geoFRA WHERE MBRContains(LineFromText(CONCAT( '(' , @lon + 700 / ( 111.1 / cos(RADIANS(@lon))) , ' ' , @lat + 700 / 111.1 , ',' , @lon - 700 / ( 111.1 / cos(RADIANS(@lat))) , ' ' , @lat - 700 / 111.1 , ')' ) ,geopoints)

and second try :

SET@lat = 48.88; SET@lon = 2.34; SET @kmRange = 172; -- = 50 Miles SELECT *, (3956 * 2 * ASIN(SQRT(POWER(SIN((@lat - abs(`lat`)) * pi()/180 / 2),2) + COS(@lat * pi()/180 ) * COS(abs(`lat`) * pi()/180) * POWER(SIN((lon - `lon`) * pi()/180 / 2), 2)))) as distance FROM `geoFRA` WHERE MBRContains(LineString(Point(@lat + @kmRange / 111.1, @lon + @kmRange / (111.1 / COS(RADIANS(@lat)))), Point(@lat - @kmRange / 111.1, @lon - @kmRange / (111.1 / COS(RADIANS(@lat))))), `geopoints`) Order By distance

I begin to think there is some mariadb incompatibility ?! or did i miss something?

thanks for any help.., flau

最满意答案

CREATE TABLE geoFRA (id int NOT NULL, geopoints point NOT NULL); INSERT INTO geoFRA (id, geopoints) VALUES (1, geomFromText('POINT(48 2)')), (2, geomFromText('POINT(48 3)')), (3, geomFromText('POINT(48.88 2.34)')), (4, geomFromText('POINT(49 2)')), (5, geomFromText('POINT(49 3)')); SET @p=geomFromText('POINT(48.88 2.34)'); SELECT X(geopoints), Y(geopoints), asText(geopoints), ST_Distance(geopoints, @p) as d FROM geoFRA ORDER BY d;

这将返回按距离排序的地理输出。 使用没有X（）的地理输出，Y（）和asText（）以标准二进制（WKB）格式返回它们： http ：//dev.mysql.com/doc/refman/5.7/en/gis-data-formats 的.html＃GIS的WKB格式

CREATE TABLE geoFRA (id int NOT NULL, geopoints point NOT NULL); INSERT INTO geoFRA (id, geopoints) VALUES (1, geomFromText('POINT(48 2)')), (2, geomFromText('POINT(48 3)')), (3, geomFromText('POINT(48.88 2.34)')), (4, geomFromText('POINT(49 2)')), (5, geomFromText('POINT(49 3)')); SET @p=geomFromText('POINT(48.88 2.34)'); SELECT X(geopoints), Y(geopoints), asText(geopoints), ST_Distance(geopoints, @p) as d FROM geoFRA ORDER BY d;

This returns the geopoints ordered by distance. Using geopoints without X(), Y() and asText() returns them in the Well-Known Binary (WKB) format: http://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html#gis-wkb-format