PHP/Phinx - Inserting Longitude/Latitude causes PDO MySQL geometry object error

Question

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?

Answer 1

Solved the problem using the following:

<?php

use Phinx\Seed\AbstractSeed;

class CitySeeder extends AbstractSeed
{
    public function run()
    {
        $data = [
            [
                'name' => 'birmingham',
                'geo_coords' => [0, 0],
            ],
            [
                'name' => 'london',
                'geo_coords' => [0, 0],
            ],
            [
                'name' => 'liverpool',
                'geo_coords' => [0, 0],
            ],
            [
                'name' => 'manchester',
                'geo_coords' => [0, 0],
            ],
        ];

        $conn = $this->getAdapter()->getConnection();
        $sth = $conn->prepare('INSERT INTO city (`name`, `geo_coords`) VALUES (?, POINT(?, ?))');

        foreach($data as $key => $val)
        {
            $sth->execute([
                $val['name'],
                $val['geo_coords'][0],
                $val['geo_coords'][1]]
            );
        }
    }
}