Sehael Sehael -4 years ago 173
PHP Question

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download