MySQL - Find Closest Match in range of numbers

Question

I'm working on a graph type application and I'm looking to find the best solution within a x,y axis start to end point.

MySQL Data:

Say I have the following: starting_x: 200 starting_y: 150 ending_x: 500 ending_y: 605

So I'm wanting to find the closest match between the above numbers to the database.

My query I'm working with now:

SELECT * FROM `graph` ORDER BY `start_pos_x`,`start_pos_y`,`end_pos_x`,`end_pos_y` ASC

I know this is not even close to what I'm trying to do but i'm having a hard time finding a solution on here.

Thanks!

Maybe your question is related: https://stackoverflow.com/questions/2234204/latitude-longitude-find-nearest-latitude-longitude-complex-sql-or-complex-calc — Brain90, Jul 21 '17 at 03:41
How do you define "closest" for your comparison? Minimum total distance between starting points and between ending points? Minimum area of the trapezoid formed by connecting endpoints? Something else? — rd_nielsen, Jul 21 '17 at 04:06
minimum total distance so the closest x, y to the closest x, ,y in database. — ahinkle, Jul 21 '17 at 04:11
For next time, please don't provide pictures of data. Just provide the data (see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query). — Strawberry, Jul 21 '17 at 06:29

score 1 · Accepted Answer · answered Jul 21 '17 at 04:11

This seems to be an algorithm question. First, you must define the closest match between the above numbers to the database. For example, I can define it as to find "the lowest sum of the squares of the differences between Xs and of Ys." Then, the solution might be something like:

Select 
  * 
From 
  `graph` 
Order by
  Power(starting_x-200, 2) + Power(starting_y-150, 2) 
  + Power(ending_x-500, 2) + Power(ending_y-605, 2) ASC

MySQL - Find Closest Match in range of numbers

1 Answers1