iT邦幫忙

2023 iThome 鐵人賽

DAY 29
0
Mobile Development

Google Maps SDK for Android 與 GIS App 開發筆記系列 第 29

Day 29: android-spatialite 實作與常用的 Spatial SQL Functions

  • 分享至 

  • xImage
  •  

如同昨天所介紹的, sevar83/android-spatialite 是比官方版本更容易也更好使用的 android-spatialite library。

今天就帶大家實際走一遍實際操作的流程。

加入相依套件 dependency

如果是使用 Android Gradle 4.0 以前的版本,要在專案層級的 build.gradle 加入以下設定:

allprojects {
  repositories {
    // 重點是下面這行
    maven { url "https://jitpack.io" }
  }
}

如果是使用 KTS 的版本,則是在 settings.gradle.kts 中加入以下:

dependencyResolutionManagement {
    
    repositories {
        // 略...
        maven {
            setUrl("https://jitpack.io")
        }
    }
}

最後,在 App 層級下的 build.gradle 加入 implementation

如果是 KTS 版本:

implementation("com.github.sevar83:android-spatialite:2.0.1")

groovy 版本:

implementation 'com.github.sevar83:android-spatialite:2.0.1'

基本用法

就如作者在 REAME 中所說明的,android-spatialite 的 API 跟 Android SQLite API 有 99% 的相似。以我的使用經驗來說,根本就是一模一樣,只要你有熟 Android SQLite API 基本上不會有什麼問題。

以下簡單帶過幾個常見的操作方式。

org.spatialite.database.SQLiteDatabase

跟 Android SQLite API 一樣,最終都是需要建立資料庫連線以取得 SQLiteDatabase 物件,並用該物件執行 CRUD 的操作。

常見的方法有:

SQLiteDatabase.openOrCreateDatabase(dbFile, null)
SQLiteDatabase.openDatabase(dbFilePath,null, SQLiteDatabase.OPEN_READWRITE)

execSQL() 執行非查詢類的 SQL 語法

通常資料庫 Schema 調整的需求,都會使用 execSQL() 來執行相關的 SQL Command。

像是:

val tableName = "test1"
// 新增欄位到資料表
db.execSQL("ALTER TABLE $tableName ADD COLUMN version TEXT;")
// 更新欄位數值
db.execSQL("UPDATE $tableName SET version = '109Dec'")

// 連接其他路徑的 SQLite file
val attachSql = "ATTACH DATABASE '${it.absolutePath}' AS $dbName;"
db.execSQL(attachSql)

insert() 新增資料

傳入資料表名稱 tableName、資料可以為 NULL 的欄位nullColumnHack、寫入的資料ContentValue,即可新增資料到資料表上。新增成功的話會回傳資料的 row id,反之為 -1。

ContentValues() 是一個 key value 的物件,在這裡的使用上,key 要傳入的是資料表欄位名稱,value 則是此欄位要更新的值。

val tableName = "sample1"
val values = ContentValues()
values.put("name", "John")
values.put("phone", "0911111111")
db.insert(tableName, null, values)

delete 刪除資料

透過參數的傳入,可刪除資料表中符合指定條件的資料,並回傳實際刪除的資料筆數。

這個方法可以降低自己寫 SQL Command 執行刪除的出錯率 。

val tableName = "table1"
val whereClause = "id = ?"
val whereArgs = arrayOf("2")

int affectedRow = db.delete(tableName, whereClause, whereArgs)

// DELETE FROM table1 WHERE id = 2;

update() 更新資料

update() 用來更新資料表內的資料。傳入的參數大致上跟 delete() 相同,唯一不同的是,多了代表更新資料的 ContentValues() 這個類別。

val tableName = "sample1"
val values = ContentValues()
values.put("name", "John")
values.put("phone", "0911111111")
val whereClause = "id = ?"
val whereArgs = arrayOf("A123456789")
db.update(tableName, values, whereClause, whereArgs)

query() 參數化查詢

跟上面的 delete()update() 一樣,是透過參數化的方式執行 SQL。
只是它執行的是 SELECT 語法,並會取得 Cursor 物件。
透過 Cursor 物件,我們就能逐筆取得資料。

實務上在 Spatialite 的操作上較少使用,因為他相對不容易下空間運算的 SQL Command。

query(String table, 
      String[] columns, 
      String selection,
      Object[] selectionArgs,
      String groupBy, 
      String having,
      String orderBy, 
      String limit)

rawQuery() SQL 語法查詢

直接傳入 SQL Command 執行,並取得 Cursor 物件。
實務上比較常用這個方法做空間資料的查詢,因為相對於 qurey()rawQuery() 在下空間運算的語法比較方便直覺。

假設,我們要根據目前螢幕所見的 Google Maps 範圍,查找資料庫中的空間資料,可以這麼寫:

// 1. 將螢幕邊界拼成 WKT 格式的 Polygon
val screenBoundWkt =
    "POLYGON(($fromLng $toLat, $fromLng $fromLat, $toLng $fromLat, $toLng $toLat, $fromLng $toLat))"

// 2. 將螢幕邊界 WKT 轉成 SRID 4326 的 Geometry (Google Map 是用 SRID 4326)
// 再用螢幕邊界的 Geometry 去跟 資料表內的 Geometry 欄位 (原始資料是 SRID 23032,要轉 4326) 交集 
val sql = "SELECT AsWKT(Transform(Geometry, 4326)) FROM Comuni_11 WHERE (SELECT Intersects(GeomFromText(\"$screenBoundWkt\", 4326), Transform(Geometry, 4326)) = 1) "

// 執行 Query
db.rawQuery(sql, null)?.use { cursor ->
    if (cursor.count > 0) {
        cursor.moveToFirst()
        // 取得回傳的 WKT 欄位
        val wkt = cursor.getStringOrNull(0)
        // ...
    }
}

參考 Android SQLite API 的寫法

以下的範例參考自 Android Doc

SpatialiteHelper 繼承 org.spatialite.database.SQLiteOpenHelper ,並將資料庫的 CRUD 都寫在這個類別中統一管理。

class SpatialiteHelper(
    private val context: Context?,
    name: String = DB_NAME,
    factory: SQLiteDatabase.CursorFactory? = null,
    version: Int = 1
) : SQLiteOpenHelper(context, name, factory, version) {
    override fun onCreate(db: SQLiteDatabase?) {
        // 資料庫初始化行為 (建立資料表或是引入外部 SQLite 檔案)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        // 處理資料庫升降版
    }

    // ----------------------------------------------------

    private val db: SQLiteDatabase by lazy {
        // 取得可讀寫的 DB
        writableDatabase
    }

    /**
     * 根據經緯度範圍查找
     *
     * @param fromLat
     * @param toLat
     * @param fromLng
     * @param toLng
     */
    fun queryLandsByBounds(
        fromLat: Double,
        toLat: Double,
        fromLng: Double,
        toLng: Double
    ): List<List<PolygonData>> {
        // ...
    }

    companion object {
        val TAG: String = SpatialiteHelper::class.java.simpleName

        const val DB_NAME = "italy.sqlite"
    }
}

以上就是基本的 SQLite API 操作,如果你對 API 的使用上有問題,可以在 Android Studio 中以查詢原始碼的方式查看,或是參考 Android SQLite API 的官方文件,因為他們真的 99% 相同~

常用 Spatial SQL functions

前面的 rawQuery() 區塊中,有稍為帶到一些空間運算的 SQL functions,以下將更詳細的說明。

Spatialite 可操作的空間運算方法有很多,以下只介紹一些筆者常用或曾用過的方法,如果沒有符合你的情境的,可以到 Spatialite-SQL 語法清單 (4.3.0)查詢。

不過要注意的是,今天所使用的 android-spatialite,Spatialite 版本是 4.3.0a,查詢前要確認清單版本是否與套件一致。

另外,也是有可能發生 SQL function 在 App 中執行,但出現找不到該方法的錯誤,這時候也有可能是版本差異所造成的。如果 Google 後都沒有解法,就只能找其他替代的運算方法,或是用其他方法繞開。

座標系統轉換 Transform

在 GIS App 中我們常面對不同來源的資料,有時候資料的座標系統不見得相同,這時候就需要透過轉換的方式,讓資料間使用的座標系統一致。

Transform( geom Geometry , newSRID Integer )

Transform() 方法讓我們能夠傳入 Geometry 與 SRID,將資料轉成指定的 SRID Geometry。以前面 rawQuery() 的範例來說,我們就是將原始資料 (SRID: 23032) 轉成與 Google Maps 相同的 SRID 4326。

MakePoint() 座標轉 Geometry 物件

有時候我們的原始資料可能是經度、緯度分開儲存的兩個欄位,但我們又需要用它們來做空間運算時,就可以利用 MakePoint() 方法,將座標轉為點位的 Geometry

  • x: 經度 (double)
  • y: 緯度 (double)
  • SRID: x,y 的座標系
MakePoint(x,y,SRID) : Geometry

交集 Intersects()

Intersects(Geometry, Geometry)

前面的 rawQuery() 範例中有使用到,用來比較兩個 Geometry 物件有沒有交集,如果有的話會回傳 1,沒有會回傳 0,不明會回傳 -1。

範圍內 WithIn()

WithIn(Geometry, Geometry)

另一個常用的 function,用來計算 Geometry 是否有包含在另一個內。如果有的話會回傳 1,沒有會回傳 0,不明會回傳 -1。

AsWKT() Geometry 轉 WKT 字串

AsWKT(Geometry)

空間運算完的結果,總有要讀出來的時候。
這時候就可以用這個方法將 Geometry 轉成 WKT 字串。

GeomFromText() WKT 字串 轉 Geometry

GeomFromText(WKT, SRID)

傳入 WKT 字串與其對應的 SRID,就可以將其轉成 Geometry 物件。

有時候資料庫內存的如果是 WKT 字串,這時候就可以使用這個方法轉換,以利後續的空間運算。

參考資料

小結

因為跟 Android SQLite API 相似度很高,所以 org.spatialite.databaseJava/Kotlin 端的操作都不複雜。以筆者的經驗,花比較多心力的都是在空間資料的轉換與運算。有時候為了找出最合適的 SQL Functions 常常花不少時間在反覆測試與搜尋。

希望今天的這些分享可以幫到有需要的人~

明天最後一天見!!


上一篇
Day 28: 手機上的空間資料庫--Sptialite 介紹
下一篇
Day 30: 完賽心得
系列文
Google Maps SDK for Android 與 GIS App 開發筆記30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言