Using spatial data in Doctrine 2

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:

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

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.

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!

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…

<?phpnamespace 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;publicfunction getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker){return'GeomFromText('.$this->arg->dispatch($sqlWalker).')';}publicfunction 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);}}

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?

publicfunction __toString(){//Output from this is used with POINT_STR in DQL so must be in specific formatreturnsprintf('POINT(%f %f)',$this->latitude,$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);
}

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:

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');

//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.