phinx: Unable to seed data with POINT datatype

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

About this issue

Original URL
State: closed
Created 8 years ago
Reactions: 1
Comments: 15 (1 by maintainers)

Most upvoted comments

$sth should be outside the foreach loop.

SignpostMarv on Dec 13, 2016

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

JamesTheHacker on Dec 13, 2016

@JamesTheHacker Pretty much.

SignpostMarv on Dec 12, 2016

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 on Dec 12, 2016