Getting distance from given coordinates in doctrine geographical records

Question

Hello everybody,

I'm developing a Symfony 1.4 project where one of the tables has the Geographical template. The present functions can give the distance between two records, but not between records and given coordinates.

How can be implemented such feature in Symfony way?

More specific: I need the records ordered by descending distance from a given latitude and longitude coordinates

Thanks you all!

Answer 1

This doesn't sound like something for which there is a "symfony way". To retrieve the data from your database in descending distance from a given latitude and longitude coordinates, just use a Doctrine DQL query in the normal way. You do, however, need to understand a few basics about what an ascending/descending latitude or longitude means in terms of real-world distance. I don't mean any GIS expertise here, just which direction a higher latitude goes on the map. That will allow you to construct your ascending/descending Doctrine queries.

For calculating a real-world distance between two lat/longs, I suggest creating a custom helper function to which you pass your lat and long and which returns distance. That's another question & answer altogether.

Answer 2

    $radius = 200;
    $lat = mysql_real_escape_string($zipcode1->getLatitude());
    $long = mysql_real_escape_string($zipcode1->getLongitude());
    $q = Doctrine_Query::create()
    ->select("j.address, j.longitude, j.latitude, (3959 * acos(cos
(radians('".$lat."')) * cos(radians(latitude)) * cos(radians
(longitude) - radians('".$long."')) + sin(radians('".$lat."')) * sin
(radians(latitude)))) AS distance")
    ->from("Job j")
    ->having("distance < ?", $radius)
    ->orderBy("distance desc");
    $results = $q->execute(); 

Found this here: http://groups.google.com/group/doctrine-user/browse_thread/thread/5f4042649c9fa275

I guess a more "symfony" way to do would be to edit the models so there is a functions such as:

$distance = $locationModel->DistanceFrom($anotherLocation);
$locations = LocationModel::GetLocationsClosestTo($lat, $long);

For my app I stored lat/long as decimals like so:

latitude: {type: decimal(4), scale: 4}
longitude: {type: decimal(4), scale: 4}

Because google maps api returns lats/longs with up to 4 decimal places