[DBAL-337] Column types are truncated when mapping PostGIS types Created: 04/Sep/12 Updated: 07/Sep/12 Resolved: 05/Sep/12 |
|
| Status: | Resolved |
| Project: | Doctrine DBAL |
| Component/s: | Platforms, Schema Managers |
| Affects Version/s: | 2.2.1 |
| Fix Version/s: | None |
| Security Level: | All |
| Type: | Bug | Priority: | Major |
| Reporter: | Jonathan Derrough | Assignee: | Benjamin Eberlei |
| Resolution: | Invalid | Votes: | 0 |
| Labels: | postgis | ||
| Environment: |
Linux Debian Stable, Symfony 2.0, Doctrine 2.2.1, PostgreSQL, PostGIS |
||
| Description |
|
When performing a doctrine schema update with Symfony2 ('php app/console doctrine:schema:update --force --verbose') using a custom PostGIS type mapping, column type descriptions are truncated. How to reproduce: 1) add a custom 'point' type to Doctrine's type mapping in a controller's boot method $em = $this->container->get('doctrine.orm.default_entity_manager'); $conn = $em->getConnection(); if (!Type::hasType('point')) { Type::addType('point', 'Demo\GeoBundle\Lib\Type\PointType'); $conn->getDatabasePlatform()->registerDoctrineTypeMapping('point', 'point'); } PointType.php <?php namespace Demo\GeoBundle\Lib\Type; use Demo\GeoBundle\Lib\Point; use Doctrine\DBAL\Types\Type; use Doctrine\DBAL\Platforms\AbstractPlatform; class PointType extends Type { const POINT = 'point'; /** * * @param array $fieldDeclaration * @param AbstractPlatform $platform * @return string */ public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform) { return 'geography(POINT,'.Point::$SRID.')'; } /** * * @param type $value * @param AbstractPlatform $platform * @return Point */ public function convertToPHPValue($value, AbstractPlatform $platform) { return Point::fromGeoJson($value); } public function getName() { return self::POINT; //return 'geography(POINT,'.Point::$SRID.')'; } public function convertToDatabaseValue($value, AbstractPlatform $platform) { return $value->toWKT(); } public function canRequireSQLConversion() { return true; } public function convertToPHPValueSQL($sqlExpr, $platform) { return 'ST_AsGeoJSON('.$sqlExpr.') '; } public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform) { return $sqlExpr; } } ?> Point.php
<?php
namespace Demo\GeoBundle\Lib;
use Demo\GeoBundle\Lib\GeoException;
class Point {
private $lat;
private $lon;
public static $SRID = '4326';
private function __construct($lon, $lat) {
$this->lat = $lat;
$this->lon = $lon;
}
public function setLongitude($lon) {
$this->lon = $lon;
}
public function getLongitude() {
return $this->lon;
}
public function setLatitude($lat) {
$this->lat = $lat;
}
public function getLatitude() {
return $this->lat;
}
public function toGeoJson(){
$array = array("type" => "Point", "coordinates" => array ($this->lon, $this->lat));
return \json_encode($array);
}
/**
*
* @return string
*/
public function toWKT() {
return 'SRID='.self::$SRID.';POINT('.$this->lon.' '.$this->lat.')';
}
/**
*
* @param string $geojson
* @return Point
*/
public static function fromGeoJson($geojson)
{
$a = json_decode($geojson);
//check if the geojson string is correct
if ($a == null or !isset($a->type) or !isset($a->coordinates)){
throw GeoException::badJsonString();
}
if ($a->type != "Point"){
throw GeoException::badGeoType();
} else {
$lon = $a->coordinates[0];
$lat = $a->coordinates[1];
return Point::fromLonLat($lon, $lat);
}
}
/**
*
* @return string
*/
public function __toString() {
return $this->lon . ' ' . $this->lat;
}
/**
*
* @return array
*/
public function toArray() {
return array($this->lon, $this->lat);
}
public static function fromLonLat($lon, $lat)
{
if (($lon > -180.0 && $lon < 180.0) && ($lat > -90.0 && $lat < 90.0))
{
return new Point($lon, $lat);
} else {
throw GeoException::badCoordinates($lon, $lat);
}
}
}
?>
GeoException.php <?php namespace Demo\GeoBundle\Lib; class GeoException extends \RuntimeException { public function __construct($message = null, \Exception $previous = null, $code = 0) { parent::__construct($message, $code, $previous); } public static function badJSonString() { return new GeoException("Bad JSon string"); } public static function badGeoType() { return new GeoException("Bad geo type"); } public static function badCoordinates($lon, $lat) { return new GeoException("Bad coordinates: " . $lon . " " . $lat); } } 2) with Symfony2, create a simple entity using the Point type: TestPoint.php <?php namespace Demo\TestBundle\Entity; use Doctrine\ORM\Mapping as ORM; /** * @ORM\Entity * @ORM\Table(name="test_points") */ class TestPoint { /** * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @ORM\Column(type="point") */ protected $coordinates; /** * Get id * * @return integer */ public function getId() { return $this->id; } /** * Set coordinates * * @param point $coordinates * @return Partner */ public function setCoordinates($coordinates) { $this->coordinates = $coordinates; return $this; } /** * Get coordinates * * @return point */ public function getCoordinates() { return $this->coordinates; } public function setLongitude($lon) { if ($this->coordinates == null) { $this->coordinates = Point::fromLonLat($lon, 0); } else { $this->coordinates->setLongitude($lon); } } public function getLongitude() { return $this->coordinates == null ? 0 : $this->coordinates->getLongitude(); } public function setLatitude($lat) { if ($this->coordinates == null) { $this->coordinates = Point::fromLonLat(0, $lat); } else { $this->coordinates->setLatitude($lat); } } public function getLatitude() { return $this->coordinates == null ? 0 : $this->coordinates->getLatitude(); } } 3) use the update script to create the table: php app/console doctrine:schema:update --force --verbose 4) run it again to update: php app/console doctrine:schema:update --force --verbose Errors should ensue: Errors [Doctrine\DBAL\DBALException] Exception trace: doctrine:schema:update [--complete] [--dump-sql] [--force] [--em[="..."]] What seems to be happening is that the returned type for the column holding the points is truncated. "Full" type is 'geography(Point,4326)', returned type from Doctrine is 'geography' (missing the specifics). |
| Comments |
| Comment by Jonathan Derrough [ 04/Sep/12 ] |
|
Further investigation showed that the truncation makes sense in a general context as types (ex: varchar) are simplifications of complete types (ex: character varying (255)). But it still presents a problem for PostGIS users. A quick workaround would be to change the mapping to: in a controller's boot method $em = $this->container->get('doctrine.orm.default_entity_manager'); $conn = $em->getConnection(); if (!Type::hasType('point')) { Type::addType('point', 'Demo\GeoBundle\Lib\Type\PointType'); $conn->getDatabasePlatform()->registerDoctrineTypeMapping('geography', 'point'); } It seems to work fine with several custom PostGIS types mapped but it wasn't fully tested. |
| Comment by Benjamin Eberlei [ 05/Sep/12 ] |
|
The way you do it its actually a workaround, but Doctrine Bundle has a way to configure the registerDoctrineMappingType() calls via configuration. Check with "php app/console config:dump-reference DoctrineBundle" |
| Comment by Jonathan Derrough [ 05/Sep/12 ] |
|
Yes, I am aware of the mapping configuration with the config file. I tested it again: config.yml
# Doctrine Configuration
doctrine:
dbal:
driver: %database_driver%
host: %database_host%
port: %database_port%
dbname: %database_name%
user: %database_user%
password: %database_password%
charset: UTF8
types:
point: Demo\GeoBundle\Lib\Type\PointType
linestring: Demo\GeoBundle\Lib\Type\LineStringType
orm:
auto_generate_proxy_classes: %kernel.debug%
auto_mapping: true
But the issue here remains, the same error is outputed: [Doctrine\DBAL\DBALException] As I detailed previously, the fetched type of a geography column is truncated. So my workaround to avoid the errors during update was to register my custom types with 'geography' as db-type. It doesn't seem to be the right way since only one doctrine-type can be map a db-type (so here, 'geography' would be associated with either 'point' or 'linestring'). |
| Comment by Benjamin Eberlei [ 05/Sep/12 ] |
|
Thats only half the picture, see "mapping_types" here http://symfony.com/doc/current/reference/configuration/doctrine.html |
| Comment by Jonathan Derrough [ 07/Sep/12 ] |
|
With the updated config.yml: config.yml
# Doctrine Configuration
doctrine:
dbal:
driver: %database_driver%
host: %database_host%
port: %database_port%
dbname: %database_name%
user: %database_user%
password: %database_password%
charset: UTF8
types:
point: Demo\GeoBundle\Lib\Type\PointType
linestring: Demo\GeoBundle\Lib\Type\LineStringType
mapping_types:
geography(point,4326): point
geography(linestring,4326): linestring
orm:
auto_generate_proxy_classes: %kernel.debug%
auto_mapping: true
errors are still showing. Using: config.yml
mapping_types:
geography: point
geography: linestring
errors are gone but I guess it is the same as:
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('geography', 'point');
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('geography', 'linestring');
and I don't see how it would be right since the 'linestring' mapping would replace the 'point' mapping. I'm affraid I don't get it. |