Complex INSERTs (mysql Geometry POINT) with Doctrine in Symfony 1.4.x

Question

I'm using symfony 1.4.10, and trying to do a rather complex INSERT into a MySQL table. I'm using MySQL's geometry functions to store a set of latitude, longitude coordinates in a form where I can easily do proximity searches (that's just the background, not really too relevant).

Here's what I'd like to do in raw SQL:

INSERT INTO video_geo (vimeo_vid, coord) 
     VALUES ( 15702113, GeomFromText( 'POINT(-122.415 37.7792)' ));

UPDATE: I'm pretty sure the issue is the parameter "mismatch" with POINT. POINT takes two 'params', lat and lon but it's one column in the DB. Not sure how to apply prepared statement to it (how many question marks and what to give it, a string?)

I'm trying to use a "raw query" with Doctrine, but I can't find too much detailed documentation on it (only really basic examples from which it's hard to extrapolate).

This is my latest trial-and-error attempt:

<?php
$connection = Doctrine_Manager::connection();
$query = "INSERT INTO video_geo (vimeo_vid, coord) VALUES( ? , GeomFromText( 'POINT( ? ? )' ));";
$statement = $connection->prepare($query);
$statement->bindValue(1, $vimeoID);
$statement->bindValue(2, $lat);
$statement->bindValue(3, $lon);
$statement->execute();
?>

This results in the following error:

In the sf log:

Oct 28 22:58:14 symfony [info] {Doctrine_Connection_Statement} execute : INSERT INTO video_geo (vimeo_vid, coord) VALUES( ?, GeomFromText( 'POINT( ? ? )' )); - ()

Exception dumped:

Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in...

I'm thinking the issue is with POINT...should I be passing one parameter? I'm not sure what the type is.

The example in the symfony manual doesn't show how to do prepared statements, I think that's the info I'm looking for here.

Any help appreciated! Thx...

Peter

Have you looked here? stackoverflow.com/questions/2774947/using-raw-sql-with-doctrine — Mark Byers
Thanks, that's helpful. I actually made some updates in the original post (I was using Propel syntax not doctrine). Progress but I'm still getting an error. I'll check out the link but it seems like the syntax I have above is correct, it's just the POINT 'type' that is likely causing an issue...is this one prepared param (of type string?), two? — PeterG
Are you aware of the existence of the Geographical behavior? — greg0ire

domi27 domi27 · Accepted Answer · 2011-11-23T06:12:33

Doctrine uses PDO for MySQL queries.

I'm not quite sure, but i think your problem are the "two question marks" within the POINT() function.

This is from PDO docu : ... placeholder must be used in the place of the whole value ..

So you may try :

$query = "INSERT INTO video_geo (vimeo_vid, coord) VALUES( ? , GeomFromText( 'POINT( ? )' ));";
$statement = $connection->prepare($query);
$statement->bindValue(1, $vimeoID);
$statement->bindValue(2, $lat . " " . $lon);

And as a short hint, i would advice to use named parameters :-)

Complex INSERTs (mysql Geometry POINT) with Doctrine in Symfony 1.4.x

1 Answers