Monday, March 20, 2017

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/gpx/'.$filename.
            "', 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



via Sehael

Advertisement