Reading and writing spatial/geometry fields in CakePHP 5.1.0+

Need Help
1

(This is a follow-up to Read data from spatial MySQL field (Point, Polygon ...). Starting a new thread because that one focuses on reading, and my question is both on reading and writing.)

What’s the correct way to read from and write to the Geospatial Types, newly supported in 5.1.0?

What I have

I have a database column that CakePHP recognises as a POINT, so a good start:

debug($myTable->getSchema()->getColumn('position'))

[
  'type' => 'point',
  'length' => null,
  'null' => true,
  'default' => null,
  'comment' => '',
  'precision' => null,
  'srid' => null,
]

The doc (Database Basics - 5.x) says:

Currently they can be defined in migrations, read in schema reflection, and have values set as text.

Reading

Does read in schema reflection mean… simply reading the values? Because if I do debug($entity->position) on a value I previously saved via MySQL Workbench, I get some binary gibberish. But if I then unpack it (as per How to unpack MySQL Multipoint geometry data in PHP? - Stack Overflow), I do get a valid array:

$position = unpack('x/x/x/x/corder/Ltype/dx/dy', $entity->position);
debug($position); // ['order' => (int) 1, 'type' => (int) 1, 'x' => (float) 12.34, 'y' => (float) -56.78,]

Is this how I am supposed to use it? Or should the framework unpack it automatically?

Writing

Can’t get writing the POINT as text to work. Tried:

  • Passing 'POINT(10, 15)' with data into patchEntity():

    $data = $this->getRequest()->getData();
$data['position'] = 'POINT(10, 15)';
$entity = $myTable->patchEntity($entity, $data);
debug($entity->position); // null

  • Setting an entity property to 'POINT(10, 15)' directly:

    $entity->position = 'POINT(10, 15)';
$result = $myTable->save($entity);
debug($result);

    SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field
    UPDATE my_table SET position = 'POINT(10, 15)' , modified = '2025-02-27 00:00:14' WHERE id = 15

    I think it needs to be SET position = (ST_GeomFromText('POINT(10, 15)'))

Searched CakePHP Github, specifically test cases, for usage examples, but couldn’t find any.

Am I using it wrong?

References:

2

The point column type is mapped to the StringType class cakephp/src/Database/TypeFactory.php at a40a07e0705dad895ceb8d8df0e53a94476a1fc4 · cakephp/cakephp · GitHub

So I would say the read/write behavior you see is expected. I think a more customized database type class would be needed which does the unpacking when reading the values from the database and corresponding customization for writing.

3

Thank you for your answer.

Okay, so then I read with unpack, and write as:

$entity = $myTable->patchEntity($entity, $this->getRequest()->getData());
$entity->position = $myTable->query()->func()->ST_GeomFromText([
    "'POINT(-0 -20)'" => 'literal',
]);
$result = $myTable->save($entity);
4

Quick’n’dirty bakeoff/geospatial - Packagist