0

We have a MySQL stored procedure that accepts input as latitude and longitude. This stored procedure has to return back the 3 closest area to this lat/long and respective distance in kilometre from the given lat/long.

The area table has id, name, position. The position is of point type having POINT(latitude, longitude). I have tried following solution:

SELECT
  id,
  name,
  X(position) AS 'latitude',
  Y(position) AS 'longitude',
  (
    GLength(
      LineStringFromWKB(
        LineString(
          position, 
          GeomFromText('POINT(@lat @long)')
        )
      )
    )
  )
  AS distance
FROM areas
ORDER BY distance ASC
limit 3;

Few issues in the above solution:

  1. Query is slow. It's taking 200 millisecond for 22k records. If I remove order by clause, performance improves drastically i.e. it takes only 20 milliseconds. I have created spatial index for position column but didn't help. Can someone suggest how to optimize this query?
  2. I am not sure how to convert this distance in kilometer?

Here is screenshot of desc on the above select statement: enter image description here

Sahil Sharma
  • 3,847
  • 6
  • 48
  • 98
  • Why do you think that this is slow? It looks pretty fine - otherwise try to inspect the execution plan. And if you have the coordinates of your start and end point, it should be simple to calculate the distance afterwards, as seen in https://stackoverflow.com/questions/365826/calculate-distance-between-2-gps-coordinates – Nico Haase Jul 02 '18 at 11:50
  • @NicoHaase: It's not leveraging benefit of spatial index created on position column. The query is scanning all the rows of the table. Attached the output of desc on the above select in the question. – Sahil Sharma Jul 02 '18 at 12:03
  • @NicoHaase: If I remove order by clause, performance improves drastically i.e. it takes only 20 milliseconds – Sahil Sharma Jul 02 '18 at 12:07

0 Answers0