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

Need Help
1

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

2

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 ?

3

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

4

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.

5

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.

6

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);
    }
7

Hey @grizmio, the link is dead. Do you have a new one?

8

Geospatial Datatypes have been added to the CakePHP Core in 5.1
https://book.cakephp.org/5/en/orm/database-basics.html#geospatial-types

So you could “just” upgrade as well :wink:

9

Thank you for the quick reply @KevinPfeifer!

I’m on 5.1.2 and I’m getting some binary gibberish when I debug() a value from a POINT column directly.

But if I unpack it (as per How to unpack MySQL Multipoint geometry data in PHP? - Stack Overflow), I do get a valid array:

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

Do I have to declare something on my table class/entity class to have Cake parse it as POINT for me? If it makes a difference, this column is new, it wasn’t there back when I baked my models.

10

Its not about the baked code but rather the cached schema.
If you changed your schema recently try a bin/cake cache clear_all

As far as I can see you don’t need to specify anything in your table. Cake should autodetect your type according to your column type.

11

This topic is asking specifically about reading, so I continued in a new topic Reading and writing spatial/geometry fields in CakePHP 5.1.0+