CODEWITHSUNDEEP
mysqldatabasegis
coderunner
coderunner

Reputation: 935

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;

Upvotes: 1

Views: 335

Answers (1)

e4c5
e4c5

Reputation: 53734

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;

Upvotes: 1

Related Questions