* Unicode Reminder メモ * * Dieses Script berechnet andhand von Geodb-Daten die adm1..adm4-Daten * für die GNS-DB. ***************************************************************************/ $opt['rootpath'] = '../../'; require_once $opt['rootpath'] . 'lib2/cli.inc.php'; require_once $opt['rootpath'] . 'lib2/search/search.inc.php'; $rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'"); while ($rLocations = sql_fetch_array($rsLocations)) { $minlat = geomath::getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1); $maxlat = geomath::getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1); $minlon = geomath::getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1); $maxlon = geomath::getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1); // den nächsgelegenen Ort in den geodb ermitteln $sql = "SELECT " . geomath::getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . " `distance`,\n `geodb_coordinates`.`loc_id` `loc_id`\n FROM `geodb_coordinates`\n WHERE\n `lon` > '" . sql_escape($minlon) . "' AND `lon` < '" . sql_escape($maxlon) . "' AND\n `lat` > '" . sql_escape($minlat) . "' AND `lat` < '" . sql_escape($maxlat) . "'\n HAVING `distance` < 10\n ORDER BY `distance` ASC\n LIMIT 1"; $rs = sql($sql); if (mysql_num_rows($rs) == 1) { $r = sql_fetch_array($rs); mysql_free_result($rs); $locid = $r['loc_id']; $admtxt1 = GeoDb::landFromLocid($locid); if ($admtxt1 == '0') { $admtxt1 = ''; } // bundesland ermitteln $rsAdm2 = sql("SELECT `full_name`, `short_form`\n FROM `gns_locations`\n WHERE `rc`='&1'\n AND `fc`='A'\n AND `dsg`='ADM1'\n AND `cc1`='&2'\n AND `adm1`='&3'\n AND `nt`='N'\n LIMIT 1", $rLocations['rc'], $rLocations['cc1'], $rLocations['adm1']); if (mysql_num_rows($rsAdm2) == 1) { $rAdm2 = sql_fetch_array($rsAdm2); $admtxt2 = $rAdm2['short_form']; if ($admtxt2 == '') {
function output_namesearch($sName, $nLat, $nLon, $nResultId) { global $login, $opt; echo '<caches>' . "\n"; $rs = sql_slave("SELECT " . geomath::getSqlDistanceFormula($nLon, $nLat, 0) . " AS `distance`, \n `caches`.`name`, `caches`.`wp_oc` \n FROM `map2_data` \n INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id`\n INNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\n WHERE `caches`.`name` LIKE '&1' \n AND (`cache_status`.`allow_user_view`=1 OR `caches`.`user_id`='&3')\n AND `map2_data`.`result_id`='&2'\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 30", '%' . $sName . '%', $nResultId, $login->userid); $caches_found = 0; while ($r = sql_fetch_assoc($rs)) { echo '<cache name="' . xmlentities($r['name']) . '" wpoc="' . xmlentities($r['wp_oc']) . '" />' . "\n"; ++$caches_found; } sql_free_result($rs); if (!$caches_found && preg_match('/^[^\\s[:punct:]]{2,}\\.[^\\s[:punct:]]{2,}\\.[^\\s[:punct:]]{2,}$/', $sName)) { $result = @file_get_contents('http://api.what3words.com/w3w?key=' . $opt['lib']['w3w']['apikey'] . '&string=' . urlencode($sName)); if ($result) { $json = json_decode($result, true); if (!is_null($json['words']) && !is_null($json['position']) && count($json['position']) == 2) { echo '<coord name="' . xmlentities(implode('.', $json['words'])) . '" latitude="' . xmlentities($json["position"][0]) . '" longitude="' . xmlentities($json["position"][1]) . '" />' . "\n"; } } } echo '</caches>' . "\n"; exit; }
} if (isset($lat_rad) && isset($lon_rad)) { $sql .= geomath::getSqlDistanceFormula($lon_rad * 180 / 3.14159, $lat_rad * 180 / 3.14159, 0, $multiplier[$distance_unit]) . ' `distance`, '; } else { if (!$login->logged_in()) { $sql .= 'NULL distance, '; } else { // get the user's home coords $rs_coords = sql_slave("SELECT `latitude`, `longitude` FROM `user` WHERE `user_id`='&1'", $login->userid); $record_coords = sql_fetch_array($rs_coords); if ($record_coords['latitude'] == 0 && $record_coords['longitude'] == 0) { $sql .= 'NULL distance, '; } else { $lon_rad = $record_coords['longitude'] * 3.14159 / 180; $lat_rad = $record_coords['latitude'] * 3.14159 / 180; $sql .= geomath::getSqlDistanceFormula($record_coords['longitude'], $record_coords['latitude'], 0, $multiplier[$distance_unit]) . ' `distance`, '; } sql_free_result($rs_coords); } } if ($options['sort'] == 'bylastlog' || $options['sort'] == 'bymylastlog') { $sAddFields .= ', MAX(`cache_logs`.`date`) AS `lastLog`'; $sAddJoin .= ' LEFT JOIN `cache_logs` ON `caches`.`cache_id`=`cache_logs`.`cache_id`'; if ($options['sort'] == 'bymylastlog') { $sAddJoin .= ' AND `cache_logs`.`user_id`=' . sql_escape($login->userid); } $sGroupBy .= ' GROUP BY `caches`.`cache_id`'; } $sql .= '`caches`.`cache_id`, `caches`.`status`, `caches`.`type`,
function startXmlSession($sModifiedSince, $bCache, $bCachedesc, $bCachelog, $bUser, $bPicture, $bRemovedObject, $bPictureFromCachelog, $selection, $sAgent) { global $opt, $ocxmlversion; // session anlegen sql("INSERT INTO `xmlsession` (`last_use`, `modified_since`, `date_created`, `agent`) VALUES (NOW(), '&1', NOW(), '&2')", date('Y-m-d H:i:s', strtotime($sModifiedSince)), $sAgent); $sessionid = mysql_insert_id(); $recordcount['caches'] = 0; $recordcount['cachedescs'] = 0; $recordcount['cachelogs'] = 0; $recordcount['users'] = 0; $recordcount['pictures'] = 0; $recordcount['removedobjects'] = 0; if ($selection['type'] == 0) { // ohne selection if ($bCache == 1) { if ($ocxmlversion >= 15) { // Starting with version 15, we include the 'needs maintenance' and // 'listing is outdated' flags in the <cache> records. $wherefield = 'GREATEST(`listing_last_modified`,`flags_last_modified`)'; } else { if ($ocxmlversion == 14) { // Starting with version 14, we include listing_last_modified in the // <caches> records, so this date is relevant for updates $wherefield = '`listing_last_modified`'; } else { $wherefield = '`last_modified`'; } } sql("INSERT INTO xmlsession_data (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 2, `cache_id` FROM `caches` WHERE " . $wherefield . " >= '&2' AND `status`!=5", $sessionid, $sModifiedSince); $recordcount['caches'] = mysql_affected_rows(); } if ($bCachedesc == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 3, `cache_desc`.`id` FROM `cache_desc` INNER JOIN `caches` ON `cache_desc`.`cache_id`=`caches`.`cache_id` WHERE `cache_desc`.`last_modified` >= '&2' AND `caches`.`status`!=5", $sessionid, $sModifiedSince); $recordcount['cachedescs'] = mysql_affected_rows(); } if ($bCachelog == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 1, `cache_logs`.`id` FROM `cache_logs` INNER JOIN `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id` WHERE `cache_logs`.`last_modified` >= '&2' AND `caches`.`status`!=5", $sessionid, $sModifiedSince); $recordcount['cachelogs'] = mysql_affected_rows(); } if ($bUser == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 4, `user_id` FROM `user` WHERE `last_modified` >= '&2'", $sessionid, $sModifiedSince); $recordcount['users'] = mysql_affected_rows(); } if ($bPicture == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n\t\t\t `caches` ON `pictures`.`object_type`=2 AND\n\t\t\t `pictures`.`object_id`=`caches`.`cache_id`\n\t\t\t WHERE `pictures`.`last_modified` >= '&2' AND\n\t\t\t `caches`.`status`!=5\n\t\t\t UNION DISTINCT\n\t\t\t SELECT &1, 6, `pictures`.`id` FROM `pictures` INNER JOIN\n\t\t\t `cache_logs` ON `pictures`.`object_type`=1 AND\n\t\t\t `pictures`.`object_id`=`cache_logs`.`id` INNER JOIN\n\t\t\t `caches` ON `cache_logs`.`cache_id`=`caches`.`cache_id`\n\t\t\t WHERE `pictures`.`last_modified` >= '&2' AND\n\t\t\t `caches`.`status`!=5", $sessionid, $sModifiedSince); $recordcount['pictures'] = mysql_affected_rows(); } if ($bRemovedObject == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2' AND `type`<>8", $sessionid, $sModifiedSince); $recordcount['removedobjects'] = mysql_affected_rows(); } } else { $sqlWhere = ''; $sqlHaving = ''; if ($selection['type'] == 1) { sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n\t\t\t SELECT DISTINCT `cache_countries`.`cache_id` FROM `caches`, `cache_countries` WHERE `caches`.`cache_id`=`cache_countries`.`cache_id` AND `cache_countries`.`country`='&1' AND `caches`.`status`!=5", $selection['country']); } else { if ($selection['type'] == 2) { $sql = 'CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), `distance` double, KEY (`cache_id`)) ENGINE=MEMORY '; $sql .= 'SELECT `cache_coordinates`.`cache_id`, '; $sql .= geomath::getSqlDistanceFormula($selection['lon'], $selection['lat'], $selection['distance'], 1, 'longitude', 'latitude', 'cache_coordinates') . ' `distance` '; $sql .= 'FROM `caches`, `cache_coordinates` WHERE '; $sql .= '`cache_coordinates`.`cache_id`=`caches`.`cache_id`'; $sql .= ' AND `caches`.`status`!=5'; $sql .= ' AND `cache_coordinates`.`latitude` > ' . geomath::getMinLat($selection['lon'], $selection['lat'], $selection['distance']); $sql .= ' AND `cache_coordinates`.`latitude` < ' . geomath::getMaxLat($selection['lon'], $selection['lat'], $selection['distance']); $sql .= ' AND `cache_coordinates`.`longitude` >' . geomath::getMinLon($selection['lon'], $selection['lat'], $selection['distance']); $sql .= ' AND `cache_coordinates`.`longitude` < ' . geomath::getMaxLon($selection['lon'], $selection['lat'], $selection['distance']); $sql .= ' HAVING `distance` < ' . ($selection['distance'] + 0); sql($sql); } else { if ($selection['type'] == 3) { sql("CREATE TEMPORARY TABLE `tmpxmlSesssionCaches` (`cache_id` int(11), PRIMARY KEY (`cache_id`)) ENGINE=MEMORY\n\t\t\t SELECT `cache_id` FROM `caches` WHERE `cache_id`='&1' AND `status`!=5", $selection['cacheid'] + 0); } } } if ($bCache == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 2, `tmpxmlSesssionCaches`.`cache_id` FROM `tmpxmlSesssionCaches`, `caches`\n\t\t\t WHERE `tmpxmlSesssionCaches`.`cache_id`=`caches`.`cache_id` AND `caches`.`last_modified` >= '&2'", $sessionid, $sModifiedSince); $recordcount['caches'] = mysql_affected_rows(); } if ($bCachedesc == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 3, `cache_desc`.`id` FROM `cache_desc`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `cache_desc`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_desc`.`last_modified` >= '&2'", $sessionid, $sModifiedSince); $recordcount['cachedescs'] = mysql_affected_rows(); } if ($bCachelog == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 1, `cache_logs`.`id` FROM `cache_logs`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `cache_logs`.`cache_id`=`tmpxmlSesssionCaches`.`cache_id` AND `cache_logs`.`last_modified` >= '&2'", $sessionid, $sModifiedSince); $recordcount['cachelogs'] = mysql_affected_rows(); } if ($bPicture == 1) { // cachebilder sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 6, `pictures`.`id` FROM `pictures`, `tmpxmlSesssionCaches`\n\t\t\t WHERE `pictures`.`object_id`=`tmpxmlSesssionCaches`.`cache_id` AND `pictures`.`object_type`=2 AND\n\t\t\t `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince); $recordcount['pictures'] = mysql_affected_rows(); // bilder von logs if ($bPictureFromCachelog == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t\t SELECT DISTINCT &1, 6, `pictures`.id FROM `pictures` , `cache_logs`, `tmpxmlSesssionCaches`\n\t\t\t\t WHERE `tmpxmlSesssionCaches`.`cache_id`=`cache_logs`.`cache_id` AND\n\t\t\t\t `pictures`.`object_type`=1 AND `pictures`.`object_id`=`cache_logs`.`id` AND\n\t\t\t\t `pictures`.`last_modified` >= '&2'", $sessionid, $sModifiedSince); $recordcount['pictures'] += mysql_affected_rows(); } } if ($bRemovedObject == 1) { sql("INSERT INTO `xmlsession_data` (`session_id`, `object_type`, `object_id`)\n\t\t\t SELECT DISTINCT &1, 7, `id` FROM `removed_objects` WHERE `removed_date` >= '&2'", $sessionid, $sModifiedSince); $recordcount['removedobjects'] = mysql_affected_rows(); } } sql('UPDATE `xmlsession` SET `caches`=&1, `cachedescs`=&2, `cachelogs`=&3, `users`=&4, `pictures`=&5, `removedobjects`=&6 WHERE `id`=&7 LIMIT 1', $recordcount['caches'], $recordcount['cachedescs'], $recordcount['cachelogs'], $recordcount['users'], $recordcount['pictures'], $recordcount['removedobjects'], $sessionid); return $sessionid; }
function sqlStringbySearchradius($distance, $lat, $lon, $multiplier, $distance_unit) { global $sql_select, $sql_from, $sql_innerjoin; //all target caches are between lat - max_lat_diff and lat + max_lat_diff $max_lat_diff = $distance / (111.12 * $multiplier[$distance_unit]); //all target caches are between lon - max_lon_diff and lon + max_lon_diff //TODO: check!!! $max_lon_diff = $distance * 180 / (abs(sin((90 - $lat) * 3.14159 / 180)) * 6378 * $multiplier[$distance_unit] * 3.14159); $lon_rad = $lon * 3.14159 / 180; $lat_rad = $lat * 3.14159 / 180; sql_temp_table_slave('result_caches'); $cachesFilter = "CREATE TEMPORARY TABLE &result_caches ENGINE=MEMORY\n SELECT\n (" . geomath::getSqlDistanceFormula($lon, $lat, $distance, $multiplier[$distance_unit]) . ") `distance`,\n `caches`.`cache_id` `cache_id`\n FROM `caches` FORCE INDEX (`latitude`)\n WHERE\n `longitude` > " . ($lon - $max_lon_diff) . "\n AND `longitude` < " . ($lon + $max_lon_diff) . "\n AND `latitude` > " . ($lat - $max_lat_diff) . "\n AND `latitude` < " . ($lat + $max_lat_diff) . "\n HAVING `distance` < " . ($distance + 0); sql_slave($cachesFilter); sql_slave("ALTER TABLE &result_caches ADD PRIMARY KEY ( `cache_id` )"); $sql_select[] = '&result_caches.`cache_id`'; $sql_from = '&result_caches'; $sql_innerjoin[] = '`caches` ON `caches`.`cache_id`=&result_caches.`cache_id`'; }
* Dieses Script berechnet andhand von Geodb-Daten die adm1..adm4-Daten * für die GNS-DB. ***************************************************************************/ $opt['rootpath'] = '../../'; require_once $opt['rootpath'] . 'lib2/cli.inc.php'; require_once $opt['rootpath'] . 'lib2/search/search.inc.php'; require_once $opt['rootpath'] . 'lib2/logic/geomath.class.php'; require_once $opt['rootpath'] . 'lib2/logic/geodb.inc.php'; $rsLocations = sql("SELECT `uni`, `lat`, `lon`, `rc`, `cc1`, `adm1` FROM `gns_locations` WHERE `dsg` LIKE 'PPL%'"); while ($rLocations = sql_fetch_array($rsLocations)) { $minlat = geomath::getMinLat($rLocations['lon'], $rLocations['lat'], 10, 1); $maxlat = geomath::getMaxLat($rLocations['lon'], $rLocations['lat'], 10, 1); $minlon = geomath::getMinLon($rLocations['lon'], $rLocations['lat'], 10, 1); $maxlon = geomath::getMaxLon($rLocations['lon'], $rLocations['lat'], 10, 1); // den nächsgelegenen Ort in den geodb ermitteln $sql = 'SELECT ' . geomath::getSqlDistanceFormula($rLocations['lon'], $rLocations['lat'], 10, 1, 'lon', 'lat', 'geodb_coordinates') . ' `distance`, `geodb_coordinates`.`loc_id` `loc_id` FROM `geodb_coordinates` WHERE `lon` > ' . $minlon . ' AND `lon` < ' . $maxlon . ' AND `lat` > ' . $minlat . ' AND `lat` < ' . $maxlat . ' HAVING `distance` < 10 ORDER BY `distance` ASC LIMIT 1'; $rs = sql($sql); if (mysql_num_rows($rs) == 1) { $r = sql_fetch_array($rs); mysql_free_result($rs); $locid = $r['loc_id']; $admtxt1 = geodb_landFromLocid($locid);
function output_namesearch($sName, $nLat, $nLon, $nResultId) { global $login; $sName = '%' . $sName . '%'; echo '<caches>' . "\n"; $rs = sql_slave("SELECT " . geomath::getSqlDistanceFormula($nLon, $nLat, 0) . " AS `distance`, \r\n `caches`.`name`, `caches`.`wp_oc` \r\n FROM `map2_data` \r\n INNER JOIN `caches` ON `map2_data`.`cache_id`=`caches`.`cache_id`\r\n INNeR JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id`\r\n WHERE `caches`.`name` LIKE '&1' \r\n AND (`cache_status`.`allow_user_view`=1 OR `caches`.`user_id`='&3')\r\n AND `map2_data`.`result_id`='&2'\r\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 30", $sName, $nResultId, $login->userid); while ($r = sql_fetch_assoc($rs)) { echo '<cache name="' . xmlentities($r['name']) . '" wpoc="' . xmlentities($r['wp_oc']) . '" />' . "\n"; } sql_free_result($rs); echo '</caches>'; exit; }
function output_namesearch($name, $lat, $lon) { $name = '%' . $name . '%'; echo '<caches>' . "\n"; $rs = sql_slave("SELECT " . geomath::getSqlDistanceFormula($lon, $lat, 0) . " AS `distance`, \r\n `caches`.`name`, `caches`.`wp_oc` \r\n FROM `caches` \r\n WHERE `name` LIKE '&1' \r\n AND `status` IN (1, 2) \r\n\t\t\t\t\t ORDER BY `distance` ASC LIMIT 30", $name); while ($r = sql_fetch_assoc($rs)) { echo '<cache name="' . xmlentities($r['name']) . '" wpoc="' . xmlentities($r['wp_oc']) . '" />' . "\n"; } sql_free_result($rs); echo '</caches>'; exit; }