EF Core未使用NetTopologySuite Point的SRID问题排查

我正在使用PostGIS扩展（对其并不熟悉），尝试匹配路线内的部分坐标。目前遇到的核心问题是EF Core似乎未使用NetTopologySuite Point的SRID。

EF Core实现代码 #

var spatialReference = 4326;
var factory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: spatialReference);

var startingPoint = factory.CreatePoint(new Coordinate(route.CityA.Long, route.CityA.Lat));
var endingPoint = factory.CreatePoint(new Coordinate(route.CityB.Long, route.CityB.Lat));

Console.WriteLine("Starting point SRID: {0}", startingPoint.SRID);

var startingPointTolerance = 0.01;
var endingPointTolerance = 0.01;

var matchingRoutes = await _context.Set<Rides>()
   .Where(x => 
       x.Route != null &&
       EF.Functions.IsWithinDistance(x.Route.StartPoint, startingPoint, startingPointTolerance, false) &&
       EF.Functions.IsWithinDistance(x.Route.EndPoint, endingPoint, endingPointTolerance, false))
   .ToListAsync();

生成的SQL查询语句 #

-- INFO --
Executed DbCommand (27ms) [
Parameters=[
    @__startingPoint_1='POINT (26.10626 44.43225)' (DbType = Object), 
    @__startingPointTolerance_2='0.01', 
    @__endingPoint_3='POINT (23.816667 44.333333)' (DbType = Object), 
    @__endingPointTolerance_4='0.01'], 
    CommandType='Text', CommandTimeout='30']

-- QUERY --
    SELECT r."Id", r."DriverId", r."EndCity", r."Route", r."StartCity"
    FROM "Rides" AS r
    WHERE r."Route" IS NOT NULL AND 
    ST_DWithin(
        ST_StartPoint(r."Route"), 
        @__startingPoint_1, 
        @__startingPointTolerance_2, 
        FALSE) 
    AND 
    ST_DWithin(
        ST_EndPoint(r."Route"), 
        @__endingPoint_3, 
        @__endingPointTolerance_4, 
        FALSE)

原生SQL解决方案 #

var sqlQuery = $@"
SELECT r.""Id"", r.""DriverId"", r.""StartCity"", r.""EndCity"", r.""Route""
FROM ""Rides"" r
WHERE
    (
        ST_DWithin(
            ST_StartPoint(r.""Route""),
            ST_SetSRID(ST_MakePoint(@city_a_lat, @city_a_long), @srid),
            @starting_point_tolerance
        )
        AND
        ST_DWithin(
            ST_EndPoint(r.""Route""),
            ST_SetSRID(ST_MakePoint(@city_b_lat, @city_b_long), @srid),
            @ending_point_tolerance
        )
    );";

var city_a_lat = new NpgsqlParameter("city_a_lat", route.CityA.Lat) { NpgsqlDbType = NpgsqlDbType.Double };
var city_a_long = new NpgsqlParameter("city_a_long", route.CityA.Long) { NpgsqlDbType = NpgsqlDbType.Double };
var starting_point_tolerance = new NpgsqlParameter("starting_point_tolerance", startingPointTolerance) { NpgsqlDbType = NpgsqlDbType.Double };

var city_b_lat = new NpgsqlParameter("city_b_lat", route.CityB.Lat) { NpgsqlDbType = NpgsqlDbType.Double };
var city_b_long = new NpgsqlParameter("city_b_long", route.CityB.Long) { NpgsqlDbType = NpgsqlDbType.Double };
var ending_point_tolerance = new NpgsqlParameter("ending_point_tolerance", endingPointTolerance) { NpgsqlDbType = NpgsqlDbType.Double };

var srid = new NpgsqlParameter("srid", spatialReference) { NpgsqlDbType = NpgsqlDbType.Integer };

var matchingRoutesRaw = await _context.Set<Rides>()
    .FromSqlRaw(sqlQuery, city_a_lat, city_a_long, starting_point_tolerance, city_b_lat, city_b_long, ending_point_tolerance, srid)
    .ToListAsync();

问题成因 #

这是EF Core处理空间类型时的已知问题：

• 问题1：更新空间数据时提示“空间参考标识符(SRID)无效”
• 问题2：无法通过EF Core LINQ查询修改Point的SRID

本质是EF Core在将NetTopologySuite的Geometry对象转换为数据库参数时，未正确携带SRID信息，导致生成的SQL中缺少ST_SetSRID调用，而PostGIS要求参与空间计算的对象必须有匹配的SRID才能正确执行。

内容的提问来源于stack exchange，提问作者Ispilante-Brusli