如何按距给定点的距离对行进行排序，mysql？

发布于 2024-09-06 03:33:50 字数 680 浏览 7 评论 0原文

我试图从表中得到 100 分，并且到给定点的距离最近。

我正在使用

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` FROM `ip_group_city` ORDER BY distance LIMIT 100

（是的，这很痛苦。我刚刚用谷歌搜索过。我不知道如何正确测量MySQL中的距离）

执行需要很长时间。 EXPLAIN 表示不存在 possible_keys。

我在 pt 列上创建了一个 SPATIAL 索引：

CREATE SPATIAL INDEX sp_index ON  ip_group_city (pt);

虽然我真的不知道如何正确使用它。你能帮我吗？

原文

I'm trying to get 100 points from my table with a lowest distance to a given point.

I'm using

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` FROM `ip_group_city` ORDER BY distance LIMIT 100

(Yeah, that's painful. I've just googled it. I have no idea how to measure distance in MySQL correctly)

It takes very long time for execute. EXPLAIN says that there are no possible_keys.

I created a SPATIAL index on the pt column:

CREATE SPATIAL INDEX sp_index ON  ip_group_city (pt);

Though I don't really know how to use it correctly. Can you please help me?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

给我一枪 2024-09-13 03:33:50

因为您没有 WHERE 子句，因此没有受影响的索引。我认为您应该通过添加使用 MBR_（MySQL 5.0 或更高版本）或 ST_ 函数（MySQL 5.6 或更高版本）来改进此查询。
像这样的东西：

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` 
FROM `ip_group_city` 
WHERE
MBRWithin(
        pt, -- your point
        GeomFromText('Polygon( -- your line (in polygon format) from pt to target point 
                        (
                            #{bound.ne.lat} #{bound.ne.lng}, --North East Lat - North East Long
                            #{bound.ne.lat} #{bound.sw.lng}, --North East Lat - South West Long
                            #{bound.sw.lat} #{bound.sw.lng}, --
                            #{bound.sw.lat} #{bound.ne.lng},
                            #{bound.ne.lat} #{bound.ne.lng}
                        )
                    )')
      )
ORDER BY distance LIMIT 100

Because you don't have WHERE clause therefore no affected index. I think you should improve this query by add using MBR_ (MySQL 5.0 or later) or ST_ functions (MySQL 5.6 or later).
Something like:

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` 
FROM `ip_group_city` 
WHERE
MBRWithin(
        pt, -- your point
        GeomFromText('Polygon( -- your line (in polygon format) from pt to target point 
                        (
                            #{bound.ne.lat} #{bound.ne.lng}, --North East Lat - North East Long
                            #{bound.ne.lat} #{bound.sw.lng}, --North East Lat - South West Long
                            #{bound.sw.lat} #{bound.sw.lng}, --
                            #{bound.sw.lat} #{bound.ne.lng},
                            #{bound.ne.lat} #{bound.ne.lng}
                        )
                    )')
      )
ORDER BY distance LIMIT 100

回复收藏 0 原文