I want to convert data with a KML file to another one with CSV-, SQL- and SHP-format using ogr2ogr, but I encounter the problem about loosing content of KML file what I want. This article is for describing what problems are and what solutions can used for overcome.

Problem 1: I can use GUI-based software, which name is QGIS-LTR, with command parameters of ogr2ogr to successfully transform a file from KML-format to CSV-, SQL- and SHP-format one. I cannot get same result if I use same name package: ogr2ogr, which is installed within homebrew, to do same thing by command-line way. The example KML-format comes from opendata platform of Taipei city and it has extra fields: area, fd_depth, fdate, town_name, and address. These fields are wrapped in tags of ExtendedData and SchemaData.

I use QGIS-LTR 3.22.5 and gdal 3.4.2.

Explain: It’s because ogr2ogr, which is installed by way of homebrew, doesn’t bind with LIBXML. This way only binds KML driver. In contrast, after intalling QGIS-LTR, it binds both of KML and LIBKML.

Notice1: KML and LIBKML are different.
Notice2: You can use this command: brew install gdal within terminal to install ogr2ogr for comparison to QGIS-LTR. I had installed ogr2ogr package using this way at April 2022. The gdal version number at that time is 3.4.2. Files, which are converted from KML-format by ogr2ogr whose is installed using brew way, only have two fields: name and description. This is not what I want.
Notice3: If you install version 2 of gdal by brew, gdal has binded both of drivers of KML and LIBKML.
Notice4: Version 3 series, which comes from other communities such as conda-forge/gdal-feedstock, will automatically install and bind LIBKML during install process.

Reference link:

1. https://gis.stackexchange.com/questions/403975/ogr2ogr-unable-to-find-driver-libkml
2. https://gis.stackexchange.com/a/403978/203770
3. conda-forge/gdal-feedstock

Problem 2: I want exported content is ready to be SQL-based commands in a file and then imported to other table(s) in database. I need schema and temporary table, which are written to file, follow what I assign in options of command line.

Solution: I use some options of ogr2ogr and some of they are format-specific. list of options I use:

• lco means layer creation option. Different target format has its own options.
• nln means new layer name

I use QGIS-LTR and its provided ogr2ogr to convert KML-format file. Steps for finding use path about converting file in QGIS-LTR:

1. From menu bar, which is at top of screen, please make Processing -> Toolbox to be checked. This will display Processing Toolbox.
2. At view of Processing Toolbox, please expand list of GDAL, click Vector conversion and then double click Convert format. This will display view of Convert format.
3. At view of Convert format, please select a file for input layer by clicking select file. After choosing a file, back to view of Convert format.
4. At view of Convert format, please click Save to File at Converted row. This will display view of Save file.
5. At view of Save file, you can assign name and format for target conversion, this is used for output file. After setting a output file, back to view of Convert format.
6. At view of Convert format, please type -lco and -nln with their related parameters respectively. I will describe examples of -lco and -nln later.
7. Finally, click Run button. Just wait here.

If I convert KML-format file to CSV-format, I can use following options at row of Additional creation options :

-lco GEOMETRY=AS_WKT -lco GEOMETRY_NAME=effect_range -lco STRING_QUOTING=ALWAYS

For KML-format to SQL-format:

-lco GEOMETRY_NAME=effect_range -lco SRID=3824 -lco SCHEMA=public -lco DROP_TABLE=NO -lco CREATE_TABLE=NO -nln tpe

For KML-format to SHP-format:

-lco ENCODING=UTF-8

If you want to know what options of lco can be applied, please refer document of GDAL.
Reference:

1. CSV layer creation option
2. SQL layer creation option
3. SHP layer creation option

Problem3: I want to let a table, which has no any geometry column, starting to have a geometry column and it needs to store any type of geometry such as polygon, dot, line, area, …..etc.

Solution: First, I add new column to table and then set to geometry type for storing geometry info. Second, I change this column’s EPSG ID to 3824 by UpdateGeometrySRID.

ALTER TABLE yourtablename ADD COLUMN effect_range GEOMETRY;
SELECT UpdateGeometrySRID('yourtablename', 'effect_range', 3824);

Notice:

1. 3824 is a SRID from EPSG for encoding info about Taiwan geometry.
2. UpdateGeometrySRID is an update function for changing spatial identifier of a geometry column to what I want.
3. effect_range is a column name I used in this solution. You can use your name.

Problem4: How do I dump data to a table from another one?

Solution: use option of ogr2ogr:

• -sql is for selecting data of fields from a table. The output it generates will become input for other formats such as another table in database or CSV-format file.

If I use above fields and table from other problem, which I mentioned from opendata, here is example:

-sql "SELECT area, fd_depth, fdate, town_name, address, effect_range FROM tpe"

Notice: tpe is a table, effect_range is a geometry field within tpe, and other fields are normal text fields within same table.

  — 中文版本 —  

問題 1:
QGIS-LTR 使用 ogr2ogr 能夠正常轉換 kml 到 CSV 、 SQL 和 SHP 檔案，但直接用 ogr2ogr 指令轉換，無法如想要的格式內容，這是為什麼？ 我使用 gdal 3.4.2 and QGIS-LTR 3.22.5.

回答:
因為 brew install gdal (地理資訊, 3 系列, ex:3.4.2 版) 安裝得到的 ogr2ogr 並沒有跟 LIBKML 綁定，只有綁定 KML ，用 ogr2ogr 指令轉換 KML 資料都只會有兩個欄位: name, description 。但 KML 格式 driver 有兩種: KML 和 LIBKML (Google 做的)，所以用終端機指令安裝的 ogr2ogr 轉換，無法得到想要的格式內容。 QGIS-LTR 因為有 LIBKML，所以用 QGIS-LTR 3.22.5 的 ogr2ogr 去轉換 KML 到 CSV, SQL, SHR 格式，都能得到原始資料含有的欄位。

提醒:

1. gdal 2 系列透過 homebrew 安裝，則含有 LIBKML 安裝選項。
2. 其他社群版本(conda-forge/gdal-feedstock)的 gdal 3 系列，會自動安裝與綁定 LIBKML
3. mac homebrew 安裝的 gdal 套件(地理資訊) 3 系列版本，用 ogr2ogr 指令轉換資料都只會有兩個欄位: name, description 。

參考連結：

1. https://gis.stackexchange.com/questions/403975/ogr2ogr-unable-to-find-driver-libkml
2. https://gis.stackexchange.com/a/403978/203770
3. conda-forge/gdal-feedstock

問題 2: 我想要轉換 KML 內容成 SQL 指令到一個檔案，接著倒進去資料庫的表格 ，我需要指定資料 來源的 schema 和表格，這能夠從指令的選項值指定，使這兩者能一起產生在輸出的檔案。

解決方案： 我使用 ogr2ogr 指令的參數，這裡的一些參數是會隨著輸出檔案格式不同而改變，以下是清單： • lco意思是 layer 建立選項 • nln意思是新 layer 名稱  

我使用 QGIS-LTR 軟體和它附帶的 ogr2ogr 程式去轉換 KML 格式檔案。用 QGIS-LTR 軟體轉換檔案格式內容的步驟：

1. 從畫面上方的選單列，讓 Process 底下的 Toolbox 被勾選，這會顯示 Processing toolbox 畫面。
2. 在 Processing toolbox 畫面，請展開 GDAL 清單，接著點選 Vector conversion ，再點選兩次 Conver format，這會顯示 Conver format 畫面。
3. 在 Convert format 畫面，請在 input layer 並點選 select file，以選擇一個檔案。選擇一個檔案後，請回到 Convert format 畫面。
4. 在 Convert format 畫面，請在 Converted 列點選 Save to File，以顯示 Save file 畫面。
5. 在 Save file 畫面，請指定輸出檔案的名稱和格式，例如輸出格式是 CSV。指定完畢後，請回到 Convert format 畫面。
6. 在 Convert format 畫面，請輸入參數-lco和 -nln，和以及兩者的相關選項值。我稍後會用相關例子說明 -lco和-nln。
7. 最後，點選 Run 按鈕，等待轉換內容完畢。

如果我轉換 KML 格式檔案內容到 CSV 格式，我能在 Additional creation options 列使用以下參數和選項值：

-lco GEOMETRY=AS_WKT -lco GEOMETRY_NAME=effect_range -lco STRING_QUOTING=ALWAYS

  如果是轉換 KML 格式到 SQL 格式：

-lco GEOMETRY_NAME=effect_range -lco SRID=3824 -lco SCHEMA=public -lco DROP_TABLE=NO -lco CREATE_TABLE=NO -nln tpe

如果是轉換 KML 格式到 SHP 格式：

-lco ENCODING=UTF-8

如果你想知道更多能使用哪些 -lco的選項值，請參考 GDAL文件： Reference:

1. CSV layer creation option

2. SQL layer creation option

3. SHP layer creation option

問題 3： 我想要讓一個原本沒有 geometry 欄位的表格，開始帶有 一個 geometry 型態的欄位，預期它應能儲存任意的 geometry 資料，像是多邊形、點、線、區域，……等等資料。

解決方式： 我會使用兩個指令，第一個是在指定的資料表增加一個欄位，並設定它是 geometry 型態，假設欄位名稱是 effect_range。第二個是，指定剛才新增的欄位，它的 EPSG ID 是 3824，藉由 UpdateGeometrySRID。以下是 SQL 指令：

ALTER TABLE yourtablename ADD COLUMN effect_range GEOMETRY;
SELECT UpdateGeometrySRID('your')

提醒:

1. 3824 是一個 EPSG 裡的 SRID ，用在編碼臺灣的地圖資訊
2. UpdateGeometrySRID 是一個 PostGIS 裡的更新函式，用於更新 Geometry 型態欄位使用的 SRID 值。
3. effect_range 是一個我自己定義的欄位名稱，你能使用自己想要的名稱。

問題 4:
我如何將資料內容從一個資料表倒向另一個資料表？

解決方案：用 ogr2ogr 的選項值

• -sql 是一個參數，能撰寫一個 SQL 的 select 敘述句，進一步取得想要的資料欄位。select 的結果會作為資料庫的另一個表格或者是 CSV 格式檔案的輸入。

如果我用上述問題提過的「開放資料台北」欄位和表格，以下是範例：

-sql "SELECT area, fd_depth, fdate, town_name, address, effect_range FROM tpe"

提醒: tpe 是一個表格， effect_range 是一個在 tpe 表格的 geometry 型態欄位，同表格的其他欄位則是一般的文字型態欄位。