How to handle mySql POINT fields in Laravel

user_1273091 0
Viewed 10176

I am creating a table like this:

 /**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{

    Schema::create('places', function (Blueprint $table) {
        $table->engine = 'MyISAM';

        $table->increments('id');
        $table->text('description');

        $table->longText('address');
        $table->point('coordinates');
        $table->timestamps();
    });
}

I created a field directly to my database using:

INSERT INTO `places` (`id`, `description`, `address`, `coordinates`, `created_at`, `updated_at`)
VALUES
    (1, 'Plaza Condesa', 'Av. Juan Escutia 4, Hipodromo Condesa, Hipódromo, 06140 Cuauhtémoc, CDMX', X'000000000101000000965B5A0D89693340CC1B711214CB58C0', NULL, NULL);

Then I retrieve it in Laravel using:

MyModel::first()

All the values seem to be correctly except the coordinates field where I get something like this:

�[Z
�i3@�q�X�

How can I get the POINT field using Laravel?

laravel-5.5 laravel geospatial mysql
1 Answers

What you have at the moment is only the data in your database. Schema::create just created the Table in your database and than you did a pure SQL insert statement.

You did not store a String or an Integer, you used the Point Data type
https://dev.mysql.com/doc/refman/5.7/en/gis-class-point.html

Next you used Laravel Eloquent to get this data, but from the Eloquent point of view you got some Binary data, and if you echo it out, it looks like what you posted.

What you need is some logic in your Model Class that translates the Binary to the Format that you want.

This is an adapted example, to your situation, form the the following Post that loads the result AsText from the DB: Laravel model with POINT/POLYGON etc. using DB::raw expressions

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Places extends Model
{
    protected $geometry = ['coordinates'];

    /**
     * Select geometrical attributes as text from database.
     *
     * @var bool
     */
    protected $geometryAsText = true;

    /**
     * Get a new query builder for the model's table.
     * Manipulate in case we need to convert geometrical fields to text.
     *
     * @param  bool $excludeDeleted
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function newQuery($excludeDeleted = true)
    {
        if (!empty($this->geometry) && $this->geometryAsText === true)
        {
            $raw = '';
            foreach ($this->geometry as $column)
            {
                $raw .= 'AsText(`' . $this->table . '`.`' . $column . '`) as `' . $column . '`, ';
            }
            $raw = substr($raw, 0, -2);

            return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw));
        }

        return parent::newQuery($excludeDeleted);
    }
}

Now you can do e.g. echo Places::first()->coordinates and the result will be something like POINT(19.4122475 -99.1731001).

Depending on what you tying to do you may also have a look at Eloquent Events. https://laravel.com/docs/5.5/eloquent#events Here you can hook into more precise to change things as you need them.

user_54279500
Related