Implement SpatiaLite for SQLDelight

[matheusmatos]

Description

Hi everyone, I would like to be able to do this with SQLDelight:

CREATE TABLE polygons (
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    geom GEOMETRY,
);

getPolygonsByPoint:
SELECT
    id, name
FROM polygons
WHERE ST_Contains(geom, MakePoint(?, ?));

The ST_Contains function and the GEOMETRY data type are implemented by the SpatiaLite library.

I'm unsure if someone is already working on this, but if not, I would like to attempt the implementation.

My plan is to initially develop a Kotlin Multiplatform library specifically for SpatiaLite, independent of SQLDelight integration. Do you think this is the best approach, or would it be more beneficial to implement it directly as an extension of SQLDelight?

If anyone has suggestions or would like to discuss this further, it would be greatly appreciated.

Thanks!

[matheusmatos]

I forgot to mention, my need is to use it on both Android and iOS, preferably in a multiplatform module. However, I believe the implementation will be applicable to any platform.

[dellisd]

So I think this boils down to three things (or maybe a few more):

The first is to add the Spatialite grammar which is done with a dialect module, similar to how JSON support for SQLite is implemetned. This makes the SQLDelight compiler aware of the Spatialite functions and geometry types.

Next comes the runtime component. You need some (multiplatform) Kotlin representation for the different types of geometry and the SQLDelight compiler needs to be able to generate code that will expose these types at runtime. I think making the dialect aware of this is done by implementing the DialectType interface which is used by the SQLDelight compiler to generate the code that is used in queries.

Spatialite stores geometry data as BLOBs, so I think the dialect type would need to return some code that can call SqlCursor.getBytes() and then decode it appropriately into the right classes. In terms of actual geometry implementations that can be used at runtime, I'll shamelessly plug my spatial-k library here, but any implementation would work.

Lastly there's linking Spatialite into your app to actually have it run on Android an iOS. That's out of scope for SQLDelight, but as long you can do that then the SQLDelight multiplatform drivers will work for you!

---

Overall though, this is probably not something that we'd merge into the SQLDelight repository itself, but rather it can live as a separate project that implements the SQLDelight dialect APIs. That project could then be used with SQLDelight. This is the place to ask questions about implementing it though!

Personally I'd love to have Spatialite working with SQLDelight, but I'm a bit short on time to work on it for the time being.

[dellisd]

After thinking more about this, you wouldn't actually need to expose the new types as geometry classes since they can be exposed as BLOBs. You can do the conversion to a geometry class as a plain old adapter.