CODEWITHSUNDEEP
phppostgresqllaravelpostgis
Marin Leontenko
Marin Leontenko

Reputation: 771

PostgreSQL raw SQL insert (Laravel 5.5)

I'm using Laravel to make a simple REST service that inserts a point geometry into a PostgreSQL (extended with PostGIS) table along with 2 other string values. Since Eloquent (as far as i know) doesn't support PostGIS functions, I'm using raw SQL queries.

My controller looks like this:

<?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';
    }

}

The values sent through the request are:

name: Lidl

shop: convenience

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

I get the following error:

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

If I run the following query in my database, data is inserted correctly:

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))

Any help debugging this would be apreciated. Is it something about the quotes in query? Is there a way to make controller return the query as a string instead of querying the database? That way I could run it manually and see what is wrong.

Upvotes: 3

Views: 2908

Answers (1)

Sohel0415
Sohel0415

Reputation: 9853

Use DB::statement() to make a raw query as your raw query is working.

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

Upvotes: 1

Related Questions