Read data from spatial MySQL field (Point, Polygon ...)

Hi !

I implemented a custom Database/Type to map a spatial field in my DB and I can’t read data through my Entity.

I followed the documentation to create a spatial Point field : https://book.cakephp.org/3.0/en/orm/database-basics.html#mapping-custom-datatypes-to-sql-expressions
I can write successfully to my DB, with no custom SQL request, just using Table and Entity objects.

As recommended by the doc, PointType class implements ExpressionTypeInterface and defines toExpression method to allow CakePHP ORM to construct a valid SQL query :

    public function toExpression($value)
    {
        if ($value instanceof Point) {
            return new QueryExpression('POINT(' . $value->lat() . ',' . $value->long() . ')');
        }
        if (is_array($value)) {
            return new QueryExpression('POINT(' . $value[0] . ',' . $value[1] . ')');
        }
    }

But now, my problem is to retrieve the data : toPHP method receive a Blob data from MySQL (spatial field)! This is not what I want. I would like to get data text format to parse it easily.

    public function toPHP($value, Driver $d)
    {
        // MySQL return blob, $value can't be parsed
        return Point::parse($value); // Point factory fail
    }

SELECT query should use the the ST_AsText function…

mysql> SET @p = 'POINT(1, 1)';
mysql> SELECT ST_AsText(ST_GeomFromText(@p));
+---------------------------------+
| ST_AsText(ST_GeomFromText(@p))  |
+---------------------------------+
| POINT(1, 1)                     |
+---------------------------------+

Anyone can help me ?
I can post my code on pastebin if needed.
Thanks

So, I found a temp workaround with the query builder :

->select(['id', 'label', 'point' => 'ST_AsText(point)'])

But this force me to write explicitly all fields’ name, for all requests …
Is there a way to override one field ? Or kind of a virtual field virtual_point => “ST_AsText(point)” and leave original point as hidden ?

Hi @TeChn4K did you find a solution?
It must be a way, redoing the query using Driver can’t be a solution, two queries per each record can’t be the right way

Rather late, but since this result is number 1 form search engines.

Complete solution: https://stackoverflow.com/questions/59578158/cakephp-3-use-sql-function-in-select-by-default-in-table-object/59590212#59590212

Summary: you can use the beforeFind() to change the field selection. By tucking it inside a behavior it is easily re-used.

Really late, but maybe someone find it useful, with cake 4 Im using:
https://github.com/grizmio/spatialdatatypes
its based in the previous link on stackoverflow, but only worse coded.

I’m using cake 4 with some legacy code since cake 2, the $query->getDefaultTypes(); for the column I am using is a String type, so that solution didn’t work…

The solution I found was this:

    public function beforeFind(\Cake\Event\EventInterface $event, \Cake\ORM\Query $query, \ArrayObject $options, $primary): void
    {
        // ->sql() transforms the query, setting the select fields when it is not setted
        $query->sql();

        $select = $query->clause('select');
        $fields = array_keys($select);

        foreach ($fields as $field) {
            if (in_array($field, [geometry fields])) {
                $select[$field] = 'ST_AsText(DeliveryFees.polygon)';
            }
        }
        $query->select($select, overwrite: true);
    }