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