How to solve Symfony 5 exception: Unknown database type point requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it

Recently in a new project that i'm currently working on, I've been working with a new datatype that I never used on production projects, the Point Data Type. In this case I'm using MySQL as the database engine, so inserting and modifying records that use the mentioned data type is quite simple:

INSERT INTO `tableName`
    (`id`, `name`, `coordinates`) 
        VALUES 
    (NULL, 'My First Record', ST_GeomFromText('POINT(40.71727401 -74.00898606)'));

However, I use the Doctrine ORM with Symfony 5, so there are a couple of things that need to be tweaked before making it work flawlessly. One of the possible problems that you may face in Symfony when working with this data type, is that it cannot be auto mapped by the doctrine:mapping:import command to do reverse engineering. This means, that if you have a database that exists already and you try to generate the entities automatically with the following command:

php bin/console doctrine:mapping:import "App\Entity" "annotation" --path=src/Entity

The specified exception will appear (unknown database type requested). Fortunately, Doctrine does support it, however you need to install the Doctrine 2 Spatial extension to make it work. In this short tutorial, I will explain you how to easily allow to do reverse-engineering to your database with spatial data types.

1. Install Doctrine 2 Spatial Extension

To solve this problem, you need to install the Doctrine 2 Spatial Extension. This Doctrine2 library offers multi-platform support for spatial types and functions. Currently, MySQL and PostgreSQL with PostGIS are supported. The following SQL/OpenGIS types have been implemented as PHP objects and accompanying Doctrine types:

• Geometry
• Point
• LineString
• Polygon
• MultiPoint
• MultiLineString
• MultiPolygon
• Geography

Similar to Geometry but SRID value is always used (SRID supported in PostGIS only), and accepts only valid "geographic" coordinates.

• Point
• LineString
• Polygon

There is support for both WKB/WKT and EWKB/EWKT return values. Currently only WKT/EWKT is used in statements. To install this extension, run the following command in your Symfony project:

composer require creof/doctrine2-spatial

For more information about this project, please visit the official repository at Github here.

2. Enable Point Type

After installing the extension library, you only need to enable the types that you need to add support for mapping. In this case, I only need the point datatype, so the following configuration will do the trick on the doctrine.yaml configuration file:

# project/config/packages/doctrine.yaml
doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        types:
            point: CrEOF\Spatial\DBAL\Types\Geometry\PointType

The same process will work for different types as Geometry, MultiPoint, MultiPolygon, Polygon, LineString, MultineString. Be sure to clear the cache of your symfony project and try mapping again. Your auto generated entities will now contain the columns with point type:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * ExampleEntity
 *
 * @ORM\Table(name="example", indexes={@ORM\Index(name="coordinates", columns={"coordinates"})})
 * @ORM\Entity
 */
class Example
{
    /**
     * @var point
     *
     * @ORM\Column(name="coordinates", type="point", nullable=true)
     */
    private $coordinates;

    public function getCoordinates()
    {
        return $this->coordenadas;
    }

    public function setCoordinates($coordinates): self
    {
        $this->coordinates = $coordinates;

        return $this;
    }
}

Happy coding ❤️!