{"id":949,"date":"2016-07-14T14:07:30","date_gmt":"2016-07-14T18:07:30","guid":{"rendered":"https:\/\/www.sharpinnovations.com\/blog\/?p=949"},"modified":"2016-07-14T14:07:57","modified_gmt":"2016-07-14T18:07:57","slug":"querying-by-distance-in-mysql","status":"publish","type":"post","link":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/","title":{"rendered":"Querying by Distance in MySQL"},"content":{"rendered":"

Is your website doing everything possible to help your clients find you and get to you? One valuable tool that you can use to accomplish this important goal is to include a \u201cLocations\u201d page on your site. Suppose that your business is a chain of restaurants. In that case, a Locations page can tell customers which of your eateries is closest to the hungry person\u2019s present location. \u00a0If you don\u2019t yet have such a page, it\u2019s definitely something worth considering, and here\u2019s how programmers go about building it.<\/p>\n

Typically, such a page consists of a search field that allows their users to provide a location, and then returns a list of nearby destinations sorted by distance, often accompanied by a Google Map.<\/p>\n

Your only asset is a database table containing all possible locations with columns specifying latitude and longitude. When the user submits their location, you’ll likely utilize the Google Maps API to retrieve the coordinates from an address, which you can then use to compare against your database records. <\/p>\n

Conceptually, it’s simple. Query your table and do a little bit of math, and you’ll have your result set. However, if you’re like me, maybe spherical trigonometry isn’t in your area of expertise, and you need to turn to someone else for a drop-in solution. Well, here you go!<\/p>\n

$lat = 40.3337075; \/\/ user-submitted latitude\r\n$lon = -75.6374083; \/\/ user-submitted longitude\r\n$dist = 200; \/\/ search radius in miles\r\n\r\n$query = \"\r\n\tSELECT loc.*,\r\n\t\t3956 * 2 * ASIN(SQRT(POWER(SIN(($lat-ABS(latitude)) * PI()\/180\/2),2) + COS($lat * PI()\/180) * COS(ABS(latitude) * PI()\/180) * POWER(SIN(($lon-longitude) * PI()\/180\/2),2))) AS distance\r\n\tFROM locations_table AS loc\r\n\tWHERE longitude BETWEEN ($lon-$dist \/ ABS(COS(RADIANS($lat))*69)) AND ($lon+$dist \/ ABS(COS(RADIANS($lat)) * 69))\r\n\tAND latitude BETWEEN ($lat-($dist\/69)) AND ($lat+($dist\/69))\r\n\tGROUP BY loc.id\r\n\tHAVING distance < $dist\r\n\tORDER BY distance ASC\r\n\tLIMIT 5\r\n\";\r\n<\/code><\/pre>\n
Let’s look at this query a little more closely – but not so closely that our heads explode. This example assumes that you have a database table named “locations_table”, and columns named “latitude”, “longitude”, and “id.”<\/p>\n
Select all of the columns from table “loc.”<\/p>\n
SELECT loc.*,<\/code><\/pre>\n
In addition to the locations table, let’s select a custom value called “distance”, which is the calculated distance between the user-submitted location and the location of the current record. The following mathematical sorcery is the Flat Earth Society’s worst nightmare.<\/p>\n
3956 * 2 * ASIN(SQRT(POWER(SIN(($lat-ABS(latitude)) * PI()\/180\/2),2) + COS($lat * PI()\/180) * COS(ABS(latitude) * PI()\/180) * POWER(SIN(($lon-longitude) * PI()\/180\/2),2))) AS distance<\/code><\/pre>\n
Query data from the locations table, and give it the alias “loc”.<\/p>\n
FROM locations_table AS loc<\/code><\/pre>\n
Return only those records where the values of longitude are within a certain range, performing more wizardry that considers the curvature of the earth.<\/p>\n
WHERE longitude BETWEEN ($lon-$dist \/ ABS(COS(RADIANS($lat))*69)) AND ($lon+$dist \/ ABS(COS(RADIANS($lat)) * 69))<\/code><\/pre>\n
Also return only those records where the values of latitude are within a certain range.<\/p>\n
AND latitude BETWEEN ($lat-($dist\/69)) AND ($lat+($dist\/69))<\/code><\/pre>\n
Consider only those records with a distance value that is lower than the one you specified. This is very important to keep your query as light as possible.<\/p>\n
GROUP BY loc.id HAVING distance < $dist ORDER BY distance ASC<\/code><\/pre>\n
Finally, to maintain some semblance of efficiency, limit your result set.<\/p>\n
LIMIT 5<\/code><\/pre>\n
It is important to note that this is, by nature, a very heavy and inefficient query. It becomes exponentially slower as you increase the value of $dist, so I’d keep a nice sensible cap on both that value and LIMIT to keep things speedy.<\/p>\n
If your database table is burgeoning with locations and you run a reasonably high-traffic web site, you’re going to want to strongly consider implementing a caching solution that helps mitigate the load.<\/p>\n
We hope you find this bit of logic useful!<\/p>\n","protected":false},"excerpt":{"rendered":"
Is your website doing everything possible to help your clients find you and get to you? One valuable tool that you can use to accomplish this important goal is to include a \u201cLocations\u201d page on your site. Suppose that your business is a chain of restaurants. In that case, a Locations page can tell customers…<\/p>\n","protected":false},"author":12,"featured_media":959,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[112],"tags":[164,165,163,162],"class_list":["post-949","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming","tag-code","tag-location-page","tag-mysql","tag-querying-by-distance"],"yoast_head":"\nQuerying by Distance in MySQL - Sharp Innovations Blog<\/title>\n<meta name=\"description\" content=\"Is your website doing everything possible to help your clients find you and get to you? One valuable tool that you can use to accomplish this important goal is to include a \u201cLocations\u201d page on your site. Suppose that your business is a chain of restaurants. In that case, a Locations page can tell customers...\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Querying by Distance in MySQL - Sharp Innovations Blog\" \/>\n<meta property=\"og:description\" content=\"Is your website doing everything possible to help your clients find you and get to you? One valuable tool that you can use to accomplish this important goal is to include a \u201cLocations\u201d page on your site. Suppose that your business is a chain of restaurants. In that case, a Locations page can tell customers...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Sharp Innovations Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-07-14T18:07:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-07-14T18:07:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sharpinnovations.com\/blog\/wp-content\/uploads\/2016\/07\/Querying-by-Distance-in-MySQL-Header.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"400\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Chris Shontz\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Chris Shontz\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/\",\"url\":\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/\",\"name\":\"Querying by Distance in MySQL - Sharp Innovations Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/#website\"},\"datePublished\":\"2016-07-14T18:07:30+00:00\",\"dateModified\":\"2016-07-14T18:07:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/#\/schema\/person\/c882af0a57370cbf1da8c55806edc269\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sharpinnovations.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Querying by Distance in MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/#website\",\"url\":\"https:\/\/www.sharpinnovations.com\/blog\/\",\"name\":\"Sharp Innovations Blog\",\"description\":\"Web Design, Development, and Internet Marketing Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sharpinnovations.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/#\/schema\/person\/c882af0a57370cbf1da8c55806edc269\",\"name\":\"Chris Shontz\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sharpinnovations.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1aff591b17d663da7aaf9dd63644085bf71609c57988a73506a80fbca9a6fdc8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1aff591b17d663da7aaf9dd63644085bf71609c57988a73506a80fbca9a6fdc8?s=96&d=mm&r=g\",\"caption\":\"Chris Shontz\"},\"url\":\"https:\/\/www.sharpinnovations.com\/blog\/author\/cshontz\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Querying by Distance in MySQL - Sharp Innovations Blog","description":"Is your website doing everything possible to help your clients find you and get to you? One valuable tool that you can use to accomplish this important goal is to include a \u201cLocations\u201d page on your site. Suppose that your business is a chain of restaurants. In that case, a Locations page can tell customers...","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"Querying by Distance in MySQL - Sharp Innovations Blog","og_description":"Is your website doing everything possible to help your clients find you and get to you? One valuable tool that you can use to accomplish this important goal is to include a \u201cLocations\u201d page on your site. Suppose that your business is a chain of restaurants. In that case, a Locations page can tell customers...","og_url":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/","og_site_name":"Sharp Innovations Blog","article_published_time":"2016-07-14T18:07:30+00:00","article_modified_time":"2016-07-14T18:07:57+00:00","og_image":[{"width":800,"height":400,"url":"https:\/\/www.sharpinnovations.com\/blog\/wp-content\/uploads\/2016\/07\/Querying-by-Distance-in-MySQL-Header.jpg","type":"image\/jpeg"}],"author":"Chris Shontz","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Chris Shontz","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/","url":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/","name":"Querying by Distance in MySQL - Sharp Innovations Blog","isPartOf":{"@id":"https:\/\/www.sharpinnovations.com\/blog\/#website"},"datePublished":"2016-07-14T18:07:30+00:00","dateModified":"2016-07-14T18:07:57+00:00","author":{"@id":"https:\/\/www.sharpinnovations.com\/blog\/#\/schema\/person\/c882af0a57370cbf1da8c55806edc269"},"breadcrumb":{"@id":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sharpinnovations.com\/blog\/2016\/07\/querying-by-distance-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sharpinnovations.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Querying by Distance in MySQL"}]},{"@type":"WebSite","@id":"https:\/\/www.sharpinnovations.com\/blog\/#website","url":"https:\/\/www.sharpinnovations.com\/blog\/","name":"Sharp Innovations Blog","description":"Web Design, Development, and Internet Marketing Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sharpinnovations.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sharpinnovations.com\/blog\/#\/schema\/person\/c882af0a57370cbf1da8c55806edc269","name":"Chris Shontz","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sharpinnovations.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1aff591b17d663da7aaf9dd63644085bf71609c57988a73506a80fbca9a6fdc8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1aff591b17d663da7aaf9dd63644085bf71609c57988a73506a80fbca9a6fdc8?s=96&d=mm&r=g","caption":"Chris Shontz"},"url":"https:\/\/www.sharpinnovations.com\/blog\/author\/cshontz\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/posts\/949","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/comments?post=949"}],"version-history":[{"count":15,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/posts\/949\/revisions"}],"predecessor-version":[{"id":969,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/posts\/949\/revisions\/969"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/media\/959"}],"wp:attachment":[{"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/media?parent=949"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/categories?post=949"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sharpinnovations.com\/blog\/wp-json\/wp\/v2\/tags?post=949"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}