Insert with postgis functions using Laravel

Question

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)")
    ]);

Sehael · Accepted Answer · 2017-03-22 23:13:25Z

3

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.

answered Mar 22, 2017 at 23:13

Sehael

3,7361 gold badge26 silver badges39 bronze badges

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

Comments

user4212639 · Accepted Answer · 2017-03-21 02:47:34Z

1

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.

edited Mar 21, 2017 at 2:47

answered Mar 21, 2017 at 2:40

user4212639

4996 silver badges6 bronze badges

2 Comments

Sehael Over a year ago

It doesn't look like that package supports WKT, it's build is failing, and it's documentation is horrible. Also, if you read my whole question, you would see that I have already tried DB::insert() Thanks for the suggestion

user4212639 Over a year ago

Agree the documentation isn't great. It does look like it supports WKT from their source code.

macieks · Accepted Answer · 2018-03-16 11:05:45Z

1

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

answered Mar 16, 2018 at 11:05

macieks

4918 silver badges14 bronze badges

Collectives™ on Stack Overflow

Insert with postgis functions using Laravel

EDIT

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related