LGMap: coordinates/local_monographs_coordinates

annotate coordinates/local_monographs_coordinates_old.php @ 26:9f67e8afa1cc default tip

add new file for new LGService UI project

author	Calvin Yeh <cyeh@mpiwg-berlin.mpg.de>
date	Thu, 28 Sep 2017 23:58:34 +0200
parents	6bdc6f8c97f0
children

rev	line source
4 6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	1 <?php
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	2 include_once("../../interface/config.php");
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	3 include_once('../../interface/Lib_mb_utf8.php');
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	4 set_time_limit(0);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	5 ini_set('memory_limit', '-1');
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	6 ini_set("auto_detect_line_endings", true);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	7
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	8 $link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	9 mysql_query("SET NAMES utf8");
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	10
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	11 if (!$link_mysql) {
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	12 die('Could not connect: ' . mysql_error());
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	13 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	14
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	15 $db_selected = mysql_select_db($mysql_database, $link_mysql);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	16 if (!$db_selected) {
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	17 die ('Can\'t use foo : ' . mysql_error());
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	18 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	19
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	20 function getCoordinateFromFile(){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	21 $fp=fopen("local_monographs_coordinates_filtered.txt","r");
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	22 $columnNameMapping=['level1'=>0,'level2'=>1,'start_year'=>2,'end_year'=>2,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	23 'period'=>3,'books_id'=>4,'name'=>5,'place_name'=>6,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	24 'admin_type'=>7,'x'=>8,'y'=>8,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	25 'chgis_id'=>9,'1820_id'=>10,'1911_id'=>11,'cbdb_id'=>12];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	26 $data=fgetcsv($fp,10000,"\t");
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	27 $coordinateArray=array();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	28 $count=0;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	29 while(!feof($fp)){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	30 $data=fgetcsv($fp,10000,"\t");
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	31 $pattern='/([0-9]+)-([0-9]+)/';
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	32 if(!preg_match($pattern,$data[2],$match)){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	33 if($data[4]!="")
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	34 echo $data[4]." contains wrong year format<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	35 continue;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	36 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	37 foreach($columnNameMapping as $newName=>$oldName){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	38 $coordinateArray[$count][$newName]=$data[$oldName];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	39 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	40 $coordinateArray[$count]['start_year']=$match[1];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	41 $coordinateArray[$count]['end_year']=$match[2];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	42 $coordinateArray[$count]['books_id']=sprintf("%05d",$coordinateArray[$count]['books_id']);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	43 $coorArray=array();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	44 $idNameArray=['chgis_id','1820_id','1911_id','cbdb_id'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	45 $coorArray=explode(';',$coordinateArray[$count]['x']); //Check if there are multiple coordinates associated with this place/book
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	46 $size=sizeof($coorArray);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	47 foreach($idNameArray as $name){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	48 if($coordinateArray[$count][$name]==""){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	49 for($i=0;$i<$size;$i++){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	50 $coordinateArray[$count][$name][$i]='';
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	51 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	52 continue;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	53 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	54 $coordinateArray[$count][$name]=explode(';',$coordinateArray[$count][$name]);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	55 if($size!=sizeof($coordinateArray[$count][$name])){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	56 echo $coordinateArray[$count]['books_id']." column ".$name." contains wrong number of elements<br>"; // Check for data inconsistency
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	57 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	58 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	59 $coordinateArray[$count]['x']=array();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	60 $coordinateArray[$count]['y']=array();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	61 foreach($coorArray as $idx=>$coor){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	62 $coordinateArray[$count]['x'][$idx]='';
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	63 $coordinateArray[$count]['y'][$idx]='';
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	64 if($coor!=''){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	65 list($coordinateArray[$count]['x'][$idx],$coordinateArray[$count]['y'][$idx])=explode(',',$coor);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	66 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	67 $coordinateArray[$count]['x'][$idx]=trim($coordinateArray[$count]['x'][$idx]);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	68 $coordinateArray[$count]['y'][$idx]=trim($coordinateArray[$count]['y'][$idx]);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	69 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	70
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	71 $count++;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	72 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	73 return $coordinateArray;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	74 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	75
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	76 function printCoordinate($coordinateArray){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	77 echo "<table>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	78 foreach($coordinateArray as $coordinate){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	79 echo "<tr>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	80 $array=['x','y','chgis_id','1820_id','1911_id','cbdb_id'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	81 foreach($coordinate as $name=>$column){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	82 if(in_array($name,$array)){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	83 echo "<td>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	84 foreach($column as $subColumn){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	85 echo $subColumn."<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	86 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	87 continue;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	88 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	89 echo "<td>".$column;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	90 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	91 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	92 echo "</table>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	93 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	94 function bookIsInDatabase($info){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	95 $compareColumnName=['level1','level2','start_year','end_year','period','name'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	96 $query="SELECT * FROM books WHERE id='".$info['books_id']."'";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	97 $result=mysql_query($query);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	98 if(mysql_num_rows($result)!=0){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	99 $row=mysql_fetch_assoc($result);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	100 if($info['level2']=="") $info['level2']="NULL";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	101 foreach($compareColumnName as $column){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	102 if($row[$column]!=$info[$column] && $column!='level2'){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	103 echo $info['books_id']." column ".$column." is different from that in the database<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	104 //return false;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	105 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	106 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	107 return true;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	108 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	109 return false;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	110 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	111 function insertCoordinateIntoDatabase($coordinateArray){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	112 $row=0;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	113 $columnName=['books_id','place_name','admin_type','x','y','chgis_id','1820_id','1911_id','cbdb_id'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	114 foreach($coordinateArray as $coordinate){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	115 if(bookIsInDatabase($coordinate)){ // Check if the book_id already in the 'books' table
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	116 foreach($coordinate['x'] as $idx=>$c){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	117 $query="INSERT INTO coordinates_books (";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	118 $count=0;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	119 foreach($columnName as $column){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	120 $query.=$column;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	121 if($count!=sizeof($columnName)-1){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	122 $query.=",";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	123 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	124 $count++;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	125 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	126 $query.=") VALUES (";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	127 $count=0;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	128 foreach($columnName as $i=>$column){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	129 if(!is_array($coordinate[$column])){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	130 $query.="'".$coordinate[$column]."'";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	131 }else{
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	132 $query.="'".$coordinate[$column][$idx]."'";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	133 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	134 if($count!=sizeof($columnName)-1){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	135 $query.=",";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	136 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	137 $count++;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	138 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	139 $query.=")";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	140 //echo $query."<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	141 $result=mysql_query($query);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	142 if(!$result){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	143 echo "Failed during inserting: <br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	144 echo $query."<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	145 }else{
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	146 //echo $coordinate['name']."++++++++++++".$coordinate['books_id']."<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	147 $row++;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	148 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	149 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	150 //echo "<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	151 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	152 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	153 echo "# of rows: ".$row."<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	154 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	155 function getBookListFromDatabase(){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	156 $query="SELECT coor.place_name AS Address, coor.admin_type AS ADMIN_TYPE, books.level1 AS LEVEL1, books.level2 AS LEVEL2,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	157 books.name AS Name, books.period AS PERIOD,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	158 books.start_year AS 'TimeSpan:begin', books.end_year AS 'TimeSpan:end',
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	159 books.id AS BOOK_ID, coor.x AS Longitude, coor.y AS Latitude,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	160 coor.chgis_id AS CHGIS_ID, coor.1820_id AS 1820_ID,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	161 coor.1911_id AS 1911_ID, coor.cbdb_id AS CBDB_ID,
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	162 info.volume AS VOLUME, info.author AS AUTHOR, info.edition AS EDITION FROM books
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	163 JOIN coordinates_books coor ON books.id=coor.books_id
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	164 JOIN books_info info ON books.id=info.books_id";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	165 $bookArray=array();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	166 $result=mysql_query($query);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	167 while($row=mysql_fetch_assoc($result)){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	168 $bookArray[]=$row;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	169 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	170 return $bookArray;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	171 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	172
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	173 ?>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	174
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	175 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	176 <html>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	177 <head>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	178 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	179 <style>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	180 td{
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	181 min-width:80px;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	182 max-width:160px;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	183 border-right:1px #aaa solid;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	184 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	185 </style>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	186 </head>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	187 <body>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	188 <?php
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	189 $coordinateArray=getCoordinateFromFile(); //Read from local_monographs_coordinates_filtered.txt to get the most updated coordinates
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	190 //printCoordinate($coordinateArray);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	191 insertCoordinateIntoDatabase($coordinateArray); //insert them to db
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	192 $bookArray=getBookListFromDatabase();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	193 foreach($bookArray as $idx=>$book){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	194 $bookArray[$idx]['Name']="(".$book['PERIOD'].") ".$book['Name'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	195 $book['AUTHOR']=str_replace("（","(",$book['AUTHOR']);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	196 $book['AUTHOR']=str_replace("）",") ",$book['AUTHOR']);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	197 $bookArray[$idx]['Description']=$book['VOLUME']." ╱ ".$book['AUTHOR']." ╱ ".$book['EDITION'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	198 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	199 $columnNameArray=['Address','ADMIN_TYPE','LEVEL1','LEVEL2',
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	200 'Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude',
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	201 'BOOK_ID','CHGIS_ID','1820_ID','1911_ID','CBDB_ID','Description'];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	202
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	203 echo sizeof($bookArray)."<br>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	204 echo "<table>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	205 echo "<tr>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	206 $fp=fopen("books_coordinates.csv","w");
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	207 foreach($columnNameArray as $column){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	208 echo "<td>".$column;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	209 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	210 fputcsv($fp,$columnNameArray);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	211 $count=0;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	212 foreach($bookArray as $book){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	213 echo "<tr>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	214 $row=array();
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	215 foreach($columnNameArray as $column){
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	216 echo "<td>".$book[$column];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	217 $row[]=$book[$column];
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	218 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	219 fputcsv($fp,$row);
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	220 $count++;
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	221 }
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	222 echo "</table>";
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	223
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	224
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	225 ?>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	226
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	227 </body>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	228 </html>
6bdc6f8c97f0 update books_coordinates.csv Zoe Hong <zhong@mpiwg-berlin.mpg.de> parents: diff changeset	229

Mercurial > hg > LGMap

annotate coordinates/local_monographs_coordinates_old.php @ 26:9f67e8afa1cc default tip