We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Return spatial data from model without raw-sql

How can i fetch spatial data from resultset when i use Model::find() for exampe? MySql needs the functions astext(geometry), aswkt(geometry), aswkb(geometry) or asBinary(geometry) to give the geometry in a usable format. So my deeper question is: how can i "modify" the query used by phalcon in such a way that one of these mysql-functions will be used?

Here are my table definitions:

CREATE TABLE `places` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) DEFAULT NULL,
  `geometrie` geometry NOT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `streets` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `place_id` int(11) unsigned NOT NULL,
  `geometry` geometry NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The logic is:

a places can contain many streets. Many streets belong to places.

Here are my two models:

class Places extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasMany('id', 'streets', 'place_id');
    }
}

class Streets extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->belongsTo('place_id', 'Places', 'id');
    }
}

And now the simplified code:

$place = Places::findFirst(1);

foreach ($place->streets as $street) {
    echo $street->geometry;
}

it gives me something like this: ���(A�so�2VA

But what i need is something like that: POINT(11.29462491 51.48765751)

I know that there is the "afterFetch()"-Method i can use inside the model, but the "geometry-result" of the mysql -query is unusable. it is neither WKT (astext-function) nor WKB (aswkb or asbinary-function).

In raw sql i would use "select *, astext(geometry) from places where ..." But i don't want to use manual sql. I would be very happy if anyone ever had this problem and solved it.

Any suggestions?

I think that using raw sql (manual sql query) is the only way.



192
edited Aug '18

I've found a trick to solve this problem. Tested in Phalcon 3.3.

class Places extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasMany('id', 'streets', 'place_id',['params' => ['columns' => 'id, name, place-id, ST_AsText(geometry) as geometry']]);
    }
}

The columns param can alse be used in find and findFirst functions in model.