CODEWITHSUNDEEP
laraveleloquentoverridingmysql-spatial
Erich
Erich

Reputation: 2616

Overriding Laravel Model View

I'm attempting to handle map marker locations as natively as possible using MySQL spatial data types. I don't want to resort to additional columns for lat and lng, as eventually, I want to be able to encode lines and polygons (and other geometries) as well. The table has a point column type:

Schema::create('maps', function (Blueprint $table) {
    $table->increments('id');
    $table->point('map_center')->nullable();
    $table->timestamps();
});

On the view side, Eloquent is a little dumber than I imagined it would be, given the built-in support for creating these spatial types in the database. If I get() a model, map_center displays the raw encoded geometry. 

>>> $map=Map::first()
=> App\Map {#2935
     id: 1,
     map_center: b"\0\0\0\0\x01\x01\0\0\0ºõš\x1E\x14\x7FRÀb0\x7F…Ì_D@",
     created_at: null,
     updated_at: null,
   }

I wrote a center() method that returns an object containing lat and long:

public function center()
{
    if ($this->map_center) 
    {
        $result = DB::select('
            select ST_Y(map_center) as lat, ST_X(map_center) as lng 
            from maps 
            where id = :id
        ', 
        ['id' => $this->id]);
        $center['lat'] = $result[0]->lat;
        $center['lng'] = $result[0]->lng;
        return (object) $center;
    }
    else
    {
        dd($this);
    }
}

Output:

>>> $map->center()->lat
=> 40.748429

This is a decent workaround, but slightly ugly. Instead, I want Eloquent to extract that so that the model returns a human readable coordinate, something like:

>>> $map=Map::first()
=> App\Map {#2935
     id: 1,
     map_center: {
       lat: 40.748429,   // or X: and Y:
       lng: -73.985603, 
     }
     created_at: null,
     updated_at: null,
   }

Given that it's a point type (with two components), is it possible to automatically retrieve the data using ST_AsText(p) or an object containing ST_X(p) and ST_Y(p)?

Is this possible?

Upvotes: 0

Views: 620

Answers (1)

Sakibul Alam
Sakibul Alam

Reputation: 1823

MySQL stores the data as WKB (Well Known Binary) format.

Take a look at here Conversion of MySQL binary GEOMETRY fields in PHP

You can use a Model Attribute. It should be something like this.

public function getMapCenterAttribute()
{
    $center = unpack('Lpadding/corder/Lgtype/dlatitude/dlongitude', $this->map_center);
    return $center;
}

You should also have something similar for setMapCenterAttribute doing it in reverse using pack function if required.

Upvotes: 1

Related Questions