$placemark->useHoverStyle('p' . $c);
}
$Region = $placemark->addChild('Region');
$Region->setPoint($point, 0.01);
$delta = $entry['gridimage_id'] % 30;
$Region->setLod(50 + $delta, 1300 + $delta * $delta);
$Region->setFadeExtent(10, 100);
$folder->addChild($placemark);
}
$letterlength = $CONF['gridpreflen'][$prefix['reference_index']];
$sql_column = "concat(substring(grid_reference,1,{$letterlength}+1),substring(grid_reference,{$letterlength}+3,1))";
$most = $db->GetAll("select \r\ngrid_reference,x,y,\r\n{$sql_column} as hunk_square,\r\nsum(imagecount) as images\r\nfrom gridsquare \r\nwhere {$sql_where}\r\ngroup by hunk_square \r\nhaving images > 0 \r\norder by hunk_square");
foreach ($most as $id => $entry) {
$x = intval(($entry['x'] - $prefix['origin_x']) / 10) * 10 + $prefix['origin_x'];
$y = intval(($entry['y'] - $prefix['origin_y']) / 10) * 10 + $prefix['origin_y'];
list($south, $west) = $conv->internal_to_wgs84($x, $y, $ri);
list($north, $east) = $conv->internal_to_wgs84($x + 10, $y + 10, $ri);
$networklink = new kmlNetworkLink(null, $entry['hunk_square']);
$file = getKmlFilepath($kml->extension, 4, $square, $entry['hunk_square']);
$UrlTag = $networklink->useUrl("http://" . $CONF['KML_HOST'] . $file);
$html .= getHtmlLink($file, $entry['hunk_square'], 'in Hectad', " (at least {$entry['images']} images)");
if (!isset($_GET['debug'])) {
if (isset($_GET['newonly'])) {
$db->Execute("insert ignore into kmlcache set `url` = 'hectad.php?gr={$entry['hunk_square']}',filename='{$file}',`level` = 4,`rendered` = 0");
} else {
$db->Execute("replace into kmlcache set `url` = 'hectad.php?gr={$entry['hunk_square']}',filename='{$file}',`level` = 4,`rendered` = 0");
}
}
$UrlTag->setItem('viewRefreshMode', 'onRegion');
$links->addChild($networklink);
$Region2 = $networklink->addChild('Region');
$USER->mustHavePerm("admin");
}
$conv = new Conversions();
$db = NewADOConnection($GLOBALS['DSN']);
if (!$db) {
die('Database connection failed');
}
$kml = new kmlFile();
$kml->atom = true;
$stylefile = "http://{$CONF['KML_HOST']}/kml/style.kmz";
$kml->filename = "Geograph-Members.kml";
$folder = $kml->addChild('Document');
$folder->setItem('name', "Geograph British Isles Members");
$users = $db->GetAssoc("select\r\n\t\tuser.user_id,nickname,user.realname,images,gs.x,gs.y,gs.reference_index\r\n\t\tfrom user\r\n\t\t\tleft join user_stat using (user_id)\r\n\t\t\tinner join gridsquare gs on (home_gridsquare = gridsquare_id)\r\n\t\torder by realname");
foreach ($users as $user_id => $user) {
list($wgs84_lat, $wgs84_long) = $conv->internal_to_wgs84($user['x'], $user['y']);
$point = new kmlPoint($wgs84_lat, $wgs84_long);
$placemark = new kmlPlacemark($user_id, $user['realname'] . ' :: ' . $user['images'], $point);
$placemark->useCredit($entry['realname'], "http://{$_SERVER['HTTP_HOST']}/profile/{$user_id}");
if (empty($user['nickname'])) {
$placemark->setItemCDATA('description', $placemark->link);
} else {
$placemark->setItemCDATA('description', "Nickname: {$user['nickname']}<br/>{$placemark->link}");
}
$placemark->useHoverStyle('def');
$folder->addChild($placemark);
}
$base = $_SERVER['DOCUMENT_ROOT'];
$file = "/kml/members.kmz";
$kml->outputFile('kmz', false, $base . $file);
if (isset($_GET['debug'])) {
//htmlspecialchars is called on link so dont use &
$item->link = "http://{$_SERVER['HTTP_HOST']}/events/event.php?id={$recordSet->fields['geoevent_id']}";
$item->guid = $item->link;
$description = $recordSet->fields['description'];
if (strlen($description) > 160) {
$description = substr($description, 0, 157) . "...";
}
$description .= "<br/><br/><a href=\"{$item->link}\">More details of this event</a>";
$item->description = $description;
$item->date = strtotime($recordSet->fields['created']);
$item->author = $recordSet->fields['realname'];
if (($format == 'KML' || $format == 'GeoRSS' || $format == 'GPX') && $recordSet->fields['gridsquare_id']) {
$gridsquare = new GridSquare();
$grid_ok = $gridsquare->loadFromId($recordSet->fields['gridsquare_id']);
if ($grid_ok) {
list($item->lat, $item->long) = $conv->internal_to_wgs84($recordSet->fields['x'], $recordSet->fields['y']);
}
$rss->addItem($item);
} elseif ($format != 'KML') {
$rss->addItem($item);
}
$recordSet->MoveNext();
}
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
// always modified
header("Cache-Control: no-store, no-cache, must-revalidate");
// HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
$cacheid = $USER->hasPerm('basic') ? $USER->user_id : 0;
$isadmin = $USER->hasPerm('moderator') ? 1 : 0;
$smarty->assign_by_ref('isadmin', $isadmin);
$template = 'events.tpl';
if ($isadmin) {
if (!empty($_GET['id']) && preg_match('/^\\d+$/', $_GET['id'])) {
$db = NewADOConnection($GLOBALS['DSN']);
$a = intval($_GET['approve']);
$sql = "UPDATE geoevent SET approved = {$a} WHERE id = " . $db->Quote($_GET['id']);
$db->Execute($sql);
$smarty->clear_cache($template, $cacheid);
}
}
if (!$smarty->is_cached($template, $cacheid)) {
$smarty->assign('google_maps_api_key', $CONF['google_maps_api_key']);
$db = NewADOConnection($GLOBALS['DSN']);
$prev_fetch_mode = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$list = $db->getAll("\r\n\tselect geoevent.*,\r\n\t\trealname,\r\n\t\t(event_time > now()) as future,\r\n\t\tgrid_reference,x,y,\r\n\t\tsum(type='attend') as attendees\r\n\tfrom geoevent \r\n\t\tinner join user using (user_id)\r\n\t\tinner join gridsquare using (gridsquare_id)\r\n\t\tleft join geoevent_attendee using (geoevent_id)\r\n\twhere ((approved = 1) \r\n\t\tor user.user_id = {$USER->user_id}\r\n\t\tor ({$isadmin} and approved != -1)\r\n\t\t) and (event_time > date_sub(now(),interval 1 month) )\r\n\tgroup by geoevent_id\r\n\torder by future desc,event_time");
$conv = new Conversions();
$f = 0;
foreach ($list as $i => $row) {
if ($row['future']) {
$f++;
}
list($list[$i]['wgs84_lat'], $list[$i]['wgs84_long']) = $conv->internal_to_wgs84($row['x'], $row['y']);
}
$smarty->assign('future', $f);
$smarty->assign_by_ref('list', $list);
}
$smarty->display($template, $cacheid);
$kml->atom = true;
$stylefile = "http://{$CONF['KML_HOST']}/kml/style.kmz";
$folder = $kml->addChild('Document');
$folder->setItem('name', "{$gr} :: Geograph SuperLayer");
$sql_where = "gridsquare_id={$square->gridsquare_id}" . $sql_where;
$photos = $db->GetAll($sql = "select \r\n\tgridimage_id,title,title2,gi.realname as credit_realname,if(gi.realname!='',gi.realname,user.realname) as realname,user.user_id,comment,nateastings,natnorthings,natgrlen,view_direction\r\n\tfrom gridimage gi\r\n\t\t inner join user using(user_id)\r\n\twhere {$sql_where}\r\n\torder by null");
foreach ($photos as $id => $entry) {
$title = combineTexts($entry['title'], $entry['title2']);
if ($entry['nateastings']) {
if ($entry['natgrlen'] == 6) {
$entry['nateastings'] += 50;
$entry['natnorthings'] += 50;
}
list($wgs84_lat, $wgs84_long) = $conv->national_to_wgs84($entry['nateastings'], $entry['natnorthings'], $square->reference_index);
} else {
list($wgs84_lat, $wgs84_long) = $conv->internal_to_wgs84($square->x, $square->y, $square->reference_index);
}
$point = new kmlPoint($wgs84_lat, $wgs84_long);
$placemark = new kmlPlacemark_Photo($entry['gridimage_id'], $square->grid_reference . ' :: ' . $title, $point);
$placemark->useHoverStyle();
$image = new GridImage();
$image->fastInit($entry);
$placemark->useCredit($image->realname, "http://{$_SERVER['HTTP_HOST']}/photo/" . $image->gridimage_id);
$html .= getHtmlLinkP($placemark->link, $square->grid_reference . ' :: ' . $title . ' by ' . $image->realname);
$linkTag = "<a href=\"" . $placemark->link . "\">";
$details = $image->getThumbnail(120, 120, 2);
$thumb = $details['server'] . $details['url'];
$thumbTag = $details['html'];
$description = $linkTag . $thumbTag . "</a><br/>" . GeographLinks($image->comment) . " (" . $linkTag . "view full size</a>)" . "<br/><br/> © Copyright <a title=\"view user profile\" href=\"http://" . $_SERVER['HTTP_HOST'] . $image->profile_link . "\">" . $image->realname . "</a> and licensed for reuse under this <a rel=\"license\" href=\"http://creativecommons.org/licenses/by-sa/2.0/\">Creative Commons Licence</a><br/><br/>";
$placemark->setItemCDATA('description', $description);
//yes that is uppercase S!
$rectangle = "'POLYGON(({$left} {$bottom},{$right} {$bottom},{$right} {$top},{$left} {$top},{$left} {$bottom}))'";
$sql_where .= "CONTAINS(GeomFromText({$rectangle}),point_xy)";
//shame cant use dist_sqd in the next line!
$sql_where .= " and ((gs.x - {$x}) * (gs.x - {$x}) + (gs.y - {$y}) * (gs.y - {$y})) < " . $d * $d;
$sql_fields .= ", ((gs.x - {$x}) * (gs.x - {$x}) + (gs.y - {$y}) * (gs.y - {$y})) as dist_sqd";
$sql_order = ' dist_sqd ';
$sql = "SELECT grid_reference,x,y,imagecount {$sql_fields}\n\t\t\t\tFROM gridsquare gs\n\t\t\t\tWHERE {$sql_where}\n\t\t\t\tORDER BY {$sql_order}";
$db = NewADOConnection($GLOBALS['DSN']);
if (!$db) {
die('Database connection failed');
}
$data = $db->getAll($sql);
require_once 'geograph/conversions.class.php';
$conv = new Conversions();
foreach ($data as $q => $row) {
list($data[$q]['lat'], $data[$q]['long']) = $conv->internal_to_wgs84($row['x'], $row['y']);
}
$smarty->assign_by_ref('data', $data);
$smarty->assign_by_ref('searchdesc', $searchdesc);
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"Geograph-{$cacheid}.gpx\"");
customExpiresHeader(3600 * 24 * 14, true);
$smarty->display($template, $cacheid);
exit;
} else {
init_session();
//preserve the input at least
$smarty->assign('gridref', stripslashes($_REQUEST['gridref']));
$smarty->assign('distance', $d);
$smarty->assign('type', $type);
$db->Execute("INSERT INTO gridimage_search\n\t\t\tSELECT gridimage_id, gi.user_id, moderation_status, title, title2, submitted, imageclass, imagetaken, upd_timestamp, x, y, gs.grid_reference, gi.realname!='' as credit_realname,if(gi.realname!='',gi.realname,user.realname) as realname,reference_index,comment,comment2,0,0,ftf,seq_no,point_xy,GeomFromText('POINT(0 0)')\n\t\t\tFROM gridimage AS gi\n\t\t\tINNER JOIN gridsquare AS gs\n\t\t\tUSING ( gridsquare_id )\n\t\t\tINNER JOIN user ON ( gi.user_id = user.user_id )\n\t\t\tWHERE moderation_status in ('accepted','geograph') ");
}
echo "<h3>Rebuild completed</h3>";
if (!isset($_POST['update'])) {
print "<h3>It is now recommended to update the Lat/Long</h3>";
}
}
if (!empty($_POST['update'])) {
echo "<h3>Updating Lat/Long</h3>";
flush();
$start = time();
$recordSet =& $db->Execute("select gridimage_id,x,y,reference_index,nateastings,natnorthings\n\t\tfrom gridimage\n\t\tINNER JOIN gridsquare AS gs USING ( gridsquare_id )\n\t\twhere moderation_status in ('accepted','geograph')");
$count = 0;
while (!$recordSet->EOF) {
$image = $recordSet->fields;
if ($image['nateastings']) {
list($lat, $long) = $conv->national_to_wgs84($image['nateastings'], $image['natnorthings'], $image['reference_index']);
} else {
list($lat, $long) = $conv->internal_to_wgs84($image['x'], $image['y'], $image['reference_index']);
}
$db2->Execute("UPDATE LOW_PRIORITY gridimage_search SET wgs84_lat = {$lat}, wgs84_long = {$long},point_ll = GeomFromText('POINT({$long} {$lat})') WHERE gridimage_id = " . $image['gridimage_id']);
if (++$count % 500 == 0) {
printf("done %d at <b>%d</b> seconds<br/>", $count, time() - $start);
flush();
}
$recordSet->MoveNext();
}
echo "<p>Lat/Long update complete</p>";
}
$smarty->display('_std_end.tpl');
exit;
}
}
if ($found > -1) {
if (!isset($engine->results[$found]->cluster)) {
$engine->results[$found]->cluster = array();
}
$engine->results[$found]->simple_title = $engine->results[$idx]->{$breakby};
array_push($engine->results[$found]->cluster, $image);
unset($engine->results[$idx]);
}
}
} elseif ($display == 'gmap' && $engine->resultCount) {
$markers = array();
$conv = new Conversions();
if ($engine->criteria->x && $engine->criteria->y) {
list($lat, $long) = $conv->internal_to_wgs84($engine->criteria->x, $engine->criteria->y);
$markers[] = array('Center Point', $lat, $long);
}
if (preg_match_all('/\\b([a-zA-Z]{1,3} ?\\d{1,5}[ \\.]?\\d{1,5})\\b/', $engine->criteria->searchdesc, $m)) {
$m = array_unique($m[1]);
foreach ($m as $gr) {
$sq = new GridSquare();
$sq->setByFullGridRef($gr, false, true);
list($lat, $long) = $conv->gridsquare_to_wgs84($sq);
$markers[] = array($gr, $lat, $long);
}
}
$smarty->assign_by_ref('markers', $markers);
}
}
if ($engine->criteria->user_id == $USER->user_id) {
$most = $db->GetAll("select \r\ngrid_reference,x,y,avg(x) as avgx,avg(y) as avgy,\r\n{$sql_column} as hunk_square,\r\nsum(imagecount) as images,\r\nsum(percent_land >0) as land_count,\r\n(sum(has_geographs) * 100 / sum(percent_land >0)) as percentage\r\nfrom gridsquare \r\nwhere {$sql_where}\r\ngroup by hunk_square \r\nhaving images > 0 \r\norder by hunk_square");
foreach ($most as $id => $entry) {
#if ($entry['land_count'] < 10) {
# //100% = 1k
# $radius = $entry['percentage'] * 10;
#} else {
# //100% = 10k
# $radius = $entry['percentage'] * 100;
#}
// 20km x 20km = 400
$ratio = $entry['land_count'] / 400;
//100% = 10k
$radius = $entry['percentage'] / 100 * $ratio * 10000;
$x = $entry['avgx'];
$y = $entry['avgy'];
list($wgs84_lat, $wgs84_long) = $conv->internal_to_wgs84($x, $y, $ri);
$point = new kmlPoint($wgs84_lat, $wgs84_long);
$placemark = new kmlPlacemark_Circle(null, $entry['hunk_square'], $point, $radius);
$placemark->setItem('description', $entry['percentage'] . '%');
$placemark->useHoverStyle('c2');
$folder->addChild($placemark);
$x = intval(($entry['x'] - $prefix['origin_x']) / 20) * 20 + $prefix['origin_x'];
$y = intval(($entry['y'] - $prefix['origin_y']) / 20) * 20 + $prefix['origin_y'];
list($south, $west) = $conv->internal_to_wgs84($x, $y, $ri);
list($north, $east) = $conv->internal_to_wgs84($x + 20, $y + 20, $ri);
$Region = $placemark->addChild('Region');
$Region->setBoundary($north, $south, $east, $west);
$Region->setLod(100, 650);
$Region->setFadeExtent(50, 150);
$networklink = new kmlNetworkLink(null, $entry['hunk_square']);
$file = getKmlFilepath($kml->extension, 3, $square, $entry['hunk_square']);
/**
* Saves update tables based on gridimage
*/
function updateCachedTables()
{
$db =& $this->_getDB();
//quick sanity check
if (!$this->gridimage_id) {
die("no gridimage_id supplied to updateCachedTables");
}
if ($this->moderation_status == 'rejected' || $this->moderation_status == 'pending') {
$sql = "DELETE FROM gridimage_search WHERE gridimage_id = '{$this->gridimage_id}'";
$db->Execute($sql);
$db->Execute("DELETE FROM wordnet1 WHERE gid = {$this->gridimage_id}");
$db->Execute("DELETE FROM wordnet2 WHERE gid = {$this->gridimage_id}");
$db->Execute("DELETE FROM wordnet3 WHERE gid = {$this->gridimage_id}");
} elseif ($this->moderation_status) {
require_once 'geograph/conversions.class.php';
$conv = new Conversions();
if (isset($this->newsq)) {
$square = $this->newsq;
} else {
$square = $this->grid_square;
}
if (!$square) {
die("ERROR: no square known in updateCachedTables");
}
if ($square->nateastings) {
list($lat, $long) = $conv->national_to_wgs84($square->nateastings, $square->natnorthings, $square->reference_index);
} else {
list($lat, $long) = $conv->internal_to_wgs84($square->x, $square->y, $square->reference_index);
}
$sql = "REPLACE INTO gridimage_search\n\t\t\tSELECT gridimage_id,gi.user_id,moderation_status,title,title2,submitted,imageclass,imagetaken,upd_timestamp,x,y,gs.grid_reference,gi.realname!='' as credit_realname,if(gi.realname!='',gi.realname,user.realname) as realname,gs.reference_index,comment,comment2,{$lat},{$long},ftf,seq_no,point_xy,GeomFromText('POINT({$long} {$lat})')\n\t\t\tFROM gridimage AS gi INNER JOIN gridsquare AS gs USING(gridsquare_id)\n\t\t\tINNER JOIN user ON(gi.user_id=user.user_id)\n\t\t\tWHERE gridimage_id = '{$this->gridimage_id}'";
$db->Execute($sql);
$row =& $db->GetRow("select recent_id from gridimage_recent where gridimage_id={$this->gridimage_id} limit 1");
if ($row !== false && count($row)) {
$recent_id = $row['recent_id'];
$sql = "REPLACE INTO gridimage_recent\n\t\t\t\tSELECT gridimage_id,gi.user_id,moderation_status,title,title2,submitted,imageclass,imagetaken,upd_timestamp,x,y,gs.grid_reference,gi.realname!='' as credit_realname,if(gi.realname!='',gi.realname,user.realname) as realname,gs.reference_index,comment,comment2,{$lat},{$long},ftf,seq_no,point_xy,GeomFromText('POINT({$long} {$lat})'),{$recent_id}\n\t\t\t\tFROM gridimage AS gi INNER JOIN gridsquare AS gs USING(gridsquare_id)\n\t\t\t\tINNER JOIN user ON(gi.user_id=user.user_id)\n\t\t\t\tWHERE gridimage_id = '{$this->gridimage_id}'";
$db->Execute($sql);
}
} else {
//fall back if we dont know the moduration status then lets load it and start again!
$this->loadFromId($this->gridimage_id);
return $this->updateCachedTables();
}
}
