How to insert point data into mysql using PDO bindParam?

Question

DETAILS

I'd like to make use of mysql's spatial extension, so I am trying to store longitude and latitude in a mysql table of datatype POINT using bindParam. Unfortunately, I keep getting the error SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'location' cannot be null.

I've checked that longitude and latitude have values. So the problem has to be with my code, but I cannot see what I am doing wrong.

Here is the code I am using.

$location=$latitude." ".$longitude;
$sql = "INSERT INTO my_geodata SET location = PointFromText('POINT(:location)')";
      //INSERT INTO my_geodata SET location = PointFromText('POINT(-41 12)');    

try 
{
    $stmt = $dbh->prepare($sql);            
    $stmt->bindParam(':location', $location, PDO::PARAM_STR);           
    $stmt->execute();   
    $dbh = null;
}

catch(PDOException $e)
{               
    echo $error=$e->getMessage();
}

QUESTION

What am I doing wrong? How can I insert longitude and latitude into a mysql table (that uses POINT datatype) with PDO and bindParam?

VARIATION

Based on AgreeOrNot's answer, a slightly different way to achieve this is

$location = 'POINT(' . $latitude . " " . $longitude . ')';    
$sql = "INSERT INTO my_geodata (location) VALUES (PointFromText(:location))";

Have you tried using a prepared statement instead with ? instead of :? — null
– null, Commented Jul 27, 2013 at 2:37
No I haven't done that. What is the difference?binding values instead of parameters? — TryHarder
– TryHarder, Commented Jul 27, 2013 at 2:41
Ya, worth a shot if only to eliminate, it'll take a moment to test. Insert bla into bla where bla = ? sql->bindValue(1, coords); — null
– null, Commented Jul 27, 2013 at 2:45

Inglis Baderson · Accepted Answer · 2013-07-27 04:34:29Z

7

Note that parameterizing queries is not (simple) string replacement. In your code your query parameter is put in a string literal which will be kept untouched.

Try this:

$location = 'POINT(' . $latitude . " " . $longitude . ')';
$sql = "INSERT INTO my_geodata SET location = PointFromText(:location)";

edited Jul 27, 2013 at 4:34

answered Jul 27, 2013 at 4:11

Inglis Baderson

7994 silver badges13 bronze badges

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

2 Comments

doubleJ Over a year ago

It's worth noting that putting PointFromText() in the variable IS NOT the same as putting it in the query.

andrebruton Over a year ago

PointFromText( has been updated to ST_PointFromText(

malhal · Accepted Answer · 2013-10-19 21:07:37Z

1

Spatial extensions aren't designed for latitude and longitude yet, they are designed for Cartesian coordinates. The distance functions don't take into account the curvature of the earth so always give wrong results. Quite sad I know...

answered Oct 19, 2013 at 21:07

malhal

31.5k7 gold badges125 silver badges154 bronze badges

Collectives™ on Stack Overflow

How to insert point data into mysql using PDO bindParam?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related