Mercurial > hg > LGMap
annotate coordinates/provincial_capital_coordinates.php @ 7:0330b2138c87
add collecting post data from LGDataverse
| author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
|---|---|
| date | Fri, 04 Sep 2015 16:16:10 +0200 |
| parents | 2b5a5cb5d6d1 |
| children |
| rev | line source |
|---|---|
| 0 | 1 <?php |
|
3
2b5a5cb5d6d1
update books_coordinates.csv
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
2 include_once("../config/config.php"); |
|
2b5a5cb5d6d1
update books_coordinates.csv
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
3 include_once('../config/Lib_mb_utf8.php'); |
| 0 | 4 set_time_limit(0); |
| 5 ini_set('memory_limit', '-1'); | |
| 6 ini_set("auto_detect_line_endings", true); | |
| 7 | |
| 8 $link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password); | |
| 9 mysql_query("SET NAMES utf8"); | |
| 10 | |
| 11 if (!$link_mysql) { | |
| 12 die('Could not connect: ' . mysql_error()); | |
| 13 } | |
| 14 | |
| 15 $db_selected = mysql_select_db($mysql_database, $link_mysql); | |
| 16 if (!$db_selected) { | |
| 17 die ('Can\'t use foo : ' . mysql_error()); | |
| 18 } | |
| 19 | |
| 20 function getCoordinateFromFile(){ | |
| 21 $fp=fopen("provincial_capital_coordinates.txt","r"); | |
| 22 $columnNameMapping=[0=>"province_cht",1=>"province_py",2=>"province_chs", | |
| 23 3=>"capital_cht",4=>"capital_py",5=>"capital_chs", | |
| 24 6=>"x",7=>"y"]; | |
| 25 $data=fgetcsv($fp,10000,"\t"); | |
| 26 $coordinateArray=array(); | |
| 27 $count=0; | |
| 28 while(!feof($fp)){ | |
| 29 $data=fgetcsv($fp,10000,"\t"); | |
| 30 foreach($columnNameMapping as $oldName=>$newName){ | |
| 31 $coordinateArray[$count][$newName]=$data[$oldName]; | |
| 32 } | |
| 33 $count++; | |
| 34 } | |
| 35 return $coordinateArray; | |
| 36 } | |
| 37 | |
| 38 function printCoordinate($coordinateArray){ | |
| 39 echo "<table>"; | |
| 40 foreach($coordinateArray as $coordinate){ | |
| 41 echo "<tr>"; | |
| 42 foreach($coordinate as $column){ | |
| 43 echo "<td>".$column; | |
| 44 } | |
| 45 } | |
| 46 echo "</table>"; | |
| 47 } | |
| 48 function provinceIsInDatabase($province){ | |
| 49 $query="SELECT * FROM coordinates_provincial_capitals WHERE province_cht='".$province."'"; | |
| 50 $result=mysql_query($query); | |
| 51 if(mysql_num_rows($result)!=0){ | |
| 52 return true; | |
| 53 } | |
| 54 return false; | |
| 55 } | |
| 56 function insertCoordinateIntoDatabase($coordinateArray){ | |
| 57 $row=0; | |
| 58 foreach($coordinateArray as $coordinate){ | |
| 59 if(provinceIsInDatabase($coordinate['province_cht'])){ | |
| 60 echo "------------".$coordinate['province_cht']."<br>"; | |
| 61 }else{ | |
| 62 $query="INSERT INTO coordinates_provincial_capitals ("; | |
| 63 $count=0; | |
| 64 foreach($coordinate as $columnName=>$column){ | |
| 65 $query.=$columnName; | |
| 66 if($count!=sizeof($coordinate)-1){ | |
| 67 $query.=","; | |
| 68 } | |
| 69 $count++; | |
| 70 } | |
| 71 $query.=") VALUES ("; | |
| 72 $count=0; | |
| 73 foreach($coordinate as $columnName=>$column){ | |
| 74 $query.="'".$column."'"; | |
| 75 if($count!=sizeof($coordinate)-1){ | |
| 76 $query.=","; | |
| 77 } | |
| 78 $count++; | |
| 79 } | |
| 80 $query.=")"; | |
| 81 echo $query."<br>"; | |
| 82 $result=mysql_query($query); | |
| 83 if(!$result){ | |
| 84 echo "Failed during inserting: <br>"; | |
| 85 echo $query."<br>"; | |
| 86 }else{ | |
| 87 echo "++++++++++++".$coordinate['province_cht']."<br>"; | |
| 88 $row++; | |
| 89 } | |
| 90 } | |
| 91 } | |
| 92 echo "# of rows: ".$row."<br>"; | |
| 93 } | |
| 94 | |
| 95 function getBookListFromDatabase(){ | |
| 96 $query="SELECT books.level1 AS Address, books.level1 AS LEVEL1, books.name AS Name, books.period AS PERIOD, | |
| 97 books.level2 AS LEVEL2, books.start_year AS 'TimeSpan:begin', books.end_year AS 'TimeSpan:end', | |
| 98 books.id AS BOOK_ID, coor.x AS Longitude, coor.y AS Latitude, | |
| 99 info.volume AS VOLUME, info.author AS AUTHOR, info.edition AS EDITION FROM books | |
| 100 JOIN coordinates_provincial_capitals coor ON books.level1=coor.province_cht | |
| 101 JOIN books_info info ON books.id=info.books_id"; | |
| 102 $bookArray=array(); | |
| 103 $result=mysql_query($query); | |
| 104 while($row=mysql_fetch_assoc($result)){ | |
| 105 $bookArray[]=$row; | |
| 106 } | |
| 107 return $bookArray; | |
| 108 } | |
| 109 function getBookListFromFile(){ | |
| 110 $fp=fopen("local_monographs_list.txt","r"); | |
| 111 fgetcsv($fp,100000,"\t"); | |
| 112 $bookArray=array(); | |
| 113 $count=0; | |
| 114 //$columnNameMapping=[0=>"level1",2=>"level2",4=>"years",5=>"period",6=>"bookId",8=>"title",9=>"placeName"]; | |
| 115 $columnNameMapping=["Address"=>0,"Name"=>8,"LEVEL1"=>0,"PLACE_NAME"=>9, | |
| 116 "Longitude"=>0,"Latitude"=>0, | |
| 117 "TimeSpan:begin"=>0,"TimeSpan:end"=>0,"PERIOD"=>5, | |
| 118 "BOOK_ID"=>6,"Description"=>8]; | |
| 119 while(!feof($fp)){ | |
| 120 $data=fgetcsv($fp,100000,"\t"); | |
| 121 $pattern='/([0-9]+)-([0-9]+)/'; | |
| 122 if(!preg_match($pattern,$data[4],$match)){ | |
| 123 continue; | |
| 124 } | |
| 125 foreach($columnNameMapping as $newName=>$oldName){ | |
| 126 $bookArray[$count][$newName]=$data[$oldName]; | |
| 127 } | |
| 128 $bookArray[$count]['BOOK_ID']=sprintf("%05d",$bookArray[$count]['BOOK_ID']); | |
| 129 $bookArray[$count]['Name']="(".$bookArray[$count]['PERIOD'].") ".$bookArray[$count]['Name']; | |
| 130 $bookArray[$count]["TimeSpan:begin"]=$match[1]; | |
| 131 $bookArray[$count]["TimeSpan:end"]=$match[2]; | |
| 132 $coordinate=getCoordinate($bookArray[$count]["LEVEL1"]); | |
| 133 $bookArray[$count]["Longitude"]=$coordinate["x"]; | |
| 134 $bookArray[$count]["Latitude"]=$coordinate["y"]; | |
| 135 $count++; | |
| 136 } | |
| 137 fclose($fp); | |
| 138 | |
| 139 $fp=fopen("local_monographs_list_176.txt","r"); | |
| 140 fgetcsv($fp,100000,"\t"); | |
| 141 while(!feof($fp)){ | |
| 142 $data=fgetcsv($fp,100000,"\t"); | |
| 143 $pattern='/([0-9]+)-([0-9]+)/'; | |
| 144 if(!preg_match($pattern,$data[4],$match)){ | |
| 145 continue; | |
| 146 } | |
| 147 foreach($columnNameMapping as $newName=>$oldName){ | |
| 148 $bookArray[$count][$newName]=$data[$oldName]; | |
| 149 } | |
| 150 $bookArray[$count]['BOOK_ID']=sprintf("%05d",$bookArray[$count]['BOOK_ID']); | |
| 151 $bookArray[$count]['Name']="(".$bookArray[$count]['PERIOD'].") ".$bookArray[$count]['Name']; | |
| 152 $bookArray[$count]["TimeSpan:begin"]=$match[1]; | |
| 153 $bookArray[$count]["TimeSpan:end"]=$match[2]; | |
| 154 $coordinate=getCoordinate($bookArray[$count]["LEVEL1"]); | |
| 155 $bookArray[$count]["Longitude"]=$coordinate["x"]; | |
| 156 $bookArray[$count]["Latitude"]=$coordinate["y"]; | |
| 157 $count++; | |
| 158 } | |
| 159 fclose($fp); | |
| 160 | |
| 161 return $bookArray; | |
| 162 } | |
| 163 function getCoordinate($province){ | |
| 164 $query="SELECT * FROM coordinates_provincial_capitals WHERE province_cht='".$province."'"; | |
| 165 $result=mysql_query($query); | |
| 166 $coordinate=mysql_fetch_assoc($result); | |
| 167 return $coordinate; | |
| 168 } | |
| 169 function bookIsInDatabase($bookId){ | |
| 170 $query="SELECT * FROM books WHERE id='".$bookId."'"; | |
| 171 $result=mysql_query($query); | |
| 172 if(mysql_num_rows($result)!=0){ | |
| 173 return true; | |
| 174 } | |
| 175 return false; | |
| 176 } | |
| 177 function updateYearInDatabase($bookArray){ | |
| 178 $row=0; | |
| 179 foreach($bookArray as $book){ | |
| 180 if(!bookIsInDatabase($book['BOOK_ID'])){ | |
| 181 echo $book['BOOK_ID']."!!!<br>"; | |
| 182 continue; | |
| 183 } | |
| 184 $query="UPDATE books SET start_year=".$book['TimeSpan:begin'].", end_year=".$book['TimeSpan:end']." WHERE id='".$book['BOOK_ID']."'"; | |
| 185 $result=mysql_query($query); | |
| 186 if(!$result){ | |
| 187 echo "Failed during updating: <br>"; | |
| 188 echo $query."<br>"; | |
| 189 }else{ | |
| 190 $row++; | |
| 191 } | |
| 192 } | |
| 193 } | |
| 194 | |
| 195 ?> | |
| 196 | |
| 197 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> | |
| 198 <html> | |
| 199 <head> | |
| 200 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> | |
| 201 </head> | |
| 202 <body> | |
| 203 <?php | |
| 204 //$coordinateArray=getCoordinateFromFile(); | |
| 205 //printCoordinate($coordinateArray); | |
| 206 //insertCoordinateIntoDatabase($coordinateArray);//insert coordinates of provincial capitals into table "coordinates_provincial_capitals" | |
| 207 //$bookArray=getBookListFromFile();//get the additional info which is not yet in table "books" | |
| 208 //updateYearInDatabase($bookArray);//fill in the "start_year" and "end_year" columns in "books" | |
| 209 $bookArray=getBookListFromDatabase(); | |
| 210 //PROVINCIAL: Address,LEVEL1,Name,PERIOD,LEVEL2,TimeSpan:begin,TimeSpan:end,BOOK_ID,Longitude,Latitude,VOLUME,AUTHOR,EDITION | |
| 211 foreach($bookArray as $idx=>$book){ | |
| 212 $bookArray[$idx]['Name']="(".$book['PERIOD'].") ".$book['Name']; | |
| 213 $book['AUTHOR']=str_replace("（","(",$book['AUTHOR']); | |
| 214 $book['AUTHOR']=str_replace("）",") ",$book['AUTHOR']); | |
| 215 $bookArray[$idx]['Description']=$book['VOLUME']." ╱ ".$book['AUTHOR']." ╱ ".$book['EDITION']; | |
| 216 } | |
| 217 $columnNameArray=['Address','LEVEL1','Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude','BOOK_ID','Description']; | |
| 218 | |
| 219 echo sizeof($bookArray)."<br>"; | |
| 220 echo "<table>"; | |
| 221 echo "<tr>"; | |
| 222 $fp=fopen("provincial_capital_coordinates.csv","w"); | |
| 223 foreach($columnNameArray as $column){ | |
| 224 echo "<td>".$column; | |
| 225 } | |
| 226 fputcsv($fp,$columnNameArray); | |
| 227 $count=0; | |
| 228 foreach($bookArray as $book){ | |
| 229 echo "<tr>"; | |
| 230 $row=array(); | |
| 231 foreach($columnNameArray as $column){ | |
| 232 echo "<td>".$book[$column]; | |
| 233 $row[]=$book[$column]; | |
| 234 } | |
| 235 fputcsv($fp,$row); | |
| 236 $count++; | |
| 237 } | |
| 238 echo "</table>"; | |
| 239 | |
| 240 ?> | |
| 241 | |
| 242 </body> | |
| 243 </html> | |
| 244 |