Efcore.pg: NTS: Additional translations

[bricelam]

Doing my research, I found some additional translations you may want to add:

NTS | PostGIS
--- | ---
AsBinary | ST_AsBinary
Buffer | ST_Buffer
ConvexHull | ST_ConvexHull
~Dimension~ | ~ST_Dimension~
~EndPoint~ | ~ST_EndPoint~
~Envelope~ | ~ST_Envelope~
~ExteriorRing~ | ~ST_ExteriorRing~
GetInteriorRingN | ST_InteriorRingN
GetPointN | ST_PointN
IsCCW | ST_IsPolygonCCW
~IsRing~ | ~ST_IsRing~
Normalized | ST_Normalize
~NumInteriorRings~ | ~ST_NumInteriorRing~
~PointOnSurface~ | ~ST_PointOnSurface~
~SRID~ | ~ST_SRID~
~StartPoint~ | ~ST_StartPoint~
ToGMLFeature | ST_AsGML
WKBReader.Read | ST_GeomFromWKB
WKTReader.Read | ST_GeomFromText

[roji]

Yeah, went I implemented the NTS plugin I implemented everything that was straightforward. The above were more difficult for some reason - mainly because the NetTopologySuite API didn't map out-of-the-box - so I deferred them. As people request the above we'll work on adding them.

This is a good up-for-grabs for anyone wanting to get some practice on EF Core SQL translation - I'll accept PRs for individual translations (you don't have to do everything at once).

[tematim]

Hello,

I'll need these one too :
Point
ST_Equals
ST_Buffer

LineString
ST_Centroid
ST_StartPoint
ST_Equals
ST_Buffer

Polygon
ST_Centroid
ST_Multi
ST_Buffer
ST_Dump

[slavanap]

Please don't forget about ST_GeographyFromWKT. I really need that one. Now I'm trying to use NetTopologySuite.Geometries.Geometry.DefaultFactory.CreateMultiPolygon() as a workaround.

[slavanap]

More than that, I'm trying to migrate to PostGIS from SqlGeography and haven't found method similar to SqlGeography.ReorientObject() just yet.

[roji]

Note, implemented the following as part of #672: ST_Dimension, ST_EndPoint, ST_Envelope, ST_ExteriorRing, ST_IsRing, ST_NumInteriorRings, ST_PointOnSurface, ST_SRID, ST_StartPoint

[EricStG]

Are there plans to add the <-> operator to that list? while Distance would work, it doesn't use indexes

[roji]

@EricStG am far from a PostGIS expert: should the <-> operator be used everywhere instead of ST_Distance? If so, why does ST_Distance exist and not make use of indexes? Any more precise info on this would help.

[EricStG]

@roji I'm far from being an expert as well, but my understanding is that <-> returns values that gives you relative distances, but doesn't necessarily map to a real distance unit, that's why it only makes sense to use it for ordering.
From this section, they mention that only specific functions/operators can make use of indices, but not sure why this couldn't be applied to something like ST_Distance

[roji]

@EricStG thanks for the extra info, although I'm still not totally clear on what should happen, and when exactly <-> should be used instead of ST_Distance().

Can you help out by contacting the PostGIS folks and asking for clarification, possibly proposing that they make their documentation clearer on this point?

[EricStG]

To get the nearest neighbors, you would use <->
To search within a distance, you would use ST_DWithin. More info here: https://postgis.net/2013/08/26/tip_ST_DWithin/

You would use ST_Distance or ST_Distance_Sphere when you need to know the actual distance between two points. like the kilometers between two geography points, but because it's calculated for every row, performance would suffer over any significant datasets.

I'll try posting on the PostGIS mailing list to see if anyone can point me to better documentation.

[roji]

Thanks for your help on this @EricStG, we'll wait to see what comes out.

[EricStG]

@roji This is the answer I got: https://lists.osgeo.org/pipermail/postgis-users/2018-November/043009.html

ST_Distance existed before we had <->.
If you are using PostgreSQL 9.5+ with PostGIS 2.2, you can just stick with using <->, but note that it doesn't always use and index. <-> only uses and index when it is in the ORDER BY clause and one side is a constant geometry (which you can achieve even when you don't have a constant by using a LATERAL clause).
In the case of geography though <-> will always give the sphere distance, while ST_Distance by default gives the spheroid distance. So ST_Distance is slightly more accurate to use for geography.

[roji]

@EricStG OK, thanks for the info... I don't think I mind assuming PostgreSQL 9.5+ with PostGIS 2.2.

However, if I'm understanding things correctly, we may want to always use <-> for geometry, but use it for geography only under ORDER BY (since it's inaccurate compared to ST_Distance()).

In any case I'd certainly write back to the PostGIS people to propose adding this important information to their docs...

[EricStG]

Looking at the EF Core spatial data documentation, it looks like the following are still missing:

Geometry.Buffer(double, int)

Can be implemented using ST_Buffer

Geometry.InteriorPoint

Can be implemented using ST_PointOnSurface

Geometry.OgcGeometryType

Can be implemented using ST_GeometryType

Geometry.Union()

There's a comment in the code mentioning that ST_Union with a single parameter is an aggregate, but the SQLite translation uses UnaryUnion, which is supported via ST_UnaryUnion

Does this make sense? Anyone mind if I work on it?

[bricelam]

Correct, Geometry.Union() translates to ST_UnaryUnion. Aggregate Union is tracked by https://github.com/aspnet/EntityFrameworkCore/issues/13278. The expression would look something like UnaryOp.Union(geometries) but the signature probably needs to change to IEnumerable first...

[roji]

Does this make sense? Anyone mind if I work on it?

Absolutely! (it's even marked up for grabs). Looking forward to seeing a PR!

[EricStG]

Doing some quick search in the code, it looks like these are the only missing methods based on the original list:

• ST_IsPolygonCCW
• ST_Normalize
• ST_AsGML
• ST_GeomFromWKB
• ST_GeomFromText

I'll see if I can make those happen in the next few weeks

[roji]

@EricStG that would be great!

@bricelam I'm not sure if we have any unimplemented spatial operations that don't have a good corresponding NetTopologySuite API. If so, should we think about a common EF.Functions thing to possibly expose those operations across providers?

[bricelam]

I'd run them by the NTS team first. They seemed receptive to adding operations that made sense. Otherwise yes, EF.Functions (or maybe just Geometry extension methods) would be the next best thing.