Filtering TVs using XPDO

getRegionalNews.php

<?php
$lat = isset($_GET['location_latitude']) ? $_GET['location_latitude'] : '';
$lon = isset($_GET['location_longitude']) ? $_GET['location_longitude'] : '';



$resources = '';
$parents = array(242178,242939,298672,326290,354699);
$R = 6371;  // earth's mean radius, km
$rad = 30; // distance to search, km

// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));

//create new XPDO query
$c = $modx->newQuery('modResource');
$c->select($modx->getSelectColumns('modResource','modResource'));

//inner join for ID of lattitude from name 'lat'
$c->innerJoin('modTemplateVar', 'tv_lat', array('tv_lat.name' => 'lat'));
$c->select($modx->getSelectColumns('modTemplateVar','tv_lat'));

//inner join for value of tv with name 'lat'
$c->innerJoin('modTemplateVarResource', 'tv_lat_val', array('tv_lat_val.tmplvarid = tv_lat.id', 
    'tv_lat_val.contentid = modResource.id'));
$c->select($modx->getSelectColumns('modTemplateVarResource','tv_lat_val'));

//inner join for ID of longitude from name 'long'
$c->innerJoin('modTemplateVar', 'tv_long', array('tv_long.name' => 'long'));
$c->select($modx->getSelectColumns('modTemplateVar','tv_long'));

//inner join for value of tv with name 'long'
$c->innerJoin('modTemplateVarResource', 'tv_long_val', array('tv_long_val.tmplvarid = tv_long.id', 
    'tv_long_val.contentid = modResource.id'));
$c->select($modx->getSelectColumns('modTemplateVarResource','tv_long_val'));    

//where conditions
$c->where(array(
    'parent:IN' => $parents,
    'published' => 1,
    'deleted' => 0,
    'tv_long_val.value:>=' => $minLon,
    'tv_long_val.value:<=' => $maxLon,
    'tv_lat_val.value:>='  => $minLat,
    'tv_lat_val.value:<='  => $maxLat
));
//sort and limits
$c->sortby('publishedon');
$c->limit(3);

//get docs
$docs = $modx->getCollection('modResource',$c);

$output= '';

//output proper docs using chunk
foreach ($docs as $doc) {
    unset($params);
    $params['id']=$doc->get('contentid');
    $params['pagetitle']=$doc->get('pagetitle');
    $output .= $modx->getChunk('module-regional_news-xpdo.item', $params);
}


/*
$resources = $modx->runSnippet('getResources', array(
    'depth' => 0,
    'parents' => $parents,
    'limit' => 3,
    'offset' => 0,
    'includeTVs' => 1,
    'includeTVList' => 'lat,long',
    'tvFilters' => 'lat>='.$minLat.',lat<='.$maxLat.',long>='.$minLon.',long<='.$maxLon,
    'tpl' => 'module-regional_news.item',
    'sortBy' => '{"publishedon":"DESC"}'
));
*/

if (empty($output)) {
    return '<p>Es wurden keine regionalen Nachrichten im Umkreis von 30 Kilometern gefunden!</p>';
}


return $output;