[記事] MySQL and org2ogr and character set

屬於阿宅世界的技術文章，想看的再點開，切莫自誤 !


FWTools 中的org2ogr 是一個可以很方便地將地理資料檔案在 SHP/DBF/CSV 等不同格式間轉換的工具，甚至可以直接把資料塞進 Database 中，之前在 postgreSQL中用起來很完美，但在MySQL就一直碰到檔案字集與DB字集不同造成匯入中文變成亂碼的討厭的問題，Google 後有看到大陸人的解法，是將DB的預設字集設成 binary，然後再執行ogr2ogr的匯入動作，最後再把database的字集改成你想要的。但Database的字集這樣變來變去，感覺總是會影響到其他運作中的Table，所以有了以下的笨方法

執行正常的匯入動作

shell> ogr2ogr -f "MySQL" MYSQL:"test,host=localhost,user=YOUR_USER_NAME,password=YOUR_USERS_PASSWORD,port=MYSQL_LISTEN_PORT" /YOUR_FILE_LOCATION/YOUR_GIS_DATASOURCE_FILE

如果在執行過程中，產生不能建 Spatial Index 的問題，那大概是因為MySQL 預設的TABLE的engine是InnoDB或是其他，請用 -lco 參數指定

shell> ogr2ogr -f "MySQL" MYSQL:"test,host=localhost,user=YOUR_USER_NAME,password=YOUR_USERS_PASSWORD,port=MYSQL_LISTEN_PORT" -lco engine=MyISAM /YOUR_FILE_LOCATION/YOUR_GIS_DATASOURCE_FILE

這樣，在你的Database (test) 中會有一個跟檔案中layer同名的Table，如果你想指定你的Table名稱，可以在參數中加上 -nln THE_TABLE_NAME_YOUR_WANT

再來轉一份同樣的資料，但檔案格式為CSV

shell> ogr2ogr -f "CSV" /tmp /YOUR_FILE_LOCATION/YOUR_GIS_DATASOURCE_FILE

這樣會產生一個 /tmp/GIS_LAYER_NAME.csv 的檔案，再把這個檔案轉成 UTF8
shell> iconv -f big5 -t utf8  /tmp/GIS_LAYER_NAME.csv >  /tmp/GIS_LAYER_NAME_utf8.csv

到這邊可能有人會問，既然已經有了UTF8字碼的檔案，那幹麻不用這個匯入就好....我也想但是這個檔案沒有TABLE的DDL，而且你資料若包含了圖形(geometry)的話，它也不會幫你轉出來。那也可能有人會問，這樣的話直接轉GIS Datasource file 不就好了，可能可以，但我會碰到字集錯誤iconv 轉不出來的問題，所以認命點，繼續做下去。

在MySQL 裡建立一個暫存的TABLE，用來放轉好的資料

mysql> create tableYOUR_TEMP_TABLE as select * fromYOUR_TABLE where 1=2;

mysql >  load data infile '/tmp/GIS_LAYER_NAME_utf8.csv' into table YOUR_TEMP_TABLE fields terminated by ',' ignore 1 lines (CSV_COLUMNS); 

其中的CSV_COLUMNS 可以透過下面的方式知道

shell > head 1  /tmp/GIS_LAYER_NAME_utf8.csv

最後，再利用新Table中正確的資料去更新舊Table中的就好

 mysql> update YOUR_TABLE,YOUR_TEMP_TABLE set YOUR_TABLE.column1 = YOUR_TEMP_TABLE.column1, YOUR_TABLE.column2 = YOUR_TEMP_TABLE.column2 where binary YOUR_TABLE.key = YOUR_TEMP_TABLE.key

如果你用的key不是 primary key，那最好替它建上index，不然資料大的話可能會慢到你發瘋。


Ref :

• http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet

.