CODEWITHSUNDEEP
phpmysqlpdogis
TryHarder
TryHarder

Reputation: 2777

How to insert point data into mysql using PDO bindParam?

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

Upvotes: 4

Views: 6409

Answers (2)

malhal
malhal

Reputation: 30573

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...

Upvotes: 1

Inglis Baderson
Inglis Baderson

Reputation: 799

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

Upvotes: 7

Related Questions