Using spatial data in Doctrine 2

February 19, 2011 – 3:49 pm Tags: Doctrine

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.

Share this:

Subscribe to my RSS feed

32 Responses to “Using spatial data in Doctrine 2”
I’m also working with spacial data in Doctrine 2 (and MySQL) but haven’t used the MySQL spacial data types/functions.

Currently I’m using the Haversine formula within the query with a bounding rectangle index. It’s probably not as quick as the built in stuff but I found it to be more accurate.

I haven’t done anything fancy like extending Doctrine 2 though…

Do you find your method accurate enough?

By Tom Graham on Feb 19, 2011
Well we are mainly just using the distance calculations for stuff like getting lists of things within a certain radius of a point. It doesn’t require huge accuracy, so at least for what I can tell it has been quite sufficient.

I don’t actually know how the builtins calculate the distances, so it could be accurate or not.

By Jani Hartikainen on Feb 19, 2011
That’s cool – custom data types are something which I explore for mapping any kind of Value Objects via Doctrine 2, but I never got to write additional DQL functions.

By Giorgio Sironi on Feb 20, 2011
Great contribution. I will be using this for my project. I should learn a lot about extending D2 and how spatials work in the process. If you develop any other Doctrine functions like the distance, please post them.
Thanks.

By Chris on Mar 28, 2011
I am very interested in getting this to work but haven’t figured out where to save the files, etc.

Please contact me (yinyango dot org @ gmail dot com) if you know how to do this, as I’ve been unable to reach the author (Jani).

Thanks!

By Ryan on May 6, 2011
Hi Ryan, I’ve just sent a reply to your email

By Jani Hartikainen on May 6, 2011
Much thanks for sharing this! Excellent article.

I’d be curious what your implementation might be for a Doctrine2 function using MBRContains(), according to this SO post:

http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude

By Martin on Aug 13, 2011
Martin, it should be similar to the PointStr or Distance functions implementations. Simply change the ouput and parsing so it returns SQL which calls MBRContains instead and processes the arguments for it correctly.

By Jani Hartikainen on Aug 13, 2011
Hi Jani,
Thanks for a great post on this issue. I ran into a small problem with this. In short – What unit is the distance parameter in your sample queries?

I’ve played around with this code sample, but the distance calculations seem way off. For example, if I have an entity A and a point B that are geographically 100km apart and I use a query:

“SELECT c FROM App\Entity\City c WHERE DISTANCE(c.coordinate, POINT_STR(:point)) < 5"

That query will still give me the entity A within the result set. What unit exactly is that 5?

By Kimmo on Aug 23, 2011
Kimmo, I’m not entirely sure about that myself.

I was using this to find things nearby from a point, and I just played with a few values and used one that seemed nice.

By Jani Hartikainen on Aug 23, 2011
Hi people who’ve asked me about what I did:

It was a long time ago so I’ve forgotten some details, but I put these 4 files in a folder called CodeIgniter/application/libraries/Doctrine/CustomAddon:

Distance.php
Point.php
PointStr.php
PointType.php

I set the namespace of each as “CustomAddon”.

Then at the bottom of CodeIgniter/application/libraries/Doctrine.php I put:

require_once APPPATH . ‘libraries/Doctrine/CustomAddon/Distance.php’;
require_once APPPATH . ‘libraries/Doctrine/CustomAddon/Point.php’;
require_once APPPATH . ‘libraries/Doctrine/CustomAddon/PointStr.php’;
require_once APPPATH . ‘libraries/Doctrine/CustomAddon/PointType.php’;
Doctrine\DBAL\Types\Type::addType(‘point’, ‘CustomAddon\PointType’);
//Assuming $config is a Doctrine\ORM\Configuration object used to configure the EM
$config->addCustomNumericFunction(‘DISTANCE’, ‘CustomAddon\Distance’);
$config->addCustomNumericFunction(‘POINT_STR’, ‘CustomAddon\PointStr’);

// Create EntityManager
$this->em = EntityManager::create($connectionOptions, $config);

Then usage looks something like this:
$point = new \CustomAddon\Point($geo['lat'], $geo['lon']);

By Ryan on Sep 9, 2011
Hello, I have just discovered your tutorial and it seems very interesting, but I have a doubt.
How can I select the spatial information in a text format?
For example: I want to do a select, “SELECT e.somePoint FROM ExampleEntity e” and be able to get latitude and longitude from the queryresult.
Is that possible?
Thank you very much!

By Daniel on Sep 19, 2011
Daniel, Doctrine should hydrate the result as a Point object, so you can just query the methods from it.

By Jani Hartikainen on Sep 20, 2011
Yes, that’s correct if you use an object hydration mode, but if you use an array hydration mode, it’s returned as a string, which has to be decoded with : $data = unpack(‘x/x/x/x/corder/Ltype/dlat/dlon’, $string); and that’s very unpractical.
Another question, do you know in which units is the result of the distance function?
Thank you very much for your answer!

By Daniel on Sep 20, 2011
Daniel, you could probably implement two custom functions: LAT and LON. I believe for example MySQL produces the binary blob if you select a POINT, so if you wanted to use array hydration, you’d need to use the MySQL functions X() and Y(). By using functions which replicate the behavior of X and Y in DQL, you could get the array result you want.

As for the unit, I have absolutely no idea

By Jani Hartikainen on Sep 22, 2011
Hi Jani! Very nice code. Any chance of throwing an open source license on the github repo? I’d like to use it but w/o a license cannot.

By Mike Graf on Mar 5, 2012
Also some help setting up on MySQL:
```
$config-&gt;addCustomNumericFunction('DISTANCE', 'Wantlet\ORM\Distance'); ...
```
and
```
 Doctrine\DBAL\Types\Type::addType('point', 'Wantlet\ORM\PointType');
```
To my cli-config.php file for the doctrine executable.

I also had to add the type to my DBAL/Platforms/MySqlPlatform.php where all the type mappings are defined (big array near the end) ..
```
 'point' =&gt; 'point',
```
By Mike Graf on Mar 5, 2012
Hey Mike

Feel free to do whatever you want with it

By Jani Hartikainen on Mar 10, 2012
Hey jani,

Thanks for this article, it’s awesome and so far easy enough to use. I havent tested the actual calculations yet i’m just in the process of setting everything up.

I have come across an issue when trying:

doctrine.php orm:schema-tool:update –force

On the first instance of running this doctrine is fine, but on the second attempt at running this i got a crazy error saying:

Unknown database type point requested, Doctrine\DBAL\Platforms\MySqlPlatform may not support it.

If someone else gets this issue then all one needs to do is:

$em is the entity manager

$platform = $em->getConnection()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping(‘point’, ‘[the type you created]‘);

Here you simply replace [the type you created] with the new type. For me that was Location. So for me the command was:

$em->getConnection()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping(‘point’, ‘location’);

This solved the issue and doesnt seem to have any adverse effects.

HTH

By Andrew Davey on Mar 29, 2012
For information:

http://wildlyinaccurate.com/doctrine-2-resolving-unknown-database-type-enum-requested/

By Andrew Davey on Mar 29, 2012
Hello,

great tutorial, it seems to work perfect !
I just have a minor issue :
when I do a

php app/console cache:clear –env=dev –no-debug

I get this warning :

[Doctrine\DBAL\DBALException]
Type point already exists.

Is it normal ?

thanks !

By Tii on Mar 30, 2012
Tii, I’m afraid I’m not familiar with the command you’re talking about

By Jani Hartikainen on Mar 31, 2012
@Tii

It looks like you’ve registered the type point twice. Only use the method I suggest XOR the method that Andrew suggested. If you do both you’ll definitely be doubly defining the POINT type.

By Mike Graf on Mar 31, 2012
thx for this useful post post. has anyone experience with the performance using doctrine? until now we never used doctrine in our projects so far..

marco

By Marco on Apr 20, 2012
Thx for the answer Mike,

The command is symfony2′s clear cache command and I fixed the issue by adding –no-warmup to it, I just wonder if it’s really loading it twice or if it’s a symfony bug ….

By Tii on Apr 20, 2012
@Marco it’s quite fine. You always have the option of using SQL if you need more perf on some operations. For example, ZF is much heavier than Doctrine perf wise in my experience.

By Jani Hartikainen on Apr 21, 2012
I’m curious about the directory structure you have used, as well file location, when implementing this in Symfony.

By Vinny on Apr 25, 2012
@Vinny, the structure/location doesn’t really matter as long as your autoloader can figure it out, or you require the files beforehand. I haven’t used this code with Symfony 2 so can’t really help with that more.

By Jani Hartikainen on Apr 26, 2012

The best solution is to use Doctrine2 Native Query. It gives the opportunity to utalize all the MySQL Functions. For instance:

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('myBundle:ipdata', 'ipd');
 
$rsm->addFieldResult('ipd', 'ipd_id', 'id');
$rsm->addFieldResult('ipd', 'country', 'country');
$rsm->addFieldResult('ipd', 'city', 'city');
$rsm->addFieldResult('ipd', 'isp', 'isp');
$rsm->addFieldResult('ipd', 'org', 'org');
 
$rsm->addScalarResult('id', 'ipd_id'); 
 
 
$query = "SELECT * FROM ipdata_table WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('".$record->getIpAddress()."'), 0)))";
$ip_source = $ip_resource->createNativeQuery($query,$rsm)->getArrayResult();

I hope that helps!

By Ilyas on May 29, 2012

Thanks for the example

Native queries can be quick to do, but if you need more than one or two query in your application, I think it’s more convenient to write DQL functions. Then you can just keep using DQL even if you need spatial data.

By Jani Hartikainen on May 29, 2012
Hello Jani,
Thanks for the nice examples, it helped me to get started with spacial integration with doctrine.

One thing I feel is a bick “hacky” though is packing/unpacking values. Maybe WKT format is more reliable/understandable.

Here I found an example of how you can make it using WKT http://docs.doctrine-project.org/en/2.1/cookbook/advanced-field-value-conversion-using-custom-mapping-types.html

Maybe it would be helpful for someone like it was for me.

By Pavel Dubinin on Sep 27, 2012

1 Trackback(s)
Feb 19, 2011: Tweets that mention Using spatial data in Doctrine 2 | CodeUtopia - The blog of Jani Hartikainen -- Topsy.com

CodeUtopia

Using spatial data in Doctrine 2

Basic MySQL spatial data functionality

Extending Doctrine 2

Adding the point database type

Using the new POINT type

Adding functions for working with points

Enabling the new DQL functions in Doctrine

Conclusion

32 Responses to “Using spatial data in Doctrine 2”

1 Trackback(s)

Post a Comment

Search the blog

Recent posts

Categories

Recent Comments

Archives

About me

Related posts

CodeUtopia

Using spatial data in Doctrine 2

Basic MySQL spatial data functionality

Extending Doctrine 2

Adding the point database type

Using the new POINT type

Adding functions for working with points

Enabling the new DQL functions in Doctrine

Conclusion

32 Responses to “Using spatial data in Doctrine 2”

1 Trackback(s)

Post a Comment

Search the blog

Recent posts

Categories

Recent Comments

Topics

Archives

About me

Related posts