Format for submitted values

[Synchro]

I've created a point type field in my DB on a location model, I can set its value explicitly by creating a Point instance as per the examples, but what I've been unable to figure out is what format it wants on the input side, with values coming from a client. If I submit something like location=Point(51.509865,-0.118092) in my HTTP params, I end up with an error when I pass it to $location = Location::create($request->all());:

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field (SQL: insert into `locations` (`name`, `location`) values (Coffee Shop 1, Point(51.509865, -0.118092)))

It looks like this needs something like Point::toWKT() applied to it, but I can't see how/where I'm meant to apply that automatically.

Do I need to handle some separate lat/long string values and convert them to a point on input? Perhaps with a mutator? Is there some input format that "just works"?

For the record (and which I've not seen documented anywhere), the JSON output format is:

"location":{"type":"Point","coordinates":[-0.118092,51.509865]}

[Synchro]

I tried using the output format on input, but that didn't work either.

[grimzy]

Can you show your Location model and how you're saving Coffee Shop 1?

Please note that we have Point::__construct($lat, $lng) while when inserting into MySQL using this package, the latitude and longitude should be inverted and the final query should look like:

insert into `locations` (`name`, `location`) values ('Coffee Shop 1', ST_GeomFromText('POINT(-0.118092 51.509865)'))

Also, note that the JSON output is formatted into GeoJSON and I unfortunately haven't had enough time to work on #23.

[Synchro]

It's all very minimal. My model:

<?php

namespace App;
use Illuminate\Database\Eloquent\Model;
use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;

class Location extends Model
{
    use SpatialTrait;

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

    protected $spatialFields = [
        'location',
    ];
}

My controller's creation method is simply:

    public function create(Request $request)
    {
        $location = Location::create($request->all());

        return response()->json($location);
    }

What's apparent from what you say is that there is no preset format for the point field type, so I'm assuming I need to expand the point value into lat long for delivery to the client, and map it back from lat & long on input. I was hoping there would be some standardised way to handle that, as there is for strings, numbers and arrays, for example to automatically map fieldname.long and fieldname.lat into the point type on the request, and vice versa on responses. The idea there is that a simple $location = Location::create($request->all()); would "just work" with no additional processing.

I assume pretty much everyone needs to do this kind of processing, so I was a bit surprised that there was no info on how to do it.

[grimzy]

You're absolutely right, there's currently no standardized way to handle input and it's a much needed feature.

The current implementation of the package only integrates with Eloquent/MySQL and allows to serialize spatial fields to GeoJSON. Regrettably, until #23 is completed there isn't a straightforward way to deserialize from GeoJSON.
In fact, one of the dependencies of this package is a GeoJSON parser (jmikola/geojson) and can help in mapping GeoJson to spatial fields.

I assume pretty much everyone needs to do this kind of processing, so I was a bit surprised that there was no info on how to do it.

Feel free to submit a PR ;)

[viniterra]

Hi, any update on this?

I am trying to do a bulk insert in MySQL using MyModel::insert($myModelInstances[]), where myModelInstances[] is an array of serialized instances new MyModel()->toArray(). The MyModel class in question contains a Point field.

As a workaround, I have tried, after converting the instances of MyModel to array, to manually set the field point with $serializedInstance['point'] = "ST_GeomFromText('{$point->toWkt()}')";. But I am getting the same error.

If I grab the SQL that outputs the error and run it in MySQL, it inserts without any problems:

SQL: insert into my_model (point, order, fw_id) values (ST_GeomFromText('POINT(-43.92693 -19.88423)'), 0, 1067))

Any ideias on how to adress it? Thanks.

[viniterra]

I have found a workaround using:

$serializedInstance['point'] = DB::raw("ST_GeomFromText('{$point->toWkt()}')");

[ahren-condos-ca]

I have the same issue. Here is what I'm planning on doing. Curious to hear if there is an easier or better way to do it. I have:

$model->center_point = preparePoint($attributes['center_point']);

which calls

/**
     * Makes sure input is a Point
     *
     * @param  $string POINT
     * @return \Grimzy\LaravelMysqlSpatial\Types\Point
     */
    function preparePoint($point)
    {
        if (empty($point)) return NULL;
        if ($point instanceof \Grimzy\LaravelMysqlSpatial\Types\Point) return $point;

        if (gettype($point) == "string")
        {
            if (strpos($point, 'POINT') === false) // Needs to be wrapped with POINT()
            {
                $point = "POINT(".$point.")";
            }
            return \Grimzy\LaravelMysqlSpatial\Types\Point::fromWKT($point);
        }
        else return NULL;
    }

[toondaey]

Hi guys, just experienced the same issue and after a long day of googling, I found that I forgot to use the use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait; in my model.

i.e.

use Illuminate\Database\Eloquent\Model;
use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;

class MyModel extends Model
{
    use SpatialTrait;

    protected $spatialFields = [ 'field', ];

    // ...
}

That should solve it.

[Synchro]

I have found a way to make the input work, but it doesn't make much sense. I would have written this as a wiki article, but it seems that the wiki is disabled in this repo. Here goes...

In a Laravel app, I have a Location model which has a location field in the DB that is of the point geospatial type, defined like this:

$table->point('location')->spatialIndex();

The problem is that there is no defined input format for such fields - every single example of using point types I've found always defines the point value within the script; it's never a user-submitted value! In the absence of such a format, the most straightforward thing to do is implement longitude and latitude attributes as virtual attributes; Virtual because they are never stored in the database. The idea here is that when I retrieve the location property from the DB, I split it into lat & long numeric fields, and when storing, I combine them back into a value that's acceptable to the point type. So I have mutators & accessors:

    public function getLongitudeAttribute(): float
    {
        return $this->location->getLng();
    }
    public function getLatitudeAttribute(): float
    {
        return $this->location->getLat();
    }
    public function setLongitudeAttribute(float $longitude): void
    {
        $this->location->setLng($longitude);
    }
    public function setLatitudeAttribute(float $latitude): void
    {
        $this->location->setLat($latitude);
    }

To tick a bunch of other boxes for this, lat & long appear in $fillable, but location does not:

    protected $fillable = [
...
        'longitude',
        'latitude',
...
    ];

For the geospatial stuff, it needs to be told which are spatial fields, and we never want the location property to be included directly in any input or output, so I hide it:

    protected $spatialFields = [
        'location',
    ];
    protected $hidden = [
        'location',
    ];

Lat & long are added dynamically to the model using $appends, and because Laravel can't tell what type they should be (as they don't exist in the DB), I fill in $cast entries:

    protected $appends = [
        'longitude',
        'latitude',
    ];
    protected $casts = [
        'longitude' => 'float',
        'latitude'  => 'float',
    ];

That all looks about right, but when I try to create a new record, it fails because the location field has no default value. This is kind-of correct, because point values can't have default values in MySQL, however, it should be setting it to a proper value via the mutators when the long & lat attributes are set. But I found a workaround.

Add the location attribute back into the $fillable property (why???!), then in the controller, manually patch in the conversion to a point value:

$request->request->add(['location' => new Point($request->input('latitude'), $request->input('longitude'))]);

and then I pass in the location attribute and not the longitude & latitude attributes to the creation call:

        $location = Location::create(
            $request->only(
                [
    ...
                    'location',
                ]
            )
        );

While this works, it's really not the "right" way to achieve it; I should not need to inject this conversion manually; it should be implicit when setting latitude & longitude attributes. It also means that I need to do the same thing any time the location needs to be updated, and that's a recipe for bugs.

Anyway, so while this works, it seems incredibly convoluted and inelegant - I'd love to see a better way!

[Daniyal-Javani]

I use a mutator like this:

   public function setLocationAttribute($value)
    {
        $locationArray = explode(',', $value);
        $this->attributes['location'] = new Point(
            $locationArray[0],
            $locationArray[1]
        );
    }

of course the json is like this:

{
    "location": "1.2,2.3"
}