Using spatial data in Doctrine 2

Tags:

In this post I’ll introduce you to extending Doctrine 2 to provide ways to use custom data types and DQL functions.

We needed a simple way to store locations and calculate distances between points on maps and some other stuff for Wantlet. Since we are using MySQL, we decided to use MySQL’s Spatial Extensions for it since they seemed suitable.

We’re using Doctrine 2 as the ORM layer in the application, but it didn’t have any sort of data types or functionality for storing and operating on spatial data. Thankfully though, Doctrine 2 is very easy to extend to accomodate new data types and new DQL functions. It did require a small workaround for an issue not yet addressed in it, but in the end it worked out quite well.

Basic MySQL spatial data functionality

MySQL has a range of functionality for spatial data. We only needed to store points and calculate distances between them, so that’s what I’ll be focusing on here.

MySQL has a data type called POINT which stores an x, y sort of value. The function DISTANCE should work for calculating distances between points, but apparently it doesn’t for whatever reason, so I worked around the issue by using GLength and LineString.

Extending Doctrine 2

To be able to use these features in Doctrine 2, we need to add a new data type to Doctrine’s DBAL and some custom functions to the EntityManager’s configuration for extending DQL.

Adding the point database type

First, let’s check out the code for adding the POINT type:

First, we have the Point class, which is used to represent columns with the POINT type. It’s quite simple, just some getters and setters:

<?php
namespace Wantlet\ORM;
 
/**
 * Point object for spatial mapping
 */
class Point {
    private $latitude;
    private $longitude;
 
    public function __construct($latitude, $longitude) {
        $this->latitude = $latitude;
        $this->longitude = $longitude;
    }
 
    public function setLatitude($x) {
        $this->latitude = $x;
    }
 
    public function getLatitude() {
        return $this->latitude;
    }
 
    public function setLongitude($y) {
        $this->longitude = $y;
    }
 
    public function getLongitude() {
        return $this->longitude;
    }
 
    public function __toString() {
        //Output from this is used with POINT_STR in DQL so must be in specific format
        return sprintf('POINT(%f %f)', $this->latitude, $this->longitude);
    }
}

Point.php on GitHub.

The interesting part in this is the __toString function. I’ll explain it later in this post.

Next, we need a mapping type for Doctrine. Doctrine helpfully provides a base class we can extend to provide custom types:

<?php
namespace Wantlet\ORM;
 
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;
 
/**
 * Mapping type for spatial POINT objects
 */
class PointType extends Type {
    const POINT = 'point';
 
    /**
     * Gets the name of this type.
     *
     * @return string
     */
    public function getName() {
        return self::POINT;
    }
 
    /**
     * Gets the SQL declaration snippet for a field of this type.
     *
     * @param array $fieldDeclaration The field declaration.
     * @param AbstractPlatform $platform The currently used database platform.
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform) {
        return 'POINT';
    }
 
    public function convertToPHPValue($value, AbstractPlatform $platform) {
        //Null fields come in as empty strings
        if($value == '') {
            return null;
        }
 
        $data = unpack('x/x/x/x/corder/Ltype/dlat/dlon', $value);
        return new \Wantlet\ORM\Point($data['lat'], $data['lon']);
    }
 
    public function convertToDatabaseValue($value, AbstractPlatform $platform) {
        if (!$value) return;
 
        return pack('xxxxcLdd', '0', 1, $value->getLatitude(), $value->getLongitude());
    }
}

PointType.php on GitHub.

The main functionality of this class is to convert points to and from the format MySQL uses for them. We use pack and unpack because the format is in binary. From the user’s point of view, he can simply work with Point objects which simplifies it a lot.

You may notice the $platform variable in places, which is completely unused. This is something you can use to determine the type of database in question, if the same functionality needs to be done differently on a per-database basis. However, in this case, we only needed it for MySQL so everything is hardcoded in a format that works in it.

Using the new POINT type

First, the PointType must be added to DBAL in your configuration:

Doctrine\DBAL\Types\Type::addType('point', 'Wantlet\ORM\PointType');

After this, you can use the type with entities:

/**
 * @Entity
 */
class ExampleEntity {
    /**
     * @Column(type="point")
     */
    private $somePoint;
 
    public function setSomePoint(\Wantlet\ORM\Point $point) {
        $this->somePoint = $point;
    }
 
    public function getSomePoint() {
        return $this->somePoint;
    }
}

As you can see from the setter, the somePoint property will appear as a Point object – Doctrine 2 will automatically create objects of that type for it, and convert objects from that type back into database values – very very convenient!

A simple example:

$ent = new ExampleEntity();
$point = new \Wantlet\Entity\Point(10, 20.5);
 
$ent->setSomePoint($point);
 
$em->persist($ent);
$em->flush();

Adding functions for working with points

With the classes defined above, Doctrine 2 can now handle databases with points in them. It can also handle mapping columns in entities to points.

However, we can’t use points with DQL yet, and we also can’t do any typical queries either, such as getting all points within a specific distance of another point.

For this, we need to add some new DQL functions:

  • DISTANCE, for distances between two points
  • POINT_STR, for converting Point objects into format SQL can handle

Let’s first look at POINT_STR, which will allow us to use Points in DQL…

<?php
namespace Wantlet\ORM;
 
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
 
/**
 * POINT_STR function for querying using Point objects as parameters
 *
 * Usage: POINT_STR(:param) where param should be mapped to $point where $point is Wantlet\ORM\Point
 *        without any special typing provided (eg. so that it gets converted to string)
 */
class PointStr extends FunctionNode {
    private $arg;
 
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
        return 'GeomFromText(' . $this->arg->dispatch($sqlWalker) . ')';
    }
 
    public function parse(\Doctrine\ORM\Query\Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->arg = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
 
}

PointStr.php on GitHub.

Why do we need this function? This is because, as far as I know, SQL and Doctrine do not understand how to represent binary values in queries. As you may recall, the PointType class maps the point columns into binary.

We can work around this issue by using GeomFromText – it takes a string such as 'POINT(10 20)' and converts it into the respective spatial datatype.

Remember the __toString in the point type class?

public function __toString() {
    //Output from this is used with POINT_STR in DQL so must be in specific format
    return sprintf('POINT(%f %f)', $this->latitude, $this->longitude);
}

As you can see, we output exactly the kind of strings from here.

Since Doctrine 2’s default handling of parameters in queries is to simply convert them into strings, we can use POINT_STR in combination with __toString to make it easy to use points in DQL:

$query = $em->createQuery('SELECT e FROM ExampleEntity e WHERE e.somePoint = POINT_STR(:point)');
 
$point = new \Wantlet\Entity\Point(1, 2);
$query->setParameter('point', $point);
 
$query->execute();

Now the queries with points work pretty much like any other query – you just need to remember to use POINT_STR.

Now, let’s add the DISTANCE function:

<?php
namespace Wantlet\ORM;
 
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
 
/**
 * DQL function for calculating distances between two points
 *
 * Example: DISTANCE(foo.point, POINT_STR(:param))
 */
class Distance extends FunctionNode {
    private $firstArg;
    private $secondArg;
 
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
        //Need to do this hacky linestring length thing because
        //despite what MySQL manual claims, DISTANCE isn't actually implemented...
        return 'GLength(LineString(' .
               $this->firstArg->dispatch($sqlWalker) .
               ', ' .
               $this->secondArg->dispatch($sqlWalker) .
           '))';
    }
 
    public function parse(\Doctrine\ORM\Query\Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->firstArg = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->secondArg = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Distance.php on GitHub.

This class allows us to use a new DQL function called DISTANCE to calculate distances between points. It’s converted into SQL as GLength(LineString(arg1, arg2)) because it achieves the same result as the SQL DISTANCE function, which for some reason doesn’t seem to function in MySQL.

Using this function is simple, for example to get all entities within a certain distance of another point:
SELECT e FROM ExampleEntity e WHERE DISTANCE(e.somePoint, POINT_STR(:point)) < 5

Enabling the new DQL functions in Doctrine

To use the new DQL functions, we need to add them when configuring the EntityManager:

//Assuming $config is a Doctrine\ORM\Configuration object used to configure the EM
$config->addCustomNumericFunction('DISTANCE', 'Wantlet\ORM\Distance');
$config->addCustomNumericFunction('POINT_STR', 'Wantlet\ORM\PointStr');

Conclusion

As you can see, Doctrine 2 is quite easy to extend. This is another reason why it's definitely the best ORM tool for PHP in my opinion.

All the classes shown in this post are available on my GitHub repo doctrine2-spatial.