CODEWITHSUNDEEP
phppostgresqllaravelpostgis
Sehael
Sehael

Reputation: 3736

Insert with postgis functions using Laravel

I am attempting to insert geometry into my postgresql data with the postgis extension. More specifically, I want to insert a WKT linestring. This is what I have done:

$sql = "INSERT INTO myschema.trail (\"name\", \"description\", \"user_id\", \"location_id\", \"source_file\", \"geom\") 
        VALUES ('".
            $p['name']."', '".
            $p['description']."', ".
            \Auth::user()->user_id.", ".
            $p['location_id'].", '".
            'uploads/myfile'.",
            ST_GeomFromText('$wkt'::text))";
\DB::insert($sql);

When i do this, I get an error:

SQLSTATE[42883]: Undefined function: 7 ERROR: function st_geomfromtext(text) does not exist

I had first tried to do this using parameters, but had the same problem. The error shows that the following generated SQL:

INSERT INTO myschema.trail ("name", "description", "user_id", "location_id", "source_file", "geom")
VALUES ('Test', 'Test', 1, 1, 'uploads/myfile', ST_GeomFromText('LINESTRING(-114.0653326549 49.2872680351, .............'))

When I copy and paste the generated SQL statement and run the query in pgAdmin, the query executes without a problem. I have tried using the postgres user in my application incase it was a permission issue, but that didn't help.

If I remove the postgis part of the query, it runs fine from my application. For whatever reason, my Laravel application cannot use the postgis functions, but pgAdmin can.

Has anyone noticed this before, or have any ideas how I can solve this? Thanks

EDIT

I just tried the following as well, and I got the same error:

Trail::create([
        'name' => 'test',
        'description' => 'test',
        'user_id' => 1,
        'location' => 1,
        'source_file' => 'uploads/myfile',
        'geom' => \DB::raw("ST_GeomFromText('$wkt'::text)")
    ]);

Upvotes: 3

Views: 4048

Answers (3)

macieks
macieks

Reputation: 481

This i my code: 

  $last_id = DB::table('table.geom')->insertGetId(
    [
      'geom' => \DB::raw("ST_GeomFromText('$data->geom',4326)"),
      'id_layer' => $data->id_layer,
      'object_type' => $data->object_type
    ]
  );

Upvotes: 1

Sehael
Sehael

Reputation: 3736

So it appears the issue is with specifying the schema. In PgAdmin, it looks like you don't need to specify the schema that the db functions are a part of in order to use the db functions. In Laravel, you have to specify the schema for the db functions. So my code works, but I just had to add the db schema to the function. So this works:

Trail::create([
    'name' => 'test',
    'description' => 'test',
    'user_id' => 1,
    'location' => 1,
    'source_file' => 'uploads/myfile',
    'geom' => \DB::raw("public.ST_GeomFromText('$wkt'::text)")
]);

I always thought that queries defaulted to the public schema, but I guess in this case you have to define it.

Upvotes: 3

user4212639
user4212639

Reputation: 499

Are you using postgis extension for laravel? something like THIS.

By default, laravel would try to see if ST_GeomFromText is underlying database function if you don't have extention added for postgis. This will help working with object with geometries very easy. Hope that helps.

Alternatively try running a RAW SQL. Like this:

DB::insert('QUERY...');

Make sure to escape query parameters appropriately.

Upvotes: 1

Related Questions