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?

But it works fine if I write an SQL query to insert `POINT(40 -100)`. Even if I enter `POINT(10, 10)` for example I get the same error. — BugHunterUK, Dec 09 '16 at 16:59
It's hard to say what's wrong anyway. We have a MySQL error but only PHP code to inspect. — Álvaro González, Dec 12 '16 at 09:12
@ÁlvaroGonzález I'm having a hard time getting any MySQL data. Phinx doesn't offer any support to dump the raw SQL query and I can't find anything in MySQL logs. — BugHunterUK, Dec 12 '16 at 10:35
I know nothing about that library but appears to be pure PHP code. In the worse case you can simply type a `var_dump()` yourself. — Álvaro González, Dec 12 '16 at 10:41
@ÁlvaroGonzález I found the part where it fails in the library and updated the answer. I'm doing a bit of debugging now. — BugHunterUK, Dec 12 '16 at 11:04
@ÁlvaroGonzález Updated. I logged the queries in MySQL and it seems to be inserting the point as a string? Is that correct? — BugHunterUK, Dec 12 '16 at 12:54
Here's where my knowledge ends. I suppose it's all binary data and you can either execute the function that produces it or instruct MySQL to parse and run it from a string but this last case probably requires some syntax I can't tell you. Sorry. — Álvaro González, Dec 12 '16 at 13:03

BugHunterUK · Accepted Answer · 2016-12-13T10:11:55.387

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]]
            );
        }
    }
}

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

1 Answers1