Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 58269f85

Přidáno uživatelem David Raška před asi 10 roky(ů)

Changes:
- Progress of preparing and importing is not printed in CRON mode
- Server now return GPS position of address point
- Server now searches in both town district and town qurters

Zobrazit rozdíly:

README.md
- Run install.sh under root user
- Run /var/www/freenetis-addresses/import.sh under root user for first time database update
Upgrade to 1.1.0
----------------
- Before upgrading, execute SQL script in [upgrade_1.1.0.sql](upgrade_1.1.0.sql)
Changelog
---------
Changelog in debian format is available [here](changelog).
changelog
freenetis-address (1.1.0) stable; urgency=low
* FOR UPGRADE INTORMATIONS READ README.md FIRST
* Progress of preparing and importing is not printed in CRON mode
* Server now return GPS position of address point
* Server now searches in both town district and town qurters
-- David Raska <jeffraska@gmail.com> Thu, 11 Sep 2014 16:00:00 +0200
freenetis-address (0.1.0) stable; urgency=low
* First release
-- David Raska <jeffraska@gmail.com> Thu, 03 Jul 2014 08:00:00 +0200
db_structure.sql
CREATE DATABASE `addresses` DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci;
USE `addresses`;
CREATE TABLE IF NOT EXISTS `addresses` (
`country` int(11) NOT NULL,
`town_name` varchar(50) COLLATE utf8_czech_ci NOT NULL,
......
`street` varchar(50) COLLATE utf8_czech_ci NOT NULL,
`number` varchar(15) COLLATE utf8_czech_ci NOT NULL,
`zip_code` varchar(10) COLLATE utf8_czech_ci NOT NULL,
`jstk_y` varchar(15) COLLATE utf8_czech_ci NOT NULL,
`jstk_x` varchar(15) COLLATE utf8_czech_ci NOT NULL,
UNIQUE KEY `FULLTEXT` (`town_name`,`town_quarter`,`district_name`,`street`,`number`,`zip_code`,`country`),
KEY `country` (`country`),
FULLTEXT KEY `town_name` (`town_name`),
......
`street` varchar(50) COLLATE utf8_czech_ci NOT NULL,
`number` varchar(15) COLLATE utf8_czech_ci NOT NULL,
`zip_code` varchar(10) COLLATE utf8_czech_ci NOT NULL,
`jstk_y` varchar(15) COLLATE utf8_czech_ci NOT NULL,
`jstk_x` varchar(15) COLLATE utf8_czech_ci NOT NULL,
UNIQUE KEY `FULLTEXT` (`town_name`,`town_quarter`,`district_name`,`street`,`number`,`zip_code`,`country`),
KEY `country` (`country`),
FULLTEXT KEY `town_name` (`town_name`),
import.sh
for f in $FILES
do
I=$(($I+1))
echo -en "\rPreparing $I of $FILE_NUM: $f"
if [[ $cron == FALSE ]]
then
echo -en "\rPreparing $I of $FILE_NUM: $f"
fi
# remove columns #change encoding #create number with orientation number #create number without orientaion number #add country id
cat $f | cut -s -d ";" -f 3-4,7-13 | iconv -f "WINDOWS-1250" -t "UTF-8" | sed -r 's/;((č\.)(ev\.)){0,1}(č\.p\.){0,1};([0-9]*);([0-9][0-9]*);(.*)/;\3\2\5\/\6\7/g' | sed -r 's/;((č\.)(ev\.)){0,1}(č\.p\.){0,1};([0-9]*);;/;\3\2\5/g' | sed -r "s/(.*)/$country_id;\1/g" > $f.utf8
cat $f | cut -s -d ";" -f 3-4,7-15 | iconv -f "WINDOWS-1250" -t "UTF-8" | sed -r 's/;(((č\.)(ev\.))|(č\.p\.));([0-9]*);([0-9][0-9]*);(.*)/;\4\3\6\/\7\8/g' | sed -r 's/;(((č\.)(ev\.))|(č\.p\.));([0-9]*);;/;\4\3\6/g' | sed -r "s/(.*)/$country_id;\1/g" > $f.utf8
if [ $? != 0 ];
then
echo -e "\n"
print_warning "Cannot prepare addresses"
print_warning "Cannot prepare addresses - $I of $FILE_NUM: $f"
rm -rf $directory
exit 1
fi
......
for f in $FILES
do
I=$(($I+1))
echo -en "\rImporting $I of $FILE_NUM: $f "
if [[ $cron == FALSE ]]
then
echo -en "\rImporting $I of $FILE_NUM: $f"
fi
mysql $mysql_db -u $mysql_user -p$mysql_pass -h $mysql_server -P $mysql_port --local-infile -e "LOAD DATA LOCAL INFILE '$f' INTO TABLE $mysql_table_tmp FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES"
if [ $? != 0 ];
then
echo -e "\n"
print_warning "Cannot import addresses"
print_warning "Cannot import addresses - $I of $FILE_NUM: $f"
rm -rf $directory
exit 1
fi
index.php
* http://www.freenetis.org/
*
*/
function jstk2gps($x,$y,$h=200){
/*Vypocet zemepisnych souradnic z rovinnych souradnic*/
$a=6377397.15508;
$e=0.081696831215303;
$n=0.97992470462083;
$konst_u_ro=12310230.12797036;
$sinUQ=0.863499969506341;
$cosUQ=0.504348889819882;
$sinVQ=0.420215144586493;
$cosVQ=0.907424504992097;
$alfa=1.000597498371542;
$k=1.003419163966575;
$ro=sqrt($x*$x+$y*$y);
$epsilon=2*atan($y/($ro+$x));
$D=$epsilon/$n;
$S=2*atan(exp(1/$n*log($konst_u_ro/$ro)))-M_PI_2;
$sinS=sin($S);
$cosS=cos($S);
$sinU=$sinUQ*$sinS-$cosUQ*$cosS*cos($D);
$cosU=sqrt(1-$sinU*$sinU);
$sinDV=sin($D)*$cosS/$cosU;
$cosDV=sqrt(1-$sinDV*$sinDV);
$sinV=$sinVQ*$cosDV-$cosVQ*$sinDV;
$cosV=$cosVQ*$cosDV+$sinVQ*$sinDV;
$Ljtsk=2*atan($sinV/(1+$cosV))/$alfa;
$t=exp(2/$alfa*log((1+$sinU)/$cosU/$k));
$pom=($t-1)/($t+1);
do
{
$sinB=$pom;
$pom=$t*exp($e*log((1+$e*$sinB)/(1-$e*$sinB)));
$pom=($pom-1)/($pom+1);
}
while (abs($pom-$sinB)>0.000000000000001);
$Bjtsk=atan($pom/sqrt(1-$pom*$pom));
/* Pravoúhlé souřadnice ve S-JTSK */
$a=6377397.15508;
$f_1=299.152812853;
$e2=1-(1-1/$f_1)*(1-1/$f_1);
$ro=$a/sqrt(1-$e2*sin($Bjtsk)*sin($Bjtsk));
$x=($ro+$H)*cos($Bjtsk)*cos($Ljtsk);
$y=($ro+$H)*cos($Bjtsk)*sin($Ljtsk);
$z=((1-$e2)*$ro+$H)*sin($Bjtsk);
/* Pravoúhlé souřadnice v WGS-84*/
$dx=570.69;
$dy=85.69;
$dz=462.84;
$wz=-5.2611/3600*M_PI/180;
$wy=-1.58676/3600*M_PI/180;
$wx=-4.99821/3600*M_PI/180;
$m=3.543*pow(10,-6);
$xn=$dx+(1+$m)*($x+$wz*$y-$wy*$z);
$yn=$dy+(1+$m)*(-$wz*$x+$y+$wx*$z);
$zn=$dz+(1+$m)*($wy*$x-$wx*$y+$z);
/* Geodetické souřadnice v systému WGS-84*/
$a=6378137.0;
$f_1=298.257223563;
$a_b=$f_1/($f_1-1);
$p=sqrt($xn*$xn+$yn*$yn);
$e2=1-(1-1/$f_1)*(1-1/$f_1);
$theta=atan($zn*$a_b/$p);
$st=sin($theta);
$ct=cos($theta);
$t=($zn+$e2*$a_b*$a*$st*$st*$st)/($p-$e2*$a*$ct*$ct*$ct);
$B=atan($t);
$L=2*atan($yn/($p+$xn));
$H=sqrt(1+$t*$t)*($p-$a/sqrt(1+(1-$e2)*$t*$t));
/* Formát výstupních hodnot */
$B=$B/M_PI*180;
//$sirka="N";
if ($B<0)
{
$B=-$B;
//$sirka="S";
}
$stsirky=floor($B);
$B=($B-$stsirky)*60;
$minsirky=floor($B);
$B=($B-$minsirky)*60;
$vtsirky=round($B*1000)/1000;
//$sirka=$sirka+$stsirky+"°"+$minsirky+"'"+$vtsirky;
$gps = $stsirky.'°'.$minsirky.'′'.$vtsirky.'″';
$L=$L/M_PI*180;
//$delka="E";
if ($L<0)
{
$L=-$L;
//$delka="W";
}
$stdelky=floor($L);
$L=($L-$stdelky)*60;
$mindelky=floor($L);
$L=($L-$mindelky)*60;
$vtdelky=round($L*1000)/1000;
$gps .= ' '.$stdelky.'°'.$mindelky.'′'.$vtdelky.'″';
return $gps;
}
$config = parse_ini_file('/etc/freenetis-addresses.ini') or die();
......
}
$match = array();
if (preg_match('((ev\.č\.)?[0-9][0-9]*(/[0-9][0-9]*[a-zA-Z]*)*)', $street, $match))
if (preg_match('/((ev\.č\.)?[0-9][0-9]*(\/[0-9][0-9]*[a-zA-Z]*)*)$/m', $street, $match))
{
$street = preg_replace(' ((ev\.č\.)?[0-9][0-9]*(/[0-9][0-9]*[a-zA-Z]*)*)', '', $street);
$street = preg_replace('/ ((ev\.č\.)?[0-9][0-9]*(\/[0-9][0-9]*[a-zA-Z]*)*)$/m', '', $street);
// prepare query
$query = "
......
$orderby = '';
$match = array();
if (preg_match('((ev\.č\.)?[0-9][0-9]*(/[0-9][0-9]*[a-zA-Z]*)*)', $street, $match))
if (preg_match('/((ev\.č\.)?[0-9][0-9]*(\/[0-9][0-9]*[a-zA-Z]*)*)$/m', $street, $match))
{
$street = preg_replace(' ((ev\.č\.)?[0-9][0-9]*(/[0-9][0-9]*[a-zA-Z]*)*)', '', $street);
$street = preg_replace('/ ((ev\.č\.)?[0-9][0-9]*(\/[0-9][0-9]*[a-zA-Z]*)*)$/m', '', $street);
$where = "AND number LIKE '".mysql_real_escape_string($match[0])."%'";
$select = '*';
$orderby = "ORDER BY IF (number LIKE '".mysql_real_escape_string($match[0])."', 0, 1)";
$orderby = "ORDER BY IF (number LIKE '".mysql_real_escape_string($match[0])."', 0, 1), number";
}
else if (strrpos($street, ' ') !== FALSE &&
strrpos($street, ' ') + 1 == strlen($street))
......
if (!empty($town))
{
$where = "$where AND town_name LIKE '".mysql_real_escape_string($town)."%'";
$where = "$where AND town_name LIKE '".mysql_real_escape_string($town)."'";
}
if (!empty($zip))
{
$where = "$where AND zip_code LIKE '".mysql_real_escape_string($zip)."%'";
$where = "$where AND zip_code LIKE '".mysql_real_escape_string($zip)."'";
}
if (!empty($district))
{
$where = "$where AND district_name LIKE '".mysql_real_escape_string($district)."%'";
$where = "$where AND district_name LIKE '".mysql_real_escape_string($district)."'";
}
if (!empty($country))
......
}
$query = "
SELECT DISTINCT district_name, town_name, IF (district_name = town_name, 1 , 0) AS same
SELECT DISTINCT district_name, town_name, town_quarter, IF ((district_name = town_name OR town_quarter LIKE '".mysql_real_escape_string($district)."') AND town_quarter NOT LIKE '', 1 , 0) AS same
FROM ".mysql_table."
WHERE district_name LIKE '".mysql_real_escape_string($district)."%'
WHERE (district_name LIKE '".mysql_real_escape_string($district)."%' OR town_quarter LIKE '".mysql_real_escape_string($district)."%')
$where
GROUP BY district_name
ORDER BY same DESC, district_name ASC
GROUP BY district_name, town_quarter
ORDER BY same DESC, district_name ASC, town_quarter ASC
LIMIT 0,15";
}
else if ($town !== NULL)
......
SELECT town_name, district_name
FROM ".mysql_table."
WHERE town_name LIKE '".mysql_real_escape_string($town)."%'
AND zip_code NOT LIKE ''
$where
GROUP BY town_name, district_name
ORDER BY town_name ASC
......
// send headers
@header('Cache-Control: no-cache, must-revalidate');
@header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
@header('Content-type: application/json; charset=utf-8');
// send results
echo json_encode($result);
if ($mode == 'gps')
{
@header('Content-type: text/plain; charset=utf-8');
if (count($result) == 1)
{
// Count GPS coordinates from J-STK
echo jstk2gps($result[0]['jstk_x'], $result[0]['jstk_y']);
}
}
else
{
@header('Content-type: application/json; charset=utf-8');
echo json_encode($result);
}
upgrade_1.1.0.sql
/*
* This file is part of open source system FreenetIS
* and it is released under GPLv3 licence.
*
* More info about licence can be found:
* http://www.gnu.org/licenses/gpl-3.0.html
*
* More info about project can be found:
* http://www.freenetis.org/
*
*/
USE `addresses`;
ALTER TABLE `addresses` ADD `jstk_y` VARCHAR( 15 ) NOT NULL ,
ADD `jstk_x` VARCHAR( 15 ) NOT NULL;
ALTER TABLE `addresses_tmp` ADD `jstk_y` VARCHAR( 15 ) NOT NULL ,
ADD `jstk_x` VARCHAR( 15 ) NOT NULL;

Také k dispozici: Unified diff