X and Y Coordinates are swapped when trying to deal with geometry as GPS coordinates

[langdonx]

TLDR

@lauxjpn mentioned in this issue #1350 that it should be using ST_Latitude and ST_Longitude, but it's not for us. How can we give Pomelo the hint it needs to stop using ST_X and ST_Y?

Steps to reproduce

Table:

CREATE TABLE `t_address` (
  `gps_location` geometry SRID 4326,
  `gps_location_text` longtext
 )

EF Model (mapping just specifies column names with underscores):

public abstract class Address
{
  public Point GpsLocation { get; set; }
  public string GpsLocationText { get; set; }
}

This code:

// new Coordinate takes x, y
var coordinates = new NetTopologySuite.Geometries.Coordinate(-76, 36);
address.GpsLocation = new NetTopologySuite.Geometries.Point(coordinates) { SRID = 4326 };
address.GpsLocationText = address.GpsLocation.AsText();

Produces this query on SaveChanges:

-- Region Parameters
-- @p2='115', @p0='0xE6100000010100000051DA1B7C610E53C0C898BB96904F4240' (DbType = Binary), @p1='POINT (-76.2247 36.6216)' (Size = 4000)
-- EndRegion
UPDATE `t_address` SET `gps_location` = @p0, `gps_location_text` = @p1
WHERE `address_id` = @p2;
SELECT ROW_COUNT();
GO

But this LINQ:

var query = from a in Addresses
            select new
            {
              Y = va.GpsLocation.Y,
              X = va.GpsLocation.X,
              YCoordinate = va.GpsLocation.Coordinate.Y,
              XCoordinate = va.GpsLocation.Coordinate.X,
            };

Produces the following SQL which swaps the X/Y coordinates (using .Coordinate works because it makes .NET do the work):

SELECT `t`.`address_id`, ST_Y(`t`.`gps_location`), ST_X(`t`.`gps_location`), `t`.`gps_location`, `t`.`gps_location_text`
FROM `t_address` AS `t`
GO

Using ST_Latitude and ST_Longitude gets things working correclty.

The issue

X (Longitude) and Y (Latitude) coordinates are swapped for our purposes (GPS).

Further technical details

MySQL version: 8.0.23 (serverless Aurora) Operating system: Windows 11 Pomelo.EntityFrameworkCore.MySql version: 6.0.2 Microsoft.AspNetCore.App version: N/A

Other details about my project setup: Reproduced in LINQPad 7 after seeing it in our test environment.

[langdonx]

ETA: I thought a trigger was causing it, but it wasn't. The trigger was in play for a SQL Server migration.

The issue appears to be because of us specifying SRID 4326 for the geometry column. We should be able to give Pomelo a hint to use ST_LATITUDE, ST_LONGITUDE for this column, but I'm not sure it's possible right now.

~~Ah, some key missing information here. I found a trigger this morning that was in place from a SQL Server migration:~~

~~On Insert/Update:~~

SET NEW.gps_location = ST_GeomFromText(NEW.gps_location_text, 4326,'axis-order=long-lat');

~~So it looks like we are trying to store GPS as LAT, LONG which is Y, X, but of course Geometry is driven by POINT(X, Y).~~

~~So without the trigger if we use va.GpsLocation.Coordinate.Y C# will grab the X value.~~

~~I guess the same question stands:~~

~~Is there a hint we can give Pomelo to treat this as Geography and use ST_LATITUDE, ST_LONGITUDE?~~

[langdonx]

I found this .HasSpatialReferenceSystem(4326), but it doesn't seem to have any effect. ST_Y, ST_X are still used.

builder.Property(a => a.GpsLocation).HasColumnName("gps_location").HasSpatialReferenceSystem(4326);