I'm trying to realize an Raster-functionality in PostGis with a custom mapping type. To add a Raster to the Database,
I am creating a table with a column of type raster. To insert Raster-Data I'm inserting a new empty Raster with st_makeemptyraster, add a new Band with st_addband and set the values with st_setvalues.
So, this could be done in PLAIN SQL in that way:
CREATE TABLE IF NOT EXISTS rasters (id serial, rast raster);
INSERT INTO rasters(id,rast)
VALUES(
3,
st_setvalues(
st_addband(
ST_MakeEmptyRaster( 2, 2, 0.0005, 0.0005, 1, 1, 0, 0, 4326),
1,
'32BF',
1,
0
),
1,
1,
1,
ARRAY[[9, 9], [9, 9]]::double precision[][]
)
);
Now I have tried to add a custom mapping type for raster in doctrine.
I have added an entity Raster:
<?php
/**
* More Documentation here:
*
* http://postgis.net/docs/manual-dev/RT_ST_MakeEmptyRaster.html
*
* raster ST_MakeEmptyRaster(raster rast);
* raster ST_MakeEmptyRaster(integer width, integer height, float8 upperleftx, float8 upperlefty, float8 scalex, float8 scaley, float8 skewx, float8 skewy, integer srid=unknown);
* raster ST_MakeEmptyRaster(integer width, integer height, float8 upperleftx, float8 upperlefty, float8 pixelsize);
*
*
* http://postgis.net/docs/manual-2.2/RT_ST_AddBand.html
*
*
*/
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use JMS\Serializer\Annotation as JMS;
use AppBundle\Model\Raster as RasterModel;
/**
* Raster
*
* @ORM\Entity(repositoryClass="AppBundle\Entity\RasterRepository")
* @ORM\Table(name="rasters")
*/
class Raster
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var RasterModel $raster
*
* @ORM\Column(name="rast", type="raster", nullable=true)
*/
private $raster;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set raster
*
* @param RasterModel $raster
*
* @return Raster
*/
public function setRaster(RasterModel $raster)
{
$this->raster = $raster;
return $this;
}
/**
* Get raster
*
* @return Raster
*/
public function getRaster()
{
return $this->raster;
}
}
The php-Object of the Model:
<?php
namespace AppBundle\Model;
use Doctrine\Common\Collections\ArrayCollection;
/**
* Raster
*/
class Raster
{
/**
* @var Raster $raster
*/
private $raster;
/**
* @var integer
*/
private $width;
/**
* @var integer
*/
private $height;
/**
* @var float
*/
private $upperLeftX;
/**
* @var float
*/
private $upperLeftY;
/**
* @var float
*/
private $scaleX;
/**
* @var float
*/
private $scaleY;
/**
* @var float
*/
private $skewX;
/**
* @var float
*/
private $skewY;
/**
* @var float
*/
private $pixelSize;
/**
* @var integer
*/
private $srid;
/**
* @var ArrayCollection RasterBand
*/
private $bands;
/**
* Raster constructor.
*/
public function __construct()
{
$this->bands = new ArrayCollection();
}
/**
* @return Raster
*/
public function getRaster()
{
return $this->raster;
}
/**
* @param Raster $raster
* @return Raster
*/
public function setRaster($raster)
{
$this->raster = $raster;
return $this;
}
/**
* @return int
*/
public function getWidth()
{
return $this->width;
}
/**
* @param int $width
* @return Raster
*/
public function setWidth($width)
{
$this->width = $width;
return $this;
}
/**
* @return int
*/
public function getHeight()
{
return $this->height;
}
/**
* @param int $height
* @return Raster
*/
public function setHeight($height)
{
$this->height = $height;
return $this;
}
/**
* @return float
*/
public function getUpperLeftX()
{
return $this->upperLeftX;
}
/**
* @param float $upperLeftX
* @return Raster
*/
public function setUpperLeftX($upperLeftX)
{
$this->upperLeftX = $upperLeftX;
return $this;
}
/**
* @return float
*/
public function getUpperLeftY()
{
return $this->upperLeftY;
}
/**
* @param float $upperLeftY
* @return Raster
*/
public function setUpperLeftY($upperLeftY)
{
$this->upperLeftY = $upperLeftY;
return $this;
}
/**
* @return float
*/
public function getScaleX()
{
return $this->scaleX;
}
/**
* @param float $scaleX
* @return Raster
*/
public function setScaleX($scaleX)
{
$this->scaleX = $scaleX;
return $this;
}
/**
* @return float
*/
public function getScaleY()
{
return $this->scaleY;
}
/**
* @param float $scaleY
* @return Raster
*/
public function setScaleY($scaleY)
{
$this->scaleY = $scaleY;
return $this;
}
/**
* @return float
*/
public function getSkewX()
{
return $this->skewX;
}
/**
* @param float $skewX
* @return Raster
*/
public function setSkewX($skewX)
{
$this->skewX = $skewX;
return $this;
}
/**
* @return float
*/
public function getSkewY()
{
return $this->skewY;
}
/**
* @param float $skewY
* @return Raster
*/
public function setSkewY($skewY)
{
$this->skewY = $skewY;
return $this;
}
/**
* @return float
*/
public function getPixelSize()
{
return $this->pixelSize;
}
/**
* @param float $pixelSize
* @return Raster
*/
public function setPixelSize($pixelSize)
{
$this->pixelSize = $pixelSize;
return $this;
}
/**
* @return int
*/
public function getSrid()
{
return $this->srid;
}
/**
* @param int $srid
* @return Raster
*/
public function setSrid($srid)
{
$this->srid = $srid;
return $this;
}
/**
* @return ArrayCollection
*/
public function getBands()
{
return $this->bands;
}
/**
* @param ArrayCollection $bands
* @return Raster
*/
public function setBands($bands)
{
$this->bands = $bands;
return $this;
}
/**
* @param RasterBand $band
* @return array|ArrayCollection
*/
public function addBand(RasterBand $band)
{
$this->bands[] = $band;
return $this->bands;
}
/**
* @param RasterBand $band
* @return $this
*/
public function removeBand(RasterBand $band)
{
$this->bands->removeElement($band);
return $this;
}
}
And the custom mapping type:
<?php
namespace AppBundle\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;
class RasterType extends Type
{
const RASTER = 'raster';
public function getName()
{
return self::RASTER;
}
public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
{
return 'raster';
}
public function convertToPHPValue($value, AbstractPlatform $platform)
{
/**
* ToDo: Finish implementing it
*/
return new \AppBundle\Model\Raster();
}
public function convertToDatabaseValue($value, AbstractPlatform $platform)
{
return sprintf('%d, %d, %f, %f, %f, %f, %f, %f, %d', 10, 10, 0.1, 0.1, 0.1, 0.1, 0, 0, 4269);
}
public function canRequireSQLConversion()
{
return true;
}
public function convertToPHPValueSQL($sqlExpr, $platform)
{
/**
* ToDo: Finish implementing it
*/
return sprintf('AsText(%s)', $sqlExpr);
}
public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
{
return sprintf('st_makeemptyraster(%s)', $sqlExpr);
}
}
In my first intent I'm trying to add a empty raster to the database. Instead of inserting or updating the raster, the following error appears:
2) AppBundle\Tests\Entity\RasterTest::testCreateEmptyRasterAndAddBand
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO rasters (id, rast) VALUES (?, ?)' with params [2, "st_makeemptyraster(10, 10, 0.100000, 0.100000, 0.100000, 0.100000, 0.000000, 0.000000, 4269)"]:
SQLSTATE[XX000]: Internal error: 7 ERROR: rt_raster_from_wkb: wkb size (46) < min size (61)
The same data as a plain SQL-Query works fine:
INSERT INTO rasters (id, rast) VALUES (14, st_makeemptyraster(10, 10, 0.100000, 0.100000, 0.100000, 0.100000, 0.000000, 0.000000, 4269));
INSERT 0 1
Does anybody has an idea how to get this fixed? Thank you
(I'm not able to comment with my reputation less than 50. So I just can write an answere. Some mod may change it to a comment instead?)
I'm not familar with Doctrine but in first place I have an assumption to a common problem.
Can you assure that passing values to any query isn't affected by a string conversion before the query will be built? Executing a query with arguments may convert them to string values with escaped characters to avoid security issues.
An example:
$yourDoctrineAdapter->executeInsert( 42, "storedProcedureCall( 'foo' )" );
may result in something like
INSERT INTO foo ( bar, baz ) VALUES ( '42', 'storedProcedureCall( \'foo\' )' );
My second assumption is about the internal error message and not about a wrong type.
rt_raster_from_wkb: wkb size (46) < min size (61).
I don't know what's the return value of st_makeemptyraster() but your call from Doctrine may still results in a string argument instead a stored function call. The value inserted in the column rast is type boxed and then the WKB size of the stringified "call" is less than the WKB size of the stored function return value of st_makeemptyraster().
I suggest to digg into something like 'how to execute stored function calls with doctrine'.