Skip to content

Numeric value out of range when inserting  #80

Closed
@pelmered

Description

@pelmered

I get this error message when I do a simple insert to a Point field:

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field (SQL: insert into `addresses` (`type`, `address_line_1`, `address_line_2`, `city`, `postal_code`, `country`, `user_uuid`, `uuid`, `location`, `updated_at`, `created_at`) values (billing, Drottninggatan 11, , Stockholm, 111 51, SE, ad120958-31b9-44da-92a8-e990c20eac42, 5bfa2e46-8fa5-4fbc-b818-0066adc858b8, 59.3315493 18.0637367, 2019-03-07 17:07:33, 2019-03-07 17:07:33))

The only solutions I can find is to add SpatialTrait and $spatialFields, but I already have them. This the relevant parts of the model:

<?php 

class Address extends Model
{
    use SpatialTrait;

    protected $spatialFields = ['location'];

    public static function boot()
    {
        parent::boot();

        self::creating(function (Address $address) {

            if (
                empty($address->attributes['location']) &&
                $address->isComplete()
            ) {
                $geocoded = app('geocoder')->geocodeAddress($address);
                $address->attributes['location'] = new Point($geocoded['latitude'], $geocoded['longitude']);
            }
        });
    }

    public function isComplete()
    {
        return isset($this->type, $this->address_line_1, $this->city, $this->postal_code, $this->country);
    }
}

I have double and triple checked the code, but I can't find anything wrong.
Any ideas?

Activity

pelmered

pelmered commented on Mar 7, 2019

@pelmered
Author

This is probably a duplicate of #70, right?

changed the title [-]Numeric value out of range when insering [/-] [+]Numeric value out of range when inserting [/+] on Mar 7, 2019
pelmered

pelmered commented on Mar 7, 2019

@pelmered
Author

I managed to solve this by looking at the #71 PR.

I added this code:

                $point = new Point($geocoded['latitude'], $geocoded['longitude']);
                $address->geometries['location'] = $point;
                $address->attributes['location'] = new SpatialExpression($point);

This shouldn't be necessary, but it solves the problem temporarily.

miclf

miclf commented on Mar 5, 2020

@miclf

@pelmered Thanks for the workaround! I’ve just stumbled upon the same issue while trying to assign a default value to a Point when creating a model…

static::creating(function (self $model) {
    if (is_null($model->location)) {
        // Throws MySQL error: Numeric value out of range blah blah blah...
        $model->location = Point($defaultLatitude, $defaultLongitude);
    }
});

The code you isolated fixes the issue 👌

static::creating(function (self $model) {
    if (is_null($model->location)) {
        $point = Point($defaultLatitude, $defaultLongitude);
        $model->geometries['location'] = $point;
        $model->attributes['location'] = new SpatialExpression($point);
    }
});
grimzy

grimzy commented on Mar 9, 2020

@grimzy
Owner

Thanks for looking at this! And sorry for responding so late.

I'll try to look at #71 soon and put all of this in a tested PR.

linked a pull request that will close this issueFixes geometry values on created event #71on Mar 9, 2020
M-Barari

M-Barari commented on Apr 19, 2020

@M-Barari

i'm having the same problem. is there any way instead of those two line above? as in the tutorial just new Point($lat, $lng) should be enough.
here is my code:
my Setting model:

use Grimzy\LaravelMysqlSpatial\Types\Point;
use Illuminate\Database\Eloquent\Model;
use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;
protected $guarded = [];
protected $spatialFields = ['location'];
public function setSetting($data) {
$lat = $data['lat'];
$lng = $data['lng'];
$this->title = $data['title'];
$this->address = $data['address'];
$this->location = new Point($lat, $lng)
$this->save();
}

and my controller:
public function set(){
$data = $this->validateRequest();
$setting = Setting::firstOrNew(['id' => 1]);
$setting->setSetting($data);
}

and when i add "use SpatialTrait" to my model, i get this error:
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'ST_GeomFromText'

miclf

miclf commented on Apr 19, 2020

@miclf

@M-Barari In your Setting model, try to replace this line

$this->location = new Point($lat, $lng);

by this

$point = Point($lat, $lng);
$this->geometries['location'] = $point;
$this->attributes['location'] = new \Grimzy\LaravelMysqlSpatial\EloquentSpatialExpression($point);

I can’t guarantee this will work (I cannot guess by just looking at your code) but if your issue is the same as the one described above, then it should help.

As for your PDOException: SQLSTATE[42000] error, I’m not sure if it’s related.

Welcome to GitHub, by the way. Here is a tip that you may find helpful : when writing code in comments, you can surround it by ` accents to display it with indentation and syntax highlighting. Like this:

```php
echo $hello;
```

You can replace the php part by the appropriate language if it’s different (html or css, for example). You can find more info in the GitHub help: https://guides.github.com/features/mastering-markdown/

snorpey

snorpey commented on May 7, 2020

@snorpey

@M-Barari I ran into the same error and was able to fix it by installing v2.0 of the library. I'm running MySQL 5.7, which according to the readme should be used with v2.0.

mostafakram

mostafakram commented on Jun 4, 2020

@mostafakram

Facing the same issue

when trying to save

{
    [
        {
            "lat": "30.590213",
            "lng": "31.519949"
        },
        {
            "lat": "30.589770",
            "lng": "31.521848"
        }
    ]
}

I get SQL error

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field (SQL: update .... route_path = ST_GeomFromText(LINESTRING(31.519949 30.590213,31.521848 30.58977)), ....

i have tried the proposed solution and my code is

$lineString = new LineString($points);
$this->geometries['route_path'] = $lineString;
$this->attributes['route_path'] = new SpatialExpression($lineString);

but still not fixed

package version 2.2.3
php 7.4.2
mysql 5.7.30

mostafakram

mostafakram commented on Jun 7, 2020

@mostafakram

Please ignore my comment

i believe the issue was because my migration was not correct

Thanks

2 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      Participants

      @snorpey@pelmered@grimzy@miclf@mostafakram

      Issue actions

        Numeric value out of range when inserting · Issue #80 · grimzy/laravel-mysql-spatial