Need insight on this error: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field

[mlab817]

So I spent half of the day trying to insert GeoJSON data into mysql.

foreach ($features as $feature) {
    $geometry = null;
    
    if ($feature->geometry->type == 'MultiPolygon') {
        $geometry = MultiPolygon::fromJson(json_encode($feature->geometry));
    } else {
        $geometry = Polygon::fromJson(json_encode($feature->geometry));
    }

    $code = str_replace('PH', '', $feature->properties->ADM1_PCODE);

    try {
        $region = Region::where('code', $code)->firstOrFail();

        $region->update([
           // if i do this, it will throw the 1416 error because of the reason shown below
          'geometry'  => $geometry
           // the code below works but looks dumb since i have to convert it to WKT and convert back
            'geometry' => MultiPolygon::fromWKT($geometry->toWKT()),
        ]);
    } catch (ModelNotFoundException $exception) {
        array_push($errors, $exception->getMessage() . ': ' . $code);
    }
}

I noticed that the first code generates the following query:

update `regions` set `geometry` = ST_GeomFromText(POLYGON((120.768965909 16.198033657,120.697753236 16.191001208)), `regions`.`updated_at` = 2021-09-20 06:37:30 where `id` = 15

Note: I truncated the above polygon since it's too long.

The error seems to come from the lack of '' inside the ST_GeomFromText() function. See here.

Running the said query directly in mysql throws an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16.198033657,120.697753236 which again comes from the fact that the expression polygon is not a string.

Is this a bug or am I supposed to do something to the geometry variable returned from the fromJson?

[RomanDeNu]

are you still struggling with this? If so, I've solved getting GeoJson into a mysql table and may be able to help.

[mlab817]

I've sort of solved it by converting using toWKT and then fromWKT. Don't know why I need to do that though.

[sagegwatkin]

Hi @mlab817,

Have you added the SpatialTrait to your model, and specified geometry as a spatialField in the protected array?

You should have something like this:

  <?php
  
  namespace App\Models;
  
  use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;
  use Illuminate\Database\Eloquent\Model;
  
  class Region extends Model
  {
      use SpatialTrait;
  
      protected $spatialFields = [
          'geometry',
      ];

      ...

  }

As far as I can tell, the package only can only translate the spatial data into an SQL query if the trait is used on the model, and the relevant field is set in the spatialFields array.

See the docs here https://github.com/grimzy/laravel-mysql-spatial#create-a-model

[PatrickSTM]

@c-delouvencourt Do you really added the "use SpatialTrait;" to your model? It isn't enough to add the array.