I’m using a mysql database where I store map markers

Asked

Viewed 113 times

1

I’m using a mysql database where I store bookmarks from a map (in the table store the latitude and longitude of these bookmarks) my question is, I want to make a select by picking all bookmarks that are in a proximity radius of a point (or marker)I found this solution: en.stackoverflow.com/questions/55669/... however I confess not to have understood the solution suggested, so I would like to help in this issue.

  • Matheus, give me a light here!!!

1 answer

2

The solution would be from its central point to trace a range of it, which would be the radius of its circle. The Haversine formula returns in the column the distance of the searched point from its starting point. In SELECT the formula would be in a column and the search radius would be in your WHERE.

SELECT (calculation) The distances FROM table WHERE distance <= 5

Remembering the distance from the WHERE is in kilometers, if you want to search in meters would be 100m = 0.1

  • Matheus Zenker,.

  • Take a look at this link http://gis.stackexchange.com/questions/4906/why-is-law-of-cosinesmore-preferable-than-haversine-when-calculating-distance-b

  • Matheus,why when I give a SELECT id_marker, ( 3959 * acos( cos( radians(37) * cos( radians(-18.58589) ) * cos( radians( -46.514950 ) - radians(-122) + sin( radians(37) ) * sin(radians(-18.58589)) ) ) AS Distance FROM tb_marker HAVING Distance <= 5 ORDER BY Distance LIMIT 0 , 10 He returns me nothing!!

  • 1

    I created a chart to take your case. And worked perfectly the error in your query above is at the center point and not in the points that are registered. Your query would be +- like this: SELECT *, (6371 * acos( cos(radians(-18.585890)) * cos(radians(lat)) * cos(radians(-46.514950) - radians(lng)) + sin(radians(-18.585790)) * sin(radians(lat)) )) AS Distance FROM table HAVING Distance <= 10

  • 1

    Note that the other values of the formula are based on their central point and the points that will be searched. SELECT *, (6371 * acos( cos(radians(LAT_PESQUISA_PONTO_CENTRAL)) * cos(radians(lat)) * cos(radians(LNG_PESQUISA_PONTO_CENTRAL) - radians(lng)) + sin(radians(LAT_PESQUISA_PONTO_CENTRAL)) * sin(radians(lat)) )) AS Distance FROM table HAVING Distance <= 5

Browser other questions tagged

You are not signed in. Login or sign up in order to post.