按照距离在mysql中使用st_distance排序用户 [英] Sorting users by distance in mysql using st_distance

问题描述

我在这方面做了一些阅读，但几乎没有关于在mysql中使用点和st_distance函数的信息。我想这是有道理的，因为根据 http://bugs.mysql.com/bug。 php？id = 70494 直到去年中途才有记录。几乎我见过的每个解决方案都使用单独的长列和拉列，然后将它们放入如下所示的hasrsine公式中： MySQL PHP中的排序距离尽管由于几何数据类型和空间函数的存在，这似乎是一种非常混乱的方式。

我的表到目前为止是：

用户名 - varchar

位置 - 点（1 1）或其他像那样

我想选择一个用户，然后向他们显示最近的10个用户。是否有可能完全通过使用点和st_distance函数的SQL来完成此操作，还是需要进行一些更改并使用haversine公式来代替？ 解决方案

是否有可能完全通过使用point和st_distance函数的SQL来完成此操作，或者我将不得不做一些更改并使用haversine公式吗？

如几何类：

几何是根类的层次结构。它是一个无法实现的类，但具有许多属性，如下面的列表所述，这些属性对于从任何Geometry子类创建的所有几何值都是通用的。

 [  deletia  ] 

假设欧几里德（平面）几何体完成所有计算。

 [  deletia  ] 

例如，在不同的坐标系中，即使物体具有相同的坐标，两个物体之间的距离也可能不同，因为 平面 坐标系和 geocentric 系统上的距离（地球表面上的坐标）是不同的。

因此，MySQL的空间扩展（包括它的 ST_Distance（） 函数）不能用于计算 geodesics ，例如大圆距离 a>，这就是你所追求的。正如你推断的那样，你必须使用公式，例如 Haversine 。

在Google Developers网站上有一个很好的教程：创建使用PHP，MySQL& Google地图。

I've done a bit of reading on this but there's hardly any information on doing it using points and the st_distance function in mysql. I suppose this makes sense since according to http://bugs.mysql.com/bug.php?id=70494 it wasn't even documented until halfway through last year. Almost every solution I've seen instead uses separate long and lat columns, and then puts them into the haversine formula like this: sorting distance in MySQL PHP This seems like a really messy way of doing it though due to the existance of geometry data types and the spatial functions.

My table so far is:

Username - varchar

Location - Point(1 1) or something like that

I want to choose one user and then display the nearest 10 or so users to them. Is it possible to do this entirely through SQL using points and the st_distance function, or will I have to make some changes and use the haversine formula instead?

解决方案

Is it possible to do this entirely through SQL using points and the st_distance function, or will I have to make some changes and use the haversine formula instead?

As documented under Geometry Class:

Geometry is the root class of the hierarchy. It is a noninstantiable class but has a number of properties, described in the following list, that are common to all geometry values created from any of the Geometry subclasses.

[ deletia ]

All calculations are done assuming Euclidean (planar) geometry.

[ deletia ]

For example, in different coordinate systems, the distance between two objects may differ even when objects have the same coordinates, because the distance on the planar coordinate system and the distance on the geocentric system (coordinates on the Earth's surface) are different things.

Therefore MySQL's spatial extensions (including its ST_Distance() function) cannot be used to calculate geodesics such as great-circle distance, which is what you are after. You will, as you infer, have to use a formula such as Haversine instead.

There is a good tutorial on the Google Developers website: Creating a Store Locator with PHP, MySQL & Google Maps.

这篇关于按照距离在mysql中使用st_distance排序用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！