Doctrine issue: Cannot get geometry object from data you send to the GEOMETRY field

Question

I followed this guide to add point type to Doctrine.

This is how I define coordinates field in an Entity:

/**
 * @ORM\Column(type="point", nullable=true)
 */
private $coordinates;

And this is how I'm trying to save an Entity:

$obj = new GeoPoint();
$obj->setAddress('Test');
$obj->setCoordinates(new Point(40.7603807, -73.9766831));
$manager->persist($obj);
$manager->flush();

My configuration:

doctrine:
    dbal:
        types:
            point: Viny\PointType
        driver: 'pdo_mysql'
        server_version: '5.7'
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci
        url: '%env(resolve:DATABASE_URL)%'
        mapping_types:
            point: point
    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        naming_strategy: doctrine.orm.naming_strategy.underscore
        quote_strategy: backend_lib.orm.quote_strategy
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

Result DB column definition:

`coordinates` point DEFAULT NULL,

In result I get:

An exception occurred while executing 'INSERT INTO geo_point (address, coordinates) VALUES (?, ?)' with params ["Test", "POINT(-73.976683 40.760381)"]:

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field

Final query:

INSERT INTO geo_point (address, coordinates) VALUES ('Test', 'POINT(-73.976683 40.760381)');

Add a dump statement to PointType::convertToDatabaseValue and verify that something like POINT(40.7603807, -73.9766831) is being generated. This will show if the wiring is correct. Bit strange that new Point() is not throwing a warning for missing constructor arguments but off-topic. — Cerad
And I'm pretty sure you want to use types: point: App\...\PointType in your config file. Otherwise, I don't see how Doctrine will know to use your custom PointType. symfony.com/doc/current/reference/configuration/… — Cerad
I have updated error message with the INSERT statement. Also, the output of PointType::convertToDatabaseValue() is string(27) "POINT(-73.976683 40.760381)" (no comma between coordinates) — Roman Newaza

Cerad Cerad · Accepted Answer · 2019-05-23T13:02:54

Your posted doctrine configuration is wrong. It should be:

#doctrine.yaml
doctrine:
    dbal:
        types: ### Use types instead of mapping_types
            point: App\...\GeoPointType

You can tell the mapping is a problem because of your generated sql:

INSERT INTO geo_point (address, coordinates) VALUES ('Test', 'POINT(-73.976683 40.760381)');

mysql itself does not understand POINT. It needs to be wrapped in PointFromText. This wrapping is accomplished via:

    // class GeoPointType
    public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
    {
        return sprintf('PointFromText(%s)', $sqlExpr);
    }

And it is clearly not being called.

Here is a working example.

Doctrine issue: Cannot get geometry object from data you send to the GEOMETRY field

1 Answers