Efcore.pg: Add support for ST_Transform

[plamen-i]

It is common case to store geometry in one SRID (often a state-level requirement) and to render it on the map with another SRID (mostly in WebMercator due to using public cached base maps).

While this can be done in C# using NTS/ProjNet I suppose using ST_Transform can be faster approach.

Supporting:
geometry ST_Transform(geometry g1, integer srid);

seems sufficient, at least for now.

[roji]

What would be the corresponding operation with NetTopologySuite? In other words, is there an existing method or similar in NetTopologySuite which would faithfully translate to PostGIS ST_Transform?

[roji]

/cc @bricelam

[plamen-i]

Found this in documentation:
https://github.com/NetTopologySuite/NetTopologySuite/wiki/Upgrading-to-2.0-from-1.x#nettopologysuitecoordinatesystems

[bricelam]

No good equivalent in NTS/ProjNet. Just add something to EF.Functions.

[roji]

Thanks, yeah, it seemed that way...

[roji]

@plamen-i are you interested in preparing a PR for this? It should actually be quite easy - just add the appropriate extension function to EF.Functions (like these but in the NTS plugin) and provide translation for it.

[plamen-i]

Sure, I will try :-)

@bricelam I see that spatialite also supports ST_Transform - can I try to prepare PR for this to be supported in Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite?

[bricelam]

😬 I’m actually debating removing that functionality from our build of SpatiaLite. It depends on PROJ which is causing segfaults in the latest version (and has licensing issues on iOS)

[plamen-i]

@bricelam

I closely follow your discussions on spatialite about licensing, dlls for different platforms, segfaults, etc.

I am preparing to start working on a project where we will need ST_Transform in PostgreSQL/PostGIS to be supported in EF Core on the server and because of this I opened this issue. And because of missing multiplatform support (mainly Android and iOS) I will use my 'custom' spatialite on the clients (plain SQLite database, tables with 4 additional columns for XMin, XMax, YMin, YMax and one column to store WKB).

As I wrote here spatialite is a MUST on a complete offline-enabled GIS architecture. So I can assure you that when we have one COMPLETE spatialite (all additional libraries included, multiplatform, working stable) it will be much appreciated and used by whole community.

And for now... OK, lets postpone this for better times... Anyway, I will use 'custom' spatialite... Sadly that spatialite community did not respond with enthusiasm to your and my proposals/requests...

[bricelam]

What if... you just implemented ST_Transform as a user-defined function on SQLite using NTS and ProjNet?

sqliteConnection.CreateFunction(
    "ST_Transform",
    (byte[] geom, int newSrid) =>
    {
        var ntsGeom = gaiaGeoReader.Read(geom);        
        var transformation = coordinateSystemServices.CreateTransformation(ntsGeom.SRID, newSrid);
        var result = ntsGeom.Transform(transformation.MathTransform);

        return gaiaGeoWriter.Write(result);        
    });

[bricelam]

Sadly that spatialite community did not respond with enthusiasm to your and my proposals/requests...

+1 They're not a very helpful FOSS community...

[plamen-i]

What if... you just implemented ST_Transform as a user-defined function on SQLite using NTS and ProjNet?
sqliteConnection.CreateFunction(
"ST_Transform",
(byte[] geom, int newSrid) =>
{
var ntsGeom = gaiaGeoReader.Read(geom);
var transformation = coordinateSystemServices.CreateTransformation(ntsGeom.SRID, newSrid);
var result = ntsGeom.Transform(transformation.MathTransform);

    return gaiaGeoWriter.Write(result);        
});

Good idea :-) Will use it when I do need it.