php - PostgreSQL 原始 SQL 插入 (Laravel 5.5)

Question

我正在使用 Laravel 制作一个简单的 REST 服务，该服务将点几何与其他 2 个字符串值一起插入到 PostgreSQL(使用 PostGIS 扩展)表中。由于 Eloquent(据我所知)不支持 PostGIS 函数，因此我使用原始 SQL 查询。

我的 Controller 看起来像这样:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Trgovina;
use Validator;

class TrgovinaController extends Controller
{
     /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {   
        $name = $request->input('name');
        $shop = $request->input('shop');
        $geom = $request->input('geom');

        $geom = "ST_TRANSFORM(ST_GeomFromGeoJSON('".$geom."'), 3857)";


        DB::insert("INSERT INTO trgovinas (name, shop, geom) VALUES (?, ?, ?)", array($name, $shop, $geom));
        return 'Insert Successful';
    }

}

通过请求发送的值是:

名字:利德尔

商店:便利

geom: {"type":"Point","coordinates":[18.126712,42.643304],"crs":{"type":"name","properties":{"name":"EPSG:4326 "}}}

我收到以下错误:

Illuminate\Database\QueryException: SQLSTATE[XX000]: Internal error: 7 ERROR: parse error - invalid geometry HINT: "ST" <-- parse error at position 2 within geometry (SQL: INSERT INTO trgovinas (name, shop, geom) VALUES (Lidl, convenience, ST_TRANSFORM(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[18.126712 42.643304],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'), 3857))) in file C:\xampp\htdocs\volonteri\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 664

如果我在我的数据库中运行以下查询，数据将被正确插入:

INSERT INTO trgovinas (name, shop, geom) VALUES ('Lidl', 'convenience', ST_TRANSFORM(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[18.126712,42.643304],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'), 3857))

我们将不胜感激对调试此问题的任何帮助。是关于查询中的引号吗？有没有办法让 Controller 将查询作为字符串返回而不是查询数据库？这样我就可以手动运行它并查看问题所在。

Answer 1

使用 DB::statement() 进行原始查询，因为您的原始查询正在运行。

DB::statement('your query that works');