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();
}