Reading and writing geospatial in CakePHP 5.1+

in CakePHP

Limited support for SQL geospatial types was added in CakePHP 5.1.0. To quote the doc:

The geometry, point, linestring, and polygon types are also known as the “geospatial types”. CakePHP offers limited support for geospatial columns. Currently they can be defined in migrations, read in schema reflection, and have values set as text.

Creating a POINT column

Using point as an example, your database table MyTable needs to have a column position with type POINT. You will then have to have some additional processing for reading and writing while the built-in support is limited.

Here's how you doublecheck if CakePHP understands your column type correctly:

debug($myTable->getSchema()->getColumn('position'));
[
  'type' => 'point',
  'length' => null,
  'null' => true,
  'default' => null,
  'comment' => '',
  'precision' => null,
  'srid' => null,
]

If you just created the column, you may have to do bin/cake cache clear_all.

Reading POINT

As per https://stackoverflow.com/questions/52742813/how-to-unpack-mysql-multipoint-geometry-data-in-php: 

$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,
]

You may want to do this post-processing in a Virtual Field on your Entity. This way the geospatial data is already parsed whenever you need it.

Writing POINT

To save our POINT, we need to pass it through the database function ST_GeomFromText(). Because CakePHP doesn't come with all possible database functions built in, we're going to use a query expression with a custom function which is a magic wrapper.

// Take the $entity we have and patch it with request data
$entity = $myTable->patchEntity($entity, $this->getRequest()->getData());
// Specifically set the position column using query expression
$entity->position = $myTable->query()->func()->ST_GeomFromText([
    "'POINT(0.123 -20.00)'" => 'literal', // single quotes are important
]);
// Save the data
$result = $myTable->save($entity);
#cakephp #mysql