CODEWITHSUNDEEP
mysqlgeospatialdistancespatial
Valentin Golev
Valentin Golev

Reputation: 10095

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

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?

Upvotes: 5

Views: 4152

Answers (3)

Nguyen Viet Anh
Nguyen Viet Anh

Reputation: 96

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

Upvotes: 6

Craig Trader
Craig Trader

Reputation: 15679

Have a look at these questions:

Upvotes: -1

Abe Miessler
Abe Miessler

Reputation: 85086

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.

Upvotes: 0

Related Questions