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;
``````

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;
``````