PostgreSQL raw SQL insert (Laravel 5.5)

Question

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.

try this $geom = DB::raw("ST_TRANSFORM(ST_GeomFromGeoJSON('".$geom."'), 3857)") — Sohel0415
– Sohel0415, Commented Feb 2, 2018 at 12:21
@sohel0415 I tried it but it still returns the same error. If i remove query and add return $geom;, response looks like this: ST_TRANSFORM(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[18.126712,42.643304],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'), 3857) — Marin Leontenko
– Marin Leontenko, Commented Feb 2, 2018 at 12:54
should I make it a answer then?? as it may help others later — Sohel0415
– Sohel0415, Commented Feb 2, 2018 at 15:43

Sohel0415 · Accepted Answer · 2018-02-02 15:53:19Z

1

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

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

answered Feb 2, 2018 at 15:53

Sohel0415

9,89124 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL raw SQL insert (Laravel 5.5)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related