xPDO SQL to get resouces by distance

- 3 Posts
nattila Reply #1, 15 years, 4 months ago
I would build a query that returns a set of resources from the db and sorts them based on distance from a given location. Every resource have lat & lng TV.
Do you have any idea how could I acheive the sample statement below useing by xPDO?
```
SELECT *,
    SQRT(POW((69.1 * (resources.latitude - 27.950898)) , 2 ) +
    POW((53 * (resources.longitude - -82.461517)), 2)) AS distance
FROM resources
ORDER BY distance ASC
LIMIT 5
```

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 4 months ago

Something like this should do what you want:

<?php
$query = $modx->newQuery('modResource');
$query->join('modTemplateVarResource', 'lat', array('tmplvarid' => $tvLat, "lat.contentid = modResource.id"));
$query->join('modTemplateVarResource', 'lng', array('tmplvarid' => $tvLng, "lng.contentid = modResource.id"));
$query->select($modx->getSelectColumns('modResource', 'modResource', 'modResource_'));
$query->select(array(
    'distance' => "SQRT(POW((69.1 * (lat.value - 27.950898)) , 2 ) + POW((53 * (lng.value - -82.461517)), 2))"
))
$query->sortby('distance', 'ASC');
$query->limit(5);

$resources = $modx->getCollection('modResource', $query);

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek