0

Hi I want to make a query to a Mysql DB 5.1 version. There must be a distance calculation from a give point to a segment defined by two points inside the DB. I have a point to distance algorithm very similar to this one:

Shortest distance between a point and a line segment

This is JS but can work as an example:

function sqr(x) { return x * x }
function dist2(v, w) { return sqr(v.x - w.x) + sqr(v.y - w.y) }
function distToSegmentSquared(p, v, w) {
var l2 = dist2(v, w);
if (l2 == 0) return dist2(p, v);
var t = ((p.x - v.x) * (w.x - v.x) + (p.y - v.y) * (w.y - v.y)) / l2;
if (t < 0) return dist2(p, v);
if (t > 1) return dist2(p, w);
return dist2(p, { x: v.x + t * (w.x - v.x),
                y: v.y + t * (w.y - v.y) });
}
function distToSegment(p, v, w) { return Math.sqrt(distToSegmentSquared(p,      v, w)); }

And the DB query the idea is the following:

SELECT
id, (
//should it calculate the distance here?
)
) AS distance
FROM markers
ORDER BY distance
LIMIT 0 , 20;

Should I declare a procedure?

Any help is very appreciated. Thanks

EDIT: data structure:

Table markers:
id | x1 | y1 | x2 | y2            
----------------------
   |    |    |    |
Community
  • 1
  • 1
Jaimesg
  • 33
  • 5
  • Please add the data structure on which you want to execute your calculation to your post. – GreenTurtle Jan 21 '16 at 15:04
  • Hi, I've added the data structure. Thanks – Jaimesg Jan 21 '16 at 15:50
  • Not an answer but maybe a point to start for you: If you are on MySQL >= 5.6.1 check https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-distance – GreenTurtle Jan 21 '16 at 16:08
  • Thanks, I forgot to mention it's mySQL 5.1 I think it doesn't allow this function. Do you think I should create a procedure and then call it from the SELECT part of the query? – Jaimesg Jan 21 '16 at 16:19

0 Answers0