If you are storing coordinates of a location in your MySQL database, you may know that is quite convenient to store them in Point type columns in your tables. This will make pretty efficient future queries that you may need to run when searching nearest locations and so on. By default, if you use the Symfony CRUD to create forms and the user tries to submit it, there will be an error when trying to convert the coordinates from a string to a Point in the database.
In this article, I will explain to you how to easily store and manipulate Point type fields in your database using the Doctrine ORM in Symfony 5.
1. Install Doctrine 2 Spatial Extension
In order to work with the Point datatype in Doctrine, you need the Doctrine 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-spatialFor more information about this library, please visit the official repository at Github here. After installing the extension, be sure to enable the Point type in your doctrine.yaml configuration file:
# project/config/packages/doctrine.yaml
doctrine:
dbal:
url: '%env(resolve:DATABASE_URL)%'
types:
point: CrEOF\Spatial\DBAL\Types\Geometry\PointType2. Creating Entity
Either if you are creating the entity manually or you decided to use the doctrine:mapping:import command from an existing database, the entity will look like this assuming that you have at least 1 field of type point in your database, in our case, the column name is coordinates:
<?php
namespace App\Entity;
use Doctrine\ORM\Mapping as ORM;
use CrEOF\Spatial\PHP\Types\Geometry\Point;
/**
* ExampleEntity
*
* @ORM\Table(name="example", indexes={@ORM\Index(name="coordinates", columns={"coordinates"})})
* @ORM\Entity
*/
class ExampleEntity
{
/**
* @var point
*
* @ORM\Column(name="coordinates", type="point", nullable=true)
*/
private $coordinates;
/**
* Get the coordinates property.
*
* @return Point
*/
public function getCoordinates()
{
return $this->coordinates;
}
/**
* Set the coordinates property.
*
* @param Point $coordinates
* @return self
*/
public function setCoordinates(Point $coordinates): self
{
$this->coordinates = $coordinates;
return $this;
}
}Note that it needs to expect a Point type class in the setter method (from the spatial extension) and the getter will automatically return a Point type object.
3. Persisting data
I decided to create the crud automatically using the Symfony make utility:
php bin/console make:crudYou will automatically obtain the Route, Form Type, and Controller to manipulate the data of the table in your application. So if you access the route, usually everything will work as usual:
As you can see, the coordinates field is by default a text field. In this field you should be able to insert the coordinates providing a string with the longitude and latitude (X and Y) respectively, separating it with a blank space e.g -76.07 4.66. The problem right now, is if you try to store an entity without modifying the default logic, you will always get the following exception (Geometry column values must implement GeometryInterface):
This happens because you're basically persisting plain text (-76.07 4.66) into a field that expects a Geometry object (in this case, the Point Data Type). To solve this, you need to be sure that the correct data type is persisted into the database. There are multiple ways to do this, the easiest one is to simply pre-process the data of the coordinates field, creating a CallbackTransformer in the FormType of your entity like this:
<?php
namespace App\Form;
use App\Entity\Example;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;
// Import Required Classes
use Symfony\Component\Form\CallbackTransformer;
use CrEOF\Spatial\PHP\Types\Geometry\Point;
class ExampleType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
// 1. Create Form fields
$builder
->add('name')
->add('coordinates')
;
// 2. Create the model transformer for the coordinates field, converting
// the Point to a string and viceversa
$builder->get("coordinates")->addModelTransformer(new CallbackTransformer(
// Transform the Point to a string
function (?Point $point) {
if(is_null($point)) return "0 0";
// e.g "-74.07867091 4.66455174"
return "{$point->getX()} {$point->getY()}";
},
// Transform the string from the form back to a Point type
function (string $coordinates) {
// e,g "-74.07867091 4.66455174"
// lng x $coordinates[0] -74.07867091
// lat y $coordinates[1] 4.66455174
$coordinates = explode(" ", $coordinates);
return new Point($coordinates[0], $coordinates[1], null);
}
));
}
public function configureOptions(OptionsResolver $resolver)
{
$resolver->setDefaults([
'data_class' => Example::class,
]);
}
}
The callback transformer will convert the coordinates as string to a Point object and vice-versa. On your views, the coordinates field will be as well a Point object, where you can obtain every coordinate separately (when printed coordinates as a string, it will contain both coordinates):
{# Print longitude#}
{{ entity.coordinates.x }}
{# Print latitude #}
{{ entity.coordinates.y }}If you ever need to store an entity that contains a Point type field manually from a controller or service, you may persist it as well providing a Point instance as the value of the field:
<?php
$entity = new YourEntity();
$xOrLng = -74.07867091;
$yOrLat = 4.66455174;
$point = new \CrEOF\Spatial\PHP\Types\Geometry\Point($xOrLng, $yOrLat, null);
$entity->setCoordinates($point);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($entity);
$entityManager->flush();Happy coding ❤️!
