function run() { db_connect(); $rsCache = sql("SELECT `cache_id`, `latitude`, `longitude` FROM `caches` WHERE `need_npa_recalc`=1"); while ($rCache = mysql_fetch_assoc($rsCache)) { $sql = sql("DELETE FROM `cache_npa_areas` WHERE `cache_id`='&1' AND `calculated`=1", $rCache['cache_id']); mysql_query($sql); // Natura 2000 $rsLayers = sql("SELECT `id`, AsText(`shape`) AS `geometry` FROM `npa_areas` WHERE WITHIN(GeomFromText('&1'), `shape`)", 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while ($rLayers = mysql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $sql = sql("INSERT INTO `cache_npa_areas` (`cache_id`, `npa_id`, `calculated`) VALUES ('&1', '&2', 1) ON DUPLICATE KEY UPDATE `calculated`=1", $rCache['cache_id'], $rLayers['id']); mysql_query($sql); } } mysql_free_result($rsLayers); // Parki PL $rsLayers = sql("SELECT `id`, AsText(`shape`) AS `geometry` FROM `parkipl` WHERE WITHIN(GeomFromText('&1'), `shape`)", 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while ($rLayers = mysql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $sql = sql("INSERT INTO `cache_npa_areas` (`cache_id`, `parki_id`, `calculated`) VALUES ('&1', '&2', 1) ON DUPLICATE KEY UPDATE `calculated`=1", $rCache['cache_id'], $rLayers['id']); mysql_query($sql); } } mysql_free_result($rsLayers); // End of Parki PL $sql = sql("UPDATE `caches` SET `need_npa_recalc`=0 WHERE `cache_id`='&1'", $rCache['cache_id']); mysql_query($sql); } mysql_free_result($rsCache); db_disconnect(); }
public function run() { $rsCache = sql("SELECT `cache_id`, `latitude`, `longitude` FROM `caches` WHERE `need_npa_recalc`=1"); while ($rCache = sql_fetch_assoc($rsCache)) { sql("DELETE FROM `cache_npa_areas` WHERE `cache_id`='&1' AND `calculated`=1", $rCache['cache_id']); $rsLayers = sql("SELECT `id`, `type_id`, AsText(`shape`) AS `geometry` \n FROM `npa_areas` WHERE `exclude`=0 AND MBRWITHIN(GeomFromText('&1'), `shape`)", 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while ($rLayers = sql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $bExclude = false; // prüfen, ob in ausgesparter Fläche $rsExclude = sql("SELECT `id`, AsText(`shape`) AS `geometry`\n FROM `npa_areas`\n WHERE `exclude` = 1\n AND `type_id`='&1'\n AND MBRWITHIN(GeomFromText('&2'), `shape`)", $rLayers['type_id'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while (($rExclude = sql_fetch_assoc($rsExclude)) && $bExclude == false) { if (gis::ptInLineRing($rExclude['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $bExclude = true; } } sql_free_result($rsExclude); if ($bExclude == false) { sql("INSERT INTO `cache_npa_areas` (`cache_id`, `npa_id`, `calculated`)\n VALUES ('&1', '&2', 1) ON DUPLICATE KEY UPDATE `calculated`=1", $rCache['cache_id'], $rLayers['id']); } } } sql_free_result($rsLayers); sql("UPDATE `caches` SET `need_npa_recalc`=0 WHERE `cache_id`='&1'", $rCache['cache_id']); } sql_free_result($rsCache); }
function run() { global $opt; $rsCache = sql("SELECT `caches`.`cache_id`, `caches`.`latitude`, `caches`.`longitude` FROM `caches` LEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id` WHERE ISNULL(`cache_location`.`cache_id`) UNION SELECT `caches`.`cache_id`, `caches`.`latitude`, `caches`.`longitude` FROM `caches` INNER JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id` WHERE `caches`.`last_modified`>`cache_location`.`last_modified`"); while ($rCache = sql_fetch_assoc($rsCache)) { $sCode = ''; $rsLayers = sql("SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM `nuts_layer` WHERE WITHIN(GeomFromText('&1'), `shape`) ORDER BY `level` DESC", 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while ($rLayers = sql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $sCode = $rLayers['code']; break; } } sql_free_result($rsLayers); if ($sCode != '') { $adm1 = null; $code1 = null; $adm2 = null; $code2 = null; $adm3 = null; $code3 = null; $adm4 = null; $code4 = null; if (mb_strlen($sCode) > 5) { $sCode = mb_substr($sCode, 0, 5); } if (mb_strlen($sCode) == 5) { $code4 = $sCode; $adm4 = sql_value("SELECT `name` FROM `nuts_codes` WHERE `code`='&1'", null, $sCode); $sCode = mb_substr($sCode, 0, 4); } if (mb_strlen($sCode) == 4) { $code3 = $sCode; $adm3 = sql_value("SELECT `name` FROM `nuts_codes` WHERE `code`='&1'", null, $sCode); $sCode = mb_substr($sCode, 0, 3); } if (mb_strlen($sCode) == 3) { $code2 = $sCode; $adm2 = sql_value("SELECT `name` FROM `nuts_codes` WHERE `code`='&1'", null, $sCode); $sCode = mb_substr($sCode, 0, 2); } if (mb_strlen($sCode) == 2) { $code1 = $sCode; // try to get localised name first $adm1 = sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t FROM `countries`\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id` AND `countries`.`name`=`sys_trans`.`text`\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE `countries`.`short`='&1'", null, $sCode, $opt['template']['default']['locale']); if ($adm1 == null) { $adm1 = sql_value("SELECT `name` FROM `nuts_codes` WHERE `code`='&1'", null, $sCode); } } sql("INSERT INTO `cache_location` (`cache_id`, `adm1`, `adm2`, `adm3`, `adm4`, `code1`, `code2`, `code3`, `code4`) VALUES ('&1', '&2', '&3', '&4', '&5', '&6', '&7', '&8', '&9') ON DUPLICATE KEY UPDATE `adm1`='&2', `adm2`='&3', `adm3`='&4', `adm4`='&5', `code1`='&6', `code2`='&7', `code3`='&8', `code4`='&9'", $rCache['cache_id'], $adm1, $adm2, $adm3, $adm4, $code1, $code2, $code3, $code4); } else { $sCountry = sql_value("SELECT IFNULL(`sys_trans_text`.`text`, `countries`.`name`)\r\n\t\t\t\t FROM `caches` \r\n\t\t\t\t INNER JOIN `countries` ON `caches`.`country`=`countries`.`short`\r\n\t\t\t\t LEFT JOIN `sys_trans` ON `countries`.`trans_id`=`sys_trans`.`id` AND `countries`.`name`=`sys_trans`.`text`\r\n\t\t\t\t LEFT JOIN `sys_trans_text` ON `sys_trans`.`id`=`sys_trans_text`.`trans_id` AND `sys_trans_text`.`lang`='&2'\r\n\t\t\t\t WHERE `caches`.`cache_id`='&1'", null, $rCache['cache_id'], $opt['template']['default']['locale']); $sCode1 = sql_value("SELECT `caches`.`country` FROM `caches` WHERE `caches`.`cache_id`='&1'", null, $rCache['cache_id']); sql("INSERT INTO `cache_location` (`cache_id`, `adm1`, `code1`) VALUES ('&1', '&2', '&3') ON DUPLICATE KEY UPDATE `adm1`='&2', `adm2`=NULL, `adm3`=NULL, `adm4`=NULL, `code1`='&3', `code2`=NULL, `code3`=NULL, `code4`=NULL", $rCache['cache_id'], $sCountry, $sCode1); } } sql_free_result($rsCache); }
tpl_set_var('lon_float', sprintf('%0.5f', $lon_float)); tpl_set_var('lon_dir', $lon_dir); tpl_set_var('lon_deg_int', $lon_deg_int); tpl_set_var('lon_min_int', $lon_min_int); tpl_set_var('lon_sec_float', $lon_sec_float); tpl_set_var('lon_min_float', $lon_min_float); /* begin db connect */ db_connect(); if ($dblink === false) { echo 'Unable to connect to database'; exit; } $sCode = ''; $rsLayers = sql("SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM `nuts_layer` WHERE WITHIN(GeomFromText('&1'), `shape`) ORDER BY `level` DESC", 'POINT(' . $lon . ' ' . $lat . ')'); while ($rLayers = mysql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $lon . ' ' . $lat . ')')) { $sCode = $rLayers['code']; break; } } mysql_free_result($rsLayers); if ($sCode != '') { $adm1 = null; $code1 = null; $adm2 = null; $code2 = null; $adm3 = null; $code3 = null; $adm4 = null; $code4 = null; if (mb_strlen($sCode) > 5) {
function getValidLoginUserData() { global $usr, $dblink; $user_id = $usr['userid']; //Check user Level $user_level = sqlValue("SELECT level FROM ajax_chat_users WHERE `userID`={$user_id}", 0); // get region from Home coordiantes $usrcountry = sqlValue("SELECT country FROM user WHERE `user_id`={$user_id}", 0); $lon = sqlValue("SELECT longitude FROM user WHERE `user_id`={$user_id}", 0); $lat = sqlValue("SELECT latitude FROM user WHERE `user_id`={$user_id}", 0); $point = 'POINT(' . $lon . ' ' . $lat . ')'; if ($lat == 0 && $lon == 0) { $region = $usrcountry; } else { $sCode = ''; $rsLayers = sql("SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM `nuts_layer` WHERE WITHIN(GeomFromText('{$point}'), `shape`) ORDER BY `level` DESC"); while ($rLayers = mysql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $lon . ' ' . $lat . ')')) { $sCode = $rLayers['code']; break; } } mysql_free_result($rsLayers); if ($sCode != '') { $adm1 = null; $code1 = null; $adm2 = null; $code2 = null; $adm3 = null; $code3 = null; $adm4 = null; $code4 = null; if (mb_strlen($sCode) > 5) { $sCode = mb_substr($sCode, 0, 5); } if (mb_strlen($sCode) == 5) { $code4 = $sCode; $adm4 = sqlValue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); $sCode = mb_substr($sCode, 0, 4); } if (mb_strlen($sCode) == 4) { $code3 = $sCode; $adm3 = sqlvalue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); $sCode = mb_substr($sCode, 0, 3); } if (mb_strlen($sCode) == 3) { $code2 = $sCode; $adm2 = sqlvalue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); $sCode = mb_substr($sCode, 0, 2); } if (mb_strlen($sCode) == 2) { $adm1 = $usrcountry; } $region = $adm3; } } $usern = $usr['username'] . '[' . $region . ']'; $userData = array(); $userData['userID'] = $usr['userid']; $userData['userName'] = $this->trimUserName($usern); if ($user_level > 0) { $userData['userRole'] = AJAX_CHAT_MODERATOR; } else { $userData['userRole'] = AJAX_CHAT_USER; } return $userData; }
<?php include 'gis.php'; $start = microtime(true); $gis = new gis(); $x = 2; #经纬度 116.40741300000002 $y = 2; #经纬度 39.904214 $level = 1; #1代表1千米,2代表2千米 3代表5千米 4代表1万米 $limit = '0,1000000'; #需要的条数 $data = $gis->area($x, $y, $level, $limit); //插入测试一百万条数据 //$gis->addTestdata(); #print_r($data); $end = microtime(true); echo $start; echo '<hr />'; echo $end; echo '<hr />'; echo $end - $start; /** * 方法思路 * 得到特定的点后,以这个点为中点,画一个菱形◇,然后使用MySQL空间数据库的算法,取出特定数据 * 未增加索引前,遍历一百万条数据所需时间为2.5秒 * 增加索引后,遍历一百万条数据所需时间为0.78704500198364秒 * * 注意事项:gis空间数据库索引只支持MyISAM数据表 */
/** * * @@param array $opt - database accesing data from opencaching config * @param float $lat geografical latitude (coordinates) * @param float $lon geografical longitude (coordinates) * * @return array with code and names of regions selected from input geografical coordinates.void */ public function GetRegion($lat, $lon) { require_once __DIR__ . '/lib/gis/gis.class.php'; $lat_float = (double) $lat; $lon_float = (double) $lon; $sCode = ''; $tmpqery = "SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM `nuts_layer` WHERE WITHIN(GeomFromText('POINT({$lon} {$lat})'), `shape`) ORDER BY `level` DESC"; $db = new dataBase(); $db->simpleQuery($tmpqery); $rsLayers = $db->dbResultFetchAll(); foreach ($rsLayers as $rLayers) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $lon . ' ' . $lat . ')')) { $sCode = $rLayers['code']; break; } } if ($sCode != '') { $adm1 = null; $code1 = null; $adm2 = null; $code2 = null; $adm3 = null; $code3 = null; $adm4 = null; $code4 = null; if (mb_strlen($sCode) > 5) { $sCode = mb_substr($sCode, 0, 5); } if (mb_strlen($sCode) == 5) { $code4 = $sCode; $q = "SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'"; $db->simpleQuery($q); $re = $db->dbResultFetch(); $adm4 = $re["name"]; unset($re, $q); $sCode = mb_substr($sCode, 0, 4); } if (mb_strlen($sCode) == 4) { $code3 = $sCode; $q = "SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'"; $db->simpleQuery($q); $re = $db->dbResultFetch(); $adm3 = $re["name"]; unset($re, $q); $sCode = mb_substr($sCode, 0, 3); } if (mb_strlen($sCode) == 3) { $code2 = $sCode; $q = "SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'"; $db->simpleQuery($q); $re = $db->dbResultFetch(); $adm2 = $re["name"]; unset($re, $q); $sCode = mb_substr($sCode, 0, 2); } if (mb_strlen($sCode) == 2) { $code1 = $sCode; // try to get localised name first $q = "SELECT `countries`.`pl` FROM `countries` WHERE `countries`.`short`='{$sCode}'"; // TODO: country column should be localized $db->simpleQuery($q); $re = $db->dbResultFetch(); $adm1 = $re["pl"]; unset($re, $q); if ($adm1 == null) { $q = "SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'"; $db->simpleQuery($q); $re = $db->dbResultFetch(); $adm1 = $re["name"]; unset($re, $q); } } $wynik['adm1'] = $adm1; $wynik['adm2'] = $adm2; $wynik['adm3'] = $adm3; $wynik['adm4'] = $adm4; $wynik['code1'] = $code1; $wynik['code2'] = $code2; $wynik['code3'] = $code3; $wynik['code4'] = $code4; } else { $wynik = false; } return $wynik; }
* SET NEW.`last_modified`=NOW(); * END;"); * * run it once a day * */ $rootpath = '../../'; require_once $rootpath . 'lib/gis/gis.class.php'; require_once $rootpath . 'lib/common.inc.php'; global $lang; $rsCache = XDb::xSql("SELECT `caches`.`cache_id`, `caches`.`latitude`, `caches`.`longitude`\n FROM `caches`\n LEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id`\n WHERE ISNULL(`cache_location`.`cache_id`)\n UNION\n SELECT `caches`.`cache_id`, `caches`.`latitude`, `caches`.`longitude`\n FROM `caches`\n INNER JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id`\n WHERE `caches`.`country`!='PL'\n AND `caches`.`last_modified`>`cache_location`.`last_modified`"); while ($rCache = XDb::xFetchArray($rsCache)) { $sCode = ''; $rsLayers = XDb::xSql("SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM `nuts_layer`\n WHERE WITHIN(GeomFromText( ? ), `shape`)\n ORDER BY `level` DESC", 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while ($rLayers = XDb::xFetchArray($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $sCode = $rLayers['code']; break; } } XDb::xFreeResults($rsLayers); if ($sCode != '') { $adm1 = null; $code1 = null; $adm2 = null; $code2 = null; $adm3 = null; $code3 = null; $adm4 = null; $code4 = null; if (mb_strlen($sCode) > 5) {
function run() { global $dblink, $lang; /* begin db connect */ db_connect(); if ($dblink === false) { echo 'Unable to connect to database'; exit; } /* end db connect */ // global $opt; $rsCache = sql("SELECT `caches`.`cache_id`, `caches`.`latitude`, `caches`.`longitude` FROM `caches` LEFT JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id` WHERE ISNULL(`cache_location`.`cache_id`) UNION SELECT `caches`.`cache_id`, `caches`.`latitude`, `caches`.`longitude` FROM `caches` INNER JOIN `cache_location` ON `caches`.`cache_id`=`cache_location`.`cache_id` WHERE `caches`.`country`!='PL' AND `caches`.`last_modified`>`cache_location`.`last_modified`"); while ($rCache = mysql_fetch_assoc($rsCache)) { $sCode = ''; $rsLayers = sql("SELECT `level`, `code`, AsText(`shape`) AS `geometry` FROM `nuts_layer` WHERE WITHIN(GeomFromText('&1'), `shape`) ORDER BY `level` DESC", 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')'); while ($rLayers = mysql_fetch_assoc($rsLayers)) { if (gis::ptInLineRing($rLayers['geometry'], 'POINT(' . $rCache['longitude'] . ' ' . $rCache['latitude'] . ')')) { $sCode = $rLayers['code']; break; } } mysql_free_result($rsLayers); if ($sCode != '') { $adm1 = null; $code1 = null; $adm2 = null; $code2 = null; $adm3 = null; $code3 = null; $adm4 = null; $code4 = null; if (mb_strlen($sCode) > 5) { $sCode = mb_substr($sCode, 0, 5); } if (mb_strlen($sCode) == 5) { $code4 = $sCode; $adm4 = sqlValue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); $sCode = mb_substr($sCode, 0, 4); } if (mb_strlen($sCode) == 4) { $code3 = $sCode; $adm3 = sqlvalue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); $sCode = mb_substr($sCode, 0, 3); } if (mb_strlen($sCode) == 3) { $code2 = $sCode; $adm2 = sqlvalue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); $sCode = mb_substr($sCode, 0, 2); } if (mb_strlen($sCode) == 2) { $code1 = $sCode; if (checkField('countries', 'list_default_' . $lang)) { $lang_db = $lang; } else { $lang_db = "en"; } // try to get localised name first $adm1 = sqlvalue("SELECT `countries`.`{$lang}`\n FROM `countries`\n WHERE `countries`.`short`='{$sCode}'", 0); if ($adm1 == null) { $adm1 = sqlvalue("SELECT `name` FROM `nuts_codes` WHERE `code`='{$sCode}'", 0); } } $sql = sql("INSERT INTO `cache_location` (`cache_id`, `adm1`, `adm2`, `adm3`, `adm4`, `code1`, `code2`, `code3`, `code4`) VALUES ('&1', '&2', '&3', '&4', '&5', '&6', '&7', '&8', '&9') ON DUPLICATE KEY UPDATE `adm1`='&2', `adm2`='&3', `adm3`='&4', `adm4`='&5', `code1`='&6', `code2`='&7', `code3`='&8', `code4`='&9'", $rCache['cache_id'], $adm1, $adm2, $adm3, $adm4, $code1, $code2, $code3, $code4); mysql_query($sql); } else { if (checkField('countries', 'list_default_' . $lang)) { $lang_db = $lang; } else { $lang_db = "en"; } $sCountry = sqlvalue("SELECT `countries`.`pl`\n FROM `caches`\n INNER JOIN `countries` ON `caches`.`country`=`countries`.`short`\n WHERE `caches`.`cache_id`='{$rCache['cache_id']}'", 0); $sCode1 = sqlvalue("SELECT `caches`.`country` FROM `caches` WHERE `caches`.`cache_id`='&1'", null, $rCache['cache_id']); $sql = sql("INSERT INTO `cache_location` (`cache_id`, `adm1`, `code1`) VALUES ('&1', '&2', '&3') ON DUPLICATE KEY UPDATE `adm1`='&2', `adm2`=NULL, `adm3`=NULL, `adm4`=NULL, `code1`='&3', `code2`=NULL, `code3`=NULL, `code4`=NULL", $rCache['cache_id'], $sCountry, $sCode1); mysql_query($sql); } } mysql_free_result($rsCache); db_disconnect(); }