如何在Laravel中编写原始查询并传递坐标参数？

问题背景 #

已知可用原生SQL查询：

SELECT doctors.user_id, (ST_DistanceSpheroid(geometry(location), ST_GeomFromText('POINT(37.857397 55.690576)'), 'SPHEROID["WGS 84",6378137,298.257223563]')
       ) as distance FROM users INNER JOIN doctors ON doctors.user_id = users.id ORDER BY distance ASC

需要在Laravel中实现该查询并正确传递坐标参数，以下是之前的错误尝试及问题分析：

错误尝试1 #

代码：

$point = "37.854289 55.685333";
return DB::table('users')
      ->select(DB::raw('ST_DistanceSpheroid(geometry(location), ST_GeomFromText(\'POINT(?)\'), \'SPHEROID["WGS 84",6378137,298.257223563]\''), [$point])
      ->get();

报错：

"message": "stripos(): Argument #1 ($haystack) must be of type string, array given",

问题原因：DB::raw()仅接受字符串参数，不支持直接传入参数数组，参数绑定不能通过DB::raw的第二个参数实现。

错误尝试2 #

代码：

$lon = 37.857397;
$lat = 55.685333;

return DB::table('users')
    ->selectRaw(
        "(ST_DistanceSpheroid(
        geometry(location),
        ST_GeomFromText('POINT(? ?)'),
        'SPHEROID[?, ?, ?]'
    )) as distance",
        [$lon, $lat, 'WGS 84', 6378137, 298.257223563]
    )->leftJoin('doctors', 'doctors.user_id', 'users.id')->orderBy('distance', 'ASC')->get();

报错：

{
    "message": "PDO: SQLSTATE[XX000]: Internal error: 7 ОШИБКА:  SPHEROID parser - couldnt parse the spheroid\nLINE 4:             'SPHEROID[?, ?, ?]'\n                    ^ (SQL: select (ST_DistanceSpheroid(\n            geometry(location),\n            ST_GeomFromText('POINT(37.857397 55.685333)'),\n            'SPHEROID[WGS 84, 6378137, 298.257223563]'\n        )) as distance from \"users\" left join \"doctors\" on \"doctors\".\"user_id\" = \"users\".\"id\" order by \"distance\" asc)"
}

问题原因：SPHEROID的格式要求必须用双引号包裹名称，参数绑定后丢失了双引号，导致PostGIS无法解析；且SPHEROID为固定参数，无需动态绑定。

正确实现方式 #

方法一：使用selectRaw安全绑定坐标参数（推荐） #

用ST_MakePoint直接传入经纬度参数，避免字符串拼接风险，SPHEROID固定写入SQL：

$lon = 37.857397;
$lat = 55.690576;

return DB::table('users')
    ->selectRaw(
        'doctors.user_id, ST_DistanceSpheroid(geometry(location), ST_MakePoint(?, ?)::geometry, ?) as distance',
        [$lon, $lat, 'SPHEROID["WGS 84",6378137,298.257223563]']
    )
    ->join('doctors', 'doctors.user_id', '=', 'users.id')
    ->orderBy('distance', 'ASC')
    ->get();

方法二：使用DB::raw结合addBinding实现绑定 #

若需使用DB::raw，可通过addBinding手动绑定参数：

$lon = 37.857397;
$lat = 55.690576;

$query = DB::table('users')
    ->select(DB::raw('doctors.user_id, ST_DistanceSpheroid(geometry(location), ST_MakePoint(?, ?)::geometry, \'SPHEROID["WGS 84",6378137,298.257223563]\') as distance'))
    ->join('doctors', 'doctors.user_id', '=', 'users.id')
    ->orderBy('distance', 'ASC');

$query->addBinding([$lon, $lat], 'select');

return $query->get();

关键注意点 #

• SPHEROID为固定格式参数，直接写入SQL即可，不要动态绑定，避免格式错误。
• 优先使用ST_MakePoint(?, ?)构造点几何，比拼接POINT()字符串更安全，防止SQL注入。
• selectRaw是Laravel处理带参数原生查询的标准方式，语法更简洁清晰。

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