{"id":2108,"date":"2021-10-30T21:38:05","date_gmt":"2021-10-30T19:38:05","guid":{"rendered":"https:\/\/guven.atbakan.com\/blog\/?p=2108"},"modified":"2024-07-25T11:12:36","modified_gmt":"2024-07-25T08:12:36","slug":"calculate-distance-between-2-points-latitude-longitude-with-mysql-st_distance_sphere-method","status":"publish","type":"post","link":"https:\/\/guven.atbakan.com\/blog\/calculate-distance-between-2-points-latitude-longitude-with-mysql-st_distance_sphere-method\/","title":{"rendered":"Calculate distance between 2 points (latitude-longitude) with Mysql ST_Distance_Sphere method"},"content":{"rendered":"\n<p>If you want to measure distance between 2 points, you can use Mysql&#8217; <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/spatial-convenience-functions.html#function_st-distance-sphere\">ST_Distance_Sphere<\/a> method &#8211; comes with Mysql 5.7+.<\/p>\n\n\n\n<p>Assume that, you want to get closest user to given point: <code>41.0049823,28.7319855<\/code><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;?php\n$latitude = 41.0049823;\n$longitude = 28.7319855;\n\n$sql =  \"\nSELECT \n    users.id, \n    users.username, \n    ST_Distance_Sphere(point(users.longitude, users.latitude),point({$longitude}, {$latitude})) as distance \nFROM users\";<\/code><\/pre>\n\n\n\n<p>So you&#8217;ll get <code>distance<\/code> as meter unit. Not that, this method uses longitude-latitude pair. Not latitude-longitude. It&#8217;s important :) I don&#8217;t know why but I always use lat-lng convention.<\/p>\n\n\n\n<p>Let&#8217;s make another query: Get users within 100 meters for given point.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;?php\n$latitude = 41.0049823;\n$longitude = 28.7319855;\n\n$sql =  \"SELECT \n    users.id, \n    users.username, \n    ST_Distance_Sphere(point(users.longitude, users.latitude),point({$longitude}, {$latitude})) as distance \nFROM users \nWHERE distance &lt; 100\"<\/code><\/pre>\n\n\n\n<p>I was using some sinus, cosinus, tangant and so other functions before ST_Distance_Sphere. That query was running slow and sometimes it wasn&#8217;t result correct. Thanks ST_Distance_Sphere for making our life easier :)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you want to measure distance between 2 points, you can use Mysql&#8217; ST_Distance_Sphere method &#8211; comes with Mysql 5.7+. Assume that, you want to get closest user to given point: 41.0049823,28.7319855 So you&#8217;ll get distance as meter unit. Not that, this method uses longitude-latitude pair. Not latitude-longitude. It&#8217;s important :) I don&#8217;t know why [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[763],"tags":[770,772,774],"class_list":["post-2108","post","type-post","status-publish","format-standard","hentry","category-development-and-internet","tag-mysql-konum-uzaklik-en","tag-mysql-latitude-longitude-en","tag-mysql-location-radius-en"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_likes_enabled":true,"jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/posts\/2108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/comments?post=2108"}],"version-history":[{"count":12,"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/posts\/2108\/revisions"}],"predecessor-version":[{"id":2630,"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/posts\/2108\/revisions\/2630"}],"wp:attachment":[{"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/media?parent=2108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/categories?post=2108"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/guven.atbakan.com\/blog\/wp-json\/wp\/v2\/tags?post=2108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}