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

**URL:** <https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124>\
**Category:** Need Help\
**Created:** [February 21, 2017, 9:33am UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124 "2017-02-21T09:33:24Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![TeChn4K](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@TeChn4K](https://discourse.cakephp.org/u/TeChn4K)\
**Post date:** [February 21, 2017, 9:33am UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/1 "2017-02-21T09:33:24Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![TeChn4K](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@TeChn4K](https://discourse.cakephp.org/u/TeChn4K)\
**Post date:** [February 21, 2017, 1:57pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/2 "2017-02-21T13:57:19Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![grizmio](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/grizmio/32/1614_2.png) [@grizmio](https://discourse.cakephp.org/u/grizmio)\
**Post date:** [December 11, 2019, 11:40pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/3 "2019-12-11T23:40:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Hitman1O1](https://avatars.discourse-cdn.com/v4/letter/h/3be4f8/32.png) [@Hitman1O1](https://discourse.cakephp.org/u/Hitman1O1)\
**Post date:** [January 4, 2020, 11:27am UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/4 "2020-01-04T11:27:55Z")

</div>

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](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.

---

<div class="post-metadata">

**Author:** ![grizmio](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/grizmio/32/1614_2.png) [@grizmio](https://discourse.cakephp.org/u/grizmio)\
**Post date:** [March 2, 2021, 7:55pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/5 "2021-03-02T19:55:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![G\_Lasso](https://avatars.discourse-cdn.com/v4/letter/g/aca169/32.png) [@G\_Lasso](https://discourse.cakephp.org/u/G_Lasso)\
**Post date:** [May 5, 2023, 4:22pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/6 "2023-05-05T16:22:04Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![alex](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/alex/32/2375_2.png) [@alex](https://discourse.cakephp.org/u/alex)\
**Post date:** [February 26, 2025, 7:25pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/7 "2025-02-26T19:25:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [February 27, 2025, 7:12am UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/8 "2025-02-27T07:12:36Z")

</div>

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

So you could “just” upgrade as well 😉

---

<div class="post-metadata">

**Author:** ![alex](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/alex/32/2375_2.png) [@alex](https://discourse.cakephp.org/u/alex)\
**Post date:** [February 27, 2025, 12:00pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/9 "2025-02-27T12:00:10Z")

</div>

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](https://stackoverflow.com/questions/52742813/how-to-unpack-mysql-multipoint-geometry-data-in-php)), I do get a valid array:

```auto
$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.

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [February 27, 2025, 12:19pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/10 "2025-02-27T12:19:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![alex](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/alex/32/2375_2.png) [@alex](https://discourse.cakephp.org/u/alex)\
**Post date:** [February 27, 2025, 12:53pm UTC](https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124/11 "2025-02-27T12:53:45Z")

</div>

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+](https://discourse.cakephp.org/t/reading-and-writing-spatial-geometry-fields-in-cakephp-5-1-0/12436)