I have a PostGIS table of points. With my php code and pdo, I want to add a point in it. I have the coordinates, I want to get the geometry, with ST_GeomFromText. Here is the code :

$x = $_POST['x'];
$y = $_POST['y'];
$geom="ST_GeomFromText('POINT($x $y)',2154)";
$r = $bdd->prepare("INSERT INTO ouvragesp (id, yl93, xl93,annee,geom) VALUES(:identifiant , :y , :x , 2017, :geom)");
  'identifiant' => $id,
  'x' => $x,
  'y' => $y,
  'geom' => $geom));

However, when I execute it, it tells me that :

PDOStatement::execute(): SQLSTATE[XX000]: Internal error: 7 ERROR: parse error - invalid geometry HINT: "ST" <-- parse error at position 2 within geometry 

I don't see why it takes only "ST" part.

Does someone know where the problem comes from and how to solve it ?

  • did you try to escape the underscore? – Mazu_R Jul 23 '18 at 7:59
  • 1
    Sounds more like a PHP problem. – bugmenot123 Jul 23 '18 at 8:25
  • @Mazu_R yes I tried to escape it, nothing change. The problem does not come from that : whatever the word, it takes only the 2 first letters – J.Delannoy Jul 23 '18 at 8:53
  • 2
    @bugmenot123 Perhaps I should better post it on stackoverflow ? – J.Delannoy Jul 23 '18 at 8:53
  • 1
    I would as well try on stackoverflow as it seems to be PHP specific – Mazu_R Jul 23 '18 at 9:11

I found the solution thanks to this post : pdo prepare statement with functions

It is more a php question as bugmenot123 pointed out, but just in case I post the solution anyway :)

The trick was to put the function in the prepare part but the creation of the point in the execute part.

$r = $bdd->prepare("INSERT INTO ouvragesp (id, yl93, xl93,annee,geom) VALUES(:identifiant , :y , :x , 2017,ST_GeomFromText(:geom,2154))");
  'identifiant' => $id,
  'x' => $x,
  'y' => $y,
  'geom' => "POINT($x $y)"));
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.