Sqlite + Spatialextension

[TFreudi1]

Are there any sample how to create/use a spatial sqlite database on maui ? Pure SQLite is simple but I need it with spatial extensions.

[ghost]

@TFreudi1 could this work? CoPilot suggested.

Start with a basic SQLite integration in MAUI (see Microsoft's sample).

Modify the SQLite connection to load the spatial extension using SELECT load_extension('mod_spatialite');.

Use spatial SQL queries like SELECT AsText(GeomFromText('POINT(1 2)')).

[TFreudi1]

My guess is "no". Did you have "mod_spatialite" ? This must be code for Android, iOS ... maybe different CPU architectures.
It is a while ago but as I checked it that did'nt work because mod_spatialite is missing.

[ghost]

I guess you are right @TFreudi1 .
CoPilot found this route Microsoft.Data.Sqlite and extenions loading. What about this one?

[TFreudi1]

That also depends on mod_spatialite available to all platforms. If the nuget package contains mod_spatialite for all platforms it will work.
I did'nt use Microsoft.data.Sqlite , my sqlite nuget package did'nt contain it, I#m on holiday right now , I will check it when I'm back, but chances are very low because, if I'm right , it is based on that:
https://github.com/praeclarum/sqlite-net/tree/master
And there are no mods*

I only knew it from as DLL from my local Windows Installation. I got the extension from there:
https://www.gaia-gis.it/gaia-sins/

[stephenquan]

I checked the mod_spatialite package on NuGet.org Package Explorer https://nuget.info/packages/mod_spatialite/4.3.0.100-rc1 and confirmed that it only contains Windows DLLs. There’s nothing included for Android, macOS, or iOS. Also see this open issue efcore/mod_spatialite-NuGet#6

[ghost]

OK, CoPilot found alternative to SQL, NetTopologySuite, maybe somebody can get help using this.

[stephenquan]

[Edit/Rewrite]

I have created a mashup between NetTopologySuite and sqlite-net-pcl and called it SQuan.Helpers.SQLite.Spatial. It uses WKT (Well-Known Text) as the geometry format, allowing spatial data to store in text columns. Its spatial functions are loosely inspired by those in PostGIS.

using SQuan.Helpers.SQLiteSpatial; // namespace

// Example usage
SQLiteSpatialConnection db = new(":memory:");
double? area_50_units = db.ExecuteScalar<double?>("SELECT ST_Area('POLYGON((10 10,20 10,20 20,10 10))')");
string? centroid_at_5_5 = db.ExecuteScalar<string>("SELECT ST_Centroid('POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))')");
string? circle_buffer = db.ExecuteScalar<string?>("SELECT ST_Buffer('POINT(10 10)', 5)");

The API can help you create and maintain R*Tree spatial indexes, e.g.

-- Create the table.
CREATE TABLE IF NOT EXISTS UsaCities
(
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Color TEXT NOT NULL,
    WKT TEXT NULL
);

-- Populate with sample data.
INSERT INTO UsaCities (Name, Color, WKT) VALUES ('Los Angeles,CA', '#08102c', 'POINT(-118.2437 34.0522)');
INSERT INTO UsaCities (Name, Color, WKT) VALUES ('New York,NY', '#65a1d3', 'POINT(-74.006 40.7128)');

-- Create and populate the R*Tree spatial index.
CREATE VIRTUAL TABLE UsaCities_rtree USING rtree(Id, MinX, MaxX, MinY, MaxY);
INSERT INTO UsaCities_rtree 
SELECT Id, ST_MinX(WKT), ST_MaxX(WKT), ST_MinY(WKT), ST_MaxY(WKT) FROM UsaCities;

-- Test the R*Tree spatial index.
SELECT *
FROM   UsaCities c,
       UsaCities_rtree r
WHERE  r.MinX <= -114.134458 AND r.MaxX >= -124.410607
AND    r.MinY <= 42.009659   AND r.MaxY >= 32.534231
AND    c.Id = r.Id

Relevant links:

• NuGet Package: SQuan.Helpers.SQLite.Spatial
• GitHub Repo: https://github.com/stephenquan/SQuan.Helpers
• Files
  • SQLiteSpatialConnection.cs - Replacement/subclass of SQLiteConnection
  • SQLiteSpatialExtension.cs - SQLite spatial wrappers
  • SamplePage.xaml.cs - Demos populate and viewing spatial data