php - 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?

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