Using Spatia-Room with a database containing multipolygons

[mrclayman]

Hi @dalgarins ,

I posted a comment on this SO thread with my problem so it may ring a bell.

I have a SpatiaLite database with about 5,5k multipolygons that I want to use in an Android app. (Thanks for adding the method createFromAsset btw! I really appreciate it.) The table is called honitby and it contains the geometries as well as 3 other columns - id, "nazev" (name), and "kod" (code). sqlite3 CLI tool shows this for .schema honitby:

sqlite> .schema honitby
CREATE TABLE IF NOT EXISTS 'honitby' ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, 'nazev' VARCHAR(50), 'kod' VARCHAR(10), "geom" MULTIPOLYGON);

As I mentioned in my comment on SO, I am trying to use the DAO approach, so I have this interface defined for the database (the DB is going to be read-only so I can make do with just read queries):

@Dao
interface IPolygonManager {

    /**
     * Retrieves a single report from the database
     */
    @Query("""SELECT h.id, h.nazev, h.kod,
            AsText(h.geom) AS geom
        FROM honitby h
        WHERE Intersects(h.geom, MakePoint(:x, :y))""")
    @SkipQueryVerification
    fun getPolygon(x: Double, y: Double): PolygonRecord?
}

and the PolygonRecord class looks like this:

@Entity(tableName = "honitby")
data class PolygonRecord(
    @PrimaryKey                 val id: Int?,
    @ColumnInfo(name = "nazev") val name: String?,
    @ColumnInfo(name = "kod")   val code: String?,
                                val geom: MultiPolygon?)

The MultiPolygon type comes from org.locationtech.jts:jts-core library, which should be able to handle I/O on WKB/WKT geometries. Since the MultiPolygon type is a complex type, I am using a type converter class:

@TypeConverters
object PolygonDataConverters {

    @TypeConverter
    @JvmStatic
    fun toMultiPolygon(wkt: String): MultiPolygon {
        val reader = WKTReader()
        val geometry = reader.read(wkt)
        return geometry as MultiPolygon
    }

    // Writing geometry is not supported but Android requires
    // this to be here.
    @TypeConverter
    @JvmStatic
    fun fromMultiPolygon(dummy : MultiPolygon) = ""
}

and the database itself is registered with the application as follows:

@Database(entities = [PolygonRecord::class], version = 1, exportSchema = false)
@TypeConverters(PolygonDataConverters::class)
abstract class PolygonDatabase : RoomDatabase() {

    abstract fun polygonManager(): IPolygonManager

    companion object {

        private const val DATABASE_NAME = "polygons.sqlite"

        @Volatile
        private var INSTANCE: PolygonDatabase? = null

        fun getDatabase(context: Context): PolygonDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: buildDatabase(context).also { INSTANCE = it }
            }
        }

        private fun buildDatabase(context: Context) = SpatiaRoom.databaseBuilder(
            context.applicationContext,
            PolygonDatabase::class.java,
            DATABASE_NAME
        ).createFromAsset("database/${DATABASE_NAME}").build()
    }
}

I tried to use ByteArray instead of String in combination with using AsBinary(geom) instead of AsText(geom) but to no avail.

With all of this in place, when I try to run the app, I get a bunch of tracebacks, but the most interesting part is this:

     Caused by: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: honitby(cz.czu.fld.sojka.data.models.polygons.PolygonRecord).
     Expected:
    TableInfo{name='honitby', columns={kod=Column{name='kod', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, nazev=Column{name='nazev', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, geom=Column{name='geom', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='honitby', columns={kod=Column{name='kod', type='VARCHAR(10)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, nazev=Column{name='nazev', type='VARCHAR(50)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, geom=Column{name='geom', type='MULTIPOLYGON', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

To me, this looks like Room does not know how to handle the MULTIPOLYGON type. I would hope that it would consider it as a byte array, but I was unable to persuade it to do so. I am also admittedly not very familiar with SQLite's notion of affinity, but I did study up and do have some idea as to what it is. Still, I have been battling this for few evenings now and am still hitting a brick wall, so any hint or recommendation is very welcome. :blush:

I have attached a sample of my dataset, which should help you in your experiments, should you decide to do some. Thanks a lot in advance.

sample.sqlite.zip

[dalgarins]

Hi @mrclayman I decided to modify the demo project and I have added the class and converters that you did and it's working in my laptop.

the list variable has all the objects.

[dalgarins]

this is the branch: https://github.com/anboralabs/spatia-room/tree/geo-examples

could you test it in your laptop and let me know if that works for you. if not, please send me more information like Android version, device arch.

[mrclayman]

Big thanks for replying so quickly, @dalgarins . :heart: I will check out your changes in the evening and will report back with my findings.

[dalgarins]

@mrclayman I found your issue, in your case you don't need to declare

@Database(entities = [PolygonRecord::class], version = 1, exportSchema = false)
@TypeConverters(PolygonDataConverters::class)

with only

@Database(version = 1, exportSchema = false)
@TypeConverters(PolygonDataConverters::class)

it's enough, the reason it's because the table already exists.

[mrclayman]

Hmm, I just tried to remove it, having now only this:

@Database(version = 1, exportSchema = false)

but I get a compile-time error

android/app/build/tmp/kapt3/stubs/debug/cz/czu/fld/sojka/data/database/polygons/PolygonDatabase.java:10: error: annotation @Database is missing a default value for the element 'entities'
@androidx.room.Database(version = 1, exportSchema = false)

For what it's worth, this is my project-wide build.gradle

allprojects {
    repositories {
        google()
        mavenCentral()
        maven { url 'https://jitpack.io' }
    }
}

buildscript {
    ext {
        kotlinVersion = '1.5.21'
        coroutinesVersion = '1.5.1-native-mt'
        databindingVersion = '4.2.2'
        drawerLayoutVersion = '1.1.1'
        exifInterfaceVersion = "1.3.2"
        glideVersion = '4.12.0'
        jtsVersion = '1.18.1'
        junitVersion = '4.13.2'
        lifecycleVersion = '2.3.1'
        locationVersion = '18.0.0'
        mapsVersion = '17.0.1'
        navigationVersion = '2.3.5'
        preferenceVersion = '1.1.1'
        roomVersion = '2.3.0'
        spatiaRoomVersion = '0.1.1'
        touchImageViewVersion = '2.2.0'
        volleyVersion = '1.2.0'
        workVersion = '2.7.0-alpha04'
    }

    repositories {
        google()
        mavenCentral()
        maven { url 'https://jitpack.io' }
    }

    dependencies {
        classpath 'com.android.tools.build:gradle:4.2.2'
        classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlinVersion"
        classpath "androidx.navigation:navigation-safe-args-gradle-plugin:$navigationVersion"
        // NOTE: Do not place your application dependencies here; they belong
        // in the individual module build.gradle files
    }
}

task clean(type: Delete) {
    delete rootProject.buildDir
}

and this is one for the app module

apply plugin: 'com.android.application'
apply plugin: 'kotlin-android'
apply plugin: 'kotlin-kapt'
apply plugin: 'androidx.navigation.safeargs.kotlin'

android {
    compileSdkVersion 30
    defaultConfig {
        applicationId "cz.czu.fld.sojka"
        minSdkVersion 22
        targetSdkVersion 30
        versionCode 1
        versionName "1.0"
        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"

        javaCompileOptions {
            annotationProcessorOptions {
                arguments = [
                        "room.schemaLocation": "$projectDir/schemas".toString()
                ]
            }
        }
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }
    buildFeatures {
        dataBinding  = true
        viewBinding = true
    }

    compileOptions {
        sourceCompatibility = JavaVersion.VERSION_1_8
        targetCompatibility = JavaVersion.VERSION_1_8
    }
    kotlinOptions {
        jvmTarget = JavaVersion.VERSION_1_8
    }
}

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])
    implementation "org.jetbrains.kotlin:kotlin-stdlib-jdk7:$kotlinVersion"
    implementation "org.jetbrains.kotlin:kotlin-reflect:$kotlinVersion"
    implementation "androidx.appcompat:appcompat:1.3.0"
    implementation "androidx.core:core-ktx:1.6.0"
    implementation "androidx.constraintlayout:constraintlayout:2.0.4"
    implementation "com.google.android.material:material:1.4.0"
    implementation "androidx.legacy:legacy-support-v4:1.0.0"
    implementation "androidx.recyclerview:recyclerview:1.2.1"
    implementation "androidx.cardview:cardview:1.0.0"
    testImplementation "junit:junit:$rootProject.junitVersion"
    androidTestImplementation "androidx.test:runner:1.4.0"
    androidTestImplementation "androidx.test.espresso:espresso-core:3.4.0"

    // Room components
    implementation "androidx.room:room-runtime:$rootProject.roomVersion"
    kapt "androidx.room:room-compiler:$rootProject.roomVersion"
    annotationProcessor "androidx.room:room-compiler:$rootProject.roomVersion"
    //androidTestImplementation "androidx.room:room-testing:$rootProject.roomVersion"

    // Coroutines
    api "org.jetbrains.kotlinx:kotlinx-coroutines-core:$rootProject.coroutinesVersion"
    api "org.jetbrains.kotlinx:kotlinx-coroutines-android:$rootProject.coroutinesVersion"

    // Navigation
    implementation "androidx.navigation:navigation-fragment-ktx:$rootProject.navigationVersion"
    implementation "androidx.navigation:navigation-ui-ktx:$rootProject.navigationVersion"

    // Lifecycle
    implementation "androidx.lifecycle:lifecycle-common:$rootProject.lifecycleVersion"
    //implementation "androidx.lifecycle:lifecycle-extensions:$rootProject.lifecycleVersion"
    implementation "androidx.lifecycle:lifecycle-viewmodel-ktx:$rootProject.lifecycleVersion"

    // WorkManager
    implementation "androidx.work:work-runtime-ktx:$rootProject.workVersion"

    // Data binding
    implementation "androidx.databinding:databinding-common:$rootProject.databindingVersion"

    // Volley network library
    implementation "com.android.volley:volley:$rootProject.volleyVersion"

    // Preference library
    implementation "androidx.preference:preference-ktx:$rootProject.preferenceVersion"

    // TouchImageView (scrollable & zoomable ImageView) library
    implementation "com.github.MikeOrtiz:TouchImageView:$rootProject.touchImageViewVersion"

    // Glide image handling library
    implementation "com.github.bumptech.glide:glide:$rootProject.glideVersion"
    annotationProcessor "com.github.bumptech.glide:compiler:$rootProject.glideVersion"
    kapt "com.github.bumptech.glide:compiler:$rootProject.glideVersion"

    // Drawer layout support
    implementation "androidx.drawerlayout:drawerlayout:$rootProject.drawerLayoutVersion"

    // Google Play Services for Google Maps
    implementation "com.google.android.gms:play-services-location:$rootProject.locationVersion"
    implementation "com.google.android.gms:play-services-maps:$rootProject.mapsVersion"

    // EXIF interface
    implementation "androidx.exifinterface:exifinterface:$rootProject.exifInterfaceVersion"

    // Spatia-room
    implementation "com.github.anboralabs:spatia-room:$rootProject.spatiaRoomVersion"

    // JTS geometry handling library
    implementation "org.locationtech.jts:jts-core:$rootProject.jtsVersion"
    implementation "org.locationtech.jts.io:jts-io-common:$rootProject.jtsVersion"
}

// Needed to resolve duplicate class issues between hamcrest-core and junit
configurations.all {
    resolutionStrategy.dependencySubstitution {
        substitute module('org.hamcrest:hamcrest-core:1.1') with module("junit:junit:$rootProject.junitVersion")
    }
}

However, I did try your modified demo app and it also worked on my end so there is probably something off in my app. Please keep that branch around for a while longer. I intend to dive in deeper over the coming weekend. And thanks a bunch again for being so helpful. :blush: I really do appreciate it.

[dalgarins]

ok, I got it, well I think maybe create a fake entity only for that issue, and not add the PolygonRecord to the entities because is created in the db. that could solve your issue.

your welcome. let me know if you have another problem. I going to close this issue.

[mrclayman]

So it seems that you were onto something. I created a small demo app just for that issue alone and once I started using a dummy model that did not fit the data structure in the database, the issue went away. It looks like Room is being pretty aggressive when it comes to validating the schema of the table being mapped onto the entity model, but when it doesn't fit, it just skips that, which is weird behavior IMO, but at least there now seems to be a way for me. Thanks again for the assistance. :blush:

EDIT: It's also important that the dummy model do not reference in the @Entity annotation the name of the table it is used with.

[dalgarins]

Hi @mrclayman I also wanted to clarify some concepts there are entities and "projections" as pojos in room, an entity is a table reference and a projection is an object that room could create at runtime only to return you the result.

your problem like you said it's because the entity and the table didn't match, and for that reason it's better to manage like a projection instead of an entity. I guess you will have another table in your project that you could use as entity and this geo-queries you could manage as projections.

let me share with you some documentation: https://developer.android.com/training/data-storage/room/accessing-data in topic: Return a subset of a table's columns

[mrclayman]

Thanks for the response, although I don't think I see how the docs you linked are related. I am familiar with the concept of an entity class defining the model that a table in an SQLite database is mapped to. In fact, apart from the polygon database, I have another, separate database in my app with a table I map a fairly complex data type to (nested compound data types/classes, foreign-key linked enumerations etc.). I can deal with that fairly easily. Admittedly, it's a database the app creates on-the-fly and has complete control over.

This particular issue, though, I have never faced. The docs linked by you don't even mention projecting a table of data onto a different entity type. Moreover, the @Database declaration requires an existing class to be declared as the entity type. In my case, the "dummy" model I use only has the id column defined as the primary key, which I previously noticed is also a requirement imposed on entity classes by Room.

I may just be misunderstanding what you are trying to say, but I'm still grateful to you for providing me with a clue I could follow to diagnose the issue. Thanks again for that. :slightly_smiling_face:

[dalgarins]

@mrclayman Hi my friend, today I decided to help you a little with your problem, this is the solution:

data class PolygonRecord(
    @PrimaryKey val id: Int?,
    @ColumnInfo(name = "nazev") val name: String?,
    @ColumnInfo(name = "kod")   val code: String?,
    val geom: MultiPolygon?
)

@Entity(tableName = "data_licenses")
data class DataLicenseEntity(
    @PrimaryKey val id: Int?,
    val name: String?,
    val url: String?
)

@Database(entities = [DataLicenseEntity::class], version = 1, exportSchema = false)
@TypeConverters(PolygonDataConverters::class)
abstract class PolygonDatabase : RoomDatabase() {

    abstract fun polygonManager(): IPolygonManager

    companion object {

        private const val DATABASE_NAME = "polygons.sqlite"

        @Volatile
        private var INSTANCE: PolygonDatabase? = null

        fun getDatabase(context: Context): PolygonDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: buildDatabase(context).also { INSTANCE = it }
            }
        }

        private fun buildDatabase(context: Context) = SpatiaRoom.databaseBuilder(
            context.applicationContext,
            PolygonDatabase::class.java,
            DATABASE_NAME
        ).createFromAsset("database/${DATABASE_NAME}").build()
    }
}

that will work for you.

[mrclayman]

@dalgarins , thank you very much for getting back to me and for the proposed solution. I managed to get it working eventually. As I mentioned earlier, I was able to get around the model validation step by assigning a very minimal model class to the Polygon database, similar to what you are doing above. In my case it was just an id column with the PrimaryKey annotation to keep Room happy. Once I figured out what I had to do, the rest was relatively straightforward. 😊 (Although I did struggle a bit with the proper way to offload database operations to worker threads and had to dig deeper into Kotlin coroutines to achieve that. 😄 ) Thanks again for your invaluable input. 🤗

Btw. do you intend to remove the obsolete annotation from the createFromAsset method that you added earlier? I remember Android Studio notifying me about that.