how to sort rows by a distance from a given point, mysql?

Question

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?

score 6 · Accepted Answer · answered Dec 03 '12 at 04:51

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

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

I've used the great circle equation to do these types of calculations in the past. I'm not sure how the performance compares but it might be worth trying it and comparing.

Here is a good SO post that goes over how to do it in MySQL.

edited May 23 '17 at 10:27

Community

1
1

answered Jun 21 '10 at 16:28

Abe Miessler

82,532
99
305
486

1

it does not use spatial at all, too... what is it (spatial index) for, then? – Valentin Golev Jun 21 '10 at 17:05
Yours is the only type of solution that would potentially use the spatial index. I'm just suggesting another method worth trying to see how the performance compares. – Abe Miessler Jun 21 '10 at 17:16
oh. I started with a method, similar to yours, and got a lot worst perfomance – Valentin Golev Jun 22 '10 at 07:18

score -1 · Answer 3 · edited May 23 '17 at 10:32

-1

Have a look at these questions:

Finding Cities within 'X' Kilometers (or Miles)

edited May 23 '17 at 10:32

Community

1
1

answered Jun 21 '10 at 17:00

Craig Trader

15,507
6
37
55

it's not sorting and it does not use spatial at all – Valentin Golev Jun 21 '10 at 17:05

how to sort rows by a distance from a given point, mysql?

3 Answers3