Mercurial > hg > extraction-interface

comparison map/coordinates/provincial_capital_coordinates.php @ 0:b12c99b7c3f0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
commit for previous development
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Mon, 19 Jan 2015 17:13:49 +0100
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:b12c99b7c3f0
1 <?php
2 include_once("../../interface/config.php");
3 include_once('../../interface/Lib_mb_utf8.php');
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