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

5

I wanted to access longitude/latitude efficiently and use spatial index, so I prepared

With this architecture, reading&writing longitude/latitude can be done with standard ORM operations. Please note that searching using spatial index still requires calling some SQL functions.

Here is an example table:

CREATE TABLE spatial_test (
    id int unsigned AUTO_INCREMENT PRIMARY KEY,
    longitude double NOT NULL,
    latitude double NOT NULL,
    location point GENERATED ALWAYS AS (ST_SRID(Point(longitude, latitude), 4326)) STORED NOT NULL SRID 4326
);
CREATE SPATIAL INDEX spatial_test_location ON spatial_test(location);
1 Like