I'm attempting to create a CitySeeder
using Phinx. But I'm getting the following error:
[PDOException]
SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field
Here is my seeders/CitySeeder.php
class. The geo_coords
field uses a POINT
datatype:
<?php
use Phinx\Seed\AbstractSeed;
class CitySeeder extends AbstractSeed
{
public function run()
{
$data = [
[
'name' => 'birmingham',
'geo_coords' => 'POINT(0 0)'
],
[
'name' => 'london',
'geo_coords' => 'POINT(0 0)'
],
[
'name' => 'liverpool',
'geo_coords' => 'POINT(0 0)'
],
[
'name' => 'manchester',
'geo_coords' => 'POINT(40 -100)'
],
];
$cityTable = $this->table('city');
$cityTable->insert($data)->save();
}
}
Which is strange because if I enter that manually into the database it works.
Does the longitude/latitude have to be formatted in a certain way? I've tried using an array, and a space separated long lat
format but I still get the same error. I've even went as far as looking through the source code but can not find anything useful.
Any help would be much appreciated.
Edit
I've inspected the code from the Phinx library where the error is happening:
public function insert(Table $table, $row)
{
$this->startCommandTimer();
$this->writeCommand('insert', array($table->getName()));
$sql = sprintf(
"INSERT INTO %s ",
$this->quoteTableName($table->getName())
);
$columns = array_keys($row);
$sql .= "(". implode(', ', array_map(array($this, 'quoteColumnName'), $columns)) . ")";
$sql .= " VALUES (" . implode(', ', array_fill(0, count($columns), '?')) . ")";
$stmt = $this->getConnection()->prepare($sql);
$stmt->execute(array_values($row));
$this->endCommandTimer();
}
The data from array_values($sql)
at the point of failure is:
array(2) {
[0]=>
string(10) "birmingham"
[1]=>
string(26) "POINT(0 0)"
}
And the query after $sql
is set:
string(55) "INSERT INTO `city` (`name`, `geo_coords`) VALUES (?, ?)"
When doing the following after prepare()
: die(var_dump($stmt->debugDumpParams()));
:
SQL: [55] INSERT INTO `city` (`name`, `geo_coords`) VALUES (?, ?)
Params: 0
NULL
Logging the MySQL queries shows the following:
2016-12-12T12:53:12.721287Z 12 Query INSERT INTO `city` (`name`, `geo_coords`) VALUES ('birmingham', 'POINT(0, 0)')
I believe this is incorrect because the POINT is being inserted as a string?
Solved the problem using the following: