Phinx: Unable to seed data with POINT datatype

[JamesTheHacker]

This is the second time I've posted this issue. I closed my last one as the data I was testing with was incorrect. I can not insert data into a table that has a POINT column type and get 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's the data I'm trying to seed:

<?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(0 0)"
            ],
        ];

        $cityTable = $this->table('city');
        $cityTable->insert($data)->save();
    }
}

I will happily submit a pull request to update the documentation if anyone can help with this. The docs are severely out of data and do not discuss seeding data that uses POINT. I'm afraid if I can not resolve this issue it's a deal breaker and will have to switch to an alternative migration tool, which is sad because I really like Phinx.

The following query works fine, so I know it's not an issue with MySQL:

SELECT ST_Distance_Sphere(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POINT(180 0)'));

The major issue with Phinx is that you can't even dump the raw SQL to check the query. I see an issue has been raised to add this #93

I can't get help on this anywhere and would appreciate any advice. As I say I'm happy to update the docs if this issue can be solved.

Thanks

[JamesTheHacker]

@robmorgan this is a bug. I discovered what was happening. I was logging the MySQL query when inserting the POINT using seed data and it returned the following:

2016-12-12T12:53:12.721287Z    12 Query INSERT INTO `city` (`name`, `geo_coords`) VALUES ('birmingham', 'POINT(0, 0)')

The POINT is being inserted as a string. It fails here (PDOAdapter.php):

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

I'm happy to submit a pull request and make an attempt at fixing if this is something you want to do? I think the solution would be instead of passing the data into execute you could instead bind the parameters.

Edit: I tried the binding, doesn't work either. The raw query still wraps POINT in quotes.

It would be ideal to have POINT set in a seeder using an array. So you can do something like this:

$statement = $db->prepare($query);
$point = sprintf("POINT(%F %F)", 2.3, 5.7);
$statement->bindParam(':point', $point, PDO::PARAM_STR);

This will require a few changes in the library though and some checking to see if the data type is a POINT.

[SignpostMarv]

The POINT is being inserted as a string.

Doesn't look like a bug, use GeomFromText

GeomFromText('Point(0, 0)')

[JamesTheHacker]

@SignpostMarv I've tried this:

$data = [
    [
         'name' => 'birmingham',
         'geo_coords' => "GeomFromText('Point(0, 0)')"
    ]
];

Doesn't work. It will still wrap it in ". If you're referring to public function insert(Table $table, $row) ... that's the code within Phinx.

[SignpostMarv]

I'm thinking more calling prepare/query directly.

[JamesTheHacker]

@SignpostMarv do you have an example?

[SignpostMarv]

I've just taken a quick peek around the source, it looks like prepare isn't exposed on the adapter interface, so you'd need to do something like this:

$conn = $this->getAdapter()->getConnection();
$sth = $conn->prepare('INSERT INTO `table` (`pointField`) VALUES(GeomFromText(CONCAT("Point(", ?, " ", ?, ")"))');
$sth->execute([0, 0]);

p.s. getConnection() isn't on the interface either, but it is a public method on the PDO Adapter.

[SignpostMarv]

So I guess I'll retract my "not a bug" statement and replace it with "INSERT method doesn't work with sql methods".

[JamesTheHacker]

@SignpostMarv That's the problem there's nothing I can do to get this to work. I have a project that depends on Phinx and we're stuck. I wouldn't mind if there was an easy solution to fixing this and contributing back to Phinx but it will require some work.

I guess that'll be tonights task for me because there's no alternative.

[SignpostMarv]

@JamesTheHacker Does the manual query construction method not behave as expected?

[JamesTheHacker]

@SignpostMarv Sorry I just reread your response. I misinterpreted it. So within the seeder class that extends AbstractSeed grab the connection and manually construct the query to insert the POINT data?

[SignpostMarv]

@JamesTheHacker Pretty much.

[JamesTheHacker]

@SignpostMarv That pretty much solves my problem actually. I've sat here the entire day figuring out how to solve this this.

[JamesTheHacker]

@SignpostMarv solved it like this (incase anyone is interested). Many thanks for your help:

<?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();

        foreach($data as $key => $val)
        {
            $sth = $conn->prepare('INSERT INTO city (`name`, `geo_coords`) VALUES (?, POINT(?, ?))');
            $sth->execute([
                $val['name'],
                $val['geo_coords'][0],
                $val['geo_coords'][1]]
            );
        }
    }
}

[SignpostMarv]

$sth should be outside the foreach loop.

[piotr-cz]

Thanks guys