{"id":250,"date":"2018-01-13T19:27:59","date_gmt":"2018-01-13T18:27:59","guid":{"rendered":"https:\/\/numa-bord.com\/miniblog\/?p=250"},"modified":"2022-05-26T14:23:59","modified_gmt":"2022-05-26T12:23:59","slug":"doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km","status":"publish","type":"post","link":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/","title":{"rendered":"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \u00ab\u00a0XX\u00a0\u00bb km"},"content":{"rendered":"

Il y’a quelques mois je pr\u00e9sentais une fonction PHP permettant de calculer la distance entre 2 coordonn\u00e9es GPS (latitude et longitude)<\/a>. Coordonn\u00e9es qui peuvent par exemple \u00eatre r\u00e9cup\u00e9rer via l’API Google Map<\/a><\/p>\n

Nous allons voir dans cette article, comment r\u00e9cup\u00e9rer toutes les enregistrements d’une table qui sont situ\u00e9 \u00e0 moins de \u00ab\u00a050km\u00a0\u00bb d’une position pr\u00e9cise. Il nous faut donc les coordon\u00e9es de la position pour laquelle on fait la recherche, ainsi qu’une table avec les colonnes \u00ab\u00a0lat\u00a0\u00bb et \u00ab\u00a0lng\u00a0\u00bb<\/p>\n

La formule permettant de faire le calcul (en km) directement en MySql et la suivante : <\/p>\n

\/\/la formule utilisant nos coordon\u00e9es "$lat" et "$lng" ainsi que les colonne "lat" et "lng" de la table "table"<\/span>
\n$sqlDistance<\/span> =<\/span> '(6378 * acos(cos(radians('<\/span> .<\/span> $lat<\/span> .<\/span> ')) * cos(radians(table.lat)) * cos(radians(table.lng) - radians('<\/span> .<\/span> $lng<\/span> .<\/span> ')) + sin(radians('<\/span> .<\/span> $lat<\/span> .<\/span> ')) * sin(radians(table.lat))))'<\/span>;<\/span><\/div><\/div>\n

Formule utilisable pour rechercher les enregistrements inf\u00e9rieur \u00e0 50km de notre position, via le requ\u00eate suivante :<\/p>\n

$requete<\/span> =<\/span> 'SELECT * FROM table WHERE '<\/span>.<\/span>$sqlDistance<\/span>.<\/span>' < 50 '<\/span>;<\/span><\/div><\/div>\n

Si l’on travaille sous Symfony et avec Doctrine, en ajoutant notre condition dans un \u00ab\u00a0querybuilder\u00a0\u00bb comme ci-dessous \u00e7a ne va pas fonctionner.<\/p>\n

\/\/Imaginons que nous sommes dans une requ\u00eate classique cr\u00e9e avec le querybuilder $qb <\/span>
\n\/\/On veut rajouter la condition de la distance (en utilisant une $distance variable dans ce cas).<\/span>
\n$qb<\/span>-><\/span>andWhere<\/span>(<\/span>""<\/span> .<\/span> $sqlDistance<\/span> .<\/span> " < :distance"<\/span>)<\/span>-><\/span>setParameter<\/span>(<\/span>'distance'<\/span>,<\/span> $distance<\/span>)<\/span>;<\/span><\/div><\/div>\n

Doctrine ne connait pas les diff\u00e9rentes fonctions de calculs \u00ab\u00a0acos\u00a0\u00bb, \u00ab\u00a0cos\u00a0\u00bb, \u00ab\u00a0radian\u00a0\u00bb et \u00ab\u00a0sin\u00a0\u00bb utilis\u00e9es dans la formule. Nous allons les importer gr\u00e2ce \u00e0 la biblioth\u00e8que DoctrineExtensions<\/a>. Pour cela, il suffit de faire un petit coup de composer sur notre projet : <\/p>\n

composer require beberlei\/<\/span>DoctrineExtensions<\/div><\/div>\n

Et ensuite d’indiquer les fonctions dont on \u00e0 besoin dans notre configuration de doctrine de la mani\u00e8re suivante :<\/p>\n

doctrine<\/span>:
\n    orm<\/span>:
\n        dql<\/span>:
\n            numeric_functions<\/span>:
\n                acos<\/span>: <\/span>DoctrineExtensions\\Query\\Mysql\\Acos
\n                cos<\/span>: <\/span>DoctrineExtensions\\Query\\Mysql\\Cos
\n                radians<\/span>: <\/span>DoctrineExtensions\\Query\\Mysql\\Radians
\n                sin<\/span>: <\/span>DoctrineExtensions\\Query\\Mysql\\Sin<\/div><\/div>\n

Tout doit \u00e0 pr\u00e9sente fonctionner. Vous pouvez de cette mani\u00e8re importer une flop\u00e9e de fonctions, la configuration compl\u00e8te \u00e9tant disponible Sur cette page<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Il y’a quelques mois je pr\u00e9sentais une fonction PHP permettant de calculer la distance entre 2 coordonn\u00e9es GPS (latitude et longitude). Coordonn\u00e9es qui peuvent par exemple \u00eatre r\u00e9cup\u00e9rer via l’API Google Map Nous allons voir dans cette article, comment r\u00e9cup\u00e9rer toutes les enregistrements d’une table qui sont situ\u00e9 \u00e0 moins de \u00ab\u00a050km\u00a0\u00bb d’une position pr\u00e9cise. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,4,14],"tags":[],"class_list":["post-250","post","type-post","status-publish","format-standard","hentry","category-developpement","category-doctrine","category-sql"],"acf":[],"yoast_head":"\nDoctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de "XX" km - Pense b\u00eate d'un d\u00e9veloppeur web<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de "XX" km - Pense b\u00eate d'un d\u00e9veloppeur web\" \/>\n<meta property=\"og:description\" content=\"Il y’a quelques mois je pr\u00e9sentais une fonction PHP permettant de calculer la distance entre 2 coordonn\u00e9es GPS (latitude et longitude). Coordonn\u00e9es qui peuvent par exemple \u00eatre r\u00e9cup\u00e9rer via l’API Google Map Nous allons voir dans cette article, comment r\u00e9cup\u00e9rer toutes les enregistrements d’une table qui sont situ\u00e9 \u00e0 moins de \u00ab\u00a050km\u00a0\u00bb d’une position pr\u00e9cise. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\" \/>\n<meta property=\"og:site_name\" content=\"Pense b\u00eate d'un d\u00e9veloppeur web\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-13T18:27:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-05-26T12:23:59+00:00\" \/>\n<meta name=\"author\" content=\"Numa\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Numa\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\"},\"author\":{\"name\":\"Numa\",\"@id\":\"https:\/\/numa-bord.com\/miniblog\/#\/schema\/person\/f9d00acd1703f17e5a6895283eb46a7e\"},\"headline\":\"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \u00ab\u00a0XX\u00a0\u00bb km\",\"datePublished\":\"2018-01-13T18:27:59+00:00\",\"dateModified\":\"2022-05-26T12:23:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\"},\"wordCount\":389,\"commentCount\":1,\"articleSection\":[\"D\u00e9veloppement\",\"Doctrine\",\"SQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\",\"url\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\",\"name\":\"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \\\"XX\\\" km - Pense b\u00eate d'un d\u00e9veloppeur web\",\"isPartOf\":{\"@id\":\"https:\/\/numa-bord.com\/miniblog\/#website\"},\"datePublished\":\"2018-01-13T18:27:59+00:00\",\"dateModified\":\"2022-05-26T12:23:59+00:00\",\"author\":{\"@id\":\"https:\/\/numa-bord.com\/miniblog\/#\/schema\/person\/f9d00acd1703f17e5a6895283eb46a7e\"},\"breadcrumb\":{\"@id\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/numa-bord.com\/miniblog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \u00ab\u00a0XX\u00a0\u00bb km\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/numa-bord.com\/miniblog\/#website\",\"url\":\"https:\/\/numa-bord.com\/miniblog\/\",\"name\":\"Pense b\u00eate d'un d\u00e9veloppeur web\",\"description\":\"(php, javascript, Symfony, Wordpress....)\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/numa-bord.com\/miniblog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/numa-bord.com\/miniblog\/#\/schema\/person\/f9d00acd1703f17e5a6895283eb46a7e\",\"name\":\"Numa\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f21d1af4658a7106211915940584534c1e0b3eef3f12eb67a697686cad70b64a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f21d1af4658a7106211915940584534c1e0b3eef3f12eb67a697686cad70b64a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f21d1af4658a7106211915940584534c1e0b3eef3f12eb67a697686cad70b64a?s=96&d=mm&r=g\",\"caption\":\"Numa\"},\"url\":\"https:\/\/numa-bord.com\/miniblog\/author\/negonner\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \"XX\" km - Pense b\u00eate d'un d\u00e9veloppeur web","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:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/","og_locale":"fr_FR","og_type":"article","og_title":"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \"XX\" km - Pense b\u00eate d'un d\u00e9veloppeur web","og_description":"Il y’a quelques mois je pr\u00e9sentais une fonction PHP permettant de calculer la distance entre 2 coordonn\u00e9es GPS (latitude et longitude). Coordonn\u00e9es qui peuvent par exemple \u00eatre r\u00e9cup\u00e9rer via l’API Google Map Nous allons voir dans cette article, comment r\u00e9cup\u00e9rer toutes les enregistrements d’une table qui sont situ\u00e9 \u00e0 moins de \u00ab\u00a050km\u00a0\u00bb d’une position pr\u00e9cise. […]","og_url":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/","og_site_name":"Pense b\u00eate d'un d\u00e9veloppeur web","article_published_time":"2018-01-13T18:27:59+00:00","article_modified_time":"2022-05-26T12:23:59+00:00","author":"Numa","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Numa","Dur\u00e9e de lecture estim\u00e9e":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#article","isPartOf":{"@id":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/"},"author":{"name":"Numa","@id":"https:\/\/numa-bord.com\/miniblog\/#\/schema\/person\/f9d00acd1703f17e5a6895283eb46a7e"},"headline":"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \u00ab\u00a0XX\u00a0\u00bb km","datePublished":"2018-01-13T18:27:59+00:00","dateModified":"2022-05-26T12:23:59+00:00","mainEntityOfPage":{"@id":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/"},"wordCount":389,"commentCount":1,"articleSection":["D\u00e9veloppement","Doctrine","SQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/","url":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/","name":"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \"XX\" km - Pense b\u00eate d'un d\u00e9veloppeur web","isPartOf":{"@id":"https:\/\/numa-bord.com\/miniblog\/#website"},"datePublished":"2018-01-13T18:27:59+00:00","dateModified":"2022-05-26T12:23:59+00:00","author":{"@id":"https:\/\/numa-bord.com\/miniblog\/#\/schema\/person\/f9d00acd1703f17e5a6895283eb46a7e"},"breadcrumb":{"@id":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/numa-bord.com\/miniblog\/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/numa-bord.com\/miniblog\/"},{"@type":"ListItem","position":2,"name":"Doctrine : Recherche dans une table contenant des latitudes et longitudes celle situ\u00e9s \u00e0 moins de \u00ab\u00a0XX\u00a0\u00bb km"}]},{"@type":"WebSite","@id":"https:\/\/numa-bord.com\/miniblog\/#website","url":"https:\/\/numa-bord.com\/miniblog\/","name":"Pense b\u00eate d'un d\u00e9veloppeur web","description":"(php, javascript, Symfony, Wordpress....)","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/numa-bord.com\/miniblog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"https:\/\/numa-bord.com\/miniblog\/#\/schema\/person\/f9d00acd1703f17e5a6895283eb46a7e","name":"Numa","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/secure.gravatar.com\/avatar\/f21d1af4658a7106211915940584534c1e0b3eef3f12eb67a697686cad70b64a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f21d1af4658a7106211915940584534c1e0b3eef3f12eb67a697686cad70b64a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f21d1af4658a7106211915940584534c1e0b3eef3f12eb67a697686cad70b64a?s=96&d=mm&r=g","caption":"Numa"},"url":"https:\/\/numa-bord.com\/miniblog\/author\/negonner\/"}]}},"_links":{"self":[{"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/posts\/250","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/comments?post=250"}],"version-history":[{"count":7,"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/posts\/250\/revisions"}],"predecessor-version":[{"id":257,"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/posts\/250\/revisions\/257"}],"wp:attachment":[{"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/media?parent=250"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/categories?post=250"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/numa-bord.com\/miniblog\/wp-json\/wp\/v2\/tags?post=250"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}