coderunner coderunner - 3 months ago 12
MySQL Question

Calculating nearest places using Mysql GIS features

We have 2 tables which store trip requests one for customer and one from driver. We have to show the nearest drivers to the customers and nearest customers to the drivers using MYSQL 5.7 GIS feature.

Both the tables store latitude and longitude of the requests.

So if a driver requests for a trip I want to calculate distances and show the nearest customers in 10km radius and their locations to the driver from the customer request table.

I tried the

st_distance_sphere
which works good for
(Point,Point)
.
But if I pass in a
multipoint
and a
point
it returns zero.

Shoudn't
multipoint,point
return distances between all points in first place to the point in 2nd place?


Please correct if I am wrong.The query I tried is below.

SELECT
round(
st_distance_sphere(
st_geomfromtext(
'Multipoint(72.87765590000004 17.385044,73.8567436 18.5204303)'
),
st_geomfromtext(
'POINT (73.8567436 18.5204303)'
)
)
) as dist;

Answer

It returns the closest distance between the two geometries. In your multipoint you have

Multipoint(72.87765590000004 17.385044,73.8567436 18.5204303)

The second set of points here are exactly the as

POINT (73.8567436 18.5204303)'

So a distance of 0 is the correct response.

If you want to find the distance of a given point to multiple different points, you would need to have them as separate records in the table and have a query like this:

SELECT 
  round(
    st_distance_sphere(some_point-field, 
      st_geomfromtext(
         'POINT (73.8567436 18.5204303)'
      )
    )
  ) as dist;
Comments