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?

Answer 1

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.

Answer 2

Have a look at these questions:

• http://stackoverflow.com/questions/808963/finding-cities-within-x-kilometers-or-miles