visit our site

How to Get Nearby Locations from MySQL Database

In PHP and Symfony

by Dragos Holban on September 14, 2017

If you have a MySQL table with locations (latitude and longitude columns) you can get a list of locations near a specific point using the following query:

In this query we have noted the latitude of the reference point with <latitude>, its longitude with <longitude> and the maximum distance to search for with <distance> (in kilometers).

To make this calculate the distance in miles, just remove the * 1.609344 from the end of the calculation:

You can test the results using Google Maps. To measure distances in Google Maps you need to right-click and choose “Measure distance” from the popup menu.

How to Get Nearby Locations in Symfony with Doctrine

If you’re like me and use Symfony a lot, here’s how you can add a repository method for your entity to return objects ordered by distance from a specific location:

To make this work we need to add the ACOS, SIN, COS and PI functions to Doctrine’s DQL as it does not have them by default.

First create 4 new classes in a new AppBundle/DQL folder, one for each function we need to define.

AppBundle/DQL/Acos.php

AppBundle/DQL/Sin.php

AppBundle/DQL/Cos.php

AppBundle/DQL/Pi.php

The next and the last step is to add the above definitions to your app’s config.yml file:

That’s it. Now it will all work as expected. If you need to add more specific functions to DQL, just look at the above classes and make your own.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

+1
Share
Tweet
Share
Stumble
Pin