Laravel model with POINT/POLYGON etc. using DB::raw expressions

Question

I have some models that use geospatial fields like POINT, POLYGON or MULTIPOLYGON. I would like to tell my model to process these attributes in a special way, for me to get the desired model attributes set.

Example: Every regular Model::find() or other Eloquent method should apply some custom code before storing or after retrieving a database value.

$area->surface is a POLYGON field in MySQL, but in my model class I would like to handle $area->surfare as an array of points.

On SELECT I would therefore like to 1) fetch the value using a raw expression to get a text representation of the value, and 2) go through some custom PHP code to convert the WKT string into an array.

On INSERT/UPDATE I would like to take the attribute value (an array) and 1) convert it into a WKT string, whereafter 2) it's written to the databse using a DB raw statement that stores the value.

I'd like to set this on a field-basis, not as special get/set functions for each field, and not in the controllers - because I have many geosptial fields.

Is there a way to achieve this in Laravel?

(A more abstract version of the same question, is how I can create code that manipulates attribute values for the actual SQL queries, rather than just some value-based manipulation via mutators & accessors)

UPDATE: Looking deeper into the Laravel Doc and API, I found that maybe the Eloquent::newQuery() method is what I need to manipulate? Would that be used for any query regardless if SELECT, INSERT or UPDATE?

Pruritus

Answer 1

We have now solved this generically for all models by extending our base model with the following functionaly:

• We define an array of attributes that hold geometric data.
• We decide on a per-model-basis if we want this to be auto-loaded as text.
• We change the default query builder to select the geometry attributes as text from the database.

Here is an excerpt from the base model we now use:

/**
 * The attributes that hold geometrical data.
 *
 * @var array
 */
protected $geometry = array();

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

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

Pruritus

I just tried this and it worked almost as expected when selecting the column (returning POLYGON((98.07 30.12,-98.07 30.53, etc))). Would you mind sharing how you handled a PHP array as input? – Borneol

Answer 2

You can use very cool package for points git package

Instalation

composer require matanyadaev/laravel-eloquent-spatial

Examples:

Creating migration

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePlacesTable extends Migration
{
    public function up(): void
    {
        Schema::create('places', static function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->point('location')->nullable();
            $table->polygon('area')->nullable();
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('places');
    }
}

Run migrations

php artisan migrate

Creating place model

Fill the $fillable and $casts arrays and use the HasSpatial trait in your new model:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use MatanYadaev\EloquentSpatial\SpatialBuilder;
use MatanYadaev\EloquentSpatial\Objects\Point;
use MatanYadaev\EloquentSpatial\Objects\Polygon;
use MatanYadaev\EloquentSpatial\Traits\HasSpatial;

/**
 * @property Point $location
 * @property Polygon $area
 * @method static SpatialBuilder query()
 */
class Place extends Model
{
    use HasSpatial;

    protected $fillable = [
        'name',
        'location',
        'area',
    ];

    protected $casts = [
        'location' => Point::class,
        'area' => Polygon::class,
    ];
}

Creating objects

use App\Models\Place;
use MatanYadaev\EloquentSpatial\Objects\Polygon;
use MatanYadaev\EloquentSpatial\Objects\LineString;
use MatanYadaev\EloquentSpatial\Objects\Point;
use MatanYadaev\EloquentSpatial\Enums\Srid;

$londonEye = Place::create([
    'name' => 'London Eye',
    'location' => new Point(51.5032973, -0.1217424),
]);

$whiteHouse = Place::create([
    'name' => 'White House',
    'location' => new Point(38.8976763, -77.0365298, Srid::WGS84->value), // with SRID
]);

$vaticanCity = Place::create([
    'name' => 'Vatican City',
    'area' => new Polygon([
        new LineString([
              new Point(12.455363273620605, 41.90746728266806),
              new Point(12.450309991836548, 41.906636872349075),
              new Point(12.445632219314575, 41.90197359839437),
              new Point(12.447413206100464, 41.90027269624499),
              new Point(12.457906007766724, 41.90000118654431),
              new Point(12.458517551422117, 41.90281205461268),
              new Point(12.457584142684937, 41.903107507989986),
              new Point(12.457734346389769, 41.905918239316286),
              new Point(12.45572805404663, 41.90637337450963),
              new Point(12.455363273620605, 41.90746728266806),
        ]),
    ]),
])

Also package has different usefull methods with examples

whereDistance
orderByDistance
whereDistanceSphere
orderByDistanceSphere
whereWithin
whereNotWithin
whereContains
whereNotContains
whereEquals

Laina

Answer 3

If mutators and accessor does not fit your needs, you can manipulate those attributes with Model Events.

Then you can execute code when some of the Eloquent events are fired: creating, created, updating, updated, saving, saved, deleting, deleted, restoring, restored.

Prehension

The way I understand observers, is that I get the model instance passed, which is not really what I'm looking for. I would like a way to inject into the SQL being executed on SELECT, INSERT & UPDATE to manipulate the way a certain field is being read or written. E.g. for a polygon I'd like any SELECT to always use DB::raw('AsText(polygon) as polygon') in any SELECT operation fired via the Model. – Pruritus

Answer 4

For all who use postgres database, laravel-postgis can be use for geometric data

Metope