MySQL query to get route between 2 lat-lng coordinates

Question

I'm looking for a query to get the route between two points.

I have this link, let's say:

example.com/any? lat1 = 31.0000 & lng1 = 31.0000 & lat2 = 31.0000 & lng2 = 31.0000

So inside the function, I want to write a query that goes to the table example,

id | lat1    | lng1    | lat2    | lng2     | time | distance_between_inKm
1  | 31.0000 | 31.0000 | 31.0000 | 31.0000   | 30   | 20

So now I want to go to the link and get the nearest || Closest* route or row

I'm getting these from link lat1 & lng1 and lat2 & lng2 so I want to get the closest for the two.

So what I want is that I have a table contains static data for polylines and info for 2 points so I want to use them like Google Matrix api but from my server, let's say:

Update

I tried this query but it ignore the AND:

    SELECT id,distance_between,time_between,start_latitude, start_longitude,end_latitude,end_longitude, SQRT(
        POW(69.1 * (start_latitude - 31.908482676577364), 2) +
        POW(69.1 * (35.1666776731924 - start_longitude) * COS(start_latitude / 57.3), 2)) 

AND
        SQRT(
        POW(69.1 * (end_latitude - 31.966051), 2) +
        POW(69.1 * (35.894587 - end_longitude) * COS(end_latitude / 57.3), 2))
        AS distance
    FROM matrix_api USE INDEX (start_latitude,start_longitude,end_latitude,end_longitude,distance_between,time_between) HAVING distance < 0.2 ORDER BY distance LIMIT 1;

So I want something like that give input 1 and 2 and give me the row that has the 2

Close for the start latlngs and the end latlngs at the same time

Update

I tried this too, no luck still:

    SELECT id,distance_between,time_between,start_latitude, start_longitude,end_latitude,end_longitude, 
SQRT(
    POW(69.1 * (start_latitude - 32.016659), 2) +
    POW(69.1 * (35.727839 - start_longitude) * COS(start_latitude / 57.3), 2) AND

POW(69.1 * (end_latitude - 31.966051), 2) +
    POW(69.1 * (35.894587 - end_longitude) * COS(end_latitude / 57.3), 2)

) 

    AS distance
FROM matrix_api USE INDEX (start_latitude,start_longitude,end_latitude,end_longitude,distance_between,time_between) HAVING distance < 0.2 ORDER BY distance LIMIT 1;

Sample input:

lat1=0.0000 lng1=0.0000
lat2=0.0000 lng2=0.0000

Output:

The row that I already have in table Distance between: 0.0 And time between: 0.0

So inside table we have the lat1 and lng1 lat2 lng2 And distance between. I want to get the row with nearest lat1 lng1 in same time nearest to lat2 lng2. But here the 2 are in same row saved so I want it like Google API Matrix closest distance between inside the row I need or found.

Answer 1

A small change in your query:

SELECT id,distance_between,time_between
,start_latitude, start_longitude,end_latitude,end_longitude
FROM
( 
 SELECT id,distance_between,time_between
 ,start_latitude, start_longitude,end_latitude,end_longitude,
 SQRT(
 POW(69.1 * (start_latitude - 31.908482676577364), 2) +
 POW(69.1 * (35.1666776731924 - start_longitude) * COS(start_latitude / 57.3), 2)
 ) as startlatlngDistance
 ,SQRT(
 POW(69.1 * (end_latitude - 31.966051), 2) +
 POW(69.1 * (35.894587 - end_longitude) * COS(end_latitude / 57.3), 2))
 AS endlatlngDistance
 FROM matrix_api 
 USE INDEX (start_latitude,start_longitude,end_latitude
 ,end_longitude,distance_between,time_between) 
) as A
WHERE startlatlngDistance < 0.2 AND endlatlngDistance < 0.2 
ORDER BY startlatlngDistance LIMIT 1;