.Net Core+PostgreSQL+EF Core 如何从JSON列查询离用户最近位置

我们可以结合PostgreSQL的JSON解析能力+PostGIS空间扩展，搭配EF Core实现需求，具体步骤如下：

前置准备 #

• 首先给PostgreSQL安装PostGIS扩展，用于空间距离计算，执行SQL启用扩展：

CREATE EXTENSION IF NOT EXISTS postgis;

• .NET项目安装对应NuGet包：Npgsql.EntityFrameworkCore.PostgreSQL、Npgsql.EntityFrameworkCore.PostgreSQL.PostGIS

方案1：无需修改现有表结构直接查询 #

如果不想调整现有的JSON存储逻辑，可以直接在EF Core中调用PostgreSQL原生函数实现查询，示例代码如下：

// 假设用户传入的经纬度参数为 userLat、userLng，类型为decimal
var nearestPoint = await _dbContext.YourDirectoryTable
    .SelectMany(d => EF.Functions.JsonToRecordset(d.LocationJsonColumn, "locations")
        .Select(l => new 
        {
            DirectoryId = d.Id,
            Lat = EF.Functions.Convert<decimal>(l.GetProperty("lat")),
            Lng = EF.Functions.Convert<decimal>(l.GetProperty("lng"))
        }))
    .Select(p => new 
    {
        p.DirectoryId,
        p.Lat,
        p.Lng,
        // 计算两点球面距离，单位为米，默认使用WGS84坐标系（SRID=4326）
        Distance = EF.Functions.StDistanceSphere(
            EF.Functions.StMakePoint(p.Lng, p.Lat, 4326),
            EF.Functions.StMakePoint(userLng, userLat, 4326)
        )
    })
    .OrderBy(p => p.Distance)
    .FirstOrDefaultAsync();

注意：PostGIS的StMakePoint函数参数顺序是经度在前、纬度在后，不要传反。

方案2：优化查询性能（推荐数据量较大的场景） #

JSON字段直接查询没有索引，数据量大时性能较差，可以通过增加生成列+空间索引优化：

1. 先给表增加生成列，自动从JSON中提取所有位置点的几何集合：

ALTER TABLE your_directory_table 
ADD COLUMN location_geoms geometry(GeometryCollection,4326) 
GENERATED ALWAYS AS (
    ST_Collect(
        ARRAY(
            SELECT ST_MakePoint(
                (elem->>'lng')::numeric, 
                (elem->>'lat')::numeric, 
                4326
            ) 
            FROM jsonb_array_elements(location_json_column->'locations') elem
        )
    )
) STORED;

2. 给生成列创建GIST空间索引：

CREATE INDEX idx_directory_location_geoms ON your_directory_table USING GIST(location_geoms);

3. 之后查询可以直接走空间索引，速度提升非常明显。

额外建议 #

如果后续位置查询是高频需求，建议直接把分支机构位置从JSON中拆出来做单独的子表，关联名录主表ID，每个位置单独存一行经纬度和几何字段，后续维护、查询、索引优化都会更方便。

内容的提问来源于stack exchange，提问作者salah